This is an automated email from the ASF dual-hosted git repository. amashenkov pushed a commit to branch ignite-21983 in repository https://gitbox.apache.org/repos/asf/ignite-3.git
commit 607529f94b1572a49b398ec9354b21436e65f643 Author: amashenkov <[email protected]> AuthorDate: Tue May 21 19:23:17 2024 +0300 wip --- .../sql/cast/test_boolean_cast.test | 2 +- .../sql/cast/test_boolean_cast.test_ignore | 2 +- .../sql/types/boolean/test_boolean_type.test | 120 +++++++++++++++++++++ .../sql/types/null/test_boolean_null.test | 6 +- 4 files changed, 125 insertions(+), 5 deletions(-) diff --git a/modules/sql-engine/src/integrationTest/sql/cast/test_boolean_cast.test b/modules/sql-engine/src/integrationTest/sql/cast/test_boolean_cast.test index 05f24475c4..6aa541a340 100644 --- a/modules/sql-engine/src/integrationTest/sql/cast/test_boolean_cast.test +++ b/modules/sql-engine/src/integrationTest/sql/cast/test_boolean_cast.test @@ -1,5 +1,5 @@ # name: test/sql/cast/test_boolean_cast.test -# description: Test boolean casts +# description: SQL feature T031(BOOLEAN data type). Cast operator. # group: [cast] # Ignored with https://issues.apache.org/jira/browse/IGNITE-19331 diff --git a/modules/sql-engine/src/integrationTest/sql/cast/test_boolean_cast.test_ignore b/modules/sql-engine/src/integrationTest/sql/cast/test_boolean_cast.test_ignore index 9a90c2a5cb..3827986b3a 100644 --- a/modules/sql-engine/src/integrationTest/sql/cast/test_boolean_cast.test_ignore +++ b/modules/sql-engine/src/integrationTest/sql/cast/test_boolean_cast.test_ignore @@ -1,5 +1,5 @@ # name: test/sql/cast/test_boolean_cast.test -# description: Test boolean casts +# description: SQL feature T031(BOOLEAN data type). Cast operator. # group: [cast] # Ignored with https://issues.apache.org/jira/browse/IGNITE-19331 diff --git a/modules/sql-engine/src/integrationTest/sql/types/boolean/test_boolean_type.test b/modules/sql-engine/src/integrationTest/sql/types/boolean/test_boolean_type.test new file mode 100644 index 0000000000..0b32409abb --- /dev/null +++ b/modules/sql-engine/src/integrationTest/sql/types/boolean/test_boolean_type.test @@ -0,0 +1,120 @@ +# name: test/sql/cast/test_boolean_type.test +# description: SQL feature T031(BOOLEAN data type) +# group: [cast] + +statement ok +CREATE TABLE test (a INTEGER, b BOOLEAN); + +# Specification does not make a distinction between the null value of the boolean data type and the truth value Unknown. +# They may be used interchangeably to mean exactly the same thing. +# <truth value> ::= +# TRUE +# | FALSE +# | UNKNOWN +statement ok +INSERT INTO test VALUES (1, TRUE), (2, FALSE), (3, NULL); + +# IS [NOT] (TRUE|FALSE|UNKNOWN) +query TT +SELECT b IS TRUE, b IS NOT TRUE FROM test ORDER BY a; +---- +true false +false true +false true + +query TT +SELECT b IS FALSE, b IS NOT FALSE FROM test ORDER BY a; +---- +false true +true false +false true + +skipif ignite3 +# Ignored: lack of supporting UNKNOWN keyword +query TT +SELECT b IS UNKNOWN, b IS NOT UNKNOWN FROM test ORDER BY a; +---- +false true +false true +true false + +# IS [NOT] NULL +query TT +SELECT b IS NULL, b IS NOT NULL FROM test ORDER BY a; +---- +false true +false true +true false + + +# Comparison to null is always unknown +query T +SELECT a FROM test WHERE b = NULL ORDER BY a; +---- + +# The correct way to search NULLs +query T +SELECT a FROM test WHERE b IS NULL ORDER BY a; +---- +3 + +skipif ignite3 +# Ignored: lack of supporting UNKNOWN keyword +query T +SELECT a FROM test WHERE b IS UNKNOWN ORDER BY a; +---- +3 + +# P or not P is not always true. +# The result of the comparison col = null is UNKNOWN in both cases; +# NOT(UNKNOWN) is also UNKNOWN; +# OR only evaluates as true if one operand is true. +query T +SELECT a FROM test WHERE (b = NULL) OR NOT (b = NULL) ORDER BY a; +---- + +# NOT IN (NULL, …) is never true +query T +SELECT a FROM test WHERE b NOT IN (NULL, TRUE) ORDER BY a; +---- + +query T +SELECT a FROM test WHERE b NOT IN (NULL, FALSE) ORDER BY a; +---- + + +# AND +skipif ignite3 +# Ignored: lack of supporting UNKNOWN keyword +query TTT +SELECT b AND TRUE, b AND FALSE, b AND UNKNOWN FROM test ORDER BY a; +---- +true false null +false false false +true false null + + +query TTT +SELECT b AND TRUE, b AND FALSE, b AND (1 = NULL) FROM test ORDER BY a; +---- +true false null +false false false +null false null + + +# OR +skipif ignite3 +# Ignored: lack of supporting UNKNOWN keyword +query TTT +SELECT b OR TRUE, b OR FALSE, b OR UNKNOWN FROM test ORDER BY a; +---- +true true true +true false null +true null null + +query TTT +SELECT b OR TRUE, b OR FALSE, b OR NULL FROM test ORDER BY a; +---- +true true true +true false null +true null null diff --git a/modules/sql-engine/src/integrationTest/sql/types/null/test_boolean_null.test b/modules/sql-engine/src/integrationTest/sql/types/null/test_boolean_null.test index 0f39ce1fa5..8c34751533 100644 --- a/modules/sql-engine/src/integrationTest/sql/types/null/test_boolean_null.test +++ b/modules/sql-engine/src/integrationTest/sql/types/null/test_boolean_null.test @@ -1,5 +1,5 @@ # name: test/sql/types/null/test_boolean_null.test -# description: Booleans and NULLs +# description: SQL feature T031(BOOLEAN data type). Booleans and NULLs # group: [null] statement ok @@ -31,6 +31,6 @@ true false false true # Comparisions query TTTT -SELECT NULL = NULL, NULL <> NULL, 42 = NULL, 42 <> NULL +SELECT NULL = NULL, NULL <> NULL, 42 = NULL, 42 <> NULL, (NULL = 1) OR (1 = 1), (NULL = 1) AND (0 = 1) ---- -NULL NULL NULL NULL +NULL NULL NULL NULL TRUE FALSE
