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 CC6856EC40; Thu, 3 Jun 2021 00:10:37 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org CC6856EC40 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1622668237; bh=NtmvVo2IHk05Rmgy7rqAtCAokwj3QPFEdG21Dmf39G4=; h=To:Cc:References:In-Reply-To:Date:Subject:List-Id: List-Unsubscribe:List-Archive:List-Post:List-Help:List-Subscribe: From:Reply-To:From; b=AHgEiyEZ3B8W7usL7V7+Tjyj5n+ud7w3GKnHsVRTQqTnEhVwsrZ/Xy26/B5rpk48B B/Bu2RqhinCPBMQJO4A10giPDSlZUQNO4TJ684Llj+U1EW8NILJSkE0baDl6w31PZh HbZn2ECI2QddwuLAo9ctfL4RiCJ9Zaxklowju29k= Received: from smtp47.i.mail.ru (smtp47.i.mail.ru [94.100.177.107]) (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 7850F6EC40 for ; Thu, 3 Jun 2021 00:10:36 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 7850F6EC40 Received: by smtp47.i.mail.ru with esmtpa (envelope-from ) id 1loY8J-0000B2-Iq; Thu, 03 Jun 2021 00:10:36 +0300 To: "'Mergen Imeev'" Cc: References: In-Reply-To: Date: Thu, 3 Jun 2021 00:10:35 +0300 Message-ID: <053e01d757f3$bae97350$30bc59f0$@tarantool.org> MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQIN143y2CAuVXbaZAstAFvLluhQBgHvPVHwAjNpdW0B074BI6pkg8+w Content-Language: ru X-7564579A: 78E4E2B564C1792B X-77F55803: 4F1203BC0FB41BD9D5B0DA836B685C5407454A95E60932C8E3171F0D0805CD56182A05F538085040A6B99A7F3D5326BB1835130BEF4E62ABCA05B0E2E9D19AE0F7A524C2B2EEEE46 X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE7F10646C0E1989908EA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F79006370CE4B4B08BC34B6C8638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D8A5288157829048E27598F38848AE7773117882F4460429724CE54428C33FAD305F5C1EE8F4F765FCF1175FABE1C0F9B6A471835C12D1D9774AD6D5ED66289B52BA9C0B312567BB23117882F446042972877693876707352033AC447995A7AD18CB629EEF1311BF91D2E47CDBA5A96583BA9C0B312567BB2376E601842F6C81A19E625A9149C048EE7B96B19DC40933212D242C3BD2E3F4C64AD6D5ED66289B52698AB9A7B718F8C46E0066C2D8992A16725E5C173C3A84C3BF1527D61138A0E9BA3038C0950A5D36B5C8C57E37DE458B0BC6067A898B09E46D1867E19FE14079C09775C1D3CA48CF3D321E7403792E342EB15956EA79C166A417C69337E82CC275ECD9A6C639B01B78DA827A17800CE7588D3C263EAE74EA731C566533BA786AA5CC5B56E945C8DA X-B7AD71C0: AC4F5C86D027EB782CDD5689AFBDA7A24209795067102C07E8F7B195E1C978312F2D079A43F4F7B766BC45FBD0D174ED X-C1DE0DAB: 0D63561A33F958A5A91EE6FB0BA2D960EFA138CC4B1D980675DDBD215D242E3BD59269BC5F550898D99A6476B3ADF6B47008B74DF8BB9EF7333BD3B22AA88B938A852937E12ACA75FBC5FED0552DA851410CA545F18667F91A7EA1CDA0B5A7A0 X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D340A58BF0C7EAC340FDCDE1861D934BB7BFB81A73BBAE11904E4B20C139C89B6C3B21CB92A77B541BC1D7E09C32AA3244C8209CBA3968AC18E2A7AD9865F7399E3435BF7150578642F729B2BEF169E0186 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2bioj+mfSpkNmA2orF0o97TmsGA== X-Mailru-Sender: B5B6A6EBBD94DAD840208BF9E14C1DD2C12A27C8E0D767A6E4B67E3C6B273435191FF8DA59C9521C1EC9E4A2C82A33BC8C24925A86E657CE0C70AEE3C9A96FBAB3D7EE8ED63280BE112434F685709FCF0DA7A0AF5A3A8387 X-Mras: Ok Subject: Re: [Tarantool-patches] [PATCH 1/3] sql: fixes for boolean expressions in explicit converstion tables 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: Timur Safin via Tarantool-patches Reply-To: Timur Safin Errors-To: tarantool-patches-bounces@dev.tarantool.org Sender: "Tarantool-patches" Pushed "Send" button too early. Continue... : From: Timur Safin : Sent: Thursday, June 3, 2021 12:04 AM : To: 'Mergen Imeev' : Cc: 'tarantool-patches@dev.tarantool.org' : Subject: RE: [PATCH 1/3] sql: fixes for boolean expressions in = explicit : converstion tables :=20 ... : : > diff --git a/test/sql-tap/e_select1.test.lua b/test/sql- : : tap/e_select1.test.lua : : > index ab0faa376..28ea1d82f 100755 : : > --- a/test/sql-tap/e_select1.test.lua : : > +++ b/test/sql-tap/e_select1.test.lua : : > @@ -910,7 +910,7 @@ test:do_select_tests( : : > : : > {"3", "SELECT sum(b+1) FROM z1 NATURAL LEFT JOIN z3", {- : 43.06}}, : : > {"4", "SELECT sum(b+2) FROM z1 NATURAL LEFT JOIN z3", {- : 38.06}}, : : > - {"5", "SELECT sum(CAST(b IS NOT NULL AS INTEGER)) FROM z1 : NATURAL : : LEFT JOIN z3", {5}}, : : > + {"5", "SELECT sum(CASE WHEN b IS NOT NULL THEN 1 ELSE 0 = END) : FROM : : z1 NATURAL LEFT JOIN z3", {5}}, : : > }) : : > : : > -- EVIDENCE-OF: R-26684-40576 Each non-aggregate expression in = the : : > diff --git a/test/sql-tap/in1.test.lua b/test/sql-tap/in1.test.lua : : > index 4b51da6e8..0fb059760 100755 : : > --- a/test/sql-tap/in1.test.lua : : > +++ b/test/sql-tap/in1.test.lua : : > @@ -97,13 +97,13 @@ test:do_execsql_test( : : > -- : : > }) : : > : : > -test:do_execsql_test( : : > +test:do_catchsql_test( : : 13. Do you really need to change type of the test? I believe you can = do : the : : same you did in the previous test. Good point. I've updated this result file via --update-result before=20 I realized the more accurate way in the different test. : : : : > "in-1.7", : : > [[ : : > SELECT a+ 100*CAST((a BETWEEN 1 and 3) AS INTEGER) FROM = t1 : ORDER : : BY b : : > ]], { : : > -- : : > - 101, 102, 103, 4, 5, 6, 7, 8, 9, 10 : : > + 1, "Type mismatch: can not convert TRUE to integer" : : > -- : : > }) : : > : : > @@ -154,13 +154,13 @@ test:do_execsql_test( : : > -- : : > }) : : > : : > -test:do_execsql_test( : : > +test:do_catchsql_test( : : 14. Same. : : : : > "in-2.5", : : > [[ : : > SELECT a+100*(CAST(b IN (8,16,24) AS INTEGER)) FROM t1 = ORDER BY : b : : > ]], { : : > -- : : > - 1, 2, 103, 104, 5, 6, 7, 8, 9, 10 : : > + 1, "Type mismatch: can not convert FALSE to integer" : : > -- : : > }) : : > : : > @@ -204,13 +204,13 @@ test:do_execsql_test( : : > -- : : > }) : : > : : > -test:do_execsql_test( : : > +test:do_catchsql_test( : : 15. Same. : : : : > "in-2.10", : : > [[ : : > SELECT a FROM t1 WHERE LEAST(0, CAST(b IN (a,30) AS INT)) = <> 0 : : > ]], { : : > -- : : > - : : > + 1, "Type mismatch: can not convert FALSE to integer" : : > -- : : > }) : : > : : > @@ -250,13 +250,13 @@ test:do_execsql_test( : : > -- : : > }) : : > : : > -test:do_execsql_test( : : > +test:do_catchsql_test( : : 16. Same. : : : : > "in-3.3", : : > [[ : : > SELECT a + 100*(CAST(b IN (SELECT b FROM t1 WHERE a<5) AS : : INTEGER)) FROM t1 ORDER BY b : : > ]], { : : > -- : : > - 101, 102, 103, 104, 5, 6, 7, 8, 9, 10 : : > + 1, "Type mismatch: can not convert TRUE to integer" : : > -- : : > }) : : > : : > diff --git a/test/sql-tap/misc3.test.lua = b/test/sql-tap/misc3.test.lua : : > index 313484b5d..c2dc67355 100755 : : > --- a/test/sql-tap/misc3.test.lua : : > +++ b/test/sql-tap/misc3.test.lua : : > @@ -510,7 +510,7 @@ test:do_execsql_test( : : > test:do_execsql_test( : : > "misc-8.2", : : > [[ : : > - SELECT count(*) FROM t3 WHERE 1+CAST((b IN ('abc','xyz')) = AS : : INTEGER)=3D=3D2 : : > + SELECT count(*) FROM t3 WHERE b IN ('abc','xyz') : : 17. Somehow this change looks a bit too much. What checks this test? : : : : > ]], { : : > -- : : > 2 : : > diff --git a/test/sql/boolean.result b/test/sql/boolean.result : : > index 177a39fb9..b268eb2fe 100644 : : > --- a/test/sql/boolean.result : : > +++ b/test/sql/boolean.result : : > @@ -502,23 +502,13 @@ INSERT INTO t3 VALUES (4, false) : : > -- Check CAST from BOOLEAN to the other types. : : > SELECT cast(true AS INTEGER), cast(false AS INTEGER); : : > | --- : : > - | - metadata: : : > - | - name: COLUMN_1 : : > - | type: integer : : > - | - name: COLUMN_2 : : > - | type: integer : : > - | rows: : : > - | - [1, 0] : : > + | - null : : > + | - 'Type mismatch: can not convert TRUE to integer' : : > | ... : : > SELECT cast(true AS NUMBER), cast(false AS NUMBER); : : > | --- : : > - | - metadata: : : > - | - name: COLUMN_1 : : > - | type: number : : > - | - name: COLUMN_2 : : > - | type: number : : > - | rows: : : > - | - [1, 0] : : > + | - null : : > + | - 'Type mismatch: can not convert TRUE to number' : : > | ... : : > -- gh-4462: ensure that text representation is uppercase. : : > SELECT cast(true AS TEXT), cast(false AS TEXT); : : > @@ -545,25 +535,13 @@ SELECT cast(true AS BOOLEAN), cast(false AS : : BOOLEAN); : : > -- Check CAST to BOOLEAN from the other types. : : > SELECT cast(100 AS BOOLEAN), cast(1 AS BOOLEAN), cast(0 AS = BOOLEAN); : : > | --- : : > - | - metadata: : : > - | - name: COLUMN_1 : : > - | type: boolean : : > - | - name: COLUMN_2 : : > - | type: boolean : : > - | - name: COLUMN_3 : : > - | type: boolean : : > - | rows: : : > - | - [true, true, false] : : > + | - null : : > + | - 'Type mismatch: can not convert 100 to boolean' : : > | ... : : > SELECT cast(0.123 AS BOOLEAN), cast(0.0 AS BOOLEAN); : : > | --- : : > - | - metadata: : : > - | - name: COLUMN_1 : : > - | type: boolean : : > - | - name: COLUMN_2 : : > - | type: boolean : : > - | rows: : : > - | - [true, false] : : > + | - null : : > + | - 'Type mismatch: can not convert 0.123 to boolean' : : > | ... : : > SELECT cast('true' AS BOOLEAN), cast('false' AS BOOLEAN); : : > | --- : : > diff --git a/test/sql/types.result b/test/sql/types.result : : > index 687ca3b15..d59cbef7d 100644 : : > --- a/test/sql/types.result : : > +++ b/test/sql/types.result : : > @@ -1035,11 +1035,8 @@ box.execute("SELECT = CAST(18446744073709551615 AS : : SCALAR);") : : > ... : : > box.execute("SELECT CAST(18446744073709551615 AS BOOLEAN);") : : > --- : : > -- metadata: : : > - - name: COLUMN_1 : : > - type: boolean : : > - rows: : : > - - [true] : : > +- null : : > +- 'Type mismatch: can not convert 18446744073709551615 to = boolean' : : > ... : : > box.execute("SELECT CAST('18446744073709551615' AS INTEGER);") : : > --- : : > @@ -1105,11 +1102,8 @@ box.execute("SELECT CAST(-1.5 AS = UNSIGNED);") : : > ... : : > box.execute("SELECT CAST(true AS UNSIGNED);") : : > --- : : > -- metadata: : : > - - name: COLUMN_1 : : > - type: unsigned : : > - rows: : : > - - [1] : : > +- null : : > +- 'Type mismatch: can not convert TRUE to unsigned' : : > ... : : > box.execute("SELECT CAST('123' AS UNSIGNED);") : : > --- : : > -- : : > 2.29.2 : : > : : 18. Please add tests for this patch. Are you serious here? Updates of older tests and extensive matrix in = e_casts.test.lua=20 are tests for this patch. There is no point to create any kind of = result-based test, neither manual tap test, at the moment when we have full conversion = table covered in e_casts.test.lua. Timur