[tarantool-patches] [PATCH 3/3] sql: make LIKE accept only TEXT arguments

[Nikita Pettik <korablev@tarantool.org>]

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 <like predicate>

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;
     ]], {
         -- <tkt1537-3.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;
     ]], {
         -- <tkt1537-3.2>
         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