[tarantool-patches] [PATCH v2 2/8] sql: GREATEST, LEAST instead of MIN/MAX overload
Kirill Shcherbatov
kshcherbatov at tarantool.org
Thu Aug 8 17:50:46 MSK 2019
The MIN/MAX functions are typically used only as aggregate
functions in other RDBMS(MSSQL, Postgress, MySQL, Oracle) while
Tarantool's SQLite legacy code use them also in meaning
GREATEST/LEAST scalar function. This must be fixed.
Moreover it is an important stem to get rid of function's name
overloading required for replace FuncDef cache with Tarantool's
function cache.
Closes #4405
Needed for #2200, #4113, #2233
---
src/box/sql/func.c | 6 +-
src/box/bootstrap.snap | Bin 5907 -> 5944 bytes
src/box/lua/upgrade.lua | 20 +++++
test-run | 2 +-
test/box-py/bootstrap.result | 4 +-
test/box/access.result | 2 +-
test/box/access.test.lua | 2 +-
test/box/access_bin.result | 2 +-
test/box/access_bin.test.lua | 2 +-
test/box/access_misc.result | 134 +++++++++++++++++----------------
test/box/access_sysview.result | 8 +-
test/box/function1.result | 8 +-
test/box/function1.test.lua | 2 +-
test/sql-tap/coalesce.test.lua | 2 +-
test/sql-tap/func3.test.lua | 12 +--
test/sql-tap/func5.test.lua | 34 ++++-----
test/sql-tap/in1.test.lua | 6 +-
test/sql-tap/insert1.test.lua | 2 +-
test/sql-tap/misc1.test.lua | 2 +-
test/sql-tap/select1.test.lua | 22 +++---
test/sql-tap/select2.test.lua | 6 +-
test/sql-tap/select3.test.lua | 2 +-
test/sql-tap/with1.test.lua | 2 +-
test/sql/iproto.result | 8 +-
test/sql/iproto.test.lua | 4 +-
test/sql/types.result | 12 +--
test/sql/types.test.lua | 6 +-
test/wal_off/func_max.result | 8 +-
28 files changed, 172 insertions(+), 148 deletions(-)
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index faf86bd4b..e00764c3f 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -1843,12 +1843,10 @@ sqlRegisterBuiltinFunctions(void)
FUNCTION_COLL(trim, 1, 3, 0, trim_func_one_arg),
FUNCTION_COLL(trim, 2, 3, 0, trim_func_two_args),
FUNCTION_COLL(trim, 3, 3, 0, trim_func_three_args),
- FUNCTION(min, -1, 0, 1, minmaxFunc, FIELD_TYPE_SCALAR),
- FUNCTION(min, 0, 0, 1, 0, FIELD_TYPE_SCALAR),
+ FUNCTION(least, -1, 0, 1, minmaxFunc, FIELD_TYPE_SCALAR),
AGGREGATE2(min, 1, 0, 1, minmaxStep, minMaxFinalize,
SQL_FUNC_MINMAX, FIELD_TYPE_SCALAR),
- FUNCTION(max, -1, 1, 1, minmaxFunc, FIELD_TYPE_SCALAR),
- FUNCTION(max, 0, 1, 1, 0, FIELD_TYPE_SCALAR),
+ FUNCTION(greatest, -1, 1, 1, minmaxFunc, FIELD_TYPE_SCALAR),
AGGREGATE2(max, 1, 1, 1, minmaxStep, minMaxFinalize,
SQL_FUNC_MINMAX, FIELD_TYPE_SCALAR),
FUNCTION2(typeof, 1, 0, 0, typeofFunc, SQL_FUNC_TYPEOF,
diff --git a/src/box/bootstrap.snap b/src/box/bootstrap.snap
diff --git a/src/box/lua/upgrade.lua b/src/box/lua/upgrade.lua
index 046eb3ee4..73b39332e 100644
--- a/src/box/lua/upgrade.lua
+++ b/src/box/lua/upgrade.lua
@@ -910,6 +910,25 @@ local function upgrade_to_2_2_1()
create_func_index()
end
+--------------------------------------------------------------------------------
+-- Tarantool 2.2.2
+--------------------------------------------------------------------------------
+
+local function upgrade_to_2_2_2()
+ log.info("Create GREATEST and LEAST SQL Builtins")
+ local new_builtins = {"GREATEST", "LEAST"}
+ local _func = box.space[box.schema.FUNC_ID]
+ local _priv = box.space[box.schema.PRIV_ID]
+ local datetime = os.date("%Y-%m-%d %H:%M:%S")
+ for _, v in pairs(new_builtins) do
+ local t = _func:auto_increment({ADMIN, v, 1, 'SQL_BUILTIN', '',
+ 'function', {}, 'any', 'none', 'none',
+ false, false, true, {}, setmap({}), '',
+ datetime, datetime})
+ _priv:replace{ADMIN, PUBLIC, 'function', t.id, box.priv.X}
+ end
+end
+
--------------------------------------------------------------------------------
local function get_version()
@@ -944,6 +963,7 @@ local function upgrade(options)
{version = mkversion(2, 1, 2), func = upgrade_to_2_1_2, auto = true},
{version = mkversion(2, 1, 3), func = upgrade_to_2_1_3, auto = true},
{version = mkversion(2, 2, 1), func = upgrade_to_2_2_1, auto = true},
+ {version = mkversion(2, 2, 2), func = upgrade_to_2_2_2, auto = true},
}
for _, handler in ipairs(handlers) do
diff --git a/test-run b/test-run
index 947c65cda..f8311bc09 160000
--- a/test-run
+++ b/test-run
@@ -1 +1 @@
-Subproject commit 947c65cda7f26ceb5f601bbf154e70f9c3f755e9
+Subproject commit f8311bc092b99d0a1c05740673b8a46c3f83296f
diff --git a/test/box-py/bootstrap.result b/test/box-py/bootstrap.result
index 7fc0ca97d..25f374bab 100644
--- a/test/box-py/bootstrap.result
+++ b/test/box-py/bootstrap.result
@@ -4,7 +4,7 @@ box.internal.bootstrap()
box.space._schema:select{}
---
- - ['max_id', 511]
- - ['version', 2, 2, 1]
+ - ['version', 2, 2, 2]
...
box.space._cluster:select{}
---
@@ -236,6 +236,8 @@ box.space._priv:select{}
- [1, 2, 'function', 63, 4]
- [1, 2, 'function', 64, 4]
- [1, 2, 'function', 65, 4]
+ - [1, 2, 'function', 66, 4]
+ - [1, 2, 'function', 67, 4]
- [1, 2, 'space', 276, 2]
- [1, 2, 'space', 277, 1]
- [1, 2, 'space', 281, 1]
diff --git a/test/box/access.result b/test/box/access.result
index ba72b5f74..3976adfde 100644
--- a/test/box/access.result
+++ b/test/box/access.result
@@ -691,7 +691,7 @@ box.schema.func.exists(1)
---
- true
...
-box.schema.func.exists(66)
+box.schema.func.exists(68)
---
- false
...
diff --git a/test/box/access.test.lua b/test/box/access.test.lua
index 219cdb04a..89a63c904 100644
--- a/test/box/access.test.lua
+++ b/test/box/access.test.lua
@@ -276,7 +276,7 @@ box.schema.user.exists{}
box.schema.func.exists('nosuchfunc')
box.schema.func.exists('guest')
box.schema.func.exists(1)
-box.schema.func.exists(66)
+box.schema.func.exists(68)
box.schema.func.exists('box.schema.user.info')
box.schema.func.exists()
box.schema.func.exists(nil)
diff --git a/test/box/access_bin.result b/test/box/access_bin.result
index 3fc0749f7..9f3ec8ada 100644
--- a/test/box/access_bin.result
+++ b/test/box/access_bin.result
@@ -299,7 +299,7 @@ box.schema.user.grant('guest', 'execute', 'universe')
function f1() return box.space._func:get(1)[4] end
---
...
-function f2() return box.space._func:get(66)[4] end
+function f2() return box.space._func:get(68)[4] end
---
...
box.schema.func.create('f1')
diff --git a/test/box/access_bin.test.lua b/test/box/access_bin.test.lua
index 48528c07f..6ca752dd4 100644
--- a/test/box/access_bin.test.lua
+++ b/test/box/access_bin.test.lua
@@ -113,7 +113,7 @@ test:drop()
-- notice that guest can execute stuff, but can't read space _func
box.schema.user.grant('guest', 'execute', 'universe')
function f1() return box.space._func:get(1)[4] end
-function f2() return box.space._func:get(66)[4] end
+function f2() return box.space._func:get(68)[4] end
box.schema.func.create('f1')
box.schema.func.create('f2',{setuid=true})
c = net.connect(box.cfg.listen)
diff --git a/test/box/access_misc.result b/test/box/access_misc.result
index cfb72a9a6..8ae77abc3 100644
--- a/test/box/access_misc.result
+++ b/test/box/access_misc.result
@@ -839,136 +839,140 @@ box.space._space:select()
box.space._func:select()
---
session = nil
---
diff --git a/test/box/access_sysview.result b/test/box/access_sysview.result
index a82127ebb..3072b7394 100644
--- a/test/box/access_sysview.result
+++ b/test/box/access_sysview.result
@@ -258,11 +258,11 @@ box.session.su('guest')
...
#box.space._vpriv:select{}
---
-- 80
+- 82
...
#box.space._vfunc:select{}
---
-- 65
+- 67
...
#box.space._vcollation:select{}
---
@@ -290,11 +290,11 @@ box.session.su('guest')
...
#box.space._vpriv:select{}
---
-- 80
+- 82
...
#box.space._vfunc:select{}
---
-- 65
+- 67
...
#box.space._vsequence:select{}
---
diff --git a/test/box/function1.result b/test/box/function1.result
index 00e5880cd..5b091f72b 100644
--- a/test/box/function1.result
+++ b/test/box/function1.result
@@ -97,7 +97,7 @@ box.func["function1.args"]
exports:
lua: true
sql: false
- id: 66
+ id: 68
setuid: false
is_multikey: false
is_deterministic: false
@@ -417,7 +417,7 @@ func
exports:
lua: true
sql: false
- id: 66
+ id: 68
setuid: false
is_multikey: false
is_deterministic: false
@@ -489,7 +489,7 @@ func
exports:
lua: true
sql: false
- id: 66
+ id: 68
setuid: false
is_multikey: false
is_deterministic: false
@@ -806,7 +806,7 @@ sql_builtin_list = {
"RANDOMBLOB", "NULLIF", "ZEROBLOB", "MIN", "MAX", "COALESCE", "EVERY",
"EXISTS", "EXTRACT", "SOME", "GREATER", "LESSER", "SOUNDEX",
"LIKELIHOOD", "LIKELY", "UNLIKELY", "_sql_stat_get", "_sql_stat_push",
- "_sql_stat_init",
+ "_sql_stat_init", "GREATEST", "LEAST"
}
test_run:cmd("setopt delimiter ''");
---
diff --git a/test/box/function1.test.lua b/test/box/function1.test.lua
index 5eb597d16..f894472f8 100644
--- a/test/box/function1.test.lua
+++ b/test/box/function1.test.lua
@@ -285,7 +285,7 @@ sql_builtin_list = {
"RANDOMBLOB", "NULLIF", "ZEROBLOB", "MIN", "MAX", "COALESCE", "EVERY",
"EXISTS", "EXTRACT", "SOME", "GREATER", "LESSER", "SOUNDEX",
"LIKELIHOOD", "LIKELY", "UNLIKELY", "_sql_stat_get", "_sql_stat_push",
- "_sql_stat_init",
+ "_sql_stat_init", "GREATEST", "LEAST"
}
test_run:cmd("setopt delimiter ''");
ok = true
diff --git a/test/sql-tap/coalesce.test.lua b/test/sql-tap/coalesce.test.lua
index 5740c1b37..5501e6992 100755
--- a/test/sql-tap/coalesce.test.lua
+++ b/test/sql-tap/coalesce.test.lua
@@ -103,7 +103,7 @@ test:do_test(
"coalesce-1.6",
function()
return test:execsql [[
- SELECT coalesce(b,NOT b,-b,abs(b),lower(b),length(b),min(b,5),b*123,c)
+ SELECT coalesce(b,NOT b,-b,abs(b),lower(b),length(b),LEAST(b,5),b*123,c)
FROM t1 ORDER BY a;
]]
end, {
diff --git a/test/sql-tap/func3.test.lua b/test/sql-tap/func3.test.lua
index 2d0579d93..95d96965e 100755
--- a/test/sql-tap/func3.test.lua
+++ b/test/sql-tap/func3.test.lua
@@ -135,8 +135,8 @@ test:do_catchsql_test(
test:do_test(
"func3-5.20",
function()
- return test:execsql "EXPLAIN SELECT likelihood(min(1.0+'2.0',4*11), 0.5)"
- end, test:execsql("EXPLAIN SELECT min(1.0+'2.0',4*11)"))
+ return test:execsql "EXPLAIN SELECT likelihood(LEAST(1.0+'2.0',4*11), 0.5)"
+ end, test:execsql("EXPLAIN SELECT LEAST(1.0+'2.0',4*11)"))
-- EVIDENCE-OF: R-11152-23456 The unlikely(X) function returns the
-- argument X unchanged.
@@ -208,8 +208,8 @@ test:do_execsql_test(
test:do_test(
"func3-5.39",
function()
- return test:execsql "EXPLAIN SELECT unlikely(min(1.0+'2.0',4*11))"
- end, test:execsql "EXPLAIN SELECT min(1.0+'2.0',4*11)")
+ return test:execsql "EXPLAIN SELECT unlikely(LEAST(1.0+'2.0',4*11))"
+ end, test:execsql "EXPLAIN SELECT LEAST(1.0+'2.0',4*11)")
-- EVIDENCE-OF: R-23735-03107 The likely(X) function returns the argument
-- X unchanged.
@@ -281,8 +281,8 @@ test:do_execsql_test(
test:do_test(
"func3-5.59",
function()
- return test:execsql "EXPLAIN SELECT likely(min(1.0+'2.0',4*11))"
- end, test:execsql "EXPLAIN SELECT min(1.0+'2.0',4*11)")
+ return test:execsql "EXPLAIN SELECT likely(LEAST(1.0+'2.0',4*11))"
+ end, test:execsql "EXPLAIN SELECT LEAST(1.0+'2.0',4*11)")
--
diff --git a/test/sql-tap/func5.test.lua b/test/sql-tap/func5.test.lua
index 6da089994..0b255e659 100755
--- a/test/sql-tap/func5.test.lua
+++ b/test/sql-tap/func5.test.lua
@@ -98,13 +98,13 @@ test:do_execsql_test(
-- </func5-2.2>
})
--- The following tests ensures that max() and min() functions
--- raise error if argument's collations are incompatible.
+-- The following tests ensures that GREATEST() and LEAST()
+-- functions raise error if argument's collations are incompatible.
test:do_catchsql_test(
"func-5-3.1",
[[
- SELECT max('a' COLLATE "unicode", 'A' COLLATE "unicode_ci");
+ SELECT GREATEST('a' COLLATE "unicode", 'A' COLLATE "unicode_ci");
]],
{
-- <func5-3.1>
@@ -120,7 +120,7 @@ test:do_catchsql_test(
CREATE TABLE test2 (s2 VARCHAR(5) PRIMARY KEY COLLATE "unicode_ci");
INSERT INTO test1 VALUES ('a');
INSERT INTO test2 VALUES ('a');
- SELECT max(s1, s2) FROM test1 JOIN test2;
+ SELECT GREATEST(s1, s2) FROM test1 JOIN test2;
]],
{
-- <func5-3.2>
@@ -132,7 +132,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"func-5-3.3",
[[
- SELECT max ('abc', 'asd' COLLATE "binary", 'abc' COLLATE "unicode")
+ SELECT GREATEST ('abc', 'asd' COLLATE "binary", 'abc' COLLATE "unicode")
]],
{
-- <func5-3.3>
@@ -144,7 +144,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"func-5-3.4",
[[
- SELECT max (s1, 'asd' COLLATE "binary", s2) FROM test1 JOIN test2;
+ SELECT GREATEST (s1, 'asd' COLLATE "binary", s2) FROM test1 JOIN test2;
]], {
-- <func5-3.4>
"asd"
@@ -161,7 +161,7 @@ test:do_catchsql_test(
INSERT INTO test3 VALUES ('a');
INSERT INTO test4 VALUES ('a');
INSERT INTO test5 VALUES ('a');
- SELECT max(s3, s4, s5) FROM test3 JOIN test4 JOIN test5;
+ SELECT GREATEST(s3, s4, s5) FROM test3 JOIN test4 JOIN test5;
]],
{
-- <func5-3.5>
@@ -173,7 +173,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"func-5-3.6",
[[
- SELECT min('a' COLLATE "unicode", 'A' COLLATE "unicode_ci");
+ SELECT LEAST('a' COLLATE "unicode", 'A' COLLATE "unicode_ci");
]],
{
-- <func5-3.6>
@@ -185,7 +185,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"func-5-3.7",
[[
- SELECT min(s1, s2) FROM test1 JOIN test2;
+ SELECT LEAST(s1, s2) FROM test1 JOIN test2;
]],
{
-- <func5-3.7>
@@ -197,7 +197,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"func-5-3.8",
[[
- SELECT min ('abc', 'asd' COLLATE "binary", 'abc' COLLATE "unicode")
+ SELECT LEAST('abc', 'asd' COLLATE "binary", 'abc' COLLATE "unicode")
]],
{
-- <func5-3.8>
@@ -209,7 +209,7 @@ test:do_catchsql_test(
test:do_execsql_test(
"func-5-3.9",
[[
- SELECT min (s1, 'asd' COLLATE "binary", s2) FROM test1 JOIN test2;
+ SELECT LEAST(s1, 'asd' COLLATE "binary", s2) FROM test1 JOIN test2;
]], {
-- <func5-3.9>
"a"
@@ -220,7 +220,7 @@ test:do_execsql_test(
test:do_catchsql_test(
"func-5.3.10",
[[
- SELECT min(s3, s4, s5) FROM test3 JOIN test4 JOIN test5;
+ SELECT LEAST(s3, s4, s5) FROM test3 JOIN test4 JOIN test5;
]],
{
-- <func5-3.10>
@@ -229,32 +229,32 @@ test:do_catchsql_test(
}
)
--- Order of arguments of min/max functions doesn't affect
+-- Order of arguments of LEAST/GREATEST functions doesn't affect
-- the result: boolean is always less than numbers, which
-- are less than strings.
--
test:do_execsql_test(
"func-5-4.1",
[[
- SELECT max (false, 'STR', 1, 0.5);
+ SELECT GREATEST (false, 'STR', 1, 0.5);
]], { "STR" } )
test:do_execsql_test(
"func-5-4.2",
[[
- SELECT max ('STR', 1, 0.5, false);
+ SELECT GREATEST ('STR', 1, 0.5, false);
]], { "STR" } )
test:do_execsql_test(
"func-5-4.3",
[[
- SELECT min ('STR', 1, 0.5, false);
+ SELECT LEAST('STR', 1, 0.5, false);
]], { false } )
test:do_execsql_test(
"func-5-4.4",
[[
- SELECT min (false, 'STR', 1, 0.5);
+ SELECT LEAST(false, 'STR', 1, 0.5);
]], { false } )
test:finish_test()
diff --git a/test/sql-tap/in1.test.lua b/test/sql-tap/in1.test.lua
index ba4c06936..570cc1779 100755
--- a/test/sql-tap/in1.test.lua
+++ b/test/sql-tap/in1.test.lua
@@ -177,7 +177,7 @@ test:do_execsql_test(
test:do_execsql_test(
"in-2.7",
[[
- SELECT a FROM t1 WHERE b IN (max(5,10,b),20)
+ SELECT a FROM t1 WHERE b IN (GREATEST(5,10,b),20)
]], {
-- <in-2.7>
4, 5, 6, 7, 8, 9, 10
@@ -197,7 +197,7 @@ test:do_execsql_test(
test:do_execsql_test(
"in-2.9",
[[
- SELECT a FROM t1 WHERE b IN (max(5,10),20)
+ SELECT a FROM t1 WHERE b IN (GREATEST(5,10),20)
]], {
-- <in-2.9>
@@ -207,7 +207,7 @@ test:do_execsql_test(
test:do_execsql_test(
"in-2.10",
[[
- SELECT a FROM t1 WHERE min(0, CAST(b IN (a,30) AS INT)) <> 0
+ SELECT a FROM t1 WHERE LEAST(0, CAST(b IN (a,30) AS INT)) <> 0
]], {
-- <in-2.10>
diff --git a/test/sql-tap/insert1.test.lua b/test/sql-tap/insert1.test.lua
index 5316b858b..d28baf11a 100755
--- a/test/sql-tap/insert1.test.lua
+++ b/test/sql-tap/insert1.test.lua
@@ -273,7 +273,7 @@ test:do_catchsql_test("insert-4.6", [[
})
test:do_execsql_test("insert-4.7", [[
- INSERT INTO t3 VALUES(min(1,2,3),max(1,2,3),99);
+ INSERT INTO t3 VALUES(LEAST(1,2,3),GREATEST(1,2,3),99);
SELECT * FROM t3 WHERE c=99;
]], {
-- <insert-4.7>
diff --git a/test/sql-tap/misc1.test.lua b/test/sql-tap/misc1.test.lua
index d5e17ce01..b84093e3c 100755
--- a/test/sql-tap/misc1.test.lua
+++ b/test/sql-tap/misc1.test.lua
@@ -330,7 +330,7 @@ test:do_execsql_test(
test:do_execsql_test(
"misc1-6.4",
[[
- SELECT abort+"asc",max(key,"pragma",temp) FROM t4
+ SELECT abort+"asc",GREATEST(key,"pragma",temp) FROM t4
]], {
-- <misc1-6.4>
3, 17
diff --git a/test/sql-tap/select1.test.lua b/test/sql-tap/select1.test.lua
index 87689a584..924c0ccb1 100755
--- a/test/sql-tap/select1.test.lua
+++ b/test/sql-tap/select1.test.lua
@@ -105,7 +105,7 @@ test:do_execsql_test(
test:do_execsql_test(
"select1-1.8.2",
[[
- SELECT *, min(f1,f2), max(f1,f2) FROM test1
+ SELECT *, LEAST(f1,f2), GREATEST(f1,f2) FROM test1
]], {
-- <select1-1.8.2>
11, 22, 11, 22
@@ -197,7 +197,7 @@ test:do_execsql_test(
test:do_execsql_test(
"select1-1.12",
- [[SELECT max(test1.f1,test2.r1), min(test1.f2,test2.r2)
+ [[SELECT GREATEST(test1.f1,test2.r1), LEAST(test1.f2,test2.r2)
FROM test2, test1]], {
-- <select1-1.12>
11, 2
@@ -206,7 +206,7 @@ test:do_execsql_test(
test:do_execsql_test(
"select1-1.13",
- [[SELECT min(test1.f1,test2.r1), max(test1.f2,test2.r2)
+ [[SELECT LEAST(test1.f1,test2.r1), GREATEST(test1.f2,test2.r2)
FROM test1, test2]], {
-- <select1-1.13>
1, 22
@@ -343,7 +343,7 @@ test:do_test(
function()
local msg
local v = pcall(function()
- msg = test:execsql "SELECT MIN(f1,f2) FROM test1"
+ msg = test:execsql "SELECT LEAST(f1,f2) FROM test1"
end)
v = v == true and {0} or {1}
return table.insert(v,table.sort(msg) or msg) or v
@@ -408,7 +408,7 @@ test:do_test(
function()
local msg
local v = pcall(function()
- msg = test:execsql "SELECT max(f1,f2) FROM test1"
+ msg = test:execsql "SELECT GREATEST(f1,f2) FROM test1"
end)
v = v == true and {0} or {1}
return table.insert(v,table.sort(msg) or msg) or v
@@ -423,7 +423,7 @@ test:do_test(
function()
local msg
local v = pcall(function()
- msg = test:execsql "SELECT MAX(f1,f2)+1 FROM test1"
+ msg = test:execsql "SELECT GREATEST(f1,f2)+1 FROM test1"
end)
v = v == true and {0} or {1}
return table.insert(v,table.sort(msg) or msg) or v
@@ -526,7 +526,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"select1-2.19",
[[
- SELECT SUM(min(f1,f2)) FROM test1
+ SELECT SUM(LEAST(f1,f2)) FROM test1
]], {
-- <select1-2.19>
0, {44}
@@ -660,7 +660,7 @@ test:do_test(
function()
local msg
local v = pcall(function()
- msg = test:execsql "SELECT f1 FROM test1 WHERE min(f1,f2)!=11"
+ msg = test:execsql "SELECT f1 FROM test1 WHERE LEAST(f1,f2)!=11"
end)
v = v == true and {0} or {1}
return table.insert(v,table.sort(msg) or msg) or v
@@ -675,7 +675,7 @@ test:do_test(
function()
local msg
local v = pcall(function()
- msg = test:execsql "SELECT f1 FROM test1 WHERE max(f1,f2)!=11"
+ msg = test:execsql "SELECT f1 FROM test1 WHERE GREATEST(f1,f2)!=11"
end)
v = v == true and {0} or {1}
return table.insert(v,table.sort(msg) or msg) or v
@@ -720,7 +720,7 @@ test:do_catchsql_test(
test:do_catchsql_test(
"select1-4.3",
[[
- SELECT f1 FROM test1 ORDER BY min(f1,f2)
+ SELECT f1 FROM test1 ORDER BY LEAST(f1,f2)
]], {
-- <select1-4.3>
0, {11, 33}
@@ -1546,7 +1546,7 @@ end
test:do_execsql_test(
"select1-8.5",
[[
- SELECT min(1,2,3), -max(1,2,3)
+ SELECT LEAST(1,2,3), -GREATEST(1,2,3)
FROM test1 ORDER BY f1
]], {
-- <select1-8.5>
diff --git a/test/sql-tap/select2.test.lua b/test/sql-tap/select2.test.lua
index e08c8b3b6..24fad2149 100755
--- a/test/sql-tap/select2.test.lua
+++ b/test/sql-tap/select2.test.lua
@@ -201,7 +201,7 @@ test:do_execsql_test(
INSERT INTO aa VALUES(3);
INSERT INTO bb VALUES(2);
INSERT INTO bb VALUES(4);
- SELECT * FROM aa, bb WHERE max(a,b)>2;
+ SELECT * FROM aa, bb WHERE GREATEST(a,b)>2;
]], {
-- <select2-4.1>
1, 4, 3, 2, 3, 4
@@ -232,7 +232,7 @@ test:do_execsql_test(
test:do_execsql_test(
"select2-4.4",
[[
- SELECT * FROM aa, bb WHERE min(a,b) <> 0;
+ SELECT * FROM aa, bb WHERE LEAST(a,b) <> 0;
]], {
-- <select2-4.4>
1, 2, 1, 4, 3, 2, 3, 4
@@ -242,7 +242,7 @@ test:do_execsql_test(
test:do_execsql_test(
"select2-4.5",
[[
- SELECT * FROM aa, bb WHERE NOT min(a,b) <> 0;
+ SELECT * FROM aa, bb WHERE NOT LEAST(a,b) <> 0;
]], {
-- <select2-4.5>
1, 0, 3, 0
diff --git a/test/sql-tap/select3.test.lua b/test/sql-tap/select3.test.lua
index b51a9e8b6..19f853dc7 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, min(log,avg(n))+0
+ ORDER BY max(n+log*2)+0, GREATEST(log,avg(n))+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-tap/with1.test.lua b/test/sql-tap/with1.test.lua
index 6985c589e..f82b73e63 100755
--- a/test/sql-tap/with1.test.lua
+++ b/test/sql-tap/with1.test.lua
@@ -547,7 +547,7 @@ test:do_execsql_test("8.1-mandelbrot", [[
SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
),
a(t) AS (
- SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '')
+ SELECT group_concat( substr(' .+*#', 1+LEAST(iter/7,4), 1), '')
FROM m2 GROUP BY cy
)
SELECT group_concat(TRIM(TRAILING FROM t),x'0a') FROM a;
diff --git a/test/sql/iproto.result b/test/sql/iproto.result
index 82ad2eddb..69df26238 100644
--- a/test/sql/iproto.result
+++ b/test/sql/iproto.result
@@ -788,18 +788,18 @@ res.metadata
-- arguments of all scalar type, we can't say nothing more than
-- SCALAR.
--
-cn:execute("SELECT min(1, 2, 3);")
+cn:execute("SELECT LEAST(1, 2, 3);")
---
- metadata:
- - name: min(1, 2, 3)
+ - name: LEAST(1, 2, 3)
type: scalar
rows:
- [1]
...
-cn:execute("SELECT max(1, 2, 3);")
+cn:execute("SELECT GREATEST(1, 2, 3);")
---
- metadata:
- - name: max(1, 2, 3)
+ - name: GREATEST(1, 2, 3)
type: scalar
rows:
- [3]
diff --git a/test/sql/iproto.test.lua b/test/sql/iproto.test.lua
index dd60afe79..5dfe95ccc 100644
--- a/test/sql/iproto.test.lua
+++ b/test/sql/iproto.test.lua
@@ -240,8 +240,8 @@ res.metadata
-- arguments of all scalar type, we can't say nothing more than
-- SCALAR.
--
-cn:execute("SELECT min(1, 2, 3);")
-cn:execute("SELECT max(1, 2, 3);")
+cn:execute("SELECT LEAST(1, 2, 3);")
+cn:execute("SELECT GREATEST(1, 2, 3);")
cn:close()
box.execute('DROP TABLE t1')
diff --git a/test/sql/types.result b/test/sql/types.result
index 83820af53..65b3ba79d 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -680,10 +680,10 @@ box.execute("SELECT quote(b) FROM t;")
- ['false']
- ['NULL']
...
-box.execute("SELECT min(b, true) FROM t;")
+box.execute("SELECT LEAST(b, true) FROM t;")
---
- metadata:
- - name: min(b, true)
+ - name: LEAST(b, true)
type: scalar
rows:
- [true]
@@ -1550,10 +1550,10 @@ box.execute("SELECT quote(i) FROM t;")
rows:
- [18446744073709551613]
...
-box.execute("SELECT min(-1, i) FROM t;")
+box.execute("SELECT LEAST(-1, i) FROM t;")
---
- metadata:
- - name: min(-1, i)
+ - name: LEAST(-1, i)
type: scalar
rows:
- [-1]
@@ -2023,10 +2023,10 @@ box.execute("SELECT quote(v) FROM t;")
rows:
- ['X''616263''']
...
-box.execute("SELECT min(v, x'') FROM t;")
+box.execute("SELECT LEAST(v, x'') FROM t;")
---
- metadata:
- - name: min(v, x'')
+ - name: LEAST(v, x'')
type: scalar
rows:
- ['']
diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua
index 0d005b377..2ba37c092 100644
--- a/test/sql/types.test.lua
+++ b/test/sql/types.test.lua
@@ -146,7 +146,7 @@ box.execute("SELECT upper(b) FROM t;")
box.execute("SELECT abs(b) FROM t;")
box.execute("SELECT typeof(b) FROM t;")
box.execute("SELECT quote(b) FROM t;")
-box.execute("SELECT min(b, true) FROM t;")
+box.execute("SELECT LEAST(b, true) FROM t;")
box.execute("SELECT quote(b) FROM t;")
-- Test index search using boolean values.
@@ -335,7 +335,7 @@ box.execute("SELECT upper(i) FROM t;")
box.execute("SELECT abs(i) FROM t;")
box.execute("SELECT typeof(i) FROM t;")
box.execute("SELECT quote(i) FROM t;")
-box.execute("SELECT min(-1, i) FROM t;")
+box.execute("SELECT LEAST(-1, i) FROM t;")
box.execute("SELECT quote(i) FROM t;")
box.execute("CREATE INDEX i ON t(i);")
@@ -444,7 +444,7 @@ box.execute("SELECT upper(v) FROM t;")
box.execute("SELECT abs(v) FROM t;")
box.execute("SELECT typeof(v) FROM t;")
box.execute("SELECT quote(v) FROM t;")
-box.execute("SELECT min(v, x'') FROM t;")
+box.execute("SELECT LEAST(v, x'') FROM t;")
box.execute("CREATE INDEX iv ON t(v);")
box.execute("SELECT v FROM t WHERE v = x'616263';")
diff --git a/test/wal_off/func_max.result b/test/wal_off/func_max.result
index a3ab5b431..78db38d6b 100644
--- a/test/wal_off/func_max.result
+++ b/test/wal_off/func_max.result
@@ -42,11 +42,11 @@ test_run:cmd("setopt delimiter ''");
...
func_limit()
---
-- error: 'Failed to create function ''func31936'': function id is too big'
+- error: 'Failed to create function ''func31934'': function id is too big'
...
drop_limit_func()
---
-- error: Function 'func31936' does not exist
+- error: Function 'func31934' does not exist
...
box.schema.user.create('testuser')
---
@@ -62,11 +62,11 @@ session.su('testuser')
...
func_limit()
---
-- error: 'Failed to create function ''func31936'': function id is too big'
+- error: 'Failed to create function ''func31934'': function id is too big'
...
drop_limit_func()
---
-- error: Function 'func31936' does not exist
+- error: Function 'func31934' does not exist
...
session.su('admin')
---
--
2.22.0
More information about the Tarantool-patches
mailing list