From: Nikita Pettik <korablev@tarantool.org> To: tarantool-patches@freelists.org Cc: v.shpilevoy@tarantool.org, Nikita Pettik <korablev@tarantool.org> Subject: [tarantool-patches] [PATCH 2/5] sql: fix resulting type calculation for CASE-WHEN stmt Date: Wed, 24 Jul 2019 14:42:44 +0300 [thread overview] Message-ID: <c1c777be1f5c0ae9f5177197e58fcd32e86154d7.1563967510.git.korablev@tarantool.org> (raw) In-Reply-To: <cover.1563967510.git.korablev@tarantool.org> In-Reply-To: <cover.1563967510.git.korablev@tarantool.org> 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
next prev parent reply other threads:[~2019-07-24 11:42 UTC|newest] Thread overview: 22+ messages / expand[flat|nested] mbox.gz Atom feed top 2019-07-24 11:42 [tarantool-patches] [PATCH 0/5] Introduce VARBINARY in SQL Nikita Pettik 2019-07-24 11:42 ` [tarantool-patches] [PATCH 1/5] sql: always erase numeric flag after stringifying Nikita Pettik 2019-07-24 11:42 ` Nikita Pettik [this message] 2019-07-25 22:12 ` [tarantool-patches] Re: [PATCH 2/5] sql: fix resulting type calculation for CASE-WHEN stmt Vladislav Shpilevoy [not found] ` <a061e845-eeb1-00d1-9141-3b9bb87768f5@tarantool.org> 2019-07-28 23:56 ` n.pettik 2019-07-24 11:42 ` [tarantool-patches] [PATCH 3/5] sql: use 'varbinary' as a name of type instead of 'blob' Nikita Pettik 2019-07-25 22:11 ` [tarantool-patches] " Vladislav Shpilevoy [not found] ` <2e655514-0fec-8baf-20a8-d49e5586b047@tarantool.org> 2019-07-28 23:56 ` n.pettik 2019-07-29 21:03 ` Vladislav Shpilevoy 2019-07-30 13:43 ` n.pettik 2019-07-24 11:42 ` [tarantool-patches] [PATCH 4/5] sql: make built-ins raise errors for varbin args Nikita Pettik 2019-07-25 22:11 ` [tarantool-patches] " Vladislav Shpilevoy [not found] ` <05d15035-2552-1f05-b7ce-facfbbc3a520@tarantool.org> 2019-07-28 23:59 ` n.pettik 2019-07-24 11:42 ` [tarantool-patches] [PATCH 5/5] sql: introduce VARBINARY column type Nikita Pettik 2019-07-25 22:12 ` [tarantool-patches] " Vladislav Shpilevoy [not found] ` <49a188eb-dafe-44e7-a0fd-e9244b68e721@tarantool.org> 2019-07-29 0:03 ` n.pettik 2019-07-29 20:55 ` Vladislav Shpilevoy 2019-07-30 13:44 ` n.pettik 2019-07-30 19:41 ` Vladislav Shpilevoy 2019-07-30 19:52 ` Vladislav Shpilevoy 2019-07-31 14:51 ` n.pettik 2019-08-01 8:42 ` [tarantool-patches] Re: [PATCH 0/5] Introduce VARBINARY in SQL Kirill Yukhin
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox Avoid top-posting and favor interleaved quoting: https://en.wikipedia.org/wiki/Posting_style#Interleaved_style * Reply using the --to, --cc, and --in-reply-to switches of git-send-email(1): git send-email \ --in-reply-to=c1c777be1f5c0ae9f5177197e58fcd32e86154d7.1563967510.git.korablev@tarantool.org \ --to=korablev@tarantool.org \ --cc=tarantool-patches@freelists.org \ --cc=v.shpilevoy@tarantool.org \ --subject='Re: [tarantool-patches] [PATCH 2/5] sql: fix resulting type calculation for CASE-WHEN stmt' \ /path/to/YOUR_REPLY https://kernel.org/pub/software/scm/git/docs/git-send-email.html * If your mail client supports setting the In-Reply-To header via mailto: links, try the mailto: link
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox