From: Mergen Imeev via Tarantool-patches <tarantool-patches@dev.tarantool.org> To: imun@tarantool.org Cc: tarantool-patches@dev.tarantool.org Subject: [Tarantool-patches] [PATCH v2 1/3] sql: disallow explicit cast of BOOLEAN to number Date: Mon, 2 Aug 2021 20:25:27 +0300 [thread overview] Message-ID: <16669f8b0f6b4bca54a57b5706eedf441661edb3.1627925076.git.imeevma@gmail.com> (raw) In-Reply-To: <cover.1627925076.git.imeevma@gmail.com> This patch removes explicit cast of BOOLEAN values to numeric types and explicit cast of numeric values to BOOLEAN. Part of #4470 --- src/box/sql/mem.c | 44 -------------- test/sql-tap/cast.test.lua | 102 +++++++++++++++++++++++++++++++- test/sql-tap/cse.test.lua | 18 +++--- test/sql-tap/e_select1.test.lua | 3 +- test/sql-tap/in1.test.lua | 17 +++--- test/sql-tap/misc3.test.lua | 12 +--- test/sql/boolean.result | 71 +++++++++++----------- test/sql/boolean.test.sql | 13 ++-- test/sql/types.result | 14 ++--- 9 files changed, 173 insertions(+), 121 deletions(-) diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c index e4ce233e0..8f56cd289 100644 --- a/src/box/sql/mem.c +++ b/src/box/sql/mem.c @@ -634,17 +634,6 @@ int_to_str0(struct Mem *mem) return mem_copy_str0(mem, str); } -static inline int -int_to_bool(struct Mem *mem) -{ - assert((mem->type & (MEM_TYPE_INT | MEM_TYPE_UINT)) != 0); - mem->u.b = mem->u.i != 0; - mem->type = MEM_TYPE_BOOL; - assert(mem->flags == 0); - mem->field_type = FIELD_TYPE_BOOLEAN; - return 0; -} - static inline int str_to_str0(struct Mem *mem) { @@ -870,28 +859,6 @@ double_to_str0(struct Mem *mem) return 0; } -static inline int -double_to_bool(struct Mem *mem) -{ - assert(mem->type == MEM_TYPE_DOUBLE); - mem->u.b = mem->u.r != 0.; - mem->type = MEM_TYPE_BOOL; - assert(mem->flags == 0); - mem->field_type = FIELD_TYPE_BOOLEAN; - return 0; -} - -static inline int -bool_to_int(struct Mem *mem) -{ - assert(mem->type == MEM_TYPE_BOOL); - mem->u.u = (uint64_t)mem->u.b; - mem->type = MEM_TYPE_UINT; - assert(mem->flags == 0); - mem->field_type = FIELD_TYPE_UNSIGNED; - return 0; -} - static inline int bool_to_str0(struct Mem *mem) { @@ -942,8 +909,6 @@ mem_to_int(struct Mem *mem) return bytes_to_int(mem); if (mem->type == MEM_TYPE_DOUBLE) return double_to_int(mem); - if (mem->type == MEM_TYPE_BOOL) - return bool_to_int(mem); return -1; } @@ -979,8 +944,6 @@ mem_to_number(struct Mem *mem) assert(mem->type < MEM_TYPE_INVALID); if (mem_is_num(mem)) return 0; - if (mem->type == MEM_TYPE_BOOL) - return bool_to_int(mem); if ((mem->type & (MEM_TYPE_STR | MEM_TYPE_BIN)) != 0) { if (bytes_to_int(mem) == 0) return 0; @@ -1062,8 +1025,6 @@ mem_cast_explicit(struct Mem *mem, enum field_type type) return bytes_to_uint(mem); case MEM_TYPE_DOUBLE: return double_to_uint(mem); - case MEM_TYPE_BOOL: - return bool_to_int(mem); default: return -1; } @@ -1077,13 +1038,8 @@ mem_cast_explicit(struct Mem *mem, enum field_type type) switch (mem->type) { case MEM_TYPE_BOOL: return 0; - case MEM_TYPE_INT: - case MEM_TYPE_UINT: - return int_to_bool(mem); case MEM_TYPE_STR: return str_to_bool(mem); - case MEM_TYPE_DOUBLE: - return double_to_bool(mem); default: return -1; } diff --git a/test/sql-tap/cast.test.lua b/test/sql-tap/cast.test.lua index 8e7b6640e..3dc49c38e 100755 --- a/test/sql-tap/cast.test.lua +++ b/test/sql-tap/cast.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool local test = require("sqltester") -test:plan(79) +test:plan(91) --!./tcltestrunner.lua -- 2005 June 25 @@ -877,4 +877,104 @@ test:do_execsql_test( -- </cast-5.1> }) +-- gh-4470: Make explicit casts work according to our rules. + +-- Make sure that explicit cast from BOOLEAN to numeric types throws an error. +test:do_catchsql_test( + "cast-6.1.1", + [[ + SELECT CAST(TRUE AS UNSIGNED); + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to unsigned" + }) + +test:do_catchsql_test( + "cast-6.1.2", + [[ + SELECT CAST(FALSE AS UNSIGNED); + ]], { + 1, "Type mismatch: can not convert boolean(FALSE) to unsigned" + }) + +test:do_catchsql_test( + "cast-6.1.3", + [[ + SELECT CAST(TRUE AS INTEGER); + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to integer" + }) + +test:do_catchsql_test( + "cast-6.1.4", + [[ + SELECT CAST(FALSE AS INTEGER); + ]], { + 1, "Type mismatch: can not convert boolean(FALSE) to integer" + }) + +test:do_catchsql_test( + "cast-6.1.5", + [[ + SELECT CAST(TRUE AS DOUBLE); + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to double" + }) + +test:do_catchsql_test( + "cast-6.1.6", + [[ + SELECT CAST(FALSE AS DOUBLE); + ]], { + 1, "Type mismatch: can not convert boolean(FALSE) to double" + }) + +test:do_catchsql_test( + "cast-6.1.7", + [[ + SELECT CAST(TRUE AS NUMBER); + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to number" + }) + +test:do_catchsql_test( + "cast-6.1.8", + [[ + SELECT CAST(FALSE AS NUMBER); + ]], { + 1, "Type mismatch: can not convert boolean(FALSE) to number" + }) + +-- Make sure that explicit cast numeric value to BOOLEAN throws an error. +test:do_catchsql_test( + "cast-6.2.1", + [[ + SELECT CAST(0 AS BOOLEAN); + ]], { + 1, "Type mismatch: can not convert integer(0) to boolean" + }) + +test:do_catchsql_test( + "cast-6.2.2", + [[ + SELECT CAST(-1 AS BOOLEAN); + ]], { + 1, "Type mismatch: can not convert integer(-1) to boolean" + }) + +test:do_catchsql_test( + "cast-6.2.3", + [[ + SELECT CAST(1.5 AS BOOLEAN); + ]], { + 1, "Type mismatch: can not convert double(1.5) to boolean" + }) + +test:do_catchsql_test( + "cast-6.2.4", + [[ + SELECT CAST(CAST(1 AS NUMBER) AS BOOLEAN); + ]], { + 1, "Type mismatch: can not convert integer(1) to boolean" + }) + test:finish_test() diff --git a/test/sql-tap/cse.test.lua b/test/sql-tap/cse.test.lua index e09f955a0..665633aed 100755 --- a/test/sql-tap/cse.test.lua +++ b/test/sql-tap/cse.test.lua @@ -31,11 +31,11 @@ test:do_test( INSERT INTO t1 VALUES(2,21,22,23,24,25); ]] return test:execsql [[ - SELECT b, -b, ~b, NOT CAST(b AS BOOLEAN), NOT NOT CAST(b AS BOOLEAN), b-b, b+b, b*b, b/b, b FROM t1 + SELECT b, -b, ~b, b - b, b + b, b * b, b / b, b FROM t1; ]] end, { -- <cse-1.1> - 11, -11, -12, false, true, 0, 22, 121, 1, 11, 21, -21, -22, false, true, 0, 42, 441, 1, 21 + 11, -11, -12, 0, 22, 121, 1, 11, 21, -21, -22, 0, 42, 441, 1, 21 -- </cse-1.1> }) @@ -102,7 +102,7 @@ test:do_execsql_test( test:do_execsql_test( "cse-1.6.3", [[ - SELECT CASE WHEN CAST(b AS BOOLEAN) THEN d WHEN CAST(e AS BOOLEAN) THEN f ELSE 999 END, b, c, d FROM t1 + SELECT CASE WHEN b != 0 THEN d WHEN e != 0 THEN f ELSE 999 END, b, c, d FROM t1; ]], { -- <cse-1.6.3> 13, 11, 12, 13, 23, 21, 22, 23 @@ -112,7 +112,7 @@ test:do_execsql_test( test:do_execsql_test( "cse-1.6.4", [[ - SELECT b, c, d, CASE WHEN CAST(b AS BOOLEAN) THEN d WHEN CAST(e AS BOOLEAN) THEN f ELSE 999 END FROM t1 + SELECT b, c, d, CASE WHEN b != 0 THEN d WHEN e != 0 THEN f ELSE 999 END FROM t1; ]], { -- <cse-1.6.4> 11, 12, 13, 13, 21, 22, 23, 23 @@ -122,7 +122,7 @@ test:do_execsql_test( test:do_execsql_test( "cse-1.6.5", [[ - SELECT b, c, d, CASE WHEN false THEN d WHEN CAST(e AS BOOLEAN) THEN f ELSE 999 END FROM t1 + SELECT b, c, d, CASE WHEN false THEN d WHEN e != 0 THEN f ELSE 999 END FROM t1; ]], { -- <cse-1.6.5> 11, 12, 13, 15, 21, 22, 23, 25 @@ -132,10 +132,10 @@ test:do_execsql_test( test:do_execsql_test( "cse-1.7", [[ - SELECT a, -a, ~a, NOT CAST(a AS BOOLEAN), NOT NOT CAST(a AS BOOLEAN), a-a, a+a, a*a, a/a, a FROM t1 + SELECT a, -a, ~a, a - a, a + a, a * a, a / a, a FROM t1 ]], { -- <cse-1.7> - 1, -1 ,-2, false, true, 0, 2, 1, 1, 1, 2, -2, -3, false, true, 0, 4, 4, 1, 2 + 1, -1 ,-2, 0, 2, 1, 1, 1, 2, -2, -3, 0, 4, 4, 1, 2 -- </cse-1.7> }) @@ -152,10 +152,10 @@ test:do_execsql_test( test:do_execsql_test( "cse-1.9", [[ - SELECT NOT CAST(b AS BOOLEAN), ~b, NOT NOT CAST(b AS BOOLEAN), b FROM t1 + SELECT ~b, b FROM t1 ]], { -- <cse-1.9> - false, -12, true, 11, false, -22, true, 21 + -12, 11, -22, 21 -- </cse-1.9> }) diff --git a/test/sql-tap/e_select1.test.lua b/test/sql-tap/e_select1.test.lua index ab0faa376..8293965b8 100755 --- a/test/sql-tap/e_select1.test.lua +++ b/test/sql-tap/e_select1.test.lua @@ -910,7 +910,8 @@ 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..bdf1f207a 100755 --- a/test/sql-tap/in1.test.lua +++ b/test/sql-tap/in1.test.lua @@ -100,10 +100,11 @@ test:do_execsql_test( test:do_execsql_test( "in-1.7", [[ - SELECT a+ 100*CAST((a BETWEEN 1 and 3) AS INTEGER) FROM t1 ORDER BY b + SELECT a, a BETWEEN 1 AND 3 FROM t1 ORDER BY b; ]], { -- <in-1.7> - 101, 102, 103, 4, 5, 6, 7, 8, 9, 10 + 1, true, 2, true, 3, true, 4, false, 5, false, + 6, false, 7, false, 8, false, 9, false, 10, false -- </in-1.7> }) @@ -157,10 +158,11 @@ test:do_execsql_test( test:do_execsql_test( "in-2.5", [[ - SELECT a+100*(CAST(b IN (8,16,24) AS INTEGER)) FROM t1 ORDER BY b + SELECT b, b IN (8,16,24) FROM t1 ORDER BY b; ]], { -- <in-2.5> - 1, 2, 103, 104, 5, 6, 7, 8, 9, 10 + 2, false, 4, false, 8, true, 16, true, 32, false, 64, false, + 128, false, 256, false, 512, false, 1024, false -- </in-2.5> }) @@ -207,7 +209,7 @@ test:do_execsql_test( test:do_execsql_test( "in-2.10", [[ - SELECT a FROM t1 WHERE LEAST(0, CAST(b IN (a,30) AS INT)) <> 0 + SELECT a, b FROM t1 WHERE LEAST(TRUE, b IN (a, 30)); ]], { -- <in-2.10> @@ -253,10 +255,11 @@ test:do_execsql_test( test:do_execsql_test( "in-3.3", [[ - SELECT a + 100*(CAST(b IN (SELECT b FROM t1 WHERE a<5) AS INTEGER)) FROM t1 ORDER BY b + SELECT a, b, b IN (SELECT b FROM t1 WHERE a < 5) FROM t1 ORDER BY b; ]], { -- <in-3.3> - 101, 102, 103, 104, 5, 6, 7, 8, 9, 10 + 1, 2, true, 2, 4, true, 3, 8, true, 4, 16, true, 5, 32, false, 6, 64, + false, 7, 128, false, 8, 256, false, 9, 512, false, 10, 1024, false -- </in-3.3> }) diff --git a/test/sql-tap/misc3.test.lua b/test/sql-tap/misc3.test.lua index 313484b5d..944f8bd40 100755 --- a/test/sql-tap/misc3.test.lua +++ b/test/sql-tap/misc3.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool local test = require("sqltester") -test:plan(34) +test:plan(33) --!./tcltestrunner.lua -- 2003 December 17 @@ -507,14 +507,4 @@ test:do_execsql_test( -- </misc-8.1> }) -test:do_execsql_test( - "misc-8.2", - [[ - SELECT count(*) FROM t3 WHERE 1+CAST((b IN ('abc','xyz')) AS INTEGER)==2 - ]], { - -- <misc-8.2> - 2 - -- </misc-8.2> - }) - test:finish_test() diff --git a/test/sql/boolean.result b/test/sql/boolean.result index 5594d92cb..d54de8fe7 100644 --- a/test/sql/boolean.result +++ b/test/sql/boolean.result @@ -500,25 +500,25 @@ INSERT INTO t3 VALUES (4, false) | ... -- Check CAST from BOOLEAN to the other types. -SELECT cast(true AS INTEGER), cast(false AS INTEGER); +SELECT cast(true AS INTEGER); | --- - | - metadata: - | - name: COLUMN_1 - | type: integer - | - name: COLUMN_2 - | type: integer - | rows: - | - [1, 0] + | - null + | - 'Type mismatch: can not convert boolean(TRUE) to integer' | ... -SELECT cast(true AS NUMBER), cast(false AS NUMBER); +SELECT cast(false AS INTEGER); | --- - | - metadata: - | - name: COLUMN_1 - | type: number - | - name: COLUMN_2 - | type: number - | rows: - | - [1, 0] + | - null + | - 'Type mismatch: can not convert boolean(FALSE) to integer' + | ... +SELECT cast(true AS NUMBER); + | --- + | - null + | - 'Type mismatch: can not convert boolean(TRUE) to number' + | ... +SELECT cast(false AS NUMBER); + | --- + | - null + | - 'Type mismatch: can not convert boolean(FALSE) to number' | ... -- gh-4462: ensure that text representation is uppercase. SELECT cast(true AS TEXT), cast(false AS TEXT); @@ -543,27 +543,30 @@ 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); +SELECT cast(100 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 integer(100) to boolean' | ... -SELECT cast(0.123 AS BOOLEAN), cast(0.0 AS BOOLEAN); +SELECT cast(1 AS BOOLEAN); | --- - | - metadata: - | - name: COLUMN_1 - | type: boolean - | - name: COLUMN_2 - | type: boolean - | rows: - | - [true, false] + | - null + | - 'Type mismatch: can not convert integer(1) to boolean' + | ... +SELECT cast(0 AS BOOLEAN); + | --- + | - null + | - 'Type mismatch: can not convert integer(0) to boolean' + | ... +SELECT cast(0.123 AS BOOLEAN); + | --- + | - null + | - 'Type mismatch: can not convert double(0.123) to boolean' + | ... +SELECT cast(0.0 AS BOOLEAN); + | --- + | - null + | - 'Type mismatch: can not convert double(0.0) to boolean' | ... SELECT cast('true' AS BOOLEAN), cast('false' AS BOOLEAN); | --- diff --git a/test/sql/boolean.test.sql b/test/sql/boolean.test.sql index 65a830349..13991ca0c 100644 --- a/test/sql/boolean.test.sql +++ b/test/sql/boolean.test.sql @@ -127,15 +127,20 @@ INSERT INTO t3 VALUES (3, true) INSERT INTO t3 VALUES (4, false) -- Check CAST from BOOLEAN to the other types. -SELECT cast(true AS INTEGER), cast(false AS INTEGER); -SELECT cast(true AS NUMBER), cast(false AS NUMBER); +SELECT cast(true AS INTEGER); +SELECT cast(false AS INTEGER); +SELECT cast(true AS NUMBER); +SELECT cast(false AS NUMBER); -- gh-4462: ensure that text representation is uppercase. SELECT cast(true AS TEXT), cast(false AS TEXT); 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); -SELECT cast(0.123 AS BOOLEAN), cast(0.0 AS BOOLEAN); +SELECT cast(100 AS BOOLEAN); +SELECT cast(1 AS BOOLEAN); +SELECT cast(0 AS BOOLEAN); +SELECT cast(0.123 AS BOOLEAN); +SELECT cast(0.0 AS BOOLEAN); SELECT cast('true' AS BOOLEAN), cast('false' AS BOOLEAN); SELECT cast('TRUE' AS BOOLEAN), cast('FALSE' AS BOOLEAN); diff --git a/test/sql/types.result b/test/sql/types.result index c1e1a8ef1..8da94d126 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 integer(18446744073709551615) to boolean' ... box.execute("SELECT CAST('18446744073709551615' AS INTEGER);") --- @@ -1102,11 +1099,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 boolean(TRUE) to unsigned' ... box.execute("SELECT CAST('123' AS UNSIGNED);") --- -- 2.25.1
next prev parent reply other threads:[~2021-08-02 17:25 UTC|newest] Thread overview: 7+ messages / expand[flat|nested] mbox.gz Atom feed top 2021-08-02 17:25 [Tarantool-patches] [PATCH v2 0/3] Fix explicit casts Mergen Imeev via Tarantool-patches 2021-08-02 17:25 ` Mergen Imeev via Tarantool-patches [this message] 2021-08-02 17:25 ` [Tarantool-patches] [PATCH v2 2/3] sql: disallow explicit cast of VARBINARY to number Mergen Imeev via Tarantool-patches 2021-08-02 17:25 ` [Tarantool-patches] [PATCH v2 3/3] sql: fix STRING to BOOLEAN explicit cast Mergen Imeev via Tarantool-patches -- strict thread matches above, loose matches on Subject: below -- 2021-08-04 8:32 [Tarantool-patches] [PATCH v2 0/3] Fix explicit casts Mergen Imeev via Tarantool-patches 2021-08-04 8:32 ` [Tarantool-patches] [PATCH v2 1/3] sql: disallow explicit cast of BOOLEAN to number Mergen Imeev via Tarantool-patches 2021-07-30 7:11 [Tarantool-patches] [PATCH v2 0/3] Fix explicit casts Mergen Imeev via Tarantool-patches 2021-07-30 7:11 ` [Tarantool-patches] [PATCH v2 1/3] sql: disallow explicit cast of BOOLEAN to number Mergen Imeev via Tarantool-patches 2021-07-27 11:31 [Tarantool-patches] [PATCH v2 0/3] Fix explicit casts Mergen Imeev via Tarantool-patches 2021-07-27 11:31 ` [Tarantool-patches] [PATCH v2 1/3] sql: disallow explicit cast of BOOLEAN to number Mergen Imeev 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=16669f8b0f6b4bca54a57b5706eedf441661edb3.1627925076.git.imeevma@gmail.com \ --to=tarantool-patches@dev.tarantool.org \ --cc=imeevma@tarantool.org \ --cc=imun@tarantool.org \ --subject='Re: [Tarantool-patches] [PATCH v2 1/3] sql: disallow explicit cast of BOOLEAN to number' \ /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