[tarantool-patches] [PATCH 2/5] sql: fix resulting type calculation for CASE-WHEN stmt

[Nikita Pettik]

Before this patch, resulting type for CASE-WHEN statement was assumed to
be the same as type of argument of first THEN clause. Obviously, it is
wrong and could lead to sad consequence (e.g. creating ephemeral table
with inconsistent format). To deal with this, we check all THEN
arguments: if all of them have the same type, then such type will be
resulting of the whole statement; if at least two types are different,
we can't determine actual resulting type during compilation stage and
assign SCALAR as a most general type in SQL now.

Need for #4206
---
 src/box/sql/expr.c      | 27 +++++++++++++++++++++------
 test/sql/types.result   | 35 +++++++++++++++++++++++++++++++++++
 test/sql/types.test.lua |  8 ++++++++
 3 files changed, 64 insertions(+), 6 deletions(-)

diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
index d7104d8a0..97f5bd180 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -85,18 +85,33 @@ sql_expr_type(struct Expr *pExpr)
 		return sql_type_result(rhs_type, lhs_type);
 	case TK_CONCAT:
 		return FIELD_TYPE_STRING;
-	case TK_CASE:
-		assert(pExpr->x.pList->nExpr >= 2);
+	case TK_CASE: {
+		struct ExprList *cs = pExpr->x.pList;
+		assert(cs->nExpr >= 2);
 		/*
 		 * CASE expression comes at least with one
 		 * WHEN and one THEN clauses. So, first
 		 * expression always represents WHEN
 		 * argument, and the second one - THEN.
-		 *
-		 * TODO: We must ensure that all THEN clauses
-		 * have arguments of the same type.
+		 * In case at least one type of THEN argument
+		 * is different from others then we can't
+		 * determine type of returning value at compiling
+		 * stage and set SCALAR (i.e. most general) type.
 		 */
-		return sql_expr_type(pExpr->x.pList->a[1].pExpr);
+		enum field_type ref_type = sql_expr_type(cs->a[1].pExpr);
+		for (int i = 1; i <= cs->nExpr / 2; i = i * 2) {
+			if (ref_type != sql_expr_type(cs->a[i + 1].pExpr))
+				return FIELD_TYPE_SCALAR;
+		}
+		/*
+		 * ELSE clause is optional but we should check
+		 * its type as well.
+		 */
+		if (cs->nExpr % 2 == 1 &&
+		    ref_type != sql_expr_type(cs->a[cs->nExpr - 1].pExpr))
+			return FIELD_TYPE_SCALAR;
+		return ref_type;
+	}
 	case TK_LT:
 	case TK_GT:
 	case TK_EQ:
diff --git a/test/sql/types.result b/test/sql/types.result
index cdfb1e783..1db4b980d 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -966,3 +966,38 @@ box.execute('SELECT ?', {true})
   rows:
   - [true]
 ...
+-- Make sure that CASE-THEN statement return type is SCALAR in
+-- case two THEN clauses feature different types.
+--
+box.execute("SELECT CASE 1 WHEN 1 THEN x'0000000000' WHEN 2 THEN 'str' END")
+---
+- metadata:
+  - name: CASE 1 WHEN 1 THEN x'0000000000' WHEN 2 THEN 'str' END
+    type: scalar
+  rows:
+  - ["\0\0\0\0\0"]
+...
+box.execute("SELECT CASE 1 WHEN 1 THEN 666 WHEN 2 THEN 123 END")
+---
+- metadata:
+  - name: CASE 1 WHEN 1 THEN 666 WHEN 2 THEN 123 END
+    type: integer
+  rows:
+  - [666]
+...
+box.execute("SELECT CASE 1 WHEN 1 THEN 666 WHEN 2 THEN 123 ELSE 321 END")
+---
+- metadata:
+  - name: CASE 1 WHEN 1 THEN 666 WHEN 2 THEN 123 ELSE 321 END
+    type: integer
+  rows:
+  - [666]
+...
+box.execute("SELECT CASE 1 WHEN 1 THEN 666 WHEN 2 THEN 123 ELSE 'asd' END")
+---
+- metadata:
+  - name: CASE 1 WHEN 1 THEN 666 WHEN 2 THEN 123 ELSE 'asd' END
+    type: scalar
+  rows:
+  - [666]
+...
diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua
index ae1a0ab72..b66a3e068 100644
--- a/test/sql/types.test.lua
+++ b/test/sql/types.test.lua
@@ -234,3 +234,11 @@ box.execute('SELECT \'9223372036854\' + 1;')
 
 -- Fix BOOLEAN bindings.
 box.execute('SELECT ?', {true})
+
+-- Make sure that CASE-THEN statement return type is SCALAR in
+-- case two THEN clauses feature different types.
+--
+box.execute("SELECT CASE 1 WHEN 1 THEN x'0000000000' WHEN 2 THEN 'str' END")
+box.execute("SELECT CASE 1 WHEN 1 THEN 666 WHEN 2 THEN 123 END")
+box.execute("SELECT CASE 1 WHEN 1 THEN 666 WHEN 2 THEN 123 ELSE 321 END")
+box.execute("SELECT CASE 1 WHEN 1 THEN 666 WHEN 2 THEN 123 ELSE 'asd' END")
-- 
2.15.1