[Tarantool-patches] [PATCH v1 7/7] sql: fix comparison with SCALAR value

[Mergen Imeev via Tarantool-patches <tarantool-patches@dev.tarantool.org>]

After this patch, SCALAR values will be able to be compared with values
of any other scalar type. The comparison will be done according to the
SCALAR rules, which means boolean values < numeric values < string
values < binary values < uuid values.

Closes #6221

@TarantoolBot document
Title: SCALAR and NUMBER values in SQL

SCALAR values can now be compared with values of any other scalar type,
but cannot be implicitly cast to any other scalar type. This means that
SCALAR values cannot participate in arithmetic, bitwise operations,
concatenation, or functions that, by definition, do not accept SCALAR
values.

NUMBER values now also cannot be implicitly cast to any other numeric
type, which means that NUMBER values cannot participate in arithmetic
and bitwise operations, or in functions that, by definition, do not
accept NUMBER values.
---
 ...21-re-introduce-scalar-and-number-types.md |  8 +++
 src/box/sql/mem.c                             |  4 ++
 test/sql-tap/cast.test.lua                    |  4 +-
 test/sql-tap/metatypes.test.lua               | 51 ++++++++++++++++++-
 test/sql/boolean.result                       | 14 +++--
 5 files changed, 74 insertions(+), 7 deletions(-)
 create mode 100644 changelogs/unreleased/gh-6221-re-introduce-scalar-and-number-types.md

diff --git a/changelogs/unreleased/gh-6221-re-introduce-scalar-and-number-types.md b/changelogs/unreleased/gh-6221-re-introduce-scalar-and-number-types.md
new file mode 100644
index 000000000..842e8a4e2
--- /dev/null
+++ b/changelogs/unreleased/gh-6221-re-introduce-scalar-and-number-types.md
@@ -0,0 +1,8 @@
+## feature/sql
+
+* The SCALAR and NUMBER types have been reworked in SQL. Now SCALAR values
+cannot be implicitly cast to any other scalar type, and NUMBER values cannot be
+implicitly cast to any other numeric type. This means that arithmetic and
+bitwise operations and concatenation are no longer allowed for SCALAR and NUMBER
+values. In addition, any SCALAR value can now be compared with values of any
+other scalar type using the SCALAR rules (gh-6221).
diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index 732d1b012..066940fac 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -2062,6 +2062,10 @@ mem_cmp(const struct Mem *a, const struct Mem *b, int *result,
 			*result = 1;
 		return 0;
 	}
+	if (((a->flags | b->flags) & MEM_Scalar) != 0) {
+		*result = mem_cmp_scalar(a, b, coll);
+		return 0;
+	}
 	if (class_a != class_b) {
 		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(b),
 			 mem_type_class_to_str(a));
diff --git a/test/sql-tap/cast.test.lua b/test/sql-tap/cast.test.lua
index e0295fd3b..68d2ae585 100755
--- a/test/sql-tap/cast.test.lua
+++ b/test/sql-tap/cast.test.lua
@@ -1157,12 +1157,12 @@ test:do_execsql_test(
     })
 
 -- Make sure that there is no unnecessary implicit casts in IN operator.
-test:do_catchsql_test(
+test:do_execsql_test(
     "cast-12",
     [[
         SELECT 1 IN (SELECT '1');
     ]], {
-        1, "Type mismatch: can not convert integer(1) to string"
+        false
     })
 
 test:finish_test()
diff --git a/test/sql-tap/metatypes.test.lua b/test/sql-tap/metatypes.test.lua
index b767e3f31..349c670ba 100755
--- a/test/sql-tap/metatypes.test.lua
+++ b/test/sql-tap/metatypes.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 local test = require("sqltester")
-test:plan(13)
+test:plan(19)
 
 -- Check that SCALAR and NUMBER meta-types works as intended.
 box.execute([[CREATE TABLE t (i INT PRIMARY KEY, s SCALAR, n NUMBER);]])
@@ -133,6 +133,55 @@ test:do_catchsql_test(
         1, "Inconsistent types: expected string or varbinary got scalar('asd')"
     })
 
+-- Check that SCALAR values can be compared to values of any other scalar type.
+test:do_execsql_test(
+    "metatypes-6.1",
+    [[
+        SELECT s > false FROM t;
+    ]], {
+        true, true, true, true, true, true
+    })
+
+test:do_execsql_test(
+    "metatypes-6.2",
+    [[
+        SELECT s = 1 FROM t;
+    ]], {
+        true, false, false, false, false, false
+    })
+
+test:do_execsql_test(
+    "metatypes-6.3",
+    [[
+        SELECT s != 1.5 FROM t;
+    ]], {
+        true, true, true, true, true, true
+    })
+
+test:do_execsql_test(
+    "metatypes-6.4",
+    [[
+        SELECT s <= 'abc' FROM t;
+    ]], {
+        true, true, true, true, false, false
+    })
+
+test:do_execsql_test(
+    "metatypes-6.5",
+    [[
+        SELECT s < x'10' FROM t;
+    ]], {
+        true, true, true, true, false, false
+    })
+
+test:do_execsql_test(
+    "metatypes-6.6",
+    [[
+        SELECT s > CAST('11111111-1111-1111-1111-111111111110' AS UUID) FROM t;
+    ]], {
+        false, false, false, false, false, true
+    })
+
 box.execute([[DROP TABLE t;]])
 
 test:finish_test()
diff --git a/test/sql/boolean.result b/test/sql/boolean.result
index a8400ee49..a9ce37e11 100644
--- a/test/sql/boolean.result
+++ b/test/sql/boolean.result
@@ -137,13 +137,19 @@ INSERT INTO ts(s) VALUES ('abc'), (12.5);
  | ...
 SELECT s FROM ts WHERE s = true;
  | ---
- | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to string'
+ | - metadata:
+ |   - name: S
+ |     type: scalar
+ |   rows:
+ |   - [true]
  | ...
 SELECT s FROM ts WHERE s < true;
  | ---
- | - null
- | - 'Type mismatch: can not convert boolean(TRUE) to string'
+ | - metadata:
+ |   - name: S
+ |     type: scalar
+ |   rows:
+ |   - [false]
  | ...
 SELECT s FROM ts WHERE s IN (true, 1, 'abcd');
  | ---
-- 
2.25.1