[Tarantool-patches] [PATCH v1 07/10] sql: fix result type of min() and max() functions
imeevma at tarantool.org
imeevma at tarantool.org
Fri Aug 13 06:17:22 MSK 2021
Prior to this, the patch functions MIN(), MAX(), LEAST() and GREATEST()
showed SCALAR as result types in metadata. However, in reality, the type
of the result could be any scalar type. After this patch, the type of
the result will always be the same as the type in the metadata. Also,
for MIN() and MAX() functions, the type of the result will be the same
as the type of the argument. For the LEAST() and GREATEST() functions,
the result type will be the same as the type of the arguments if all
arguments are of the same type, or it will be NUMBER if all arguments
are of numeric types, or it will be SCALAR.
Part of #6105
---
src/box/sql/func.c | 59 ++++++++++++++++++++++--
test/sql-tap/built-in-functions.test.lua | 49 +++++++++++++++++++-
test/sql-tap/minmax2.test.lua | 3 +-
test/sql-tap/select3.test.lua | 2 +-
test/sql/iproto.result | 4 +-
test/sql/max-on-index.result | 6 +--
test/sql/prepared.result | 4 +-
test/sql/types.result | 18 ++++----
8 files changed, 122 insertions(+), 23 deletions(-)
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index b22edda20..7ecbb6203 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -2023,13 +2023,37 @@ static struct sql_func_definition definitions[] = {
{"COUNT", 0, {}, FIELD_TYPE_INTEGER, countStep, countFinalize},
{"COUNT", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_INTEGER, countStep,
countFinalize},
- {"GREATEST", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR, minmaxFunc, NULL},
+
+ {"GREATEST", -1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, minmaxFunc,
+ NULL},
+ {"GREATEST", -1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, minmaxFunc,
+ NULL},
+ {"GREATEST", -1, {FIELD_TYPE_NUMBER}, FIELD_TYPE_NUMBER, minmaxFunc,
+ NULL},
+ {"GREATEST", -1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_VARBINARY,
+ minmaxFunc, NULL},
+ {"GREATEST", -1, {FIELD_TYPE_UUID}, FIELD_TYPE_UUID, minmaxFunc, NULL},
+ {"GREATEST", -1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, minmaxFunc,
+ NULL},
+ {"GREATEST", -1, {FIELD_TYPE_SCALAR}, FIELD_TYPE_SCALAR, minmaxFunc,
+ NULL},
+
{"GROUP_CONCAT", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY},
FIELD_TYPE_STRING, groupConcatStep, groupConcatFinalize},
{"HEX", 1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_STRING, hexFunc, NULL},
{"IFNULL", 2, {FIELD_TYPE_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR,
sql_builtin_stub, NULL},
- {"LEAST", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR, minmaxFunc, NULL},
+
+ {"LEAST", -1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, minmaxFunc,
+ NULL},
+ {"LEAST", -1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, minmaxFunc, NULL},
+ {"LEAST", -1, {FIELD_TYPE_NUMBER}, FIELD_TYPE_NUMBER, minmaxFunc, NULL},
+ {"LEAST", -1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_VARBINARY, minmaxFunc,
+ NULL},
+ {"LEAST", -1, {FIELD_TYPE_UUID}, FIELD_TYPE_UUID, minmaxFunc, NULL},
+ {"LEAST", -1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, minmaxFunc, NULL},
+ {"LEAST", -1, {FIELD_TYPE_SCALAR}, FIELD_TYPE_SCALAR, minmaxFunc, NULL},
+
{"LENGTH", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_INTEGER, lengthFunc,
NULL},
{"LENGTH", 1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_INTEGER, lengthFunc,
@@ -2044,10 +2068,37 @@ static struct sql_func_definition definitions[] = {
NULL},
{"LOWER", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, LowerICUFunc,
NULL},
- {"MAX", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR, minmaxStep,
+
+ {"MAX", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, minmaxStep,
+ minMaxFinalize},
+ {"MAX", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, minmaxStep,
minMaxFinalize},
- {"MIN", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR, minmaxStep,
+ {"MAX", 1, {FIELD_TYPE_NUMBER}, FIELD_TYPE_NUMBER, minmaxStep,
minMaxFinalize},
+ {"MAX", 1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_VARBINARY, minmaxStep,
+ minMaxFinalize},
+ {"MAX", 1, {FIELD_TYPE_UUID}, FIELD_TYPE_UUID, minmaxStep,
+ minMaxFinalize},
+ {"MAX", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, minmaxStep,
+ minMaxFinalize},
+ {"MAX", 1, {FIELD_TYPE_SCALAR}, FIELD_TYPE_SCALAR, minmaxStep,
+ minMaxFinalize},
+
+ {"MIN", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, minmaxStep,
+ minMaxFinalize},
+ {"MIN", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, minmaxStep,
+ minMaxFinalize},
+ {"MIN", 1, {FIELD_TYPE_NUMBER}, FIELD_TYPE_NUMBER, minmaxStep,
+ minMaxFinalize},
+ {"MIN", 1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_VARBINARY, minmaxStep,
+ minMaxFinalize},
+ {"MIN", 1, {FIELD_TYPE_UUID}, FIELD_TYPE_UUID, minmaxStep,
+ minMaxFinalize},
+ {"MIN", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, minmaxStep,
+ minMaxFinalize},
+ {"MIN", 1, {FIELD_TYPE_SCALAR}, FIELD_TYPE_SCALAR, minmaxStep,
+ minMaxFinalize},
+
{"NULLIF", 2, {FIELD_TYPE_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR,
nullifFunc, NULL},
{"POSITION", 2, {FIELD_TYPE_STRING, FIELD_TYPE_STRING},
diff --git a/test/sql-tap/built-in-functions.test.lua b/test/sql-tap/built-in-functions.test.lua
index a495a4295..353030278 100755
--- a/test/sql-tap/built-in-functions.test.lua
+++ b/test/sql-tap/built-in-functions.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
local test = require("sqltester")
-test:plan(42)
+test:plan(46)
--
-- Make sure that number of arguments check is checked properly for SQL built-in
@@ -438,4 +438,51 @@ test:do_test(
"Type mismatch: can not convert string('1') to integer"
})
+--
+-- Make sure that the type of result of MAX() and MIN() is the same as the type
+-- of the argument.
+--
+test:do_execsql_test(
+ "builtins-3.1",
+ [[
+ SELECT TYPEOF(1), TYPEOF(MAX(1)), TYPEOF(MIN(1));
+ ]],
+ {
+ 'integer', 'integer', 'integer'
+ }
+)
+
+test:do_test(
+ "builtins-3.2",
+ function()
+ return box.execute([[SELECT 1, MAX(1), MIN(1);]]).metadata
+ end, {
+ {name = "COLUMN_1", type = "integer"},
+ {name = "COLUMN_2", type = "integer"},
+ {name = "COLUMN_3", type = "integer"}
+ })
+
+--
+-- Make sure that the type of result of GREATEST() and LEAST() depends on type
+-- of arguments.
+--
+test:do_execsql_test(
+ "builtins-3.3",
+ [[
+ SELECT TYPEOF(GREATEST('1', 1)), TYPEOF(LEAST('1', 1));
+ ]],
+ {
+ 'scalar', 'scalar'
+ }
+)
+
+test:do_test(
+ "builtins-3.4",
+ function()
+ return box.execute([[SELECT GREATEST('1', 1), LEAST('1', 1);]]).metadata
+ end, {
+ {name = "COLUMN_1", type = "scalar"},
+ {name = "COLUMN_2", type = "scalar"},
+ })
+
test:finish_test()
diff --git a/test/sql-tap/minmax2.test.lua b/test/sql-tap/minmax2.test.lua
index 42dbafea8..0b9f5a63c 100755
--- a/test/sql-tap/minmax2.test.lua
+++ b/test/sql-tap/minmax2.test.lua
@@ -278,7 +278,8 @@ test:do_execsql_test(
test:do_execsql_test(
"minmax2-4.1",
[[
- SELECT coalesce(min(x),-1), coalesce(max(x),-1) FROM
+ SELECT coalesce(min(CAST(x AS INTEGER)),-1),
+ coalesce(max(CAST(x AS INTEGER)),-1) FROM
(SELECT x, y FROM t1 UNION SELECT NULL as "x", NULL as "y")
]], {
-- <minmax2-4.1>
diff --git a/test/sql-tap/select3.test.lua b/test/sql-tap/select3.test.lua
index ffba58ec2..9880c1f69 100755
--- a/test/sql-tap/select3.test.lua
+++ b/test/sql-tap/select3.test.lua
@@ -271,7 +271,7 @@ test:do_execsql_test("select3-5.1", [[
test:do_execsql_test("select3-5.2", [[
SELECT log, count(*), avg(n), max(n+log*2) FROM t1
GROUP BY log
- ORDER BY max(n+log*2)+0, GREATEST(log,avg(n))+0
+ ORDER BY max(n+log*2)+0, CAST(GREATEST(log,avg(n)) AS DOUBLE)+0
]], {
-- <select3-5.2>
0, 1, 1.0, 1, 1, 1, 2.0, 4, 2, 2, 3.5, 8, 3, 4, 6.5, 14, 4, 8, 12.5, 24, 5, 15, 24.0, 41
diff --git a/test/sql/iproto.result b/test/sql/iproto.result
index 4e9e12ad7..6212aa0c0 100644
--- a/test/sql/iproto.result
+++ b/test/sql/iproto.result
@@ -761,7 +761,7 @@ cn:execute("SELECT LEAST(1, 2, 3);")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: integer
rows:
- [1]
...
@@ -769,7 +769,7 @@ cn:execute("SELECT GREATEST(1, 2, 3);")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: integer
rows:
- [3]
...
diff --git a/test/sql/max-on-index.result b/test/sql/max-on-index.result
index a90f30993..fcb5923dd 100644
--- a/test/sql/max-on-index.result
+++ b/test/sql/max-on-index.result
@@ -49,7 +49,7 @@ box.execute("SELECT MAX(f1) FROM test1")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: integer
rows:
- [4]
...
@@ -57,7 +57,7 @@ box.execute("SELECT MAX(f2) FROM test1")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: integer
rows:
- [3]
...
@@ -65,7 +65,7 @@ box.execute("SELECT MAX(f1) FROM test2")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: integer
rows:
- [1]
...
diff --git a/test/sql/prepared.result b/test/sql/prepared.result
index d38b47c61..c4b09e514 100644
--- a/test/sql/prepared.result
+++ b/test/sql/prepared.result
@@ -430,7 +430,7 @@ execute(s.stmt_id)
| - name: COLUMN_2
| type: integer
| - name: COLUMN_3
- | type: scalar
+ | type: string
| - name: COLUMN_4
| type: integer
| rows:
@@ -444,7 +444,7 @@ execute(s.stmt_id)
| - name: COLUMN_2
| type: integer
| - name: COLUMN_3
- | type: scalar
+ | type: string
| - name: COLUMN_4
| type: integer
| rows:
diff --git a/test/sql/types.result b/test/sql/types.result
index 83cec6592..5f0ce3543 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -785,7 +785,7 @@ box.execute("SELECT min(i) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: integer
rows:
- [-1]
...
@@ -793,7 +793,7 @@ box.execute("SELECT max(i) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: integer
rows:
- [18446744073709551613]
...
@@ -855,7 +855,7 @@ box.execute("SELECT LEAST(-1, i) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: integer
rows:
- [-1]
...
@@ -1255,7 +1255,7 @@ box.execute("SELECT min(v) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: varbinary
rows:
- ['abc']
...
@@ -1263,7 +1263,7 @@ box.execute("SELECT max(v) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: varbinary
rows:
- ['abc']
...
@@ -1318,7 +1318,7 @@ box.execute("SELECT LEAST(v, x'') FROM t;")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: varbinary
rows:
- ['']
...
@@ -1819,7 +1819,7 @@ box.execute("SELECT min(d) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: double
rows:
- [-2]
...
@@ -1827,7 +1827,7 @@ box.execute("SELECT max(d) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: double
rows:
- [18000000000000000000]
...
@@ -1894,7 +1894,7 @@ box.execute("SELECT LEAST(d, 0) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: number
rows:
- [0]
- [-2]
--
2.25.1
More information about the Tarantool-patches
mailing list