From: Mergen Imeev via Tarantool-patches <tarantool-patches@dev.tarantool.org>
To: vdavydov@tarantool.org
Cc: tarantool-patches@dev.tarantool.org
Subject: [Tarantool-patches] [PATCH v1 07/10] sql: fix result type of min() and max() functions
Date: Fri, 13 Aug 2021 06:17:22 +0300 [thread overview]
Message-ID: <db46e1d7027c9224826de3d672894fac365f3618.1628824421.git.imeevma@gmail.com> (raw)
In-Reply-To: <cover.1628824421.git.imeevma@gmail.com>
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
next prev parent reply other threads:[~2021-08-13 3:20 UTC|newest]
Thread overview: 13+ messages / expand[flat|nested] mbox.gz Atom feed top
2021-08-13 3:17 [Tarantool-patches] [PATCH v1 00/10] Check types of SQL built-in functions arguments Mergen Imeev via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 01/10] sql: modify signature of TRIM() Mergen Imeev via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 02/10] sql: rework SQL built-in functions hash table Mergen Imeev via Tarantool-patches
2021-08-16 13:53 ` Vladimir Davydov via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 03/10] sql: check number of arguments during parsing Mergen Imeev via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 04/10] sql: static type check for SQL built-in functions Mergen Imeev via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 05/10] sql: runtime " Mergen Imeev via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 06/10] sql: enable types checking for some functions Mergen Imeev via Tarantool-patches
2021-08-13 3:17 ` Mergen Imeev via Tarantool-patches [this message]
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 08/10] sql: check argument types of sum(), avg(), total() Mergen Imeev via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 09/10] sql: fix quote() function Mergen Imeev via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 10/10] sql: arguments check for string value functions Mergen Imeev via Tarantool-patches
2021-08-19 11:49 ` [Tarantool-patches] [PATCH v1 00/10] Check types of SQL built-in functions arguments Vladimir Davydov via Tarantool-patches
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=db46e1d7027c9224826de3d672894fac365f3618.1628824421.git.imeevma@gmail.com \
--to=tarantool-patches@dev.tarantool.org \
--cc=imeevma@tarantool.org \
--cc=vdavydov@tarantool.org \
--subject='Re: [Tarantool-patches] [PATCH v1 07/10] sql: fix result type of min() and max() functions' \
/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