From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 51D672326C for ; Wed, 24 Jul 2019 07:52:44 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id HHhOi344MSbG for ; Wed, 24 Jul 2019 07:52:44 -0400 (EDT) Received: from smtpng1.m.smailru.net (smtpng1.m.smailru.net [94.100.181.251]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 93462217A5 for ; Wed, 24 Jul 2019 07:52:41 -0400 (EDT) Date: Wed, 24 Jul 2019 14:52:38 +0300 From: Mergen Imeev Subject: [tarantool-patches] Re: [PATCH v1 1/1] sql: test suite for BOOLEAN Message-ID: <20190724115237.GA22402@tarantool.org> References: <2ecdecf63e68a73815c48d9ff369cbd129ad28d6.1563198457.git.imeevma@gmail.com> <20190716095708.GA29957@tarantool.org> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-Subscribe: List-Owner: List-post: List-Archive: To: "n.pettik" Cc: tarantool-patches@freelists.org Hi! Thank you for review. My answers and new patch below. On Thu, Jul 18, 2019 at 09:05:37PM +0300, n.pettik wrote: > > > From 92649b7b53483d4bbb947e300acf569d85e25001 Mon Sep 17 00:00:00 2001 > > Date: Tue, 16 Jul 2019 12:43:51 +0300 > > Subject: [PATCH] sql: test suite for BOOLEAN > > > > This patch provides a test suite that allows us to make sure that > > the SQL BOOLEAN type works as intended. > > > > Part of #4228 > > Could you remove duplicate tests from sql/types.test.lua ? > That is let’s move all tests related to boolean to this suite. > Moved some tests and removed duplicate tests from sql/types.test.lua. > You added a lot of tests checking work with literals - that’s OK, > but I’d like to see the same amount of tests with table columns. Done. > I don’t ask you to duplicate these tests, but come up with new > ones. At the end of letter I suggest several ways to extend this suite. > > I’d say it would be great job if you found a few bugs (at least one). > Not sure if these are bugs: 1) Different results in case of internal convertion from BOOLEAN to TEXT: tarantool> CREATE TABLE t(a BOOLEAN PRIMARY KEY); --- - row_count: 1 ... tarantool> INSERT INTO t VALUES (true), (false); --- - row_count: 2 ... tarantool> SELECT GROUP_CONCAT(a, '+++') FROM t; --- - metadata: - name: GROUP_CONCAT(a, '+++') type: string rows: - ['false+++true'] ... tarantool> SELECT GROUP_CONCAT(CAST(a AS TEXT), '+++') FROM t; --- - metadata: - name: GROUP_CONCAT(CAST(a AS TEXT), '+++') type: string rows: - ['FALSE+++TRUE'] ... 2) CAST() does not cast from BOOLEAN to FLOAT, but it works in case FLOAT casted to BOOLEAN: tarantool> SELECT cast(0.123 AS BOOLEAN), cast(0.0 AS BOOLEAN); --- - metadata: - name: cast(0.123 AS BOOLEAN) type: boolean - name: cast(0.0 AS BOOLEAN) type: boolean rows: - [true, false] ... 3) Operator IN works in one case and doesn't work in another: tarantool> SELECT true in (1,2,3); --- - metadata: - name: true in (1,2,3) type: boolean rows: - [false] ... tarantool> SELECT true IN (VALUES(1), (2), (3)); --- - error: 'Type mismatch: can not convert true to integer' ... 4) Function LENGTH() returns NULL when argument is BOOLEAN: tarantool> SELECT length(false); --- - metadata: - name: length(false) type: integer rows: - [null] ... 5) Operator AND works differently for these cases: tarantool> SELECT 'abc' AND false; --- - metadata: - name: '''abc'' AND false' type: boolean rows: - [false] ... tarantool> SELECT 'abc' AND a FROM t WHERE a == false; --- - error: 'Type mismatch: can not convert abc to boolean' ... > > diff --git a/test/sql/boolean.test.sql b/test/sql/boolean.test.sql > > new file mode 100644 > > index 0000000..7a1a0dc > > --- /dev/null > > +++ b/test/sql/boolean.test.sql > > @@ -0,0 +1,405 @@ > > +-- Create table for tests > > +CREATE TABLE t (a BOOLEAN PRIMARY KEY); > > +INSERT INTO t VALUES (true), (false); > > I’d add cases involving secondary index comprising nulls. > Added in new table t0. > > + > > +-- Create user-defined function. > > +\set language lua > > +function return_type(arg) return type(arg) end > > +box.internal.sql_create_function("return_type", "TEXT", return_type) > > +\set language sql > > + > > +-- Check boolean as WHERE argument. > > +SELECT a FROM t WHERE a; > > +SELECT a FROM t WHERE a != true; > > + > > +-- Check that we can create index on field of type BOOLEAN. > > +CREATE INDEX i1 ON t(a); > > + > > +-- Check boolean as LIMIT argument. > > +SELECT * FROM t LIMIT true; > > +SELECT * FROM t LIMIT false; > > + > > +-- Check boolean as OFFSET argument. > > +SELECT * FROM t LIMIT 1 OFFSET true; > > +SELECT * FROM t LIMIT 1 OFFSET false; > > + > > +-- Check involvance in index search. > > +EXPLAIN QUERY PLAN SELECT a FROM t WHERE a = true; > > + > > +-- Check that ephemeral tables are used with BOOLEAN. > > +\set language lua > > +result = box.execute('EXPLAIN SELECT * FROM (VALUES(true)), t;') > > +i = 0 > > +for _,v in pairs(result.rows) do if (v[2] == 'OpenTEphemeral') then i = i + 1 end end > > +i > 0 > > +\set language sql > > + > > +-- Check BOOLEAN as argument of user-defined function. > > +SELECT return_type(a) FROM t; > > What about functions returning boolean type? > Fixed. > > + > > +-- Check BOOLEAN as argument of scalar function. > > +SELECT typeof(a) FROM t; > > + > > +-- Check BOOLEAN as argument of aggregate function. > > +SELECT count(a) FROM t GROUP BY a; > > Why only one aggregate? > Fixed. > > + > > +-- Check UNION, UNION ALL AND INTERSECT. > > +INSERT INTO t5 VALUES (100, false); > > +SELECT * FROM t4 UNION SELECT * FROM t5; > > +SELECT * FROM t4 UNION ALL SELECT * FROM t5; > > +SELECT * FROM t4 INTERSECT SELECT * FROM t5; > > + > > +-- Check SUBSELECT. > > +INSERT INTO t5(b) SELECT a FROM t4; > > +SELECT * FROM t5; > > + > > +-- Check VIEW. > > +CREATE VIEW v AS SELECT b FROM t5; > > +SELECT * FROM v; > > + > > +-- Check DISTINCT. > > +SELECT DISTINCT * FROM v; > > Add tests on CTE, joins, UPDATE, switch-case, > ORDER BY (which again requires check of OP_Sort), > more sophisticated subquery examples. > Fixed. New patch: >From 0fad3253438db915813d107dffb9db6406d9cc4d Mon Sep 17 00:00:00 2001 From: Mergen Imeev Date: Tue, 16 Jul 2019 12:43:51 +0300 Subject: [PATCH] sql: test suite for BOOLEAN This patch provides a test suite that allows us to make sure that the SQL BOOLEAN type works as intended. Part of #4228 diff --git a/test/sql/boolean.result b/test/sql/boolean.result new file mode 100644 index 0000000..29dc94c --- /dev/null +++ b/test/sql/boolean.result @@ -0,0 +1,5442 @@ +-- test-run result file version 2 +-- Create table for tests +CREATE TABLE t (a BOOLEAN PRIMARY KEY); + | --- + | - row_count: 1 + | ... +INSERT INTO t VALUES (true), (false); + | --- + | - row_count: 2 + | ... + +-- Create user-defined function. +\set language lua + | --- + | - true + | ... +function return_type(arg) return type(arg) end + | --- + | ... +function is_boolean(arg) return type(arg) == 'boolean' end + | --- + | ... +box.internal.sql_create_function("return_type", "TEXT", return_type) + | --- + | ... +box.internal.sql_create_function("is_boolean", "BOOLEAN", is_boolean) + | --- + | ... +\set language sql + | --- + | - true + | ... + +-- Check boolean as WHERE argument. +SELECT a FROM t WHERE a; + | --- + | - metadata: + | - name: A + | type: boolean + | rows: + | - [true] + | ... +SELECT a FROM t WHERE a != true; + | --- + | - metadata: + | - name: A + | type: boolean + | rows: + | - [false] + | ... + +-- Check DEFAULT values for boolean. +CREATE TABLE t0 (i INT PRIMARY KEY, a BOOLEAN DEFAULT true); + | --- + | - row_count: 1 + | ... +INSERT INTO t0 VALUES (1, false); + | --- + | - row_count: 1 + | ... +INSERT INTO t0(i) VALUES (2); + | --- + | - row_count: 1 + | ... +INSERT INTO t0 VALUES (3, NULL); + | --- + | - row_count: 1 + | ... +SELECT * FROM t0; + | --- + | - metadata: + | - name: I + | type: integer + | - name: A + | type: boolean + | rows: + | - [1, false] + | - [2, true] + | - [3, null] + | ... + +-- Check UNKNOWN value for boolean. +INSERT INTO t0 VALUES (4, UNKNOWN); + | --- + | - row_count: 1 + | ... +SELECT * FROM t0; + | --- + | - metadata: + | - name: I + | type: integer + | - name: A + | type: boolean + | rows: + | - [1, false] + | - [2, true] + | - [3, null] + | - [4, null] + | ... + +-- Make sure that SCALAR can handle boolean values. +CREATE TABLE ts (id INT PRIMARY KEY AUTOINCREMENT, s SCALAR); + | --- + | - row_count: 1 + | ... +INSERT INTO ts SELECT * FROM t0; + | --- + | - row_count: 4 + | ... +SELECT s FROM ts WHERE s = true; + | --- + | - metadata: + | - name: S + | type: scalar + | rows: + | - [true] + | ... +INSERT INTO ts(s) VALUES ('abc'), (12.5); + | --- + | - autoincrement_ids: + | - 5 + | - 6 + | row_count: 2 + | ... +SELECT s FROM ts WHERE s = true; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT s FROM ts WHERE s < true; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT s FROM ts WHERE s IN (true, 1, 'abcd'); + | --- + | - metadata: + | - name: S + | type: scalar + | rows: + | - [true] + | ... + +-- +-- Make sure that BOOLEAN is not implicitly converted to INTEGER +-- while inserted to PRIMARY KEY field. +-- +INSERT INTO ts VALUES (true, 12345); + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... + +-- Check that we can create index on field of type BOOLEAN. +CREATE INDEX i0 ON t0(a); + | --- + | - row_count: 1 + | ... + +-- Check boolean as LIMIT argument. +SELECT * FROM t LIMIT true; + | --- + | - error: 'Failed to execute SQL statement: Only positive integers are allowed in the + | LIMIT clause' + | ... +SELECT * FROM t LIMIT false; + | --- + | - error: 'Failed to execute SQL statement: Only positive integers are allowed in the + | LIMIT clause' + | ... + +-- Check boolean as OFFSET argument. +SELECT * FROM t LIMIT 1 OFFSET true; + | --- + | - error: 'Failed to execute SQL statement: Only positive integers are allowed in the + | OFFSET clause' + | ... +SELECT * FROM t LIMIT 1 OFFSET false; + | --- + | - error: 'Failed to execute SQL statement: Only positive integers are allowed in the + | OFFSET clause' + | ... + +-- Check involvance in index search. +EXPLAIN QUERY PLAN SELECT a FROM t0 WHERE a = true; + | --- + | - metadata: + | - name: selectid + | type: INTEGER + | - name: order + | type: INTEGER + | - name: from + | type: INTEGER + | - name: detail + | type: TEXT + | rows: + | - [0, 0, 0, 'SEARCH TABLE T0 USING COVERING INDEX I0 (A=?) (~10 rows)'] + | ... + +-- Check that ephemeral tables are used with BOOLEAN. +\set language lua + | --- + | - true + | ... +result = box.execute('EXPLAIN SELECT * FROM (VALUES(true)), t;') + | --- + | ... +i = 0 + | --- + | ... +for _,v in pairs(result.rows) do if (v[2] == 'OpenTEphemeral') then i = i + 1 end end + | --- + | ... +i > 0 + | --- + | - true + | ... +\set language sql + | --- + | - true + | ... + +-- Check BOOLEAN as argument of user-defined function. +SELECT return_type(a) FROM t; + | --- + | - metadata: + | - name: return_type(a) + | type: string + | rows: + | - ['boolean'] + | - ['boolean'] + | ... +SELECT return_type('false'); + | --- + | - metadata: + | - name: return_type('false') + | type: string + | rows: + | - ['string'] + | ... +SELECT is_boolean(a) FROM t LIMIT 1; + | --- + | - metadata: + | - name: is_boolean(a) + | type: boolean + | rows: + | - [true] + | ... +SELECT is_boolean('true'); + | --- + | - metadata: + | - name: is_boolean('true') + | type: boolean + | rows: + | - [false] + | ... + +-- Check BOOLEAN as argument of scalar function. +SELECT abs(a) FROM t0; + | --- + | - error: 'Inconsistent types: expected number got boolean' + | ... +SELECT lower(a) FROM t0; + | --- + | - metadata: + | - name: lower(a) + | type: string + | rows: + | - ['false'] + | - ['true'] + | - [null] + | - [null] + | ... +SELECT upper(a) FROM t0; + | --- + | - metadata: + | - name: upper(a) + | type: string + | rows: + | - ['FALSE'] + | - ['TRUE'] + | - [null] + | - [null] + | ... +SELECT quote(a) FROM t0; + | --- + | - metadata: + | - name: quote(a) + | type: string + | rows: + | - ['false'] + | - ['true'] + | - ['NULL'] + | - ['NULL'] + | ... +SELECT length(a) FROM t0; + | --- + | - metadata: + | - name: length(a) + | type: integer + | rows: + | - [null] + | - [null] + | - [null] + | - [null] + | ... +SELECT typeof(a) FROM t0; + | --- + | - metadata: + | - name: typeof(a) + | type: string + | rows: + | - ['boolean'] + | - ['boolean'] + | - ['null'] + | - ['null'] + | ... + +-- Check BOOLEAN as argument of aggregate function. +SELECT AVG(a) FROM t0; + | --- + | - error: 'Type mismatch: can not convert false to number' + | ... +SELECT MIN(a) FROM t0; + | --- + | - metadata: + | - name: MIN(a) + | type: scalar + | rows: + | - [false] + | ... +SELECT MAX(a) FROM t0; + | --- + | - metadata: + | - name: MAX(a) + | type: scalar + | rows: + | - [true] + | ... +SELECT SUM(a) FROM t0; + | --- + | - error: 'Type mismatch: can not convert false to number' + | ... +SELECT COUNT(a) FROM t0; + | --- + | - metadata: + | - name: COUNT(a) + | type: integer + | rows: + | - [2] + | ... +SELECT TOTAL(a) FROM t0; + | --- + | - error: 'Type mismatch: can not convert false to number' + | ... +SELECT GROUP_CONCAT(a, ' +++ ') FROM t0; + | --- + | - metadata: + | - name: GROUP_CONCAT(a, ' +++ ') + | type: string + | rows: + | - ['false +++ true'] + | ... + +-- Check BOOLEAN as binding parameter. +\set language lua + | --- + | - true + | ... +box.execute('SELECT ?, ?, return_type($1), typeof($2);', {true, false}) + | --- + | - metadata: + | - name: '?' + | type: BOOLEAN + | - name: '?' + | type: BOOLEAN + | - name: return_type($1) + | type: string + | - name: typeof($2) + | type: string + | rows: + | - [true, false, 'boolean', 'boolean'] + | ... + +parameters = {} + | --- + | ... +parameters[1] = {} + | --- + | ... +parameters[1]['@value2'] = true + | --- + | ... +parameters[2] = {} + | --- + | ... +parameters[2][':value1'] = false + | --- + | ... +box.execute('SELECT :value1, @value2;', parameters) + | --- + | - metadata: + | - name: :value1 + | type: BOOLEAN + | - name: '@value2' + | type: BOOLEAN + | rows: + | - [false, true] + | ... +\set language sql + | --- + | - true + | ... + +-- Check interactions with CHECK constraint. +CREATE TABLE t1 (i INT PRIMARY KEY, a BOOLEAN, CONSTRAINT ck CHECK(a != true)); + | --- + | - row_count: 1 + | ... +INSERT INTO t1 VALUES (1, false); + | --- + | - row_count: 1 + | ... +INSERT INTO t1 VALUES (2, true); + | --- + | - error: 'Check constraint failed ''CK'': a != true' + | ... + +-- Check interactions with FOREIGN KEY constraint. +CREATE TABLE t2 (a BOOLEAN PRIMARY KEY, b BOOLEAN REFERENCES t2(a)); + | --- + | - row_count: 1 + | ... +INSERT INTO t2 VALUES (false, true) + | --- + | - error: 'Failed to execute SQL statement: FOREIGN KEY constraint failed' + | ... +INSERT INTO t2 VALUES (true, false) + | --- + | - error: 'Failed to execute SQL statement: FOREIGN KEY constraint failed' + | ... +INSERT INTO t2 VALUES (true, true) + | --- + | - row_count: 1 + | ... +INSERT INTO t2 VALUES (false, true) + | --- + | - row_count: 1 + | ... + +-- Check interactions with UNIQUE constraint. +CREATE TABLE t3 (i INT PRIMARY KEY, a BOOLEAN, CONSTRAINT uq UNIQUE(a)); + | --- + | - row_count: 1 + | ... +INSERT INTO t3 VALUES (1, true) + | --- + | - row_count: 1 + | ... +INSERT INTO t3 VALUES (2, false) + | --- + | - row_count: 1 + | ... +INSERT INTO t3 VALUES (3, true) + | --- + | - error: Duplicate key exists in unique index 'unique_UQ_2' in space 'T3' + | ... +INSERT INTO t3 VALUES (4, false) + | --- + | - error: Duplicate key exists in unique index 'unique_UQ_2' in space 'T3' + | ... + +-- Check CAST from BOOLEAN to the other types. +SELECT cast(true AS INTEGER), cast(false AS INTEGER); + | --- + | - metadata: + | - name: cast(true AS INTEGER) + | type: integer + | - name: cast(false AS INTEGER) + | type: integer + | rows: + | - [1, 0] + | ... +SELECT cast(true AS REAL), cast(false AS REAL); + | --- + | - error: 'Type mismatch: can not convert true to number' + | ... +SELECT cast(true AS TEXT), cast(false AS TEXT); + | --- + | - metadata: + | - name: cast(true AS TEXT) + | type: string + | - name: cast(false AS TEXT) + | type: string + | rows: + | - ['TRUE', 'FALSE'] + | ... +SELECT cast(true AS BOOLEAN), cast(false AS BOOLEAN); + | --- + | - metadata: + | - name: cast(true AS BOOLEAN) + | type: boolean + | - name: cast(false AS BOOLEAN) + | type: boolean + | rows: + | - [true, false] + | ... + +-- Check CAST to BOOLEAN from the other types. +SELECT cast(100 AS BOOLEAN), cast(1 AS BOOLEAN), cast(0 AS BOOLEAN); + | --- + | - metadata: + | - name: cast(100 AS BOOLEAN) + | type: boolean + | - name: cast(1 AS BOOLEAN) + | type: boolean + | - name: cast(0 AS BOOLEAN) + | type: boolean + | rows: + | - [true, true, false] + | ... +SELECT cast(0.123 AS BOOLEAN), cast(0.0 AS BOOLEAN); + | --- + | - metadata: + | - name: cast(0.123 AS BOOLEAN) + | type: boolean + | - name: cast(0.0 AS BOOLEAN) + | type: boolean + | rows: + | - [true, false] + | ... +SELECT cast('true' AS BOOLEAN), cast('false' AS BOOLEAN); + | --- + | - metadata: + | - name: cast('true' AS BOOLEAN) + | type: boolean + | - name: cast('false' AS BOOLEAN) + | type: boolean + | rows: + | - [true, false] + | ... +SELECT cast('TRUE' AS BOOLEAN), cast('FALSE' AS BOOLEAN); + | --- + | - metadata: + | - name: cast('TRUE' AS BOOLEAN) + | type: boolean + | - name: cast('FALSE' AS BOOLEAN) + | type: boolean + | rows: + | - [true, false] + | ... + +-- Check usage in trigger. +CREATE TABLE t4 (i INT PRIMARY KEY, a BOOLEAN); + | --- + | - row_count: 1 + | ... +CREATE TABLE t5 (i INT PRIMARY KEY AUTOINCREMENT, b BOOLEAN); + | --- + | - row_count: 1 + | ... +CREATE TRIGGER r AFTER INSERT ON t4 FOR EACH ROW BEGIN INSERT INTO t5(b) VALUES(true); END; + | --- + | - row_count: 1 + | ... +INSERT INTO t4 VALUES (100, false); + | --- + | - autoincrement_ids: + | - 1 + | row_count: 1 + | ... +DROP TRIGGER r; + | --- + | - row_count: 1 + | ... +SELECT * FROM t4; + | --- + | - metadata: + | - name: I + | type: integer + | - name: A + | type: boolean + | rows: + | - [100, false] + | ... +SELECT * FROM t5; + | --- + | - metadata: + | - name: I + | type: integer + | - name: B + | type: boolean + | rows: + | - [1, true] + | ... + +-- Check UNION, UNION ALL AND INTERSECT. +INSERT INTO t5 VALUES (100, false); + | --- + | - row_count: 1 + | ... +SELECT * FROM t4 UNION SELECT * FROM t5; + | --- + | - metadata: + | - name: I + | type: integer + | - name: A + | type: boolean + | rows: + | - [1, true] + | - [100, false] + | ... +SELECT * FROM t4 UNION ALL SELECT * FROM t5; + | --- + | - metadata: + | - name: I + | type: integer + | - name: A + | type: boolean + | rows: + | - [100, false] + | - [1, true] + | - [100, false] + | ... +SELECT * FROM t4 INTERSECT SELECT * FROM t5; + | --- + | - metadata: + | - name: I + | type: integer + | - name: A + | type: boolean + | rows: + | - [100, false] + | ... + +-- Check SUBSELECT. +INSERT INTO t5(b) SELECT a FROM t4; + | --- + | - autoincrement_ids: + | - 101 + | row_count: 1 + | ... +SELECT * FROM t5; + | --- + | - metadata: + | - name: I + | type: integer + | - name: B + | type: boolean + | rows: + | - [1, true] + | - [100, false] + | - [101, false] + | ... + +SELECT * FROM (SELECT t4.i, t5.i, a, b FROM t4, t5 WHERE a = false OR b = true); + | --- + | - metadata: + | - name: I + | type: integer + | - name: I_1 + | type: integer + | - name: A + | type: boolean + | - name: B + | type: boolean + | rows: + | - [100, 1, false, true] + | - [100, 100, false, false] + | - [100, 101, false, false] + | ... + +-- Check VIEW. +CREATE VIEW v AS SELECT b FROM t5; + | --- + | - row_count: 1 + | ... +SELECT * FROM v; + | --- + | - metadata: + | - name: B + | type: boolean + | rows: + | - [true] + | - [false] + | - [false] + | ... + +-- Check DISTINCT. +SELECT DISTINCT * FROM v; + | --- + | - metadata: + | - name: B + | type: boolean + | rows: + | - [true] + | - [false] + | ... + +-- Check CTE +WITH temp(x, y) AS (VALUES (111, false) UNION ALL VALUES (222, true)) \ +INSERT INTO t4 SELECT * from temp; + | --- + | - row_count: 2 + | ... +SELECT * FROM t4; + | --- + | - metadata: + | - name: I + | type: integer + | - name: A + | type: boolean + | rows: + | - [100, false] + | - [111, false] + | - [222, true] + | ... + +WITH RECURSIVE cnt(x, y) AS \ +(VALUES(1, false) UNION ALL SELECT x+1, x % 2 == 1 FROM cnt WHERE x<10) \ +SELECT x, y FROM cnt; + | --- + | - metadata: + | - name: X + | type: integer + | - name: Y + | type: boolean + | rows: + | - [1, false] + | - [2, true] + | - [3, false] + | - [4, true] + | - [5, false] + | - [6, true] + | - [7, false] + | - [8, true] + | - [9, false] + | - [10, true] + | ... + +-- Check JOINs. +SELECT * FROM t4 JOIN t5 ON t4.a = t5.b; + | --- + | - metadata: + | - name: I + | type: integer + | - name: A + | type: boolean + | - name: I + | type: integer + | - name: B + | type: boolean + | rows: + | - [100, false, 100, false] + | - [100, false, 101, false] + | - [111, false, 100, false] + | - [111, false, 101, false] + | - [222, true, 1, true] + | ... +SELECT * FROM t4 LEFT JOIN t5 ON t4.a = t5.b; + | --- + | - metadata: + | - name: I + | type: integer + | - name: A + | type: boolean + | - name: I + | type: integer + | - name: B + | type: boolean + | rows: + | - [100, false, 100, false] + | - [100, false, 101, false] + | - [111, false, 100, false] + | - [111, false, 101, false] + | - [222, true, 1, true] + | ... +SELECT * FROM t4 INNER JOIN t5 ON t4.a = t5.b; + | --- + | - metadata: + | - name: I + | type: integer + | - name: A + | type: boolean + | - name: I + | type: integer + | - name: B + | type: boolean + | rows: + | - [100, false, 100, false] + | - [100, false, 101, false] + | - [111, false, 100, false] + | - [111, false, 101, false] + | - [222, true, 1, true] + | ... + +-- Check UPDATE. +UPDATE t4 SET a = NOT a; + | --- + | - row_count: 3 + | ... +SELECT * FROM t4; + | --- + | - metadata: + | - name: I + | type: integer + | - name: A + | type: boolean + | rows: + | - [100, true] + | - [111, true] + | - [222, false] + | ... + +-- Check SWITCH-CASE. +SELECT i, \ +CASE \ + WHEN a == true AND i % 2 == 1 THEN false \ + WHEN a == true and i % 2 == 0 THEN true \ + WHEN a != true then false \ +END AS a0 \ +FROM t4; + | --- + | - metadata: + | - name: I + | type: integer + | - name: A0 + | type: boolean + | rows: + | - [100, true] + | - [111, false] + | - [222, false] + | ... + +-- Check OPDER BY. +SELECT * FROM t4 UNION SELECT * FROM t5 ORDER BY a, i; + | --- + | - metadata: + | - name: I + | type: integer + | - name: A + | type: boolean + | rows: + | - [100, false] + | - [101, false] + | - [222, false] + | - [1, true] + | - [100, true] + | - [111, true] + | ... + +-- Check GROUP BY. +SELECT a, COUNT(*) FROM (SELECT * FROM t4 UNION SELECT * FROM t5) GROUP BY a; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: COUNT(*) + | type: integer + | rows: + | - [false, 3] + | - [true, 3] + | ... + +-- Check logical, bitwise and arithmetical operations. +CREATE TABLE t6 (a1 BOOLEAN PRIMARY KEY, a2 BOOLEAN); + | --- + | - row_count: 1 + | ... +INSERT INTO t6 VALUES (true, false), (false, true); + | --- + | - row_count: 2 + | ... + +SELECT NOT true; + | --- + | - metadata: + | - name: NOT true + | type: boolean + | rows: + | - [false] + | ... +SELECT NOT false; + | --- + | - metadata: + | - name: NOT false + | type: boolean + | rows: + | - [true] + | ... +SELECT a, NOT a FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: NOT a + | type: boolean + | rows: + | - [false, true] + | - [true, false] + | ... + +SELECT true AND true; + | --- + | - metadata: + | - name: true AND true + | type: boolean + | rows: + | - [true] + | ... +SELECT true AND false; + | --- + | - metadata: + | - name: true AND false + | type: boolean + | rows: + | - [false] + | ... +SELECT false AND true; + | --- + | - metadata: + | - name: false AND true + | type: boolean + | rows: + | - [false] + | ... +SELECT false AND false; + | --- + | - metadata: + | - name: false AND false + | type: boolean + | rows: + | - [false] + | ... +SELECT true OR true; + | --- + | - metadata: + | - name: true OR true + | type: boolean + | rows: + | - [true] + | ... +SELECT true OR false; + | --- + | - metadata: + | - name: true OR false + | type: boolean + | rows: + | - [true] + | ... +SELECT false OR true; + | --- + | - metadata: + | - name: false OR true + | type: boolean + | rows: + | - [true] + | ... +SELECT false OR false; + | --- + | - metadata: + | - name: false OR false + | type: boolean + | rows: + | - [false] + | ... + +SELECT a, true AND a FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: true AND a + | type: boolean + | rows: + | - [false, false] + | - [true, true] + | ... +SELECT a, false AND a FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: false AND a + | type: boolean + | rows: + | - [false, false] + | - [true, false] + | ... +SELECT a, true OR a FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: true OR a + | type: boolean + | rows: + | - [false, true] + | - [true, true] + | ... +SELECT a, false OR a FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: false OR a + | type: boolean + | rows: + | - [false, false] + | - [true, true] + | ... +SELECT a, a AND true FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a AND true + | type: boolean + | rows: + | - [false, false] + | - [true, true] + | ... +SELECT a, a AND false FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a AND false + | type: boolean + | rows: + | - [false, false] + | - [true, false] + | ... +SELECT a, a OR true FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a OR true + | type: boolean + | rows: + | - [false, true] + | - [true, true] + | ... +SELECT a, a OR false FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a OR false + | type: boolean + | rows: + | - [false, false] + | - [true, true] + | ... + +SELECT a, a1, a AND a1 FROM t, t6; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: A1 + | type: boolean + | - name: a AND a1 + | type: boolean + | rows: + | - [false, false, false] + | - [false, true, false] + | - [true, false, false] + | - [true, true, true] + | ... +SELECT a, a1, a OR a1 FROM t, t6; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: A1 + | type: boolean + | - name: a OR a1 + | type: boolean + | rows: + | - [false, false, false] + | - [false, true, true] + | - [true, false, true] + | - [true, true, true] + | ... + +SELECT -true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT -false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT -a FROM t; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... + +SELECT true + true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT true + false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT false + true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT false + false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT true - true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT true - false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT false - true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT false - false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT true * true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT true * false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT false * true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT false * false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT true / true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT true / false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT false / true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT false / false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT true % true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT true % false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT false % true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT false % false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... + +SELECT a, true + a FROM t; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a, false + a FROM t; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a, true - a FROM t; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a, false - a FROM t; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a, true * a FROM t; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a, false * a FROM t; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a, true / a FROM t; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a, false / a FROM t; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a, true % a FROM t; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a, false % a FROM t; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a, a + true FROM t; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a, a + false FROM t; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a, a - true FROM t; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a, a - false FROM t; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a, a * true FROM t; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a, a * false FROM t; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a, a / true FROM t; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a, a / false FROM t; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a, a % true FROM t; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a, a % false FROM t; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... + +SELECT a, a1, a + a1 FROM t, t6; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a, a1, a - a1 FROM t, t6; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a, a1, a * a1 FROM t, t6; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a, a1, a / a1 FROM t, t6; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a, a1, a % a1 FROM t, t6; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... + +SELECT ~true; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT ~false; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT true & true; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT true & false; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT false & true; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT false & false; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT true | true; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT true | false; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT false | true; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT false | false; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT true << true; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT true << false; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT false << true; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT false << false; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT true >> true; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT true >> false; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT false >> true; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT false >> false; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... + +SELECT a, true & a FROM t; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT a, false & a FROM t; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a, true | a FROM t; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT a, false | a FROM t; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a, true << a FROM t; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT a, false << a FROM t; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a, true >> a FROM t; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT a, false >> a FROM t; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a, a & true FROM t; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a, a & false FROM t; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a, a | true FROM t; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a, a | false FROM t; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a, a << true FROM t; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a, a << false FROM t; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a, a >> true FROM t; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a, a >> false FROM t; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... + +SELECT a, a1, a & a1 FROM t, t6; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a, a1, a | a1 FROM t, t6; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a, a1, a << a1 FROM t, t6; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a, a1, a >> a1 FROM t, t6; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... + +-- Check concatenate. +SELECT true || true; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +SELECT true || false; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +SELECT false || true; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +SELECT false || false; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... + +SELECT a, true || a FROM t; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +SELECT a, false || a FROM t; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +SELECT a, a || true FROM t; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +SELECT a, a || false FROM t; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... + +SELECT a1, a1 || a1 FROM t6; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +SELECT a2, a2 || a2 FROM t6; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +SELECT a1, a2, a1 || a1 FROM t6; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +SELECT a1, a2, a2 || a2 FROM t6; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... + +-- Check comparisons. +SELECT true > true; + | --- + | - metadata: + | - name: true > true + | type: boolean + | rows: + | - [false] + | ... +SELECT true > false; + | --- + | - metadata: + | - name: true > false + | type: boolean + | rows: + | - [true] + | ... +SELECT false > true; + | --- + | - metadata: + | - name: false > true + | type: boolean + | rows: + | - [false] + | ... +SELECT false > false; + | --- + | - metadata: + | - name: false > false + | type: boolean + | rows: + | - [false] + | ... +SELECT true < true; + | --- + | - metadata: + | - name: true < true + | type: boolean + | rows: + | - [false] + | ... +SELECT true < false; + | --- + | - metadata: + | - name: true < false + | type: boolean + | rows: + | - [false] + | ... +SELECT false < true; + | --- + | - metadata: + | - name: false < true + | type: boolean + | rows: + | - [true] + | ... +SELECT false < false; + | --- + | - metadata: + | - name: false < false + | type: boolean + | rows: + | - [false] + | ... + +SELECT a, true > a FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: true > a + | type: boolean + | rows: + | - [false, true] + | - [true, false] + | ... +SELECT a, false > a FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: false > a + | type: boolean + | rows: + | - [false, false] + | - [true, false] + | ... +SELECT a, true < a FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: true < a + | type: boolean + | rows: + | - [false, false] + | - [true, false] + | ... +SELECT a, false < a FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: false < a + | type: boolean + | rows: + | - [false, false] + | - [true, true] + | ... +SELECT a, a > true FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a > true + | type: boolean + | rows: + | - [false, false] + | - [true, false] + | ... +SELECT a, a > false FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a > false + | type: boolean + | rows: + | - [false, false] + | - [true, true] + | ... +SELECT a, a < true FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a < true + | type: boolean + | rows: + | - [false, true] + | - [true, false] + | ... +SELECT a, a < false FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a < false + | type: boolean + | rows: + | - [false, false] + | - [true, false] + | ... + +SELECT a, a1, a > a1 FROM t, t6; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: A1 + | type: boolean + | - name: a > a1 + | type: boolean + | rows: + | - [false, false, false] + | - [false, true, false] + | - [true, false, true] + | - [true, true, false] + | ... +SELECT a, a1, a < a1 FROM t, t6; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: A1 + | type: boolean + | - name: a < a1 + | type: boolean + | rows: + | - [false, false, false] + | - [false, true, true] + | - [true, false, false] + | - [true, true, false] + | ... + +SELECT true >= true; + | --- + | - metadata: + | - name: true >= true + | type: any + | rows: + | - [true] + | ... +SELECT true >= false; + | --- + | - metadata: + | - name: true >= false + | type: any + | rows: + | - [true] + | ... +SELECT false >= true; + | --- + | - metadata: + | - name: false >= true + | type: any + | rows: + | - [false] + | ... +SELECT false >= false; + | --- + | - metadata: + | - name: false >= false + | type: any + | rows: + | - [true] + | ... +SELECT true <= true; + | --- + | - metadata: + | - name: true <= true + | type: boolean + | rows: + | - [true] + | ... +SELECT true <= false; + | --- + | - metadata: + | - name: true <= false + | type: boolean + | rows: + | - [false] + | ... +SELECT false <= true; + | --- + | - metadata: + | - name: false <= true + | type: boolean + | rows: + | - [true] + | ... +SELECT false <= false; + | --- + | - metadata: + | - name: false <= false + | type: boolean + | rows: + | - [true] + | ... + +SELECT a, true >= a FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: true >= a + | type: any + | rows: + | - [false, true] + | - [true, true] + | ... +SELECT a, false >= a FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: false >= a + | type: any + | rows: + | - [false, true] + | - [true, false] + | ... +SELECT a, true <= a FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: true <= a + | type: boolean + | rows: + | - [false, false] + | - [true, true] + | ... +SELECT a, false <= a FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: false <= a + | type: boolean + | rows: + | - [false, true] + | - [true, true] + | ... +SELECT a, a >= true FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a >= true + | type: any + | rows: + | - [false, false] + | - [true, true] + | ... +SELECT a, a >= false FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a >= false + | type: any + | rows: + | - [false, true] + | - [true, true] + | ... +SELECT a, a <= true FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a <= true + | type: boolean + | rows: + | - [false, true] + | - [true, true] + | ... +SELECT a, a <= false FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a <= false + | type: boolean + | rows: + | - [false, true] + | - [true, false] + | ... + +SELECT a, a1, a >= a1 FROM t, t6; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: A1 + | type: boolean + | - name: a >= a1 + | type: any + | rows: + | - [false, false, true] + | - [false, true, false] + | - [true, false, true] + | - [true, true, true] + | ... +SELECT a, a1, a <= a1 FROM t, t6; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: A1 + | type: boolean + | - name: a <= a1 + | type: boolean + | rows: + | - [false, false, true] + | - [false, true, true] + | - [true, false, false] + | - [true, true, true] + | ... + +SELECT true == true; + | --- + | - metadata: + | - name: true == true + | type: boolean + | rows: + | - [true] + | ... +SELECT true == false; + | --- + | - metadata: + | - name: true == false + | type: boolean + | rows: + | - [false] + | ... +SELECT false == true; + | --- + | - metadata: + | - name: false == true + | type: boolean + | rows: + | - [false] + | ... +SELECT false == false; + | --- + | - metadata: + | - name: false == false + | type: boolean + | rows: + | - [true] + | ... +SELECT true != true; + | --- + | - metadata: + | - name: true != true + | type: boolean + | rows: + | - [false] + | ... +SELECT true != false; + | --- + | - metadata: + | - name: true != false + | type: boolean + | rows: + | - [true] + | ... +SELECT false != true; + | --- + | - metadata: + | - name: false != true + | type: boolean + | rows: + | - [true] + | ... +SELECT false != false; + | --- + | - metadata: + | - name: false != false + | type: boolean + | rows: + | - [false] + | ... + +SELECT a, true == a FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: true == a + | type: boolean + | rows: + | - [false, false] + | - [true, true] + | ... +SELECT a, false == a FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: false == a + | type: boolean + | rows: + | - [false, true] + | - [true, false] + | ... +SELECT a, true != a FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: true != a + | type: boolean + | rows: + | - [false, true] + | - [true, false] + | ... +SELECT a, false != a FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: false != a + | type: boolean + | rows: + | - [false, false] + | - [true, true] + | ... +SELECT a, a == true FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a == true + | type: boolean + | rows: + | - [false, false] + | - [true, true] + | ... +SELECT a, a == false FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a == false + | type: boolean + | rows: + | - [false, true] + | - [true, false] + | ... +SELECT a, a != true FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a != true + | type: boolean + | rows: + | - [false, true] + | - [true, false] + | ... +SELECT a, a != false FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a != false + | type: boolean + | rows: + | - [false, false] + | - [true, true] + | ... + +SELECT a, a1, a == a1 FROM t, t6; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: A1 + | type: boolean + | - name: a == a1 + | type: boolean + | rows: + | - [false, false, true] + | - [false, true, false] + | - [true, false, false] + | - [true, true, true] + | ... +SELECT a, a1, a != a1 FROM t, t6; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: A1 + | type: boolean + | - name: a != a1 + | type: boolean + | rows: + | - [false, false, false] + | - [false, true, true] + | - [true, false, true] + | - [true, true, false] + | ... + +SELECT true IN (true); + | --- + | - metadata: + | - name: true IN (true) + | type: boolean + | rows: + | - [true] + | ... +SELECT false IN (true); + | --- + | - metadata: + | - name: false IN (true) + | type: boolean + | rows: + | - [false] + | ... +SELECT true IN (false); + | --- + | - metadata: + | - name: true IN (false) + | type: boolean + | rows: + | - [false] + | ... +SELECT false IN (false); + | --- + | - metadata: + | - name: false IN (false) + | type: boolean + | rows: + | - [true] + | ... +SELECT true IN (true, false); + | --- + | - metadata: + | - name: true IN (true, false) + | type: boolean + | rows: + | - [true] + | ... +SELECT false IN (true, false); + | --- + | - metadata: + | - name: false IN (true, false) + | type: boolean + | rows: + | - [true] + | ... +SELECT true IN (SELECT a1 FROM t6); + | --- + | - metadata: + | - name: true IN (SELECT a1 FROM t6) + | type: boolean + | rows: + | - [true] + | ... +SELECT false IN (SELECT a1 FROM t6); + | --- + | - metadata: + | - name: false IN (SELECT a1 FROM t6) + | type: boolean + | rows: + | - [true] + | ... +SELECT true IN (SELECT a1 FROM t6 LIMIT 1); + | --- + | - metadata: + | - name: true IN (SELECT a1 FROM t6 LIMIT 1) + | type: boolean + | rows: + | - [false] + | ... +SELECT false IN (SELECT a1 FROM t6 LIMIT 1); + | --- + | - metadata: + | - name: false IN (SELECT a1 FROM t6 LIMIT 1) + | type: boolean + | rows: + | - [true] + | ... +SELECT true IN (1, 1.2, 'true', false); + | --- + | - metadata: + | - name: true IN (1, 1.2, 'true', false) + | type: boolean + | rows: + | - [false] + | ... +SELECT false IN (1, 1.2, 'true', false); + | --- + | - metadata: + | - name: false IN (1, 1.2, 'true', false) + | type: boolean + | rows: + | - [true] + | ... + +SELECT a, a IN (true) FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a IN (true) + | type: boolean + | rows: + | - [false, false] + | - [true, true] + | ... +SELECT a, a IN (false) FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a IN (false) + | type: boolean + | rows: + | - [false, true] + | - [true, false] + | ... +SELECT a, a IN (true, false) FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a IN (true, false) + | type: boolean + | rows: + | - [false, true] + | - [true, true] + | ... +SELECT a, a IN (SELECT a1 FROM t6 LIMIT 1) FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a IN (SELECT a1 FROM t6 LIMIT 1) + | type: boolean + | rows: + | - [false, true] + | - [true, false] + | ... +SELECT a, a IN (SELECT a1 FROM t6) FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a IN (SELECT a1 FROM t6) + | type: boolean + | rows: + | - [false, true] + | - [true, true] + | ... +SELECT a, a IN (1, 1.2, 'true', false) FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a IN (1, 1.2, 'true', false) + | type: boolean + | rows: + | - [false, true] + | - [true, false] + | ... + +SELECT true BETWEEN true AND true; + | --- + | - metadata: + | - name: true BETWEEN true AND true + | type: boolean + | rows: + | - [true] + | ... +SELECT false BETWEEN true AND true; + | --- + | - metadata: + | - name: false BETWEEN true AND true + | type: boolean + | rows: + | - [false] + | ... +SELECT true BETWEEN false AND false; + | --- + | - metadata: + | - name: true BETWEEN false AND false + | type: boolean + | rows: + | - [false] + | ... +SELECT false BETWEEN false AND false; + | --- + | - metadata: + | - name: false BETWEEN false AND false + | type: boolean + | rows: + | - [true] + | ... +SELECT true BETWEEN true AND false; + | --- + | - metadata: + | - name: true BETWEEN true AND false + | type: boolean + | rows: + | - [false] + | ... +SELECT false BETWEEN true AND false; + | --- + | - metadata: + | - name: false BETWEEN true AND false + | type: boolean + | rows: + | - [false] + | ... +SELECT true BETWEEN false AND true; + | --- + | - metadata: + | - name: true BETWEEN false AND true + | type: boolean + | rows: + | - [true] + | ... +SELECT false BETWEEN false AND true; + | --- + | - metadata: + | - name: false BETWEEN false AND true + | type: boolean + | rows: + | - [true] + | ... + +SELECT a, a BETWEEN true AND true FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a BETWEEN true AND true + | type: boolean + | rows: + | - [false, false] + | - [true, true] + | ... +SELECT a, a BETWEEN false AND false FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a BETWEEN false AND false + | type: boolean + | rows: + | - [false, true] + | - [true, false] + | ... +SELECT a, a BETWEEN true AND false FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a BETWEEN true AND false + | type: boolean + | rows: + | - [false, false] + | - [true, false] + | ... +SELECT a, a BETWEEN false AND true FROM t; + | --- + | - metadata: + | - name: A + | type: boolean + | - name: a BETWEEN false AND true + | type: boolean + | rows: + | - [false, true] + | - [true, true] + | ... + +-- Check interaction of BOOLEAN and INTEGER. +CREATE TABLE t7 (b INT PRIMARY KEY); + | --- + | - row_count: 1 + | ... +INSERT INTO t7 VALUES (123); + | --- + | - row_count: 1 + | ... + +SELECT true AND 2; + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... +SELECT false AND 2; + | --- + | - metadata: + | - name: false AND 2 + | type: boolean + | rows: + | - [false] + | ... +SELECT true OR 2; + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... +SELECT false OR 2; + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... +SELECT 2 AND true; + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... +SELECT 2 AND false; + | --- + | - metadata: + | - name: 2 AND false + | type: boolean + | rows: + | - [false] + | ... +SELECT 2 OR true; + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... +SELECT 2 OR false; + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... + +SELECT a1, a1 AND 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... +SELECT a1, a1 OR 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... +SELECT a1, 2 AND a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... +SELECT a1, 2 OR a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... +SELECT a2, a2 AND 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... +SELECT a2, a2 OR 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... +SELECT a2, 2 AND a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... +SELECT a2, 2 OR a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... + +SELECT b, true AND b FROM t7; + | --- + | - error: 'Type mismatch: can not convert 123 to boolean' + | ... +SELECT b, false AND b FROM t7; + | --- + | - metadata: + | - name: B + | type: integer + | - name: false AND b + | type: boolean + | rows: + | - [123, false] + | ... +SELECT b, true OR b FROM t7; + | --- + | - error: 'Type mismatch: can not convert 123 to boolean' + | ... +SELECT b, false OR b FROM t7; + | --- + | - error: 'Type mismatch: can not convert 123 to boolean' + | ... +SELECT b, b AND true FROM t7; + | --- + | - error: 'Type mismatch: can not convert 123 to boolean' + | ... +SELECT b, b AND false FROM t7; + | --- + | - metadata: + | - name: B + | type: integer + | - name: b AND false + | type: boolean + | rows: + | - [123, false] + | ... +SELECT b, b OR true FROM t7; + | --- + | - error: 'Type mismatch: can not convert 123 to boolean' + | ... +SELECT b, b OR false FROM t7; + | --- + | - error: 'Type mismatch: can not convert 123 to boolean' + | ... + +SELECT a1, b, a1 AND b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert 123 to boolean' + | ... +SELECT a1, b, a1 OR b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert 123 to boolean' + | ... +SELECT a1, b, b AND a1 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert 123 to boolean' + | ... +SELECT a1, b, b OR a1 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert 123 to boolean' + | ... +SELECT a2, b, a2 AND b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert 123 to boolean' + | ... +SELECT a2, b, a2 OR b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert 123 to boolean' + | ... +SELECT a2, b, b AND a2 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert 123 to boolean' + | ... +SELECT a2, b, b OR a2 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert 123 to boolean' + | ... + +SELECT true + 2; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT false + 2; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT true - 2; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT false - 2; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT true * 2; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT false * 2; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT true / 2; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT false / 2; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT true % 2; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT false % 2; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT 2 + true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT 2 + false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT 2 - true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT 2 - false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT 2 * true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT 2 * false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT 2 / true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT 2 / false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT 2 % true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT 2 % false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... + +SELECT a1, a1 + 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, a1 - 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, a1 * 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, a1 / 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, a1 % 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, 2 + a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, 2 - a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, 2 * a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, 2 / a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, 2 % a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a2, a2 + 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, a2 - 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, a2 * 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, a2 / 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, a2 % 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, 2 + a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, 2 - a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, 2 * a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, 2 / a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, 2 % a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... + +SELECT b, true + b FROM t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT b, false + b FROM t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT b, true - b FROM t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT b, false - b FROM t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT b, true * b FROM t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT b, false * b FROM t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT b, true / b FROM t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT b, false / b FROM t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT b, true % b FROM t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT b, false % b FROM t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT b, b + true FROM t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT b, b + false FROM t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT b, b - true FROM t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT b, b - false FROM t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT b, b * true FROM t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT b, b * false FROM t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT b, b / true FROM t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT b, b / false FROM t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT b, b % true FROM t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT b, b % false FROM t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... + +SELECT a1, b, a1 + b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, b, a1 - b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, b, a1 * b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, b, a1 / b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, b, a1 % b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, b, b + a1 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, b, b - a1 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, b, b * a1 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, b, b / a1 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, b, b % a1 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a2, b, a2 + b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, b, a2 - b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, b, a2 * b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, b, a2 / b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, b, a2 % b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, b, b + a2 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, b, b - a2 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, b, b * a2 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, b, b / a2 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, b, b % a2 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... + +SELECT true & 2; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT false & 2; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT true | 2; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT false | 2; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT true << 2; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT false << 2; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT true >> 2; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT false >> 2; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT 2 & true; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT 2 & false; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT 2 | true; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT 2 | false; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT 2 << true; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT 2 << false; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT 2 >> true; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT 2 >> false; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... + +SELECT a1, a1 & 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a1, a1 | 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a1, a1 << 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a1, a1 >> 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a1, 2 & a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a1, 2 | a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a1, 2 << a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a1, 2 >> a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a2, a2 & 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT a2, a2 | 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT a2, a2 << 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT a2, a2 >> 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT a2, 2 & a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT a2, 2 | a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT a2, 2 << a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT a2, 2 >> a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... + +SELECT b, true & b FROM t7; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT b, false & b FROM t7; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT b, true | b FROM t7; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT b, false | b FROM t7; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT b, true << b FROM t7; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT b, false << b FROM t7; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT b, true >> b FROM t7; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT b, false >> b FROM t7; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT b, b & true FROM t7; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT b, b & false FROM t7; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT b, b | true FROM t7; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT b, b | false FROM t7; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT b, b << true FROM t7; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT b, b << false FROM t7; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT b, b >> true FROM t7; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT b, b >> false FROM t7; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... + +SELECT a1, b, a1 & b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a1, b, a1 | b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a1, b, a1 << b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a1, b, a1 >> b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a1, b, b & a1 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a1, b, b | a1 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a1, b, b << a1 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a1, b, b >> a1 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a2, b, a2 & b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT a2, b, a2 | b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT a2, b, a2 << b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT a2, b, a2 >> b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT a2, b, b & a2 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT a2, b, b | a2 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT a2, b, b << a2 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT a2, b, b >> a2 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... + +SELECT true > 2; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT false > 2; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT true < 2; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT false < 2; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT 2 > true; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT 2 > false; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT 2 < true; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT 2 < false; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... + +SELECT a1, a1 > 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a1, a1 < 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a1, 2 > a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a1, 2 < a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, a2 > 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, a2 < 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, 2 > a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, 2 < a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... + +SELECT b, true > b FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, false > b FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, true < b FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, false < b FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, b > true FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, b > false FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, b < true FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, b < false FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... + +SELECT a1, b, a1 > b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a1, b, a1 < b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a1, b, b > a1 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a1, b, b < a1 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, b, a2 > b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, b, a2 < b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, b, b > a2 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, b, b < a2 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... + +SELECT true >= 2; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT false >= 2; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT true <= 2; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT false <= 2; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT 2 >= true; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT 2 >= false; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT 2 <= true; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT 2 <= false; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... + +SELECT a1, a1 >= 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a1, a1 <= 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a1, 2 >= a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a1, 2 <= a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, a2 >= 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, a2 <= 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, 2 >= a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, 2 <= a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... + +SELECT b, true >= b FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, false >= b FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, true <= b FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, false <= b FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, b >= true FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, b >= false FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, b <= true FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, b <= false FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... + +SELECT a1, b, a1 >= b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a1, b, a1 <= b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a1, b, b >= a1 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a1, b, b <= a1 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, b, a2 >= b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, b, a2 <= b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, b, b >= a2 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, b, b <= a2 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... + +SELECT true == 2; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT false == 2; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT true != 2; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT false != 2; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT 2 == true; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT 2 == false; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT 2 != true; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT 2 != false; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... + +SELECT a1, a1 == 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a1, a1 != 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a1, 2 == a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a1, 2 != a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, a2 == 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, a2 != 2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, 2 == a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, 2 != a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... + +SELECT b, true == b FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, false == b FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, true != b FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, false != b FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, b == true FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, b == false FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, b != true FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT b, b != false FROM t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... + +SELECT a1, b, a1 == b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a1, b, a1 != b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a1, b, b == a1 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a1, b, b != a1 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, b, a2 == b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, b, a2 != b FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, b, b == a2 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, b, b != a2 FROM t6, t7; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... + +SELECT true IN (0, 1, 2, 3); + | --- + | - metadata: + | - name: true IN (0, 1, 2, 3) + | type: boolean + | rows: + | - [false] + | ... +SELECT false IN (0, 1, 2, 3); + | --- + | - metadata: + | - name: false IN (0, 1, 2, 3) + | type: boolean + | rows: + | - [false] + | ... +SELECT true IN (SELECT b FROM t7); + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT false IN (SELECT b FROM t7); + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT a1, a1 IN (0, 1, 2, 3) FROM t6 + | --- + | - metadata: + | - name: A1 + | type: boolean + | - name: a1 IN (0, 1, 2, 3) + | type: boolean + | rows: + | - [false, false] + | - [true, false] + | ... + +SELECT true BETWEEN 0 and 10; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT false BETWEEN 0 and 10; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a1, a1 BETWEEN 0 and 10 FROM t6; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +SELECT a2, a2 BETWEEN 0 and 10 FROM t6; + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... + +-- Check interaction of BOOLEAN and REAL. +CREATE TABLE t8 (c REAL PRIMARY KEY); + | --- + | - row_count: 1 + | ... +INSERT INTO t8 VALUES (4.56); + | --- + | - row_count: 1 + | ... + +SELECT true AND 2.3; + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... +SELECT false AND 2.3; + | --- + | - metadata: + | - name: false AND 2.3 + | type: boolean + | rows: + | - [false] + | ... +SELECT true OR 2.3; + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... +SELECT false OR 2.3; + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... +SELECT 2.3 AND true; + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... +SELECT 2.3 AND false; + | --- + | - metadata: + | - name: 2.3 AND false + | type: boolean + | rows: + | - [false] + | ... +SELECT 2.3 OR true; + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... +SELECT 2.3 OR false; + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... + +SELECT a1, a1 AND 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... +SELECT a1, a1 OR 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... +SELECT a1, 2.3 AND a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... +SELECT a1, 2.3 OR a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... +SELECT a2, a2 AND 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... +SELECT a2, a2 OR 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... +SELECT a2, 2.3 AND a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... +SELECT a2, 2.3 OR a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... + +SELECT c, true AND c FROM t8; + | --- + | - error: 'Type mismatch: can not convert 4.56 to boolean' + | ... +SELECT c, false AND c FROM t8; + | --- + | - metadata: + | - name: C + | type: number + | - name: false AND c + | type: boolean + | rows: + | - [4.56, false] + | ... +SELECT c, true OR c FROM t8; + | --- + | - error: 'Type mismatch: can not convert 4.56 to boolean' + | ... +SELECT c, false OR c FROM t8; + | --- + | - error: 'Type mismatch: can not convert 4.56 to boolean' + | ... +SELECT c, c AND true FROM t8; + | --- + | - error: 'Type mismatch: can not convert 4.56 to boolean' + | ... +SELECT c, c AND false FROM t8; + | --- + | - metadata: + | - name: C + | type: number + | - name: c AND false + | type: boolean + | rows: + | - [4.56, false] + | ... +SELECT c, c OR true FROM t8; + | --- + | - error: 'Type mismatch: can not convert 4.56 to boolean' + | ... +SELECT c, c OR false FROM t8; + | --- + | - error: 'Type mismatch: can not convert 4.56 to boolean' + | ... + +SELECT a1, c, a1 AND c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert 4.56 to boolean' + | ... +SELECT a1, c, a1 OR c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert 4.56 to boolean' + | ... +SELECT a1, c, c AND a1 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert 4.56 to boolean' + | ... +SELECT a1, c, c OR a1 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert 4.56 to boolean' + | ... +SELECT a2, c, a2 AND c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert 4.56 to boolean' + | ... +SELECT a2, c, a2 OR c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert 4.56 to boolean' + | ... +SELECT a2, c, c AND a2 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert 4.56 to boolean' + | ... +SELECT a2, c, c OR a2 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert 4.56 to boolean' + | ... + +SELECT true + 2.3; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT false + 2.3; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT true - 2.3; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT false - 2.3; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT true * 2.3; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT false * 2.3; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT true / 2.3; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT false / 2.3; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT true % 2.3; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT false % 2.3; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT 2.3 + true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT 2.3 + false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT 2.3 - true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT 2.3 - false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT 2.3 * true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT 2.3 * false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT 2.3 / true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT 2.3 / false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT 2.3 % true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT 2.3 % false; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... + +SELECT a1, a1 + 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, a1 - 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, a1 * 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, a1 / 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, a1 % 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, 2.3 + a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, 2.3 - a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, 2.3 * a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, 2.3 / a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, 2.3 % a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a2, a2 + 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, a2 - 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, a2 * 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, a2 / 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, a2 % 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, 2.3 + a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, 2.3 - a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, 2.3 * a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, 2.3 / a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, 2.3 % a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... + +SELECT c, true + c FROM t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT c, false + c FROM t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT c, true - c FROM t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT c, false - c FROM t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT c, true * c FROM t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT c, false * c FROM t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT c, true / c FROM t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT c, false / c FROM t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT c, true % c FROM t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT c, false % c FROM t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT c, c + true FROM t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT c, c + false FROM t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT c, c - true FROM t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT c, c - false FROM t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT c, c * true FROM t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT c, c * false FROM t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT c, c / true FROM t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT c, c / false FROM t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT c, c % true FROM t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT c, c % false FROM t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... + +SELECT a1, c, a1 + c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, c, a1 - c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, c, a1 * c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, c, a1 / c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, c, a1 % c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, c, c + a1 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, c, c - a1 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, c, c * a1 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, c, c / a1 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a1, c, c % a1 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +SELECT a2, c, a2 + c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, c, a2 - c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, c, a2 * c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, c, a2 / c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, c, a2 % c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, c, c + a2 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, c, c - a2 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, c, c * a2 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, c, c / a2 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT a2, c, c % a2 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... + +SELECT true > 2.3; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT false > 2.3; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT true < 2.3; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT false < 2.3; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT 2.3 > true; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT 2.3 > false; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT 2.3 < true; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT 2.3 < false; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... + +SELECT a1, a1 > 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a1, a1 < 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a1, 2.3 > a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a1, 2.3 < a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, a2 > 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, a2 < 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, 2.3 > a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, 2.3 < a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... + +SELECT c, true > c FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, false > c FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, true < c FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, false < c FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, c > true FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, c > false FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, c < true FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, c < false FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... + +SELECT a1, c, a1 > c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a1, c, a1 < c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a1, c, c > a1 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a1, c, c < a1 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, c, a2 > c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, c, a2 < c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, c, c > a2 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, c, c < a2 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... + +SELECT true >= 2.3; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT false >= 2.3; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT true <= 2.3; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT false <= 2.3; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT 2.3 >= true; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT 2.3 >= false; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT 2.3 <= true; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT 2.3 <= false; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... + +SELECT a1, a1 >= 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a1, a1 <= 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a1, 2.3 >= a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a1, 2.3 <= a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, a2 >= 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, a2 <= 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, 2.3 >= a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, 2.3 <= a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... + +SELECT c, true >= c FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, false >= c FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, true <= c FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, false <= c FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, c >= true FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, c >= false FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, c <= true FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, c <= false FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... + +SELECT a1, c, a1 >= c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a1, c, a1 <= c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a1, c, c >= a1 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a1, c, c <= a1 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, c, a2 >= c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, c, a2 <= c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, c, c >= a2 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, c, c <= a2 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... + +SELECT true == 2.3; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT false == 2.3; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT true != 2.3; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT false != 2.3; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT 2.3 == true; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT 2.3 == false; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT 2.3 != true; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT 2.3 != false; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... + +SELECT a1, a1 == 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a1, a1 != 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a1, 2.3 == a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a1, 2.3 != a1 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, a2 == 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, a2 != 2.3 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, 2.3 == a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, 2.3 != a2 FROM t6 + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... + +SELECT c, true == c FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, false == c FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, true != c FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, false != c FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, c == true FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, c == false FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, c != true FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT c, c != false FROM t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... + +SELECT a1, c, a1 == c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a1, c, a1 != c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a1, c, c == a1 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a1, c, c != a1 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, c, a2 == c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, c, a2 != c FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, c, c == a2 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, c, c != a2 FROM t6, t8; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... + +SELECT true IN (0.1, 1.2, 2.3, 3.4); + | --- + | - metadata: + | - name: true IN (0.1, 1.2, 2.3, 3.4) + | type: boolean + | rows: + | - [false] + | ... +SELECT false IN (0.1, 1.2, 2.3, 3.4); + | --- + | - metadata: + | - name: false IN (0.1, 1.2, 2.3, 3.4) + | type: boolean + | rows: + | - [false] + | ... +SELECT a1 IN (0.1, 1.2, 2.3, 3.4) FROM t6 LIMIT 1; + | --- + | - metadata: + | - name: a1 IN (0.1, 1.2, 2.3, 3.4) + | type: boolean + | rows: + | - [false] + | ... +SELECT a2 IN (0.1, 1.2, 2.3, 3.4) FROM t6 LIMIT 1; + | --- + | - metadata: + | - name: a2 IN (0.1, 1.2, 2.3, 3.4) + | type: boolean + | rows: + | - [false] + | ... +SELECT true IN (SELECT c FROM t8); + | --- + | - error: 'Type mismatch: can not convert true to number' + | ... +SELECT false IN (SELECT c FROM t8); + | --- + | - error: 'Type mismatch: can not convert false to number' + | ... +SELECT a1 IN (SELECT c FROM t8) FROM t6 LIMIT 1; + | --- + | - error: 'Type mismatch: can not convert false to number' + | ... +SELECT a2 IN (SELECT c FROM t8) FROM t6 LIMIT 1; + | --- + | - error: 'Type mismatch: can not convert true to number' + | ... + +SELECT true BETWEEN 0.1 and 9.9; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT false BETWEEN 0.1 and 9.9; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a1, a1 BETWEEN 0.1 and 9.9 FROM t6; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +SELECT a2, a2 BETWEEN 0.1 and 9.9 FROM t6; + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... + +-- Check interaction of BOOLEAN and TEXT. +CREATE TABLE t9 (d TEXT PRIMARY KEY); + | --- + | - row_count: 1 + | ... +INSERT INTO t9 VALUES ('AsdF'); + | --- + | - row_count: 1 + | ... + +SELECT true AND 'abc'; + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... +SELECT false AND 'abc'; + | --- + | - metadata: + | - name: false AND 'abc' + | type: boolean + | rows: + | - [false] + | ... +SELECT true OR 'abc'; + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... +SELECT false OR 'abc'; + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... +SELECT 'abc' AND true; + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... +SELECT 'abc' AND false; + | --- + | - metadata: + | - name: '''abc'' AND false' + | type: boolean + | rows: + | - [false] + | ... +SELECT 'abc' OR true; + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... +SELECT 'abc' OR false; + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... + +SELECT a1, a1 AND 'abc' FROM t6; + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... +SELECT a1, a1 OR 'abc' FROM t6; + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... +SELECT a1, 'abc' AND a1 FROM t6; + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... +SELECT a1, 'abc' OR a1 FROM t6; + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... +SELECT a2, a2 AND 'abc' FROM t6; + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... +SELECT a2, a2 OR 'abc' FROM t6; + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... +SELECT a2, 'abc' AND a2 FROM t6; + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... +SELECT a2, 'abc' OR a2 FROM t6; + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... + +SELECT d, true AND d FROM t9; + | --- + | - error: 'Type mismatch: can not convert AsdF to boolean' + | ... +SELECT d, false AND d FROM t9; + | --- + | - metadata: + | - name: D + | type: string + | - name: false AND d + | type: boolean + | rows: + | - ['AsdF', false] + | ... +SELECT d, true OR d FROM t9; + | --- + | - error: 'Type mismatch: can not convert AsdF to boolean' + | ... +SELECT d, false OR d FROM t9; + | --- + | - error: 'Type mismatch: can not convert AsdF to boolean' + | ... +SELECT d, d AND true FROM t9; + | --- + | - error: 'Type mismatch: can not convert AsdF to boolean' + | ... +SELECT d, d AND false FROM t9; + | --- + | - metadata: + | - name: D + | type: string + | - name: d AND false + | type: boolean + | rows: + | - ['AsdF', false] + | ... +SELECT d, d OR true FROM t9; + | --- + | - error: 'Type mismatch: can not convert AsdF to boolean' + | ... +SELECT d, d OR false FROM t9; + | --- + | - error: 'Type mismatch: can not convert AsdF to boolean' + | ... + +SELECT a1, d, a1 AND d FROM t6, t9; + | --- + | - error: 'Type mismatch: can not convert AsdF to boolean' + | ... +SELECT a1, d, a1 OR d FROM t6, t9; + | --- + | - error: 'Type mismatch: can not convert AsdF to boolean' + | ... +SELECT a1, d, d AND a1 FROM t6, t9; + | --- + | - error: 'Type mismatch: can not convert AsdF to boolean' + | ... +SELECT a1, d, d OR a1 FROM t6, t9; + | --- + | - error: 'Type mismatch: can not convert AsdF to boolean' + | ... +SELECT a2, d, a2 AND d FROM t6, t9; + | --- + | - error: 'Type mismatch: can not convert AsdF to boolean' + | ... +SELECT a2, d, a2 OR d FROM t6, t9; + | --- + | - error: 'Type mismatch: can not convert AsdF to boolean' + | ... +SELECT a2, d, d AND a2 FROM t6, t9; + | --- + | - error: 'Type mismatch: can not convert AsdF to boolean' + | ... +SELECT a2, d, d OR a2 FROM t6, t9; + | --- + | - error: 'Type mismatch: can not convert AsdF to boolean' + | ... + +SELECT true > 'abc'; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT false > 'abc'; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT true < 'abc'; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT false < 'abc'; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT 'abc' > true; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT 'abc' > false; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT 'abc' < true; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT 'abc' < false; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... + +SELECT d, true > d FROM t9; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT d, false > d FROM t9; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT d, true < d FROM t9; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT d, false < d FROM t9; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT d, d > true FROM t9; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT d, d > false FROM t9; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT d, d < true FROM t9; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT d, d < false FROM t9; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... + +SELECT a1, d, a1 > d FROM t6, t9; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT a1, d, a1 < d FROM t6, t9; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT a1, d, d > a1 FROM t6, t9; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT a1, d, d < a1 FROM t6, t9; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT a2, d, a2 > d FROM t6, t9; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT a2, d, a2 < d FROM t6, t9; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT a2, d, d > a2 FROM t6, t9; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +SELECT a2, d, d < a2 FROM t6, t9; + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... + +SELECT true || 'abc'; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +SELECT false || 'abc'; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +SELECT 'abc' || false; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +SELECT 'abc' || true; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... + +SELECT d, true || d FROM t9; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +SELECT d, false || d FROM t9; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +SELECT d, d || false FROM t9; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +SELECT d, d || true FROM t9; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... + +SELECT d, a1 || d FROM t6, t9; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +SELECT d, a2 || d FROM t6, t9; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +SELECT d, d || a1 FROM t6, t9; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +SELECT d, d || a2 FROM t6, t9; + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... + +-- +-- Check special types of strings: 'TRUE', 'true', 'FALSE', +-- 'false'. +-- +INSERT INTO t9 VALUES ('TRUE'), ('true'), ('FALSE'), ('false'); + | --- + | - row_count: 4 + | ... + +SELECT true AND 'TRUE'; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT false AND 'TRUE'; + | --- + | - metadata: + | - name: false AND 'TRUE' + | type: boolean + | rows: + | - [false] + | ... +SELECT true OR 'TRUE'; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT false OR 'TRUE'; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT 'TRUE' AND true; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT 'TRUE' AND false; + | --- + | - metadata: + | - name: '''TRUE'' AND false' + | type: boolean + | rows: + | - [false] + | ... +SELECT 'TRUE' OR true; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT 'TRUE' OR false; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... + +SELECT a1, a1 AND 'TRUE' FROM t6; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT a1, a1 OR 'TRUE' FROM t6; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT a1, 'TRUE' AND a1 FROM t6; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT a1, 'TRUE' OR a1 FROM t6; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT a2, a2 AND 'TRUE' FROM t6; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT a2, a2 OR 'TRUE' FROM t6; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT a2, 'TRUE' AND a2 FROM t6; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT a2, 'TRUE' OR a2 FROM t6; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... + +SELECT d, true AND d FROM t9 WHERE d = 'TRUE'; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT d, false AND d FROM t9 WHERE d = 'TRUE'; + | --- + | - metadata: + | - name: D + | type: string + | - name: false AND d + | type: boolean + | rows: + | - ['TRUE', false] + | ... +SELECT d, true OR d FROM t9 WHERE d = 'TRUE'; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT d, false OR d FROM t9 WHERE d = 'TRUE'; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT d, d AND true FROM t9 WHERE d = 'TRUE'; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT d, d AND false FROM t9 WHERE d = 'TRUE'; + | --- + | - metadata: + | - name: D + | type: string + | - name: d AND false + | type: boolean + | rows: + | - ['TRUE', false] + | ... +SELECT d, d OR true FROM t9 WHERE d = 'TRUE'; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT d, d OR false FROM t9 WHERE d = 'TRUE'; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... + +SELECT a1, d, a1 AND d FROM t6, t9 WHERE d = 'TRUE'; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT a1, d, a1 OR d FROM t6, t9 WHERE d = 'TRUE'; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT a1, d, d AND a1 FROM t6, t9 WHERE d = 'TRUE'; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT a1, d, d OR a1 FROM t6, t9 WHERE d = 'TRUE'; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT a2, d, a2 AND d FROM t6, t9 WHERE d = 'TRUE'; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT a2, d, a2 OR d FROM t6, t9 WHERE d = 'TRUE'; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT a2, d, d AND a2 FROM t6, t9 WHERE d = 'TRUE'; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... +SELECT a2, d, d OR a2 FROM t6, t9 WHERE d = 'TRUE'; + | --- + | - error: 'Type mismatch: can not convert TRUE to boolean' + | ... + +SELECT true AND 'true'; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT false AND 'true'; + | --- + | - metadata: + | - name: false AND 'true' + | type: boolean + | rows: + | - [false] + | ... +SELECT true OR 'true'; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT false OR 'true'; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT 'true' AND true; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT 'true' AND false; + | --- + | - metadata: + | - name: '''true'' AND false' + | type: boolean + | rows: + | - [false] + | ... +SELECT 'true' OR true; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT 'true' OR false; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... + +SELECT a1, a1 AND 'true' FROM t6; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT a1, a1 OR 'true' FROM t6; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT a1, 'true' AND a1 FROM t6; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT a1, 'true' OR a1 FROM t6; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT a2, a2 AND 'true' FROM t6; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT a2, a2 OR 'true' FROM t6; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT a2, 'true' AND a2 FROM t6; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT a2, 'true' OR a2 FROM t6; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... + +SELECT d, true AND d FROM t9 WHERE d = 'true'; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT d, false AND d FROM t9 WHERE d = 'true'; + | --- + | - metadata: + | - name: D + | type: string + | - name: false AND d + | type: boolean + | rows: + | - ['true', false] + | ... +SELECT d, true OR d FROM t9 WHERE d = 'true'; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT d, false OR d FROM t9 WHERE d = 'true'; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT d, d AND true FROM t9 WHERE d = 'true'; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT d, d AND false FROM t9 WHERE d = 'true'; + | --- + | - metadata: + | - name: D + | type: string + | - name: d AND false + | type: boolean + | rows: + | - ['true', false] + | ... +SELECT d, d OR true FROM t9 WHERE d = 'true'; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT d, d OR false FROM t9 WHERE d = 'true'; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... + +SELECT a1, d, a1 AND d FROM t6, t9 WHERE d = 'true'; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT a1, d, a1 OR d FROM t6, t9 WHERE d = 'true'; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT a1, d, d AND a1 FROM t6, t9 WHERE d = 'true'; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT a1, d, d OR a1 FROM t6, t9 WHERE d = 'true'; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT a2, d, a2 AND d FROM t6, t9 WHERE d = 'true'; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT a2, d, a2 OR d FROM t6, t9 WHERE d = 'true'; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT a2, d, d AND a2 FROM t6, t9 WHERE d = 'true'; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... +SELECT a2, d, d OR a2 FROM t6, t9 WHERE d = 'true'; + | --- + | - error: 'Type mismatch: can not convert true to boolean' + | ... + +SELECT true AND 'FALSE'; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT false AND 'FALSE'; + | --- + | - metadata: + | - name: false AND 'FALSE' + | type: boolean + | rows: + | - [false] + | ... +SELECT true OR 'FALSE'; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT false OR 'FALSE'; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT 'FALSE' AND true; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT 'FALSE' AND false; + | --- + | - metadata: + | - name: '''FALSE'' AND false' + | type: boolean + | rows: + | - [false] + | ... +SELECT 'FALSE' OR true; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT 'FALSE' OR false; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... + +SELECT a1, a1 AND 'FALSE' FROM t6; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT a1, a1 OR 'FALSE' FROM t6; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT a1, 'FALSE' AND a1 FROM t6; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT a1, 'FALSE' OR a1 FROM t6; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT a2, a2 AND 'FALSE' FROM t6; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT a2, a2 OR 'FALSE' FROM t6; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT a2, 'FALSE' AND a2 FROM t6; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT a2, 'FALSE' OR a2 FROM t6; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... + +SELECT d, true AND d FROM t9 WHERE d = 'FALSE'; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT d, false AND d FROM t9 WHERE d = 'FALSE'; + | --- + | - metadata: + | - name: D + | type: string + | - name: false AND d + | type: boolean + | rows: + | - ['FALSE', false] + | ... +SELECT d, true OR d FROM t9 WHERE d = 'FALSE'; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT d, false OR d FROM t9 WHERE d = 'FALSE'; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT d, d AND true FROM t9 WHERE d = 'FALSE'; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT d, d AND false FROM t9 WHERE d = 'FALSE'; + | --- + | - metadata: + | - name: D + | type: string + | - name: d AND false + | type: boolean + | rows: + | - ['FALSE', false] + | ... +SELECT d, d OR true FROM t9 WHERE d = 'FALSE'; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT d, d OR false FROM t9 WHERE d = 'FALSE'; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... + +SELECT a1, d, a1 AND d FROM t6, t9 WHERE d = 'FALSE'; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT a1, d, a1 OR d FROM t6, t9 WHERE d = 'FALSE'; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT a1, d, d AND a1 FROM t6, t9 WHERE d = 'FALSE'; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT a1, d, d OR a1 FROM t6, t9 WHERE d = 'FALSE'; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT a2, d, a2 AND d FROM t6, t9 WHERE d = 'FALSE'; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT a2, d, a2 OR d FROM t6, t9 WHERE d = 'FALSE'; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT a2, d, d AND a2 FROM t6, t9 WHERE d = 'FALSE'; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... +SELECT a2, d, d OR a2 FROM t6, t9 WHERE d = 'FALSE'; + | --- + | - error: 'Type mismatch: can not convert FALSE to boolean' + | ... + +SELECT true AND 'false'; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT false AND 'false'; + | --- + | - metadata: + | - name: false AND 'false' + | type: boolean + | rows: + | - [false] + | ... +SELECT true OR 'false'; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT false OR 'false'; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT 'false' AND true; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT 'false' AND false; + | --- + | - metadata: + | - name: '''false'' AND false' + | type: boolean + | rows: + | - [false] + | ... +SELECT 'false' OR true; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT 'false' OR false; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... + +SELECT a1, a1 AND 'false' FROM t6; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT a1, a1 OR 'false' FROM t6; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT a1, 'false' AND a1 FROM t6; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT a1, 'false' OR a1 FROM t6; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT a2, a2 AND 'false' FROM t6; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT a2, a2 OR 'false' FROM t6; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT a2, 'false' AND a2 FROM t6; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT a2, 'false' OR a2 FROM t6; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... + +SELECT d, true AND d FROM t9 WHERE d = 'false'; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT d, false AND d FROM t9 WHERE d = 'false'; + | --- + | - metadata: + | - name: D + | type: string + | - name: false AND d + | type: boolean + | rows: + | - ['false', false] + | ... +SELECT d, true OR d FROM t9 WHERE d = 'false'; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT d, false OR d FROM t9 WHERE d = 'false'; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT d, d AND true FROM t9 WHERE d = 'false'; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT d, d AND false FROM t9 WHERE d = 'false'; + | --- + | - metadata: + | - name: D + | type: string + | - name: d AND false + | type: boolean + | rows: + | - ['false', false] + | ... +SELECT d, d OR true FROM t9 WHERE d = 'false'; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT d, d OR false FROM t9 WHERE d = 'false'; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... + +SELECT a1, d, a1 AND d FROM t6, t9 WHERE d = 'false'; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT a1, d, a1 OR d FROM t6, t9 WHERE d = 'false'; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT a1, d, d AND a1 FROM t6, t9 WHERE d = 'false'; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT a1, d, d OR a1 FROM t6, t9 WHERE d = 'false'; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT a2, d, a2 AND d FROM t6, t9 WHERE d = 'false'; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT a2, d, a2 OR d FROM t6, t9 WHERE d = 'false'; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT a2, d, d AND a2 FROM t6, t9 WHERE d = 'false'; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... +SELECT a2, d, d OR a2 FROM t6, t9 WHERE d = 'false'; + | --- + | - error: 'Type mismatch: can not convert false to boolean' + | ... + +-- Cleaning. +DROP VIEW v; + | --- + | - row_count: 1 + | ... +DROP TABLE t9; + | --- + | - row_count: 1 + | ... +DROP TABLE t8; + | --- + | - row_count: 1 + | ... +DROP TABLE t7; + | --- + | - row_count: 1 + | ... +DROP TABLE t6; + | --- + | - row_count: 1 + | ... +DROP TABLE t5; + | --- + | - row_count: 1 + | ... +DROP TABLE t4; + | --- + | - row_count: 1 + | ... +DROP TABLE t3; + | --- + | - row_count: 1 + | ... +DROP TABLE t2; + | --- + | - row_count: 1 + | ... +DROP TABLE t1; + | --- + | - row_count: 1 + | ... +DROP TABLE t0; + | --- + | - row_count: 1 + | ... +DROP TABLE ts; + | --- + | - row_count: 1 + | ... +DROP TABLE t; + | --- + | - row_count: 1 + | ... diff --git a/test/sql/boolean.test.sql b/test/sql/boolean.test.sql new file mode 100644 index 0000000..e45e554 --- /dev/null +++ b/test/sql/boolean.test.sql @@ -0,0 +1,1230 @@ +-- Create table for tests +CREATE TABLE t (a BOOLEAN PRIMARY KEY); +INSERT INTO t VALUES (true), (false); + +-- Create user-defined function. +\set language lua +function return_type(arg) return type(arg) end +function is_boolean(arg) return type(arg) == 'boolean' end +box.internal.sql_create_function("return_type", "TEXT", return_type) +box.internal.sql_create_function("is_boolean", "BOOLEAN", is_boolean) +\set language sql + +-- Check boolean as WHERE argument. +SELECT a FROM t WHERE a; +SELECT a FROM t WHERE a != true; + +-- Check DEFAULT values for boolean. +CREATE TABLE t0 (i INT PRIMARY KEY, a BOOLEAN DEFAULT true); +INSERT INTO t0 VALUES (1, false); +INSERT INTO t0(i) VALUES (2); +INSERT INTO t0 VALUES (3, NULL); +SELECT * FROM t0; + +-- Check UNKNOWN value for boolean. +INSERT INTO t0 VALUES (4, UNKNOWN); +SELECT * FROM t0; + +-- Make sure that SCALAR can handle boolean values. +CREATE TABLE ts (id INT PRIMARY KEY AUTOINCREMENT, s SCALAR); +INSERT INTO ts SELECT * FROM t0; +SELECT s FROM ts WHERE s = true; +INSERT INTO ts(s) VALUES ('abc'), (12.5); +SELECT s FROM ts WHERE s = true; +SELECT s FROM ts WHERE s < true; +SELECT s FROM ts WHERE s IN (true, 1, 'abcd'); + +-- +-- Make sure that BOOLEAN is not implicitly converted to INTEGER +-- while inserted to PRIMARY KEY field. +-- +INSERT INTO ts VALUES (true, 12345); + +-- Check that we can create index on field of type BOOLEAN. +CREATE INDEX i0 ON t0(a); + +-- Check boolean as LIMIT argument. +SELECT * FROM t LIMIT true; +SELECT * FROM t LIMIT false; + +-- Check boolean as OFFSET argument. +SELECT * FROM t LIMIT 1 OFFSET true; +SELECT * FROM t LIMIT 1 OFFSET false; + +-- Check involvance in index search. +EXPLAIN QUERY PLAN SELECT a FROM t0 WHERE a = true; + +-- Check that ephemeral tables are used with BOOLEAN. +\set language lua +result = box.execute('EXPLAIN SELECT * FROM (VALUES(true)), t;') +i = 0 +for _,v in pairs(result.rows) do if (v[2] == 'OpenTEphemeral') then i = i + 1 end end +i > 0 +\set language sql + +-- Check BOOLEAN as argument of user-defined function. +SELECT return_type(a) FROM t; +SELECT return_type('false'); +SELECT is_boolean(a) FROM t LIMIT 1; +SELECT is_boolean('true'); + +-- Check BOOLEAN as argument of scalar function. +SELECT abs(a) FROM t0; +SELECT lower(a) FROM t0; +SELECT upper(a) FROM t0; +SELECT quote(a) FROM t0; +SELECT length(a) FROM t0; +SELECT typeof(a) FROM t0; + +-- Check BOOLEAN as argument of aggregate function. +SELECT AVG(a) FROM t0; +SELECT MIN(a) FROM t0; +SELECT MAX(a) FROM t0; +SELECT SUM(a) FROM t0; +SELECT COUNT(a) FROM t0; +SELECT TOTAL(a) FROM t0; +SELECT GROUP_CONCAT(a, ' +++ ') FROM t0; + +-- Check BOOLEAN as binding parameter. +\set language lua +box.execute('SELECT ?, ?, return_type($1), typeof($2);', {true, false}) + +parameters = {} +parameters[1] = {} +parameters[1]['@value2'] = true +parameters[2] = {} +parameters[2][':value1'] = false +box.execute('SELECT :value1, @value2;', parameters) +\set language sql + +-- Check interactions with CHECK constraint. +CREATE TABLE t1 (i INT PRIMARY KEY, a BOOLEAN, CONSTRAINT ck CHECK(a != true)); +INSERT INTO t1 VALUES (1, false); +INSERT INTO t1 VALUES (2, true); + +-- Check interactions with FOREIGN KEY constraint. +CREATE TABLE t2 (a BOOLEAN PRIMARY KEY, b BOOLEAN REFERENCES t2(a)); +INSERT INTO t2 VALUES (false, true) +INSERT INTO t2 VALUES (true, false) +INSERT INTO t2 VALUES (true, true) +INSERT INTO t2 VALUES (false, true) + +-- Check interactions with UNIQUE constraint. +CREATE TABLE t3 (i INT PRIMARY KEY, a BOOLEAN, CONSTRAINT uq UNIQUE(a)); +INSERT INTO t3 VALUES (1, true) +INSERT INTO t3 VALUES (2, false) +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 REAL), cast(false AS REAL); +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('true' AS BOOLEAN), cast('false' AS BOOLEAN); +SELECT cast('TRUE' AS BOOLEAN), cast('FALSE' AS BOOLEAN); + +-- Check usage in trigger. +CREATE TABLE t4 (i INT PRIMARY KEY, a BOOLEAN); +CREATE TABLE t5 (i INT PRIMARY KEY AUTOINCREMENT, b BOOLEAN); +CREATE TRIGGER r AFTER INSERT ON t4 FOR EACH ROW BEGIN INSERT INTO t5(b) VALUES(true); END; +INSERT INTO t4 VALUES (100, false); +DROP TRIGGER r; +SELECT * FROM t4; +SELECT * FROM t5; + +-- Check UNION, UNION ALL AND INTERSECT. +INSERT INTO t5 VALUES (100, false); +SELECT * FROM t4 UNION SELECT * FROM t5; +SELECT * FROM t4 UNION ALL SELECT * FROM t5; +SELECT * FROM t4 INTERSECT SELECT * FROM t5; + +-- Check SUBSELECT. +INSERT INTO t5(b) SELECT a FROM t4; +SELECT * FROM t5; + +SELECT * FROM (SELECT t4.i, t5.i, a, b FROM t4, t5 WHERE a = false OR b = true); + +-- Check VIEW. +CREATE VIEW v AS SELECT b FROM t5; +SELECT * FROM v; + +-- Check DISTINCT. +SELECT DISTINCT * FROM v; + +-- Check CTE +WITH temp(x, y) AS (VALUES (111, false) UNION ALL VALUES (222, true)) \ +INSERT INTO t4 SELECT * from temp; +SELECT * FROM t4; + +WITH RECURSIVE cnt(x, y) AS \ +(VALUES(1, false) UNION ALL SELECT x+1, x % 2 == 1 FROM cnt WHERE x<10) \ +SELECT x, y FROM cnt; + +-- Check JOINs. +SELECT * FROM t4 JOIN t5 ON t4.a = t5.b; +SELECT * FROM t4 LEFT JOIN t5 ON t4.a = t5.b; +SELECT * FROM t4 INNER JOIN t5 ON t4.a = t5.b; + +-- Check UPDATE. +UPDATE t4 SET a = NOT a; +SELECT * FROM t4; + +-- Check SWITCH-CASE. +SELECT i, \ +CASE \ + WHEN a == true AND i % 2 == 1 THEN false \ + WHEN a == true and i % 2 == 0 THEN true \ + WHEN a != true then false \ +END AS a0 \ +FROM t4; + +-- Check OPDER BY. +SELECT * FROM t4 UNION SELECT * FROM t5 ORDER BY a, i; + +-- Check GROUP BY. +SELECT a, COUNT(*) FROM (SELECT * FROM t4 UNION SELECT * FROM t5) GROUP BY a; + +-- Check logical, bitwise and arithmetical operations. +CREATE TABLE t6 (a1 BOOLEAN PRIMARY KEY, a2 BOOLEAN); +INSERT INTO t6 VALUES (true, false), (false, true); + +SELECT NOT true; +SELECT NOT false; +SELECT a, NOT a FROM t; + +SELECT true AND true; +SELECT true AND false; +SELECT false AND true; +SELECT false AND false; +SELECT true OR true; +SELECT true OR false; +SELECT false OR true; +SELECT false OR false; + +SELECT a, true AND a FROM t; +SELECT a, false AND a FROM t; +SELECT a, true OR a FROM t; +SELECT a, false OR a FROM t; +SELECT a, a AND true FROM t; +SELECT a, a AND false FROM t; +SELECT a, a OR true FROM t; +SELECT a, a OR false FROM t; + +SELECT a, a1, a AND a1 FROM t, t6; +SELECT a, a1, a OR a1 FROM t, t6; + +SELECT -true; +SELECT -false; +SELECT -a FROM t; + +SELECT true + true; +SELECT true + false; +SELECT false + true; +SELECT false + false; +SELECT true - true; +SELECT true - false; +SELECT false - true; +SELECT false - false; +SELECT true * true; +SELECT true * false; +SELECT false * true; +SELECT false * false; +SELECT true / true; +SELECT true / false; +SELECT false / true; +SELECT false / false; +SELECT true % true; +SELECT true % false; +SELECT false % true; +SELECT false % false; + +SELECT a, true + a FROM t; +SELECT a, false + a FROM t; +SELECT a, true - a FROM t; +SELECT a, false - a FROM t; +SELECT a, true * a FROM t; +SELECT a, false * a FROM t; +SELECT a, true / a FROM t; +SELECT a, false / a FROM t; +SELECT a, true % a FROM t; +SELECT a, false % a FROM t; +SELECT a, a + true FROM t; +SELECT a, a + false FROM t; +SELECT a, a - true FROM t; +SELECT a, a - false FROM t; +SELECT a, a * true FROM t; +SELECT a, a * false FROM t; +SELECT a, a / true FROM t; +SELECT a, a / false FROM t; +SELECT a, a % true FROM t; +SELECT a, a % false FROM t; + +SELECT a, a1, a + a1 FROM t, t6; +SELECT a, a1, a - a1 FROM t, t6; +SELECT a, a1, a * a1 FROM t, t6; +SELECT a, a1, a / a1 FROM t, t6; +SELECT a, a1, a % a1 FROM t, t6; + +SELECT ~true; +SELECT ~false; +SELECT true & true; +SELECT true & false; +SELECT false & true; +SELECT false & false; +SELECT true | true; +SELECT true | false; +SELECT false | true; +SELECT false | false; +SELECT true << true; +SELECT true << false; +SELECT false << true; +SELECT false << false; +SELECT true >> true; +SELECT true >> false; +SELECT false >> true; +SELECT false >> false; + +SELECT a, true & a FROM t; +SELECT a, false & a FROM t; +SELECT a, true | a FROM t; +SELECT a, false | a FROM t; +SELECT a, true << a FROM t; +SELECT a, false << a FROM t; +SELECT a, true >> a FROM t; +SELECT a, false >> a FROM t; +SELECT a, a & true FROM t; +SELECT a, a & false FROM t; +SELECT a, a | true FROM t; +SELECT a, a | false FROM t; +SELECT a, a << true FROM t; +SELECT a, a << false FROM t; +SELECT a, a >> true FROM t; +SELECT a, a >> false FROM t; + +SELECT a, a1, a & a1 FROM t, t6; +SELECT a, a1, a | a1 FROM t, t6; +SELECT a, a1, a << a1 FROM t, t6; +SELECT a, a1, a >> a1 FROM t, t6; + +-- Check concatenate. +SELECT true || true; +SELECT true || false; +SELECT false || true; +SELECT false || false; + +SELECT a, true || a FROM t; +SELECT a, false || a FROM t; +SELECT a, a || true FROM t; +SELECT a, a || false FROM t; + +SELECT a1, a1 || a1 FROM t6; +SELECT a2, a2 || a2 FROM t6; +SELECT a1, a2, a1 || a1 FROM t6; +SELECT a1, a2, a2 || a2 FROM t6; + +-- Check comparisons. +SELECT true > true; +SELECT true > false; +SELECT false > true; +SELECT false > false; +SELECT true < true; +SELECT true < false; +SELECT false < true; +SELECT false < false; + +SELECT a, true > a FROM t; +SELECT a, false > a FROM t; +SELECT a, true < a FROM t; +SELECT a, false < a FROM t; +SELECT a, a > true FROM t; +SELECT a, a > false FROM t; +SELECT a, a < true FROM t; +SELECT a, a < false FROM t; + +SELECT a, a1, a > a1 FROM t, t6; +SELECT a, a1, a < a1 FROM t, t6; + +SELECT true >= true; +SELECT true >= false; +SELECT false >= true; +SELECT false >= false; +SELECT true <= true; +SELECT true <= false; +SELECT false <= true; +SELECT false <= false; + +SELECT a, true >= a FROM t; +SELECT a, false >= a FROM t; +SELECT a, true <= a FROM t; +SELECT a, false <= a FROM t; +SELECT a, a >= true FROM t; +SELECT a, a >= false FROM t; +SELECT a, a <= true FROM t; +SELECT a, a <= false FROM t; + +SELECT a, a1, a >= a1 FROM t, t6; +SELECT a, a1, a <= a1 FROM t, t6; + +SELECT true == true; +SELECT true == false; +SELECT false == true; +SELECT false == false; +SELECT true != true; +SELECT true != false; +SELECT false != true; +SELECT false != false; + +SELECT a, true == a FROM t; +SELECT a, false == a FROM t; +SELECT a, true != a FROM t; +SELECT a, false != a FROM t; +SELECT a, a == true FROM t; +SELECT a, a == false FROM t; +SELECT a, a != true FROM t; +SELECT a, a != false FROM t; + +SELECT a, a1, a == a1 FROM t, t6; +SELECT a, a1, a != a1 FROM t, t6; + +SELECT true IN (true); +SELECT false IN (true); +SELECT true IN (false); +SELECT false IN (false); +SELECT true IN (true, false); +SELECT false IN (true, false); +SELECT true IN (SELECT a1 FROM t6); +SELECT false IN (SELECT a1 FROM t6); +SELECT true IN (SELECT a1 FROM t6 LIMIT 1); +SELECT false IN (SELECT a1 FROM t6 LIMIT 1); +SELECT true IN (1, 1.2, 'true', false); +SELECT false IN (1, 1.2, 'true', false); + +SELECT a, a IN (true) FROM t; +SELECT a, a IN (false) FROM t; +SELECT a, a IN (true, false) FROM t; +SELECT a, a IN (SELECT a1 FROM t6 LIMIT 1) FROM t; +SELECT a, a IN (SELECT a1 FROM t6) FROM t; +SELECT a, a IN (1, 1.2, 'true', false) FROM t; + +SELECT true BETWEEN true AND true; +SELECT false BETWEEN true AND true; +SELECT true BETWEEN false AND false; +SELECT false BETWEEN false AND false; +SELECT true BETWEEN true AND false; +SELECT false BETWEEN true AND false; +SELECT true BETWEEN false AND true; +SELECT false BETWEEN false AND true; + +SELECT a, a BETWEEN true AND true FROM t; +SELECT a, a BETWEEN false AND false FROM t; +SELECT a, a BETWEEN true AND false FROM t; +SELECT a, a BETWEEN false AND true FROM t; + +-- Check interaction of BOOLEAN and INTEGER. +CREATE TABLE t7 (b INT PRIMARY KEY); +INSERT INTO t7 VALUES (123); + +SELECT true AND 2; +SELECT false AND 2; +SELECT true OR 2; +SELECT false OR 2; +SELECT 2 AND true; +SELECT 2 AND false; +SELECT 2 OR true; +SELECT 2 OR false; + +SELECT a1, a1 AND 2 FROM t6 +SELECT a1, a1 OR 2 FROM t6 +SELECT a1, 2 AND a1 FROM t6 +SELECT a1, 2 OR a1 FROM t6 +SELECT a2, a2 AND 2 FROM t6 +SELECT a2, a2 OR 2 FROM t6 +SELECT a2, 2 AND a2 FROM t6 +SELECT a2, 2 OR a2 FROM t6 + +SELECT b, true AND b FROM t7; +SELECT b, false AND b FROM t7; +SELECT b, true OR b FROM t7; +SELECT b, false OR b FROM t7; +SELECT b, b AND true FROM t7; +SELECT b, b AND false FROM t7; +SELECT b, b OR true FROM t7; +SELECT b, b OR false FROM t7; + +SELECT a1, b, a1 AND b FROM t6, t7; +SELECT a1, b, a1 OR b FROM t6, t7; +SELECT a1, b, b AND a1 FROM t6, t7; +SELECT a1, b, b OR a1 FROM t6, t7; +SELECT a2, b, a2 AND b FROM t6, t7; +SELECT a2, b, a2 OR b FROM t6, t7; +SELECT a2, b, b AND a2 FROM t6, t7; +SELECT a2, b, b OR a2 FROM t6, t7; + +SELECT true + 2; +SELECT false + 2; +SELECT true - 2; +SELECT false - 2; +SELECT true * 2; +SELECT false * 2; +SELECT true / 2; +SELECT false / 2; +SELECT true % 2; +SELECT false % 2; +SELECT 2 + true; +SELECT 2 + false; +SELECT 2 - true; +SELECT 2 - false; +SELECT 2 * true; +SELECT 2 * false; +SELECT 2 / true; +SELECT 2 / false; +SELECT 2 % true; +SELECT 2 % false; + +SELECT a1, a1 + 2 FROM t6 +SELECT a1, a1 - 2 FROM t6 +SELECT a1, a1 * 2 FROM t6 +SELECT a1, a1 / 2 FROM t6 +SELECT a1, a1 % 2 FROM t6 +SELECT a1, 2 + a1 FROM t6 +SELECT a1, 2 - a1 FROM t6 +SELECT a1, 2 * a1 FROM t6 +SELECT a1, 2 / a1 FROM t6 +SELECT a1, 2 % a1 FROM t6 +SELECT a2, a2 + 2 FROM t6 +SELECT a2, a2 - 2 FROM t6 +SELECT a2, a2 * 2 FROM t6 +SELECT a2, a2 / 2 FROM t6 +SELECT a2, a2 % 2 FROM t6 +SELECT a2, 2 + a2 FROM t6 +SELECT a2, 2 - a2 FROM t6 +SELECT a2, 2 * a2 FROM t6 +SELECT a2, 2 / a2 FROM t6 +SELECT a2, 2 % a2 FROM t6 + +SELECT b, true + b FROM t7; +SELECT b, false + b FROM t7; +SELECT b, true - b FROM t7; +SELECT b, false - b FROM t7; +SELECT b, true * b FROM t7; +SELECT b, false * b FROM t7; +SELECT b, true / b FROM t7; +SELECT b, false / b FROM t7; +SELECT b, true % b FROM t7; +SELECT b, false % b FROM t7; +SELECT b, b + true FROM t7; +SELECT b, b + false FROM t7; +SELECT b, b - true FROM t7; +SELECT b, b - false FROM t7; +SELECT b, b * true FROM t7; +SELECT b, b * false FROM t7; +SELECT b, b / true FROM t7; +SELECT b, b / false FROM t7; +SELECT b, b % true FROM t7; +SELECT b, b % false FROM t7; + +SELECT a1, b, a1 + b FROM t6, t7; +SELECT a1, b, a1 - b FROM t6, t7; +SELECT a1, b, a1 * b FROM t6, t7; +SELECT a1, b, a1 / b FROM t6, t7; +SELECT a1, b, a1 % b FROM t6, t7; +SELECT a1, b, b + a1 FROM t6, t7; +SELECT a1, b, b - a1 FROM t6, t7; +SELECT a1, b, b * a1 FROM t6, t7; +SELECT a1, b, b / a1 FROM t6, t7; +SELECT a1, b, b % a1 FROM t6, t7; +SELECT a2, b, a2 + b FROM t6, t7; +SELECT a2, b, a2 - b FROM t6, t7; +SELECT a2, b, a2 * b FROM t6, t7; +SELECT a2, b, a2 / b FROM t6, t7; +SELECT a2, b, a2 % b FROM t6, t7; +SELECT a2, b, b + a2 FROM t6, t7; +SELECT a2, b, b - a2 FROM t6, t7; +SELECT a2, b, b * a2 FROM t6, t7; +SELECT a2, b, b / a2 FROM t6, t7; +SELECT a2, b, b % a2 FROM t6, t7; + +SELECT true & 2; +SELECT false & 2; +SELECT true | 2; +SELECT false | 2; +SELECT true << 2; +SELECT false << 2; +SELECT true >> 2; +SELECT false >> 2; +SELECT 2 & true; +SELECT 2 & false; +SELECT 2 | true; +SELECT 2 | false; +SELECT 2 << true; +SELECT 2 << false; +SELECT 2 >> true; +SELECT 2 >> false; + +SELECT a1, a1 & 2 FROM t6 +SELECT a1, a1 | 2 FROM t6 +SELECT a1, a1 << 2 FROM t6 +SELECT a1, a1 >> 2 FROM t6 +SELECT a1, 2 & a1 FROM t6 +SELECT a1, 2 | a1 FROM t6 +SELECT a1, 2 << a1 FROM t6 +SELECT a1, 2 >> a1 FROM t6 +SELECT a2, a2 & 2 FROM t6 +SELECT a2, a2 | 2 FROM t6 +SELECT a2, a2 << 2 FROM t6 +SELECT a2, a2 >> 2 FROM t6 +SELECT a2, 2 & a2 FROM t6 +SELECT a2, 2 | a2 FROM t6 +SELECT a2, 2 << a2 FROM t6 +SELECT a2, 2 >> a2 FROM t6 + +SELECT b, true & b FROM t7; +SELECT b, false & b FROM t7; +SELECT b, true | b FROM t7; +SELECT b, false | b FROM t7; +SELECT b, true << b FROM t7; +SELECT b, false << b FROM t7; +SELECT b, true >> b FROM t7; +SELECT b, false >> b FROM t7; +SELECT b, b & true FROM t7; +SELECT b, b & false FROM t7; +SELECT b, b | true FROM t7; +SELECT b, b | false FROM t7; +SELECT b, b << true FROM t7; +SELECT b, b << false FROM t7; +SELECT b, b >> true FROM t7; +SELECT b, b >> false FROM t7; + +SELECT a1, b, a1 & b FROM t6, t7; +SELECT a1, b, a1 | b FROM t6, t7; +SELECT a1, b, a1 << b FROM t6, t7; +SELECT a1, b, a1 >> b FROM t6, t7; +SELECT a1, b, b & a1 FROM t6, t7; +SELECT a1, b, b | a1 FROM t6, t7; +SELECT a1, b, b << a1 FROM t6, t7; +SELECT a1, b, b >> a1 FROM t6, t7; +SELECT a2, b, a2 & b FROM t6, t7; +SELECT a2, b, a2 | b FROM t6, t7; +SELECT a2, b, a2 << b FROM t6, t7; +SELECT a2, b, a2 >> b FROM t6, t7; +SELECT a2, b, b & a2 FROM t6, t7; +SELECT a2, b, b | a2 FROM t6, t7; +SELECT a2, b, b << a2 FROM t6, t7; +SELECT a2, b, b >> a2 FROM t6, t7; + +SELECT true > 2; +SELECT false > 2; +SELECT true < 2; +SELECT false < 2; +SELECT 2 > true; +SELECT 2 > false; +SELECT 2 < true; +SELECT 2 < false; + +SELECT a1, a1 > 2 FROM t6 +SELECT a1, a1 < 2 FROM t6 +SELECT a1, 2 > a1 FROM t6 +SELECT a1, 2 < a1 FROM t6 +SELECT a2, a2 > 2 FROM t6 +SELECT a2, a2 < 2 FROM t6 +SELECT a2, 2 > a2 FROM t6 +SELECT a2, 2 < a2 FROM t6 + +SELECT b, true > b FROM t7; +SELECT b, false > b FROM t7; +SELECT b, true < b FROM t7; +SELECT b, false < b FROM t7; +SELECT b, b > true FROM t7; +SELECT b, b > false FROM t7; +SELECT b, b < true FROM t7; +SELECT b, b < false FROM t7; + +SELECT a1, b, a1 > b FROM t6, t7; +SELECT a1, b, a1 < b FROM t6, t7; +SELECT a1, b, b > a1 FROM t6, t7; +SELECT a1, b, b < a1 FROM t6, t7; +SELECT a2, b, a2 > b FROM t6, t7; +SELECT a2, b, a2 < b FROM t6, t7; +SELECT a2, b, b > a2 FROM t6, t7; +SELECT a2, b, b < a2 FROM t6, t7; + +SELECT true >= 2; +SELECT false >= 2; +SELECT true <= 2; +SELECT false <= 2; +SELECT 2 >= true; +SELECT 2 >= false; +SELECT 2 <= true; +SELECT 2 <= false; + +SELECT a1, a1 >= 2 FROM t6 +SELECT a1, a1 <= 2 FROM t6 +SELECT a1, 2 >= a1 FROM t6 +SELECT a1, 2 <= a1 FROM t6 +SELECT a2, a2 >= 2 FROM t6 +SELECT a2, a2 <= 2 FROM t6 +SELECT a2, 2 >= a2 FROM t6 +SELECT a2, 2 <= a2 FROM t6 + +SELECT b, true >= b FROM t7; +SELECT b, false >= b FROM t7; +SELECT b, true <= b FROM t7; +SELECT b, false <= b FROM t7; +SELECT b, b >= true FROM t7; +SELECT b, b >= false FROM t7; +SELECT b, b <= true FROM t7; +SELECT b, b <= false FROM t7; + +SELECT a1, b, a1 >= b FROM t6, t7; +SELECT a1, b, a1 <= b FROM t6, t7; +SELECT a1, b, b >= a1 FROM t6, t7; +SELECT a1, b, b <= a1 FROM t6, t7; +SELECT a2, b, a2 >= b FROM t6, t7; +SELECT a2, b, a2 <= b FROM t6, t7; +SELECT a2, b, b >= a2 FROM t6, t7; +SELECT a2, b, b <= a2 FROM t6, t7; + +SELECT true == 2; +SELECT false == 2; +SELECT true != 2; +SELECT false != 2; +SELECT 2 == true; +SELECT 2 == false; +SELECT 2 != true; +SELECT 2 != false; + +SELECT a1, a1 == 2 FROM t6 +SELECT a1, a1 != 2 FROM t6 +SELECT a1, 2 == a1 FROM t6 +SELECT a1, 2 != a1 FROM t6 +SELECT a2, a2 == 2 FROM t6 +SELECT a2, a2 != 2 FROM t6 +SELECT a2, 2 == a2 FROM t6 +SELECT a2, 2 != a2 FROM t6 + +SELECT b, true == b FROM t7; +SELECT b, false == b FROM t7; +SELECT b, true != b FROM t7; +SELECT b, false != b FROM t7; +SELECT b, b == true FROM t7; +SELECT b, b == false FROM t7; +SELECT b, b != true FROM t7; +SELECT b, b != false FROM t7; + +SELECT a1, b, a1 == b FROM t6, t7; +SELECT a1, b, a1 != b FROM t6, t7; +SELECT a1, b, b == a1 FROM t6, t7; +SELECT a1, b, b != a1 FROM t6, t7; +SELECT a2, b, a2 == b FROM t6, t7; +SELECT a2, b, a2 != b FROM t6, t7; +SELECT a2, b, b == a2 FROM t6, t7; +SELECT a2, b, b != a2 FROM t6, t7; + +SELECT true IN (0, 1, 2, 3); +SELECT false IN (0, 1, 2, 3); +SELECT true IN (SELECT b FROM t7); +SELECT false IN (SELECT b FROM t7); +SELECT a1, a1 IN (0, 1, 2, 3) FROM t6 + +SELECT true BETWEEN 0 and 10; +SELECT false BETWEEN 0 and 10; +SELECT a1, a1 BETWEEN 0 and 10 FROM t6; +SELECT a2, a2 BETWEEN 0 and 10 FROM t6; + +-- Check interaction of BOOLEAN and REAL. +CREATE TABLE t8 (c REAL PRIMARY KEY); +INSERT INTO t8 VALUES (4.56); + +SELECT true AND 2.3; +SELECT false AND 2.3; +SELECT true OR 2.3; +SELECT false OR 2.3; +SELECT 2.3 AND true; +SELECT 2.3 AND false; +SELECT 2.3 OR true; +SELECT 2.3 OR false; + +SELECT a1, a1 AND 2.3 FROM t6 +SELECT a1, a1 OR 2.3 FROM t6 +SELECT a1, 2.3 AND a1 FROM t6 +SELECT a1, 2.3 OR a1 FROM t6 +SELECT a2, a2 AND 2.3 FROM t6 +SELECT a2, a2 OR 2.3 FROM t6 +SELECT a2, 2.3 AND a2 FROM t6 +SELECT a2, 2.3 OR a2 FROM t6 + +SELECT c, true AND c FROM t8; +SELECT c, false AND c FROM t8; +SELECT c, true OR c FROM t8; +SELECT c, false OR c FROM t8; +SELECT c, c AND true FROM t8; +SELECT c, c AND false FROM t8; +SELECT c, c OR true FROM t8; +SELECT c, c OR false FROM t8; + +SELECT a1, c, a1 AND c FROM t6, t8; +SELECT a1, c, a1 OR c FROM t6, t8; +SELECT a1, c, c AND a1 FROM t6, t8; +SELECT a1, c, c OR a1 FROM t6, t8; +SELECT a2, c, a2 AND c FROM t6, t8; +SELECT a2, c, a2 OR c FROM t6, t8; +SELECT a2, c, c AND a2 FROM t6, t8; +SELECT a2, c, c OR a2 FROM t6, t8; + +SELECT true + 2.3; +SELECT false + 2.3; +SELECT true - 2.3; +SELECT false - 2.3; +SELECT true * 2.3; +SELECT false * 2.3; +SELECT true / 2.3; +SELECT false / 2.3; +SELECT true % 2.3; +SELECT false % 2.3; +SELECT 2.3 + true; +SELECT 2.3 + false; +SELECT 2.3 - true; +SELECT 2.3 - false; +SELECT 2.3 * true; +SELECT 2.3 * false; +SELECT 2.3 / true; +SELECT 2.3 / false; +SELECT 2.3 % true; +SELECT 2.3 % false; + +SELECT a1, a1 + 2.3 FROM t6 +SELECT a1, a1 - 2.3 FROM t6 +SELECT a1, a1 * 2.3 FROM t6 +SELECT a1, a1 / 2.3 FROM t6 +SELECT a1, a1 % 2.3 FROM t6 +SELECT a1, 2.3 + a1 FROM t6 +SELECT a1, 2.3 - a1 FROM t6 +SELECT a1, 2.3 * a1 FROM t6 +SELECT a1, 2.3 / a1 FROM t6 +SELECT a1, 2.3 % a1 FROM t6 +SELECT a2, a2 + 2.3 FROM t6 +SELECT a2, a2 - 2.3 FROM t6 +SELECT a2, a2 * 2.3 FROM t6 +SELECT a2, a2 / 2.3 FROM t6 +SELECT a2, a2 % 2.3 FROM t6 +SELECT a2, 2.3 + a2 FROM t6 +SELECT a2, 2.3 - a2 FROM t6 +SELECT a2, 2.3 * a2 FROM t6 +SELECT a2, 2.3 / a2 FROM t6 +SELECT a2, 2.3 % a2 FROM t6 + +SELECT c, true + c FROM t8; +SELECT c, false + c FROM t8; +SELECT c, true - c FROM t8; +SELECT c, false - c FROM t8; +SELECT c, true * c FROM t8; +SELECT c, false * c FROM t8; +SELECT c, true / c FROM t8; +SELECT c, false / c FROM t8; +SELECT c, true % c FROM t8; +SELECT c, false % c FROM t8; +SELECT c, c + true FROM t8; +SELECT c, c + false FROM t8; +SELECT c, c - true FROM t8; +SELECT c, c - false FROM t8; +SELECT c, c * true FROM t8; +SELECT c, c * false FROM t8; +SELECT c, c / true FROM t8; +SELECT c, c / false FROM t8; +SELECT c, c % true FROM t8; +SELECT c, c % false FROM t8; + +SELECT a1, c, a1 + c FROM t6, t8; +SELECT a1, c, a1 - c FROM t6, t8; +SELECT a1, c, a1 * c FROM t6, t8; +SELECT a1, c, a1 / c FROM t6, t8; +SELECT a1, c, a1 % c FROM t6, t8; +SELECT a1, c, c + a1 FROM t6, t8; +SELECT a1, c, c - a1 FROM t6, t8; +SELECT a1, c, c * a1 FROM t6, t8; +SELECT a1, c, c / a1 FROM t6, t8; +SELECT a1, c, c % a1 FROM t6, t8; +SELECT a2, c, a2 + c FROM t6, t8; +SELECT a2, c, a2 - c FROM t6, t8; +SELECT a2, c, a2 * c FROM t6, t8; +SELECT a2, c, a2 / c FROM t6, t8; +SELECT a2, c, a2 % c FROM t6, t8; +SELECT a2, c, c + a2 FROM t6, t8; +SELECT a2, c, c - a2 FROM t6, t8; +SELECT a2, c, c * a2 FROM t6, t8; +SELECT a2, c, c / a2 FROM t6, t8; +SELECT a2, c, c % a2 FROM t6, t8; + +SELECT true > 2.3; +SELECT false > 2.3; +SELECT true < 2.3; +SELECT false < 2.3; +SELECT 2.3 > true; +SELECT 2.3 > false; +SELECT 2.3 < true; +SELECT 2.3 < false; + +SELECT a1, a1 > 2.3 FROM t6 +SELECT a1, a1 < 2.3 FROM t6 +SELECT a1, 2.3 > a1 FROM t6 +SELECT a1, 2.3 < a1 FROM t6 +SELECT a2, a2 > 2.3 FROM t6 +SELECT a2, a2 < 2.3 FROM t6 +SELECT a2, 2.3 > a2 FROM t6 +SELECT a2, 2.3 < a2 FROM t6 + +SELECT c, true > c FROM t8; +SELECT c, false > c FROM t8; +SELECT c, true < c FROM t8; +SELECT c, false < c FROM t8; +SELECT c, c > true FROM t8; +SELECT c, c > false FROM t8; +SELECT c, c < true FROM t8; +SELECT c, c < false FROM t8; + +SELECT a1, c, a1 > c FROM t6, t8; +SELECT a1, c, a1 < c FROM t6, t8; +SELECT a1, c, c > a1 FROM t6, t8; +SELECT a1, c, c < a1 FROM t6, t8; +SELECT a2, c, a2 > c FROM t6, t8; +SELECT a2, c, a2 < c FROM t6, t8; +SELECT a2, c, c > a2 FROM t6, t8; +SELECT a2, c, c < a2 FROM t6, t8; + +SELECT true >= 2.3; +SELECT false >= 2.3; +SELECT true <= 2.3; +SELECT false <= 2.3; +SELECT 2.3 >= true; +SELECT 2.3 >= false; +SELECT 2.3 <= true; +SELECT 2.3 <= false; + +SELECT a1, a1 >= 2.3 FROM t6 +SELECT a1, a1 <= 2.3 FROM t6 +SELECT a1, 2.3 >= a1 FROM t6 +SELECT a1, 2.3 <= a1 FROM t6 +SELECT a2, a2 >= 2.3 FROM t6 +SELECT a2, a2 <= 2.3 FROM t6 +SELECT a2, 2.3 >= a2 FROM t6 +SELECT a2, 2.3 <= a2 FROM t6 + +SELECT c, true >= c FROM t8; +SELECT c, false >= c FROM t8; +SELECT c, true <= c FROM t8; +SELECT c, false <= c FROM t8; +SELECT c, c >= true FROM t8; +SELECT c, c >= false FROM t8; +SELECT c, c <= true FROM t8; +SELECT c, c <= false FROM t8; + +SELECT a1, c, a1 >= c FROM t6, t8; +SELECT a1, c, a1 <= c FROM t6, t8; +SELECT a1, c, c >= a1 FROM t6, t8; +SELECT a1, c, c <= a1 FROM t6, t8; +SELECT a2, c, a2 >= c FROM t6, t8; +SELECT a2, c, a2 <= c FROM t6, t8; +SELECT a2, c, c >= a2 FROM t6, t8; +SELECT a2, c, c <= a2 FROM t6, t8; + +SELECT true == 2.3; +SELECT false == 2.3; +SELECT true != 2.3; +SELECT false != 2.3; +SELECT 2.3 == true; +SELECT 2.3 == false; +SELECT 2.3 != true; +SELECT 2.3 != false; + +SELECT a1, a1 == 2.3 FROM t6 +SELECT a1, a1 != 2.3 FROM t6 +SELECT a1, 2.3 == a1 FROM t6 +SELECT a1, 2.3 != a1 FROM t6 +SELECT a2, a2 == 2.3 FROM t6 +SELECT a2, a2 != 2.3 FROM t6 +SELECT a2, 2.3 == a2 FROM t6 +SELECT a2, 2.3 != a2 FROM t6 + +SELECT c, true == c FROM t8; +SELECT c, false == c FROM t8; +SELECT c, true != c FROM t8; +SELECT c, false != c FROM t8; +SELECT c, c == true FROM t8; +SELECT c, c == false FROM t8; +SELECT c, c != true FROM t8; +SELECT c, c != false FROM t8; + +SELECT a1, c, a1 == c FROM t6, t8; +SELECT a1, c, a1 != c FROM t6, t8; +SELECT a1, c, c == a1 FROM t6, t8; +SELECT a1, c, c != a1 FROM t6, t8; +SELECT a2, c, a2 == c FROM t6, t8; +SELECT a2, c, a2 != c FROM t6, t8; +SELECT a2, c, c == a2 FROM t6, t8; +SELECT a2, c, c != a2 FROM t6, t8; + +SELECT true IN (0.1, 1.2, 2.3, 3.4); +SELECT false IN (0.1, 1.2, 2.3, 3.4); +SELECT a1 IN (0.1, 1.2, 2.3, 3.4) FROM t6 LIMIT 1; +SELECT a2 IN (0.1, 1.2, 2.3, 3.4) FROM t6 LIMIT 1; +SELECT true IN (SELECT c FROM t8); +SELECT false IN (SELECT c FROM t8); +SELECT a1 IN (SELECT c FROM t8) FROM t6 LIMIT 1; +SELECT a2 IN (SELECT c FROM t8) FROM t6 LIMIT 1; + +SELECT true BETWEEN 0.1 and 9.9; +SELECT false BETWEEN 0.1 and 9.9; +SELECT a1, a1 BETWEEN 0.1 and 9.9 FROM t6; +SELECT a2, a2 BETWEEN 0.1 and 9.9 FROM t6; + +-- Check interaction of BOOLEAN and TEXT. +CREATE TABLE t9 (d TEXT PRIMARY KEY); +INSERT INTO t9 VALUES ('AsdF'); + +SELECT true AND 'abc'; +SELECT false AND 'abc'; +SELECT true OR 'abc'; +SELECT false OR 'abc'; +SELECT 'abc' AND true; +SELECT 'abc' AND false; +SELECT 'abc' OR true; +SELECT 'abc' OR false; + +SELECT a1, a1 AND 'abc' FROM t6; +SELECT a1, a1 OR 'abc' FROM t6; +SELECT a1, 'abc' AND a1 FROM t6; +SELECT a1, 'abc' OR a1 FROM t6; +SELECT a2, a2 AND 'abc' FROM t6; +SELECT a2, a2 OR 'abc' FROM t6; +SELECT a2, 'abc' AND a2 FROM t6; +SELECT a2, 'abc' OR a2 FROM t6; + +SELECT d, true AND d FROM t9; +SELECT d, false AND d FROM t9; +SELECT d, true OR d FROM t9; +SELECT d, false OR d FROM t9; +SELECT d, d AND true FROM t9; +SELECT d, d AND false FROM t9; +SELECT d, d OR true FROM t9; +SELECT d, d OR false FROM t9; + +SELECT a1, d, a1 AND d FROM t6, t9; +SELECT a1, d, a1 OR d FROM t6, t9; +SELECT a1, d, d AND a1 FROM t6, t9; +SELECT a1, d, d OR a1 FROM t6, t9; +SELECT a2, d, a2 AND d FROM t6, t9; +SELECT a2, d, a2 OR d FROM t6, t9; +SELECT a2, d, d AND a2 FROM t6, t9; +SELECT a2, d, d OR a2 FROM t6, t9; + +SELECT true > 'abc'; +SELECT false > 'abc'; +SELECT true < 'abc'; +SELECT false < 'abc'; +SELECT 'abc' > true; +SELECT 'abc' > false; +SELECT 'abc' < true; +SELECT 'abc' < false; + +SELECT d, true > d FROM t9; +SELECT d, false > d FROM t9; +SELECT d, true < d FROM t9; +SELECT d, false < d FROM t9; +SELECT d, d > true FROM t9; +SELECT d, d > false FROM t9; +SELECT d, d < true FROM t9; +SELECT d, d < false FROM t9; + +SELECT a1, d, a1 > d FROM t6, t9; +SELECT a1, d, a1 < d FROM t6, t9; +SELECT a1, d, d > a1 FROM t6, t9; +SELECT a1, d, d < a1 FROM t6, t9; +SELECT a2, d, a2 > d FROM t6, t9; +SELECT a2, d, a2 < d FROM t6, t9; +SELECT a2, d, d > a2 FROM t6, t9; +SELECT a2, d, d < a2 FROM t6, t9; + +SELECT true || 'abc'; +SELECT false || 'abc'; +SELECT 'abc' || false; +SELECT 'abc' || true; + +SELECT d, true || d FROM t9; +SELECT d, false || d FROM t9; +SELECT d, d || false FROM t9; +SELECT d, d || true FROM t9; + +SELECT d, a1 || d FROM t6, t9; +SELECT d, a2 || d FROM t6, t9; +SELECT d, d || a1 FROM t6, t9; +SELECT d, d || a2 FROM t6, t9; + +-- +-- Check special types of strings: 'TRUE', 'true', 'FALSE', +-- 'false'. +-- +INSERT INTO t9 VALUES ('TRUE'), ('true'), ('FALSE'), ('false'); + +SELECT true AND 'TRUE'; +SELECT false AND 'TRUE'; +SELECT true OR 'TRUE'; +SELECT false OR 'TRUE'; +SELECT 'TRUE' AND true; +SELECT 'TRUE' AND false; +SELECT 'TRUE' OR true; +SELECT 'TRUE' OR false; + +SELECT a1, a1 AND 'TRUE' FROM t6; +SELECT a1, a1 OR 'TRUE' FROM t6; +SELECT a1, 'TRUE' AND a1 FROM t6; +SELECT a1, 'TRUE' OR a1 FROM t6; +SELECT a2, a2 AND 'TRUE' FROM t6; +SELECT a2, a2 OR 'TRUE' FROM t6; +SELECT a2, 'TRUE' AND a2 FROM t6; +SELECT a2, 'TRUE' OR a2 FROM t6; + +SELECT d, true AND d FROM t9 WHERE d = 'TRUE'; +SELECT d, false AND d FROM t9 WHERE d = 'TRUE'; +SELECT d, true OR d FROM t9 WHERE d = 'TRUE'; +SELECT d, false OR d FROM t9 WHERE d = 'TRUE'; +SELECT d, d AND true FROM t9 WHERE d = 'TRUE'; +SELECT d, d AND false FROM t9 WHERE d = 'TRUE'; +SELECT d, d OR true FROM t9 WHERE d = 'TRUE'; +SELECT d, d OR false FROM t9 WHERE d = 'TRUE'; + +SELECT a1, d, a1 AND d FROM t6, t9 WHERE d = 'TRUE'; +SELECT a1, d, a1 OR d FROM t6, t9 WHERE d = 'TRUE'; +SELECT a1, d, d AND a1 FROM t6, t9 WHERE d = 'TRUE'; +SELECT a1, d, d OR a1 FROM t6, t9 WHERE d = 'TRUE'; +SELECT a2, d, a2 AND d FROM t6, t9 WHERE d = 'TRUE'; +SELECT a2, d, a2 OR d FROM t6, t9 WHERE d = 'TRUE'; +SELECT a2, d, d AND a2 FROM t6, t9 WHERE d = 'TRUE'; +SELECT a2, d, d OR a2 FROM t6, t9 WHERE d = 'TRUE'; + +SELECT true AND 'true'; +SELECT false AND 'true'; +SELECT true OR 'true'; +SELECT false OR 'true'; +SELECT 'true' AND true; +SELECT 'true' AND false; +SELECT 'true' OR true; +SELECT 'true' OR false; + +SELECT a1, a1 AND 'true' FROM t6; +SELECT a1, a1 OR 'true' FROM t6; +SELECT a1, 'true' AND a1 FROM t6; +SELECT a1, 'true' OR a1 FROM t6; +SELECT a2, a2 AND 'true' FROM t6; +SELECT a2, a2 OR 'true' FROM t6; +SELECT a2, 'true' AND a2 FROM t6; +SELECT a2, 'true' OR a2 FROM t6; + +SELECT d, true AND d FROM t9 WHERE d = 'true'; +SELECT d, false AND d FROM t9 WHERE d = 'true'; +SELECT d, true OR d FROM t9 WHERE d = 'true'; +SELECT d, false OR d FROM t9 WHERE d = 'true'; +SELECT d, d AND true FROM t9 WHERE d = 'true'; +SELECT d, d AND false FROM t9 WHERE d = 'true'; +SELECT d, d OR true FROM t9 WHERE d = 'true'; +SELECT d, d OR false FROM t9 WHERE d = 'true'; + +SELECT a1, d, a1 AND d FROM t6, t9 WHERE d = 'true'; +SELECT a1, d, a1 OR d FROM t6, t9 WHERE d = 'true'; +SELECT a1, d, d AND a1 FROM t6, t9 WHERE d = 'true'; +SELECT a1, d, d OR a1 FROM t6, t9 WHERE d = 'true'; +SELECT a2, d, a2 AND d FROM t6, t9 WHERE d = 'true'; +SELECT a2, d, a2 OR d FROM t6, t9 WHERE d = 'true'; +SELECT a2, d, d AND a2 FROM t6, t9 WHERE d = 'true'; +SELECT a2, d, d OR a2 FROM t6, t9 WHERE d = 'true'; + +SELECT true AND 'FALSE'; +SELECT false AND 'FALSE'; +SELECT true OR 'FALSE'; +SELECT false OR 'FALSE'; +SELECT 'FALSE' AND true; +SELECT 'FALSE' AND false; +SELECT 'FALSE' OR true; +SELECT 'FALSE' OR false; + +SELECT a1, a1 AND 'FALSE' FROM t6; +SELECT a1, a1 OR 'FALSE' FROM t6; +SELECT a1, 'FALSE' AND a1 FROM t6; +SELECT a1, 'FALSE' OR a1 FROM t6; +SELECT a2, a2 AND 'FALSE' FROM t6; +SELECT a2, a2 OR 'FALSE' FROM t6; +SELECT a2, 'FALSE' AND a2 FROM t6; +SELECT a2, 'FALSE' OR a2 FROM t6; + +SELECT d, true AND d FROM t9 WHERE d = 'FALSE'; +SELECT d, false AND d FROM t9 WHERE d = 'FALSE'; +SELECT d, true OR d FROM t9 WHERE d = 'FALSE'; +SELECT d, false OR d FROM t9 WHERE d = 'FALSE'; +SELECT d, d AND true FROM t9 WHERE d = 'FALSE'; +SELECT d, d AND false FROM t9 WHERE d = 'FALSE'; +SELECT d, d OR true FROM t9 WHERE d = 'FALSE'; +SELECT d, d OR false FROM t9 WHERE d = 'FALSE'; + +SELECT a1, d, a1 AND d FROM t6, t9 WHERE d = 'FALSE'; +SELECT a1, d, a1 OR d FROM t6, t9 WHERE d = 'FALSE'; +SELECT a1, d, d AND a1 FROM t6, t9 WHERE d = 'FALSE'; +SELECT a1, d, d OR a1 FROM t6, t9 WHERE d = 'FALSE'; +SELECT a2, d, a2 AND d FROM t6, t9 WHERE d = 'FALSE'; +SELECT a2, d, a2 OR d FROM t6, t9 WHERE d = 'FALSE'; +SELECT a2, d, d AND a2 FROM t6, t9 WHERE d = 'FALSE'; +SELECT a2, d, d OR a2 FROM t6, t9 WHERE d = 'FALSE'; + +SELECT true AND 'false'; +SELECT false AND 'false'; +SELECT true OR 'false'; +SELECT false OR 'false'; +SELECT 'false' AND true; +SELECT 'false' AND false; +SELECT 'false' OR true; +SELECT 'false' OR false; + +SELECT a1, a1 AND 'false' FROM t6; +SELECT a1, a1 OR 'false' FROM t6; +SELECT a1, 'false' AND a1 FROM t6; +SELECT a1, 'false' OR a1 FROM t6; +SELECT a2, a2 AND 'false' FROM t6; +SELECT a2, a2 OR 'false' FROM t6; +SELECT a2, 'false' AND a2 FROM t6; +SELECT a2, 'false' OR a2 FROM t6; + +SELECT d, true AND d FROM t9 WHERE d = 'false'; +SELECT d, false AND d FROM t9 WHERE d = 'false'; +SELECT d, true OR d FROM t9 WHERE d = 'false'; +SELECT d, false OR d FROM t9 WHERE d = 'false'; +SELECT d, d AND true FROM t9 WHERE d = 'false'; +SELECT d, d AND false FROM t9 WHERE d = 'false'; +SELECT d, d OR true FROM t9 WHERE d = 'false'; +SELECT d, d OR false FROM t9 WHERE d = 'false'; + +SELECT a1, d, a1 AND d FROM t6, t9 WHERE d = 'false'; +SELECT a1, d, a1 OR d FROM t6, t9 WHERE d = 'false'; +SELECT a1, d, d AND a1 FROM t6, t9 WHERE d = 'false'; +SELECT a1, d, d OR a1 FROM t6, t9 WHERE d = 'false'; +SELECT a2, d, a2 AND d FROM t6, t9 WHERE d = 'false'; +SELECT a2, d, a2 OR d FROM t6, t9 WHERE d = 'false'; +SELECT a2, d, d AND a2 FROM t6, t9 WHERE d = 'false'; +SELECT a2, d, d OR a2 FROM t6, t9 WHERE d = 'false'; + +-- Cleaning. +DROP VIEW v; +DROP TABLE t9; +DROP TABLE t8; +DROP TABLE t7; +DROP TABLE t6; +DROP TABLE t5; +DROP TABLE t4; +DROP TABLE t3; +DROP TABLE t2; +DROP TABLE t1; +DROP TABLE t0; +DROP TABLE ts; +DROP TABLE t; diff --git a/test/sql/types.result b/test/sql/types.result index 5abe6e0..4eceb36 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -279,656 +279,6 @@ box.execute("SELECT CAST(f AS INTEGER) FROM t1;") box.space.T1:drop() --- ... --- Test basic capabilities of boolean type. --- -box.execute("SELECT true;") ---- -- metadata: - - name: 'true' - type: boolean - rows: - - [true] -... -box.execute("SELECT false;") ---- -- metadata: - - name: 'false' - type: boolean - rows: - - [false] -... -box.execute("SELECT unknown;") ---- -- metadata: - - name: unknown - type: scalar - rows: - - [null] -... -box.execute("SELECT true = false;") ---- -- metadata: - - name: true = false - type: boolean - rows: - - [false] -... -box.execute("SELECT true = true;") ---- -- metadata: - - name: true = true - type: boolean - rows: - - [true] -... -box.execute("SELECT true > false;") ---- -- metadata: - - name: true > false - type: boolean - rows: - - [true] -... -box.execute("SELECT true < false;") ---- -- metadata: - - name: true < false - type: boolean - rows: - - [false] -... -box.execute("SELECT null = true;") ---- -- metadata: - - name: null = true - type: boolean - rows: - - [null] -... -box.execute("SELECT unknown = true;") ---- -- metadata: - - name: unknown = true - type: boolean - rows: - - [null] -... -box.execute("SELECT 1 = true;") ---- -- error: 'Type mismatch: can not convert INTEGER to boolean' -... -box.execute("SELECT 'abc' = true;") ---- -- error: 'Type mismatch: can not convert TEXT to boolean' -... -box.execute("SELECT 1.123 > true;") ---- -- error: 'Type mismatch: can not convert REAL to boolean' -... -box.execute("SELECT true IN (1, 'abc', true)") ---- -- metadata: - - name: true IN (1, 'abc', true) - type: boolean - rows: - - [true] -... -box.execute("SELECT true IN (1, 'abc', false)") ---- -- metadata: - - name: true IN (1, 'abc', false) - type: boolean - rows: - - [false] -... -box.execute("SELECT 1 LIMIT true;") ---- -- error: 'Failed to execute SQL statement: Only positive integers are allowed in the - LIMIT clause' -... -box.execute("SELECT 1 LIMIT 1 OFFSET true;") ---- -- error: 'Failed to execute SQL statement: Only positive integers are allowed in the - OFFSET clause' -... -box.execute("SELECT 'abc' || true;") ---- -- error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' -... --- Boolean can take part in arithmetic operations. --- -box.execute("SELECT true + false;") ---- -- error: 'Type mismatch: can not convert false to numeric' -... -box.execute("SELECT true * 1;") ---- -- error: 'Type mismatch: can not convert true to numeric' -... -box.execute("SELECT false / 0;") ---- -- error: 'Type mismatch: can not convert false to numeric' -... -box.execute("SELECT not true;") ---- -- metadata: - - name: not true - type: boolean - rows: - - [false] -... -box.execute("SELECT ~true;") ---- -- error: 'Type mismatch: can not convert true to integer' -... -box.execute("SELECT -true;") ---- -- error: 'Type mismatch: can not convert true to numeric' -... -box.execute("SELECT true << 1;") ---- -- error: 'Type mismatch: can not convert true to integer' -... -box.execute("SELECT true | 1;") ---- -- error: 'Type mismatch: can not convert true to integer' -... -box.execute("SELECT true and false;") ---- -- metadata: - - name: true and false - type: boolean - rows: - - [false] -... -box.execute("SELECT true or unknown;") ---- -- metadata: - - name: true or unknown - type: boolean - rows: - - [true] -... -box.execute("CREATE TABLE t (id INT PRIMARY KEY, b BOOLEAN);") ---- -- row_count: 1 -... -box.execute("INSERT INTO t VALUES (1, true);") ---- -- row_count: 1 -... -box.execute("INSERT INTO t VALUES (2, false);") ---- -- row_count: 1 -... -box.execute("INSERT INTO t VALUES (3, unknown)") ---- -- row_count: 1 -... -box.execute("SELECT b FROM t;") ---- -- metadata: - - name: B - type: boolean - rows: - - [true] - - [false] - - [null] -... -box.execute("SELECT b FROM t WHERE b = false;") ---- -- metadata: - - name: B - type: boolean - rows: - - [false] -... -box.execute("SELECT b FROM t WHERE b IS NULL;") ---- -- metadata: - - name: B - type: boolean - rows: - - [null] -... -box.execute("SELECT b FROM t WHERE b IN (false, 1, 'abc')") ---- -- metadata: - - name: B - type: boolean - rows: - - [false] -... -box.execute("SELECT b FROM t WHERE b BETWEEN false AND true;") ---- -- metadata: - - name: B - type: boolean - rows: - - [true] - - [false] -... -box.execute("SELECT b FROM t WHERE b BETWEEN true AND false;") ---- -- metadata: - - name: B - type: boolean - rows: [] -... -box.execute("SELECT b FROM t ORDER BY b;") ---- -- metadata: - - name: B - type: boolean - rows: - - [null] - - [false] - - [true] -... -box.execute("SELECT b FROM t ORDER BY +b;") ---- -- metadata: - - name: B - type: boolean - rows: - - [null] - - [false] - - [true] -... -box.execute("SELECT b FROM t ORDER BY b LIMIT 1;") ---- -- metadata: - - name: B - type: boolean - rows: - - [null] -... -box.execute("SELECT b FROM t GROUP BY b LIMIT 1;") ---- -- metadata: - - name: B - type: boolean - rows: - - [null] -... -box.execute("SELECT b FROM t LIMIT true;") ---- -- error: 'Failed to execute SQL statement: Only positive integers are allowed in the - LIMIT clause' -... --- Most of aggregates don't accept boolean arguments. --- -box.execute("SELECT sum(b) FROM t;") ---- -- error: 'Type mismatch: can not convert true to number' -... -box.execute("SELECT avg(b) FROM t;") ---- -- error: 'Type mismatch: can not convert true to number' -... -box.execute("SELECT total(b) FROM t;") ---- -- error: 'Type mismatch: can not convert true to number' -... -box.execute("SELECT min(b) FROM t;") ---- -- metadata: - - name: min(b) - type: scalar - rows: - - [false] -... -box.execute("SELECT max(b) FROM t;") ---- -- metadata: - - name: max(b) - type: scalar - rows: - - [true] -... -box.execute("SELECT count(b) FROM t;") ---- -- metadata: - - name: count(b) - type: integer - rows: - - [2] -... -box.execute("SELECT group_concat(b) FROM t;") ---- -- metadata: - - name: group_concat(b) - type: string - rows: - - ['true,false'] -... --- Check other built-in functions. --- -box.execute("SELECT lower(b) FROM t;") ---- -- metadata: - - name: lower(b) - type: string - rows: - - ['true'] - - ['false'] - - [null] -... -box.execute("SELECT upper(b) FROM t;") ---- -- metadata: - - name: upper(b) - type: string - rows: - - ['TRUE'] - - ['FALSE'] - - [null] -... -box.execute("SELECT abs(b) FROM t;") ---- -- error: 'Inconsistent types: expected number got boolean' -... -box.execute("SELECT typeof(b) FROM t;") ---- -- metadata: - - name: typeof(b) - type: string - rows: - - ['boolean'] - - ['boolean'] - - ['null'] -... -box.execute("SELECT quote(b) FROM t;") ---- -- metadata: - - name: quote(b) - type: string - rows: - - ['true'] - - ['false'] - - ['NULL'] -... -box.execute("SELECT min(b, true) FROM t;") ---- -- metadata: - - name: min(b, true) - type: scalar - rows: - - [true] - - [false] - - [null] -... -box.execute("SELECT quote(b) FROM t;") ---- -- metadata: - - name: quote(b) - type: string - rows: - - ['true'] - - ['false'] - - ['NULL'] -... --- Test index search using boolean values. --- -box.execute("CREATE INDEX ib ON t(b);") ---- -- row_count: 1 -... -box.execute("SELECT b FROM t WHERE b = false;") ---- -- metadata: - - name: B - type: boolean - rows: - - [false] -... -box.execute("SELECT b FROM t WHERE b OR unknown ORDER BY b;") ---- -- metadata: - - name: B - type: boolean - rows: - - [true] -... --- Test UPDATE on boolean field. --- -box.execute("UPDATE t SET b = true WHERE b = false;") ---- -- row_count: 1 -... -box.execute("SELECT b FROM t;") ---- -- metadata: - - name: B - type: boolean - rows: - - [true] - - [true] - - [null] -... --- Test constraints functionality. --- -box.execute("CREATE TABLE parent (id INT PRIMARY KEY, a BOOLEAN UNIQUE);") ---- -- row_count: 1 -... -box.space.T:truncate() ---- -... -box.execute("ALTER TABLE t ADD CONSTRAINT fk1 FOREIGN KEY (b) REFERENCES parent (a);") ---- -- row_count: 1 -... -box.execute("INSERT INTO t VALUES (1, true);") ---- -- error: 'Failed to execute SQL statement: FOREIGN KEY constraint failed' -... -box.execute("INSERT INTO parent VALUES (1, true);") ---- -- row_count: 1 -... -box.execute("INSERT INTO t VALUES (1, true);") ---- -- row_count: 1 -... -box.execute("ALTER TABLE t DROP CONSTRAINT fk1;") ---- -- row_count: 1 -... -box.space.PARENT:drop() ---- -... -box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a BOOLEAN CHECK (a = true));") ---- -- row_count: 1 -... -box.execute("INSERT INTO t1 VALUES (1, false);") ---- -- error: 'Check constraint failed ''CK_CONSTRAINT_1_T1'': a = true' -... -box.execute("INSERT INTO t1 VALUES (1, true);") ---- -- row_count: 1 -... -box.space.T1:drop() ---- -... -box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a BOOLEAN DEFAULT true);") ---- -- row_count: 1 -... -box.execute("INSERT INTO t1 (id) VALUES (1);") ---- -- row_count: 1 -... -box.space.T1:select() ---- -- - [1, true] -... -box.space.T1:drop() ---- -... --- Check that VIEW inherits boolean type. --- -box.execute("CREATE VIEW v AS SELECT b FROM t;") ---- -- row_count: 1 -... -box.space.V:format()[1]['type'] ---- -- boolean -... -box.space.V:drop() ---- -... --- Test CAST facilities. --- -box.execute("SELECT CAST(true AS INTEGER);") ---- -- metadata: - - name: CAST(true AS INTEGER) - type: integer - rows: - - [1] -... -box.execute("SELECT CAST(true AS TEXT);") ---- -- metadata: - - name: CAST(true AS TEXT) - type: string - rows: - - ['TRUE'] -... -box.execute("SELECT CAST(true AS FLOAT);") ---- -- error: 'Type mismatch: can not convert true to number' -... -box.execute("SELECT CAST(true AS SCALAR);") ---- -- metadata: - - name: CAST(true AS SCALAR) - type: scalar - rows: - - [true] -... -box.execute("SELECT CAST(1 AS BOOLEAN);") ---- -- metadata: - - name: CAST(1 AS BOOLEAN) - type: boolean - rows: - - [true] -... -box.execute("SELECT CAST(1.123 AS BOOLEAN);") ---- -- metadata: - - name: CAST(1.123 AS BOOLEAN) - type: boolean - rows: - - [true] -... -box.execute("SELECT CAST(0.0 AS BOOLEAN);") ---- -- metadata: - - name: CAST(0.0 AS BOOLEAN) - type: boolean - rows: - - [false] -... -box.execute("SELECT CAST(0.00000001 AS BOOLEAN);") ---- -- metadata: - - name: CAST(0.00000001 AS BOOLEAN) - type: boolean - rows: - - [true] -... -box.execute("SELECT CAST('abc' AS BOOLEAN);") ---- -- error: 'Type mismatch: can not convert abc to boolean' -... -box.execute("SELECT CAST(' TrUe' AS BOOLEAN);") ---- -- metadata: - - name: CAST(' TrUe' AS BOOLEAN) - type: boolean - rows: - - [true] -... -box.execute("SELECT CAST(' falsE ' AS BOOLEAN);") ---- -- metadata: - - name: CAST(' falsE ' AS BOOLEAN) - type: boolean - rows: - - [false] -... -box.execute("SELECT CAST(' fals' AS BOOLEAN);") ---- -- error: 'Type mismatch: can not convert fals to boolean' -... -box.execute("SELECT CAST(X'4D6564766564' AS BOOLEAN);") ---- -- error: 'Type mismatch: can not convert Medved to boolean' -... --- Make sure that SCALAR can handle boolean values. --- -box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, s SCALAR);") ---- -- row_count: 1 -... -box.execute("INSERT INTO t1 SELECT * FROM t;") ---- -- row_count: 1 -... -box.execute("SELECT s FROM t1 WHERE s = true;") ---- -- metadata: - - name: S - type: scalar - rows: - - [true] -... -box.execute("INSERT INTO t1 VALUES (3, 'abc'), (4, 12.5);") ---- -- row_count: 2 -... -box.execute("SELECT s FROM t1 WHERE s = true;") ---- -- error: 'Type mismatch: can not convert TEXT to boolean' -... -box.execute("SELECT s FROM t1 WHERE s < true;") ---- -- error: 'Type mismatch: can not convert TEXT to boolean' -... -box.execute("SELECT s FROM t1 WHERE s IN (true, 1, 'abcd')") ---- -- metadata: - - name: S - type: scalar - rows: - - [true] -... -box.space.T:drop() ---- -... -box.space.T1:drop() ---- -... --- Make sure that BOOLEAN is not implicitly converted to INTEGER --- while inserted to PRIMARY KEY field. --- -box.execute("CREATE TABLE t1 (id INT PRIMARY KEY);") ---- -- row_count: 1 -... -box.execute("INSERT INTO t1 VALUES (true);") ---- -- error: 'Type mismatch: can not convert true to integer' -... -box.space.T1:drop() ---- -... -- -- gh-4103: If resulting value of arithmetic operations is -- integers, then make sure its type also integer (not number). diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua index 410864a..bddcb0f 100644 --- a/test/sql/types.test.lua +++ b/test/sql/types.test.lua @@ -80,150 +80,6 @@ box.execute("INSERT INTO t1 VALUES('0.0'), ('1.5'), ('3.9312453');") box.execute("SELECT CAST(f AS INTEGER) FROM t1;") box.space.T1:drop() --- Test basic capabilities of boolean type. --- -box.execute("SELECT true;") -box.execute("SELECT false;") -box.execute("SELECT unknown;") -box.execute("SELECT true = false;") -box.execute("SELECT true = true;") -box.execute("SELECT true > false;") -box.execute("SELECT true < false;") -box.execute("SELECT null = true;") -box.execute("SELECT unknown = true;") -box.execute("SELECT 1 = true;") -box.execute("SELECT 'abc' = true;") -box.execute("SELECT 1.123 > true;") -box.execute("SELECT true IN (1, 'abc', true)") -box.execute("SELECT true IN (1, 'abc', false)") -box.execute("SELECT 1 LIMIT true;") -box.execute("SELECT 1 LIMIT 1 OFFSET true;") -box.execute("SELECT 'abc' || true;") - --- Boolean can take part in arithmetic operations. --- -box.execute("SELECT true + false;") -box.execute("SELECT true * 1;") -box.execute("SELECT false / 0;") -box.execute("SELECT not true;") -box.execute("SELECT ~true;") -box.execute("SELECT -true;") -box.execute("SELECT true << 1;") -box.execute("SELECT true | 1;") -box.execute("SELECT true and false;") -box.execute("SELECT true or unknown;") - -box.execute("CREATE TABLE t (id INT PRIMARY KEY, b BOOLEAN);") -box.execute("INSERT INTO t VALUES (1, true);") -box.execute("INSERT INTO t VALUES (2, false);") -box.execute("INSERT INTO t VALUES (3, unknown)") -box.execute("SELECT b FROM t;") -box.execute("SELECT b FROM t WHERE b = false;") -box.execute("SELECT b FROM t WHERE b IS NULL;") -box.execute("SELECT b FROM t WHERE b IN (false, 1, 'abc')") -box.execute("SELECT b FROM t WHERE b BETWEEN false AND true;") -box.execute("SELECT b FROM t WHERE b BETWEEN true AND false;") -box.execute("SELECT b FROM t ORDER BY b;") -box.execute("SELECT b FROM t ORDER BY +b;") -box.execute("SELECT b FROM t ORDER BY b LIMIT 1;") -box.execute("SELECT b FROM t GROUP BY b LIMIT 1;") -box.execute("SELECT b FROM t LIMIT true;") - --- Most of aggregates don't accept boolean arguments. --- -box.execute("SELECT sum(b) FROM t;") -box.execute("SELECT avg(b) FROM t;") -box.execute("SELECT total(b) FROM t;") -box.execute("SELECT min(b) FROM t;") -box.execute("SELECT max(b) FROM t;") -box.execute("SELECT count(b) FROM t;") -box.execute("SELECT group_concat(b) FROM t;") - --- Check other built-in functions. --- -box.execute("SELECT lower(b) FROM t;") -box.execute("SELECT upper(b) FROM t;") -box.execute("SELECT abs(b) FROM t;") -box.execute("SELECT typeof(b) FROM t;") -box.execute("SELECT quote(b) FROM t;") -box.execute("SELECT min(b, true) FROM t;") -box.execute("SELECT quote(b) FROM t;") - --- Test index search using boolean values. --- -box.execute("CREATE INDEX ib ON t(b);") -box.execute("SELECT b FROM t WHERE b = false;") -box.execute("SELECT b FROM t WHERE b OR unknown ORDER BY b;") - --- Test UPDATE on boolean field. --- -box.execute("UPDATE t SET b = true WHERE b = false;") -box.execute("SELECT b FROM t;") - --- Test constraints functionality. --- -box.execute("CREATE TABLE parent (id INT PRIMARY KEY, a BOOLEAN UNIQUE);") -box.space.T:truncate() -box.execute("ALTER TABLE t ADD CONSTRAINT fk1 FOREIGN KEY (b) REFERENCES parent (a);") -box.execute("INSERT INTO t VALUES (1, true);") -box.execute("INSERT INTO parent VALUES (1, true);") -box.execute("INSERT INTO t VALUES (1, true);") -box.execute("ALTER TABLE t DROP CONSTRAINT fk1;") -box.space.PARENT:drop() - -box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a BOOLEAN CHECK (a = true));") -box.execute("INSERT INTO t1 VALUES (1, false);") -box.execute("INSERT INTO t1 VALUES (1, true);") -box.space.T1:drop() - -box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a BOOLEAN DEFAULT true);") -box.execute("INSERT INTO t1 (id) VALUES (1);") -box.space.T1:select() -box.space.T1:drop() - --- Check that VIEW inherits boolean type. --- -box.execute("CREATE VIEW v AS SELECT b FROM t;") -box.space.V:format()[1]['type'] -box.space.V:drop() - --- Test CAST facilities. --- -box.execute("SELECT CAST(true AS INTEGER);") -box.execute("SELECT CAST(true AS TEXT);") -box.execute("SELECT CAST(true AS FLOAT);") -box.execute("SELECT CAST(true AS SCALAR);") -box.execute("SELECT CAST(1 AS BOOLEAN);") -box.execute("SELECT CAST(1.123 AS BOOLEAN);") -box.execute("SELECT CAST(0.0 AS BOOLEAN);") -box.execute("SELECT CAST(0.00000001 AS BOOLEAN);") -box.execute("SELECT CAST('abc' AS BOOLEAN);") -box.execute("SELECT CAST(' TrUe' AS BOOLEAN);") -box.execute("SELECT CAST(' falsE ' AS BOOLEAN);") -box.execute("SELECT CAST(' fals' AS BOOLEAN);") - -box.execute("SELECT CAST(X'4D6564766564' AS BOOLEAN);") - --- Make sure that SCALAR can handle boolean values. --- -box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, s SCALAR);") -box.execute("INSERT INTO t1 SELECT * FROM t;") -box.execute("SELECT s FROM t1 WHERE s = true;") -box.execute("INSERT INTO t1 VALUES (3, 'abc'), (4, 12.5);") -box.execute("SELECT s FROM t1 WHERE s = true;") -box.execute("SELECT s FROM t1 WHERE s < true;") -box.execute("SELECT s FROM t1 WHERE s IN (true, 1, 'abcd')") - -box.space.T:drop() -box.space.T1:drop() - --- Make sure that BOOLEAN is not implicitly converted to INTEGER --- while inserted to PRIMARY KEY field. --- -box.execute("CREATE TABLE t1 (id INT PRIMARY KEY);") -box.execute("INSERT INTO t1 VALUES (true);") -box.space.T1:drop() - -- -- gh-4103: If resulting value of arithmetic operations is -- integers, then make sure its type also integer (not number).