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 E76626EC42; Thu, 19 Aug 2021 15:06:29 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org E76626EC42 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1629374790; bh=LDp9GT6oVktpoOootljiQmPzpPo0+daKRlNyOoOCOGQ=; 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=CSIVVGpP40jF6TvldgHLtGbaPvv5pcEQbgPo1sXzD45RTOXbdqa0VZqserijfCaRC EhFeY5ZOmmTP62J45nCZBonbzkamJpdGxT6iUDO5gQm9rpeFYzgJqo9Ds6IaBn/fop sJrbj4dOi57qtWPQpISJ5w7jc+PZBRFSdrwIjtUU= 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 D9B1B6EC5D for ; Thu, 19 Aug 2021 15:03:12 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org D9B1B6EC5D Received: by smtpng2.m.smailru.net with esmtpa (envelope-from ) id 1mGglL-00014w-Js; Thu, 19 Aug 2021 15:03:12 +0300 To: kyukhin@tarantool.org, v.ioffe@tarantool.org Cc: tarantool-patches@dev.tarantool.org Date: Thu, 19 Aug 2021 15:03:11 +0300 Message-Id: <74ba4f632a5f5769bfe22cbc4336799811fc4206.1629374449.git.imeevma@gmail.com> X-Mailer: git-send-email 2.25.1 In-Reply-To: References: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-4EC0790: 10 X-7564579A: 646B95376F6C166E X-77F55803: 4F1203BC0FB41BD92087353F0EC44DD906AB4890CDABF0C5CB76CEE71D3E4007182A05F538085040312FB888D9221CCFBFFD7B3CEB152296580C8EEACE4854DCA11CBB86DB8EDAB9 X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE71B1B5393BB9C1313EA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F790063781C5E355D70FD85B8638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D88412E60729A55962512EB66AF642E22F117882F4460429724CE54428C33FAD305F5C1EE8F4F765FCAA867293B0326636D2E47CDBA5A96583BD4B6F7A4D31EC0BC014FD901B82EE079FA2833FD35BB23D27C277FBC8AE2E8BAA867293B0326636D2E47CDBA5A96583BA9C0B312567BB231DD303D21008E29813377AFFFEAFD269A417C69337E82CC2E827F84554CEF50127C277FBC8AE2E8BA83251EDC214901ED5E8D9A59859A8B6300D3B61E77C8D3B089D37D7C0E48F6C5571747095F342E88FB05168BE4CE3AF X-C1DE0DAB: C20DE7B7AB408E4181F030C43753B8186998911F362727C414F749A5E30D975CF160826E4E1956AE9254545F269889C40C60EEECD176E6139C2B6934AE262D3EE7EAB7254005DCED7532B743992DF240BDC6A1CF3F042BAD6DF99611D93F60EF836F5ADB0B4F9314699F904B3F4130E343918A1A30D5E7FCCB5012B2E24CD356 X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D3438212F20E1E78D8EBC85177563AAAEF67DEB6913925AA140B4C981E4B151DDD970B437BF36E8F71C1D7E09C32AA3244CDDF2B915F4DD84E28A5AFFE660FBF75CB018FE5BB746DCD1FACE5A9C96DEB163 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2biojGSxK+6r6oBEx6RvvJJ3MQA== X-Mailru-Sender: 689FA8AB762F7393C37E3C1AEC41BA5D8FBCA97C4AB4C810B9E7DC94CEF3F44D83D72C36FC87018B9F80AB2734326CD2FB559BB5D741EB96352A0ABBE4FDA4210A04DAD6CC59E33667EA787935ED9F1B X-Mras: Ok Subject: [Tarantool-patches] [PATCH v1 7/9] 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 281a1b4a8..56ccaea9d 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -2014,13 +2014,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, @@ -2035,10 +2059,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 58f00b30b..cdef9646e 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