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 DAF0C29029 for ; Sat, 9 Mar 2019 12:00:08 -0500 (EST) 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 YwJU6Tgp_Eod for ; Sat, 9 Mar 2019 12:00:08 -0500 (EST) Received: from smtp32.i.mail.ru (smtp32.i.mail.ru [94.100.177.92]) (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 50A6028F6F for ; Sat, 9 Mar 2019 12:00:08 -0500 (EST) From: Nikita Pettik Subject: [tarantool-patches] [PATCH 3/3] sql: make LIKE accept only TEXT arguments Date: Sat, 9 Mar 2019 20:00:02 +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 According to ANSI[1] specification, LIKE arguments must be of string-like type (VARCHAR, CHAR, TEXT etc). If one of arguments is NULL, then the result of LIKE function is NULL as well. This patch makes LIKE follow these rules. ANSI 2013 Part 2: Foundation; Chapter 8.5 Closes #3954 --- src/box/sql/func.c | 18 ++++++++++++++--- src/box/sql/vdbe.c | 6 +----- src/box/sql/vdbeInt.h | 6 ++++++ test/sql-tap/tkt1537.test.lua | 4 ++-- test/sql/types.result | 45 +++++++++++++++++++++++++++++++++++++++++++ test/sql/types.test.lua | 15 +++++++++++++++ 6 files changed, 84 insertions(+), 10 deletions(-) diff --git a/src/box/sql/func.c b/src/box/sql/func.c index 0c07f7e3c..855177dff 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -809,6 +809,9 @@ sql_strlike_ci(const char *zPattern, const char *zStr, unsigned int esc) * A LIKE B * * are implemented as like(B,A). + * + * Both arguments (A and B) must be of type TEXT. If one arguments + * is NULL then result is NULL as well. */ static void likeFunc(sql_context *context, int argc, sql_value **argv) @@ -817,10 +820,19 @@ likeFunc(sql_context *context, int argc, sql_value **argv) int nPat; sql *db = sql_context_db_handle(context); int is_like_ci = SQL_PTR_TO_INT(sql_user_data(context)); + int rhs_type = sql_value_type(argv[0]); + int lhs_type = sql_value_type(argv[1]); - if (sql_value_type(argv[0]) == SQL_BLOB - || sql_value_type(argv[1]) == SQL_BLOB) { - sql_result_int(context, 0); + if (lhs_type != SQL_TEXT || rhs_type != SQL_TEXT) { + if (lhs_type == SQL_NULL || rhs_type == SQL_NULL) + return; + char *inconsistent_type = rhs_type != SQL_TEXT ? + mem_type_to_str(argv[0]) : + mem_type_to_str(argv[1]); + diag_set(ClientError, ER_INCONSISTENT_TYPES, "TEXT", + inconsistent_type); + context->fErrorOrAux = 1; + context->isError = SQL_TARANTOOL_ERROR; return; } const char *zB = (const char *) sql_value_text(argv[0]); diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index d66fdd1f3..ed7bf8870 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -617,11 +617,7 @@ vdbe_add_new_autoinc_id(struct Vdbe *vdbe, int64_t id) return 0; } -/** - * Simple type to str convertor. It is used to simplify - * error reporting. - */ -static char * +char * mem_type_to_str(const struct Mem *p) { assert(p != NULL); diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h index 61b7d58b2..c84f22caf 100644 --- a/src/box/sql/vdbeInt.h +++ b/src/box/sql/vdbeInt.h @@ -262,6 +262,12 @@ enum { MEM_PURE_TYPE_MASK = 0x1f }; +/** + * Simple type to str convertor. It is used to simplify + * error reporting. + */ +char * +mem_type_to_str(const struct Mem *p); /* Return TRUE if Mem X contains dynamically allocated content - anything * that needs to be deallocated to avoid a leak. diff --git a/test/sql-tap/tkt1537.test.lua b/test/sql-tap/tkt1537.test.lua index 29f2bff7e..a5a45601c 100755 --- a/test/sql-tap/tkt1537.test.lua +++ b/test/sql-tap/tkt1537.test.lua @@ -185,7 +185,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt1537-3.1", [[ - SELECT * FROM t1 LEFT JOIN t2 ON b LIKE 'abc%' WHERE t1.id=1; + SELECT * FROM t1 LEFT JOIN t2 ON printf('%d', b) LIKE 'abc%' WHERE t1.id=1; ]], { -- 1, "", "", "", "" @@ -195,7 +195,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt1537-3.2", [[ - SELECT * FROM t2 LEFT JOIN t1 ON a1 LIKE 'abc%' WHERE t2.id=3; + SELECT * FROM t2 LEFT JOIN t1 ON printf('%d', a1) LIKE 'abc%' WHERE t2.id=3; ]], { -- 3, 1, "", "", "" diff --git a/test/sql/types.result b/test/sql/types.result index 11b045c05..4cee1b30d 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -169,3 +169,48 @@ box.sql.execute("VALUES (TYPEOF(randomblob(5) || zeroblob(5)));") --- - - ['blob'] ... +-- gh-3954: LIKE accepts only arguments of type TEXT and NULLs. +-- +box.sql.execute("CREATE TABLE t1 (s SCALAR PRIMARY KEY);") +--- +... +box.sql.execute("INSERT INTO t1 VALUES (randomblob(5));") +--- +... +box.sql.execute("SELECT * FROM t1 WHERE s LIKE 'blob';") +--- +- error: 'Inconsistent types: expected TEXT got BLOB' +... +box.sql.execute("SELECT * FROM t1 WHERE 'blob' LIKE s;") +--- +- error: 'Inconsistent types: expected TEXT got BLOB' +... +box.sql.execute("SELECT * FROM t1 WHERE 'blob' LIKE x'0000';") +--- +- error: 'Inconsistent types: expected TEXT got BLOB' +... +box.sql.execute("SELECT s LIKE NULL FROM t1;") +--- +- - [null] +... +box.sql.execute("DELETE FROM t1;") +--- +... +box.sql.execute("INSERT INTO t1 VALUES (1);") +--- +... +box.sql.execute("SELECT * FROM t1 WHERE s LIKE 'int';") +--- +- error: 'Inconsistent types: expected TEXT got INTEGER' +... +box.sql.execute("SELECT * FROM t1 WHERE 'int' LIKE 4;") +--- +- error: 'Inconsistent types: expected TEXT got INTEGER' +... +box.sql.execute("SELECT NULL LIKE s FROM t1;") +--- +- - [null] +... +box.space.T1:drop() +--- +... diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua index 799e9aed3..bb8bf7db7 100644 --- a/test/sql/types.test.lua +++ b/test/sql/types.test.lua @@ -55,3 +55,18 @@ box.sql.execute("SELECT randomblob(5) || 'x';") -- Result of BLOBs concatenation must be BLOB. -- box.sql.execute("VALUES (TYPEOF(randomblob(5) || zeroblob(5)));") + +-- gh-3954: LIKE accepts only arguments of type TEXT and NULLs. +-- +box.sql.execute("CREATE TABLE t1 (s SCALAR PRIMARY KEY);") +box.sql.execute("INSERT INTO t1 VALUES (randomblob(5));") +box.sql.execute("SELECT * FROM t1 WHERE s LIKE 'blob';") +box.sql.execute("SELECT * FROM t1 WHERE 'blob' LIKE s;") +box.sql.execute("SELECT * FROM t1 WHERE 'blob' LIKE x'0000';") +box.sql.execute("SELECT s LIKE NULL FROM t1;") +box.sql.execute("DELETE FROM t1;") +box.sql.execute("INSERT INTO t1 VALUES (1);") +box.sql.execute("SELECT * FROM t1 WHERE s LIKE 'int';") +box.sql.execute("SELECT * FROM t1 WHERE 'int' LIKE 4;") +box.sql.execute("SELECT NULL LIKE s FROM t1;") +box.space.T1:drop() -- 2.15.1