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 0589422ECB for ; Tue, 16 Jul 2019 05:57:14 -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 m8Hq5Gbe0u8U for ; Tue, 16 Jul 2019 05:57:13 -0400 (EDT) Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149]) (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 08E0522F11 for ; Tue, 16 Jul 2019 05:57:12 -0400 (EDT) Date: Tue, 16 Jul 2019 12:57:09 +0300 From: Mergen Imeev Subject: [tarantool-patches] Re: [PATCH v1 1/1] sql: test suite for BOOLEAN Message-ID: <20190716095708.GA29957@tarantool.org> References: <2ecdecf63e68a73815c48d9ff369cbd129ad28d6.1563198457.git.imeevma@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline 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 On Mon, Jul 15, 2019 at 09:02:44PM +0300, n.pettik wrote: > An option which allows to use raw SQL in test suite was recently added: > https://github.com/tarantool/tarantool/issues/4123 > > Could you use it for this suite? Done: >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 diff --git a/test/sql/boolean.result b/test/sql/boolean.result new file mode 100644 index 0000000..d68f403 --- /dev/null +++ b/test/sql/boolean.result @@ -0,0 +1,1744 @@ +-- 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 + | --- + | ... +box.internal.sql_create_function("return_type", "TEXT", return_type) + | --- + | ... +\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 that we can create index on field of type BOOLEAN. +CREATE INDEX i1 ON t(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 t 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 T USING PRIMARY KEY (A=?) (~1 row)'] + | ... + +-- 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'] + | ... + +-- Check BOOLEAN as argument of scalar function. +SELECT typeof(a) FROM t; + | --- + | - metadata: + | - name: typeof(a) + | type: string + | rows: + | - ['boolean'] + | - ['boolean'] + | ... + +-- Check BOOLEAN as argument of aggregate function. +SELECT count(a) FROM t GROUP BY a; + | --- + | - metadata: + | - name: count(a) + | type: integer + | rows: + | - [1] + | - [1] + | ... + +-- 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 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] + | ... + +-- 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 logical, bitwise and arithmetical operations. +SELECT NOT true; + | --- + | - metadata: + | - name: NOT true + | type: boolean + | rows: + | - [false] + | ... +SELECT NOT false; + | --- + | - metadata: + | - name: NOT false + | type: boolean + | rows: + | - [true] + | ... +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 -true; + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +SELECT -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 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 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' + | ... + +-- 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' + | ... + +-- 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 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 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 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 (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 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] + | ... + +-- Check interaction of BOOLEAN and INTEGER. +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 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 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 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 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 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 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 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' + | ... + +-- Check interaction of BOOLEAN and REAL. +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 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 true & 2.3; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT false & 2.3; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT true | 2.3; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT false | 2.3; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT true << 2.3; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT false << 2.3; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT true >> 2.3; + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +SELECT false >> 2.3; + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +SELECT 2.3 & true; + | --- + | - error: 'Type mismatch: can not convert 2.3 to integer' + | ... +SELECT 2.3 & false; + | --- + | - error: 'Type mismatch: can not convert 2.3 to integer' + | ... +SELECT 2.3 | true; + | --- + | - error: 'Type mismatch: can not convert 2.3 to integer' + | ... +SELECT 2.3 | false; + | --- + | - error: 'Type mismatch: can not convert 2.3 to integer' + | ... +SELECT 2.3 << true; + | --- + | - error: 'Type mismatch: can not convert 2.3 to integer' + | ... +SELECT 2.3 << false; + | --- + | - error: 'Type mismatch: can not convert 2.3 to integer' + | ... +SELECT 2.3 >> true; + | --- + | - error: 'Type mismatch: can not convert 2.3 to integer' + | ... +SELECT 2.3 >> false; + | --- + | - error: 'Type mismatch: can not convert 2.3 to integer' + | ... + +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 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 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 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 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' + | ... + +-- Check interaction of BOOLEAN and TEXT. +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 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 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' + | ... + +-- Cleaning. +DROP VIEW v; + | --- + | - 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 + | ... 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); + +-- 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; + +-- 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; + +-- 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 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; + +-- Check VIEW. +CREATE VIEW v AS SELECT b FROM t5; +SELECT * FROM v; + +-- Check DISTINCT. +SELECT DISTINCT * FROM v; + +-- Check logical, bitwise and arithmetical operations. +SELECT NOT true; +SELECT NOT false; +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 -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 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; + +-- Check concatenate. +SELECT true || true; +SELECT true || false; +SELECT false || true; +SELECT false || false; + +-- 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 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 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 (1, 1.2, 'true', false); +SELECT false IN (1, 1.2, 'true', false); + +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; + +-- Check interaction of BOOLEAN and INTEGER. +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 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 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 true > 2; +SELECT false > 2; +SELECT true < 2; +SELECT false < 2; +SELECT 2 > true; +SELECT 2 > false; +SELECT 2 < true; +SELECT 2 < false; + +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 true == 2; +SELECT false == 2; +SELECT true != 2; +SELECT false != 2; +SELECT 2 == true; +SELECT 2 == false; +SELECT 2 != true; +SELECT 2 != false; + +SELECT true IN (0, 1, 2, 3); +SELECT false IN (0, 1, 2, 3); + +SELECT true BETWEEN 0 and 10; +SELECT false BETWEEN 0 and 10; + +-- Check interaction of BOOLEAN and REAL. +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 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 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 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 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 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 true IN (0.1, 1.2, 2.3, 3.4); +SELECT false IN (0.1, 1.2, 2.3, 3.4); + +SELECT true BETWEEN 0.1 and 9.9; +SELECT false BETWEEN 0.1 and 9.9; + +-- Check interaction of BOOLEAN and TEXT. +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 true > 'abc'; +SELECT false > 'abc'; +SELECT true < 'abc'; +SELECT false < 'abc'; +SELECT 'abc' > true; +SELECT 'abc' > false; +SELECT 'abc' < true; +SELECT 'abc' < false; + +SELECT true || 'abc'; +SELECT false || 'abc'; +SELECT 'abc' || false; +SELECT 'abc' || true; + +-- Cleaning. +DROP VIEW v; +DROP TABLE t5; +DROP TABLE t4; +DROP TABLE t3; +DROP TABLE t2; +DROP TABLE t1;