From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B11C1226A4 for ; Wed, 24 Jul 2019 07:42:51 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id WjwgaRtsbP2F for ; Wed, 24 Jul 2019 07:42:51 -0400 (EDT) Received: from smtpng1.m.smailru.net (smtpng1.m.smailru.net [94.100.181.251]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 5434221F29 for ; Wed, 24 Jul 2019 07:42:51 -0400 (EDT) From: Nikita Pettik Subject: [tarantool-patches] [PATCH 2/5] sql: fix resulting type calculation for CASE-WHEN stmt Date: Wed, 24 Jul 2019 14:42:44 +0300 Message-Id: In-Reply-To: References: In-Reply-To: References: Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-Subscribe: List-Owner: List-post: List-Archive: To: tarantool-patches@freelists.org Cc: v.shpilevoy@tarantool.org, 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