From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from [87.239.111.99] (localhost [127.0.0.1]) by dev.tarantool.org (Postfix) with ESMTP id 56D546EC40; Fri, 13 Aug 2021 06:20:37 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 56D546EC40 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1628824837; bh=RGxJT6ba8sv33/RyX7QDqHvLHDJhK+UOgbll71byJ7w=; h=To:Cc:Date:In-Reply-To:References:Subject:List-Id: List-Unsubscribe:List-Archive:List-Post:List-Help:List-Subscribe: From:Reply-To:From; b=S2qx8uqwR3Xsq3Rld/ILmMqKltR42ybJzm5/oa9BeUsnPlPDrhBGf80b+thdXWnKX MM07K8j1erHbnnFeJEVrWuXzFPMYgREGKnagQlWbIDKFNSltbHBdt95ss8F+psWdn2 NO3PaiGlyCvBhfNkKO126p+YEB5DnDn2uZqeuf1U= Received: from smtpng2.i.mail.ru (smtpng2.i.mail.ru [94.100.179.3]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id 5D5896F3F0 for ; Fri, 13 Aug 2021 06:17:23 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 5D5896F3F0 Received: by smtpng2.m.smailru.net with esmtpa (envelope-from ) id 1mENhC-0002Q1-Gk; Fri, 13 Aug 2021 06:17:22 +0300 To: vdavydov@tarantool.org Cc: tarantool-patches@dev.tarantool.org Date: Fri, 13 Aug 2021 06:17:22 +0300 Message-Id: X-Mailer: git-send-email 2.25.1 In-Reply-To: References: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-7564579A: 646B95376F6C166E X-77F55803: 4F1203BC0FB41BD92087353F0EC44DD9736CF3E71F18CE0C3E1D5927724F4AAA182A05F538085040E15AC2657329FCFE0242C3902ECA66DD7252733EFEA2567C59EDA2E5CB26795D X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE74C265300876DF183C2099A533E45F2D0395957E7521B51C2CFCAF695D4D8E9FCEA1F7E6F0F101C6778DA827A17800CE7051A6EFB787CE9C4EA1F7E6F0F101C6723150C8DA25C47586E58E00D9D99D84E1BDDB23E98D2D38BBCA57AF85F7723F2097576E238D924915B73AC2E7DF299C6CC7F00164DA146DAFE8445B8C89999728AA50765F7900637F6B57BC7E64490618DEB871D839B7333395957E7521B51C2DFABB839C843B9C08941B15DA834481F8AA50765F7900637F6B57BC7E6449061A352F6E88A58FB86F5D81C698A659EA7E827F84554CEF5019E625A9149C048EE9ECD01F8117BC8BEE2021AF6380DFAD18AA50765F790063735872C767BF85DA227C277FBC8AE2E8BDC0F6C5B2EEF3D0C75ECD9A6C639B01B4E70A05D1297E1BBCB5012B2E24CD356 X-B7AD71C0: AC4F5C86D027EB782CDD5689AFBDA7A213B5FB47DCBC3458834459D11680B5059A91936EF7B6732D513F37578A2D94F4 X-C1DE0DAB: C20DE7B7AB408E4181F030C43753B8186998911F362727C414F749A5E30D975C69415AB31670C86C9146FE417AEEA3B8B6FBFC8C55F7B6F89C2B6934AE262D3EE7EAB7254005DCED7532B743992DF240BDC6A1CF3F042BAD6DF99611D93F60EFE37876E7723AB534DC48ACC2A39D04F89CDFB48F4795C241BDAD6C7F3747799A X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D3498EF79680EE3725C252588C2B9B9FD28CC22E170D60556CB1918CAC02B93FFA43C696F448639586B1D7E09C32AA3244C09E225F84761AC7D2AE479CDC16EAB9A259227199D06760A729B2BEF169E0186 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2bioj0dLV0c3jbkxJ7dlbbdSAJw== X-Mailru-Sender: 689FA8AB762F7393C37E3C1AEC41BA5D1C77380F1A923C0275C5D9A0445C341083D72C36FC87018B9F80AB2734326CD2FB559BB5D741EB96352A0ABBE4FDA4210A04DAD6CC59E33667EA787935ED9F1B X-Mras: Ok Subject: [Tarantool-patches] [PATCH v1 07/10] sql: fix result type of min() and max() functions X-BeenThere: tarantool-patches@dev.tarantool.org X-Mailman-Version: 2.1.34 Precedence: list List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , From: Mergen Imeev via Tarantool-patches Reply-To: imeevma@tarantool.org Errors-To: tarantool-patches-bounces@dev.tarantool.org Sender: "Tarantool-patches" 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") ]], { -- 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 ]], { -- 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