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 2A9FE247B3 for ; Mon, 15 Jul 2019 09:49:11 -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 CopjOkUrR8FD for ; Mon, 15 Jul 2019 09:49:11 -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 9CF49247B1 for ; Mon, 15 Jul 2019 09:49:10 -0400 (EDT) From: imeevma@tarantool.org Subject: [tarantool-patches] [PATCH v1 1/1] sql: test suite for BOOLEAN Date: Mon, 15 Jul 2019 16:49:08 +0300 Message-Id: <2ecdecf63e68a73815c48d9ff369cbd129ad28d6.1563198457.git.imeevma@gmail.com> 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: korablev@tarantool.org Cc: tarantool-patches@freelists.org This patch provides a test suite that allows us to make sure that the functionality of the SQL BOOLEAN type works as intended. Part of #4228 --- https://github.com/tarantool/tarantool/issues/4228 https://github.com/tarantool/tarantool/tree/imeevma/gh-4228-test-suits-for-sql-types test/sql/boolean.result | 1731 +++++++++++++++++++++++++++++++++++++++++++++ test/sql/boolean.test.lua | 403 +++++++++++ 2 files changed, 2134 insertions(+) create mode 100644 test/sql/boolean.result create mode 100644 test/sql/boolean.test.lua diff --git a/test/sql/boolean.result b/test/sql/boolean.result new file mode 100644 index 0000000..4b7772f --- /dev/null +++ b/test/sql/boolean.result @@ -0,0 +1,1731 @@ +-- test-run result file version 2 +test_run = require('test_run').new() + | --- + | ... +engine = test_run:get_cfg('engine') + | --- + | ... +box.execute('pragma sql_default_engine=\''..engine..'\'') + | --- + | - row_count: 0 + | ... + +-- Create table for tests +box.execute('CREATE TABLE t (a BOOLEAN PRIMARY KEY);') + | --- + | - row_count: 1 + | ... +box.execute('INSERT INTO t VALUES (true), (false);') + | --- + | - row_count: 2 + | ... + +-- Create user-defined function. +function return_type(arg) return type(arg) end + | --- + | ... +box.internal.sql_create_function("return_type", "TEXT", return_type) + | --- + | ... + +-- Check boolean as WHERE argument. +box.execute('SELECT a FROM t WHERE a;') + | --- + | - metadata: + | - name: A + | type: boolean + | rows: + | - [true] + | ... +box.execute('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. +box.execute('CREATE INDEX i1 ON t(a);') + | --- + | - row_count: 1 + | ... + +-- Check boolean as LIMIT argument. +box.execute('SELECT * FROM t LIMIT true;') + | --- + | - error: 'Failed to execute SQL statement: Only positive integers are allowed in the + | LIMIT clause' + | ... +box.execute('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. +box.execute('SELECT * FROM t LIMIT 1 OFFSET true;') + | --- + | - error: 'Failed to execute SQL statement: Only positive integers are allowed in the + | OFFSET clause' + | ... +box.execute('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. +box.execute('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. +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 + | ... + +-- Check BOOLEAN as argument of user-defined function. +box.execute('SELECT return_type(a) FROM t;') + | --- + | - metadata: + | - name: return_type(a) + | type: string + | rows: + | - ['boolean'] + | - ['boolean'] + | ... + +-- Check BOOLEAN as argument of scalar function. +box.execute('SELECT typeof(a) FROM t;') + | --- + | - metadata: + | - name: typeof(a) + | type: string + | rows: + | - ['boolean'] + | - ['boolean'] + | ... + +-- Check BOOLEAN as argument of aggregate function. +box.execute('SELECT count(a) FROM t GROUP BY a;') + | --- + | - metadata: + | - name: count(a) + | type: integer + | rows: + | - [1] + | - [1] + | ... + +-- Check BOOLEAN as binding parameter. +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] + | ... + +-- Check interactions with CHECK constraint. +box.execute('CREATE TABLE t1 (i INT PRIMARY KEY, a BOOLEAN, CONSTRAINT ck CHECK(a != true));') + | --- + | - row_count: 1 + | ... +box.execute('INSERT INTO t1 VALUES (1, false);') + | --- + | - row_count: 1 + | ... +box.execute('INSERT INTO t1 VALUES (2, true);') + | --- + | - error: 'Check constraint failed ''CK'': a != true' + | ... + +-- Check interactions with FOREIGN KEY constraint. +box.execute('CREATE TABLE t2 (a BOOLEAN PRIMARY KEY, b BOOLEAN REFERENCES t2(a));') + | --- + | - row_count: 1 + | ... +box.execute('INSERT INTO t2 VALUES (false, true)') + | --- + | - error: 'Failed to execute SQL statement: FOREIGN KEY constraint failed' + | ... +box.execute('INSERT INTO t2 VALUES (true, false)') + | --- + | - error: 'Failed to execute SQL statement: FOREIGN KEY constraint failed' + | ... +box.execute('INSERT INTO t2 VALUES (true, true)') + | --- + | - row_count: 1 + | ... +box.execute('INSERT INTO t2 VALUES (false, true)') + | --- + | - row_count: 1 + | ... + +-- Check interactions with UNIQUE constraint. +box.execute('CREATE TABLE t3 (i INT PRIMARY KEY, a BOOLEAN, CONSTRAINT uq UNIQUE(a));') + | --- + | - row_count: 1 + | ... +box.execute('INSERT INTO t3 VALUES (1, true)') + | --- + | - row_count: 1 + | ... +box.execute('INSERT INTO t3 VALUES (2, false)') + | --- + | - row_count: 1 + | ... +box.execute('INSERT INTO t3 VALUES (3, true)') + | --- + | - error: Duplicate key exists in unique index 'unique_UQ_2' in space 'T3' + | ... +box.execute('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. +box.execute('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] + | ... +box.execute('SELECT cast(true AS REAL), cast(false AS REAL);') + | --- + | - error: 'Type mismatch: can not convert true to number' + | ... +box.execute('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'] + | ... +box.execute('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. +box.execute('CREATE TABLE t4 (i INT PRIMARY KEY, a BOOLEAN);') + | --- + | - row_count: 1 + | ... +box.execute('CREATE TABLE t5 (i INT PRIMARY KEY AUTOINCREMENT, b BOOLEAN);') + | --- + | - row_count: 1 + | ... +box.execute('CREATE TRIGGER r AFTER INSERT ON t4 FOR EACH ROW BEGIN INSERT INTO t5(b) VALUES(true); END;') + | --- + | - row_count: 1 + | ... +box.execute('INSERT INTO t4 VALUES (100, false);') + | --- + | - autoincrement_ids: + | - 1 + | row_count: 1 + | ... +box.execute('DROP TRIGGER r;') + | --- + | - row_count: 1 + | ... +box.execute('SELECT * FROM t4;') + | --- + | - metadata: + | - name: I + | type: integer + | - name: A + | type: boolean + | rows: + | - [100, false] + | ... +box.execute('SELECT * FROM t5;') + | --- + | - metadata: + | - name: I + | type: integer + | - name: B + | type: boolean + | rows: + | - [1, true] + | ... + +-- Check UNION, UNION ALL AND INTERSECT. +box.execute('INSERT INTO t5 VALUES (100, false);') + | --- + | - row_count: 1 + | ... +box.execute('SELECT * FROM t4 UNION SELECT * FROM t5;') + | --- + | - metadata: + | - name: I + | type: integer + | - name: A + | type: boolean + | rows: + | - [1, true] + | - [100, false] + | ... +box.execute('SELECT * FROM t4 UNION ALL SELECT * FROM t5;') + | --- + | - metadata: + | - name: I + | type: integer + | - name: A + | type: boolean + | rows: + | - [100, false] + | - [1, true] + | - [100, false] + | ... +box.execute('SELECT * FROM t4 INTERSECT SELECT * FROM t5;') + | --- + | - metadata: + | - name: I + | type: integer + | - name: A + | type: boolean + | rows: + | - [100, false] + | ... + +-- Check SUBSELECT. +box.execute('INSERT INTO t5(b) SELECT a FROM t4;') + | --- + | - autoincrement_ids: + | - 101 + | row_count: 1 + | ... +box.execute('SELECT * FROM t5;') + | --- + | - metadata: + | - name: I + | type: integer + | - name: B + | type: boolean + | rows: + | - [1, true] + | - [100, false] + | - [101, false] + | ... + +-- Check VIEW. +box.execute('CREATE VIEW v AS SELECT b FROM t5;') + | --- + | - row_count: 1 + | ... +box.execute('SELECT * FROM v;') + | --- + | - metadata: + | - name: B + | type: boolean + | rows: + | - [true] + | - [false] + | - [false] + | ... + +-- Check DISTINCT. +box.execute('SELECT DISTINCT * FROM v;') + | --- + | - metadata: + | - name: B + | type: boolean + | rows: + | - [true] + | - [false] + | ... + +-- Check logical, bitwise and arithmetical operations. +box.execute('SELECT NOT true;') + | --- + | - metadata: + | - name: NOT true + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT NOT false;') + | --- + | - metadata: + | - name: NOT false + | type: boolean + | rows: + | - [true] + | ... +box.execute('SELECT true AND true;') + | --- + | - metadata: + | - name: true AND true + | type: boolean + | rows: + | - [true] + | ... +box.execute('SELECT true AND false;') + | --- + | - metadata: + | - name: true AND false + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT false AND true;') + | --- + | - metadata: + | - name: false AND true + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT false AND false;') + | --- + | - metadata: + | - name: false AND false + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT true OR true;') + | --- + | - metadata: + | - name: true OR true + | type: boolean + | rows: + | - [true] + | ... +box.execute('SELECT true OR false;') + | --- + | - metadata: + | - name: true OR false + | type: boolean + | rows: + | - [true] + | ... +box.execute('SELECT false OR true;') + | --- + | - metadata: + | - name: false OR true + | type: boolean + | rows: + | - [true] + | ... +box.execute('SELECT false OR false;') + | --- + | - metadata: + | - name: false OR false + | type: boolean + | rows: + | - [false] + | ... + +box.execute('SELECT -true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT -false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT true + true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT true + false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT false + true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT false + false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT true - true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT true - false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT false - true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT false - false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT true * true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT true * false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT false * true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT false * false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT true / true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT true / false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT false / true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT false / false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT true % true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT true % false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT false % true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT false % false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... + +box.execute('SELECT true & true;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT true & false;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT false & true;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +box.execute('SELECT false & false;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +box.execute('SELECT true | true;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT true | false;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT false | true;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +box.execute('SELECT false | false;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +box.execute('SELECT true << true;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT true << false;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT false << true;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +box.execute('SELECT false << false;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +box.execute('SELECT true >> true;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT true >> false;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT false >> true;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +box.execute('SELECT false >> false;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... + +-- Check concatenate. +box.execute('SELECT true || true;') + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +box.execute('SELECT true || false;') + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +box.execute('SELECT false || true;') + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +box.execute('SELECT false || false;') + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... + +-- Check comparisons. +box.execute('SELECT true > true;') + | --- + | - metadata: + | - name: true > true + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT true > false;') + | --- + | - metadata: + | - name: true > false + | type: boolean + | rows: + | - [true] + | ... +box.execute('SELECT false > true;') + | --- + | - metadata: + | - name: false > true + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT false > false;') + | --- + | - metadata: + | - name: false > false + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT true < true;') + | --- + | - metadata: + | - name: true < true + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT true < false;') + | --- + | - metadata: + | - name: true < false + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT false < true;') + | --- + | - metadata: + | - name: false < true + | type: boolean + | rows: + | - [true] + | ... +box.execute('SELECT false < false;') + | --- + | - metadata: + | - name: false < false + | type: boolean + | rows: + | - [false] + | ... + +box.execute('SELECT true >= true;') + | --- + | - metadata: + | - name: true >= true + | type: any + | rows: + | - [true] + | ... +box.execute('SELECT true >= false;') + | --- + | - metadata: + | - name: true >= false + | type: any + | rows: + | - [true] + | ... +box.execute('SELECT false >= true;') + | --- + | - metadata: + | - name: false >= true + | type: any + | rows: + | - [false] + | ... +box.execute('SELECT false >= false;') + | --- + | - metadata: + | - name: false >= false + | type: any + | rows: + | - [true] + | ... +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: + | - [false] + | ... +box.execute('SELECT false <= true;') + | --- + | - metadata: + | - name: false <= true + | type: boolean + | rows: + | - [true] + | ... +box.execute('SELECT false <= false;') + | --- + | - metadata: + | - name: false <= false + | type: boolean + | rows: + | - [true] + | ... + +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: + | - [false] + | ... +box.execute('SELECT false == true;') + | --- + | - metadata: + | - name: false == true + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT false == false;') + | --- + | - metadata: + | - name: false == false + | type: boolean + | rows: + | - [true] + | ... +box.execute('SELECT true != true;') + | --- + | - metadata: + | - name: true != true + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT true != false;') + | --- + | - metadata: + | - name: true != false + | type: boolean + | rows: + | - [true] + | ... +box.execute('SELECT false != true;') + | --- + | - metadata: + | - name: false != true + | type: boolean + | rows: + | - [true] + | ... +box.execute('SELECT false != false;') + | --- + | - metadata: + | - name: false != false + | type: boolean + | rows: + | - [false] + | ... + +box.execute('SELECT true IN (true);') + | --- + | - metadata: + | - name: true IN (true) + | type: boolean + | rows: + | - [true] + | ... +box.execute('SELECT false IN (true);') + | --- + | - metadata: + | - name: false IN (true) + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT true IN (false);') + | --- + | - metadata: + | - name: true IN (false) + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT false IN (false);') + | --- + | - metadata: + | - name: false IN (false) + | type: boolean + | rows: + | - [true] + | ... +box.execute('SELECT true IN (true, false);') + | --- + | - metadata: + | - name: true IN (true, false) + | type: boolean + | rows: + | - [true] + | ... +box.execute('SELECT false IN (true, false);') + | --- + | - metadata: + | - name: false IN (true, false) + | type: boolean + | rows: + | - [true] + | ... +box.execute("SELECT true IN (1, 1.2, 'true', false);") + | --- + | - metadata: + | - name: true IN (1, 1.2, 'true', false) + | type: boolean + | rows: + | - [false] + | ... +box.execute("SELECT false IN (1, 1.2, 'true', false);") + | --- + | - metadata: + | - name: false IN (1, 1.2, 'true', false) + | type: boolean + | rows: + | - [true] + | ... + +box.execute('SELECT true BETWEEN true AND true;') + | --- + | - metadata: + | - name: true BETWEEN true AND true + | type: boolean + | rows: + | - [true] + | ... +box.execute('SELECT false BETWEEN true AND true;') + | --- + | - metadata: + | - name: false BETWEEN true AND true + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT true BETWEEN false AND false;') + | --- + | - metadata: + | - name: true BETWEEN false AND false + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT false BETWEEN false AND false;') + | --- + | - metadata: + | - name: false BETWEEN false AND false + | type: boolean + | rows: + | - [true] + | ... +box.execute('SELECT true BETWEEN true AND false;') + | --- + | - metadata: + | - name: true BETWEEN true AND false + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT false BETWEEN true AND false;') + | --- + | - metadata: + | - name: false BETWEEN true AND false + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT true BETWEEN false AND true;') + | --- + | - metadata: + | - name: true BETWEEN false AND true + | type: boolean + | rows: + | - [true] + | ... +box.execute('SELECT false BETWEEN false AND true;') + | --- + | - metadata: + | - name: false BETWEEN false AND true + | type: boolean + | rows: + | - [true] + | ... + +-- Check interaction of BOOLEAN and INTEGER. +box.execute('SELECT true AND 2;') + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... +box.execute('SELECT false AND 2;') + | --- + | - metadata: + | - name: false AND 2 + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT true OR 2;') + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... +box.execute('SELECT false OR 2;') + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... +box.execute('SELECT 2 AND true;') + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... +box.execute('SELECT 2 AND false;') + | --- + | - metadata: + | - name: 2 AND false + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT 2 OR true;') + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... +box.execute('SELECT 2 OR false;') + | --- + | - error: 'Type mismatch: can not convert 2 to boolean' + | ... + +box.execute('SELECT true + 2;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT false + 2;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT true - 2;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT false - 2;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT true * 2;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT false * 2;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT true / 2;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT false / 2;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT true % 2;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT false % 2;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT 2 + true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT 2 + false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT 2 - true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT 2 - false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT 2 * true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT 2 * false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT 2 / true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT 2 / false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT 2 % true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT 2 % false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... + +box.execute('SELECT true & 2;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT false & 2;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +box.execute('SELECT true | 2;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT false | 2;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +box.execute('SELECT true << 2;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT false << 2;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +box.execute('SELECT true >> 2;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT false >> 2;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +box.execute('SELECT 2 & true;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT 2 & false;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +box.execute('SELECT 2 | true;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT 2 | false;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +box.execute('SELECT 2 << true;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT 2 << false;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +box.execute('SELECT 2 >> true;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT 2 >> false;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... + +box.execute('SELECT true > 2;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT false > 2;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT true < 2;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT false < 2;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT 2 > true;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT 2 > false;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT 2 < true;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT 2 < false;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... + +box.execute('SELECT true >= 2;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT false >= 2;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT true <= 2;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT false <= 2;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT 2 >= true;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT 2 >= false;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT 2 <= true;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT 2 <= false;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... + +box.execute('SELECT true == 2;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT false == 2;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT true != 2;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT false != 2;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT 2 == true;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT 2 == false;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT 2 != true;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT 2 != false;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... + +box.execute('SELECT true IN (0, 1, 2, 3);') + | --- + | - metadata: + | - name: true IN (0, 1, 2, 3) + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT false IN (0, 1, 2, 3);') + | --- + | - metadata: + | - name: false IN (0, 1, 2, 3) + | type: boolean + | rows: + | - [false] + | ... + +box.execute('SELECT true BETWEEN 0 and 10;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... +box.execute('SELECT false BETWEEN 0 and 10;') + | --- + | - error: 'Type mismatch: can not convert INTEGER to boolean' + | ... + +-- Check interaction of BOOLEAN and REAL. +box.execute('SELECT true AND 2.3;') + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... +box.execute('SELECT false AND 2.3;') + | --- + | - metadata: + | - name: false AND 2.3 + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT true OR 2.3;') + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... +box.execute('SELECT false OR 2.3;') + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... +box.execute('SELECT 2.3 AND true;') + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... +box.execute('SELECT 2.3 AND false;') + | --- + | - metadata: + | - name: 2.3 AND false + | type: boolean + | rows: + | - [false] + | ... +box.execute('SELECT 2.3 OR true;') + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... +box.execute('SELECT 2.3 OR false;') + | --- + | - error: 'Type mismatch: can not convert 2.3 to boolean' + | ... + +box.execute('SELECT true + 2.3;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT false + 2.3;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT true - 2.3;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT false - 2.3;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT true * 2.3;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT false * 2.3;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT true / 2.3;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT false / 2.3;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT true % 2.3;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT false % 2.3;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT 2.3 + true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT 2.3 + false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT 2.3 - true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT 2.3 - false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT 2.3 * true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT 2.3 * false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT 2.3 / true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT 2.3 / false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... +box.execute('SELECT 2.3 % true;') + | --- + | - error: 'Type mismatch: can not convert true to numeric' + | ... +box.execute('SELECT 2.3 % false;') + | --- + | - error: 'Type mismatch: can not convert false to numeric' + | ... + +box.execute('SELECT true & 2.3;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT false & 2.3;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +box.execute('SELECT true | 2.3;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT false | 2.3;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +box.execute('SELECT true << 2.3;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT false << 2.3;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +box.execute('SELECT true >> 2.3;') + | --- + | - error: 'Type mismatch: can not convert true to integer' + | ... +box.execute('SELECT false >> 2.3;') + | --- + | - error: 'Type mismatch: can not convert false to integer' + | ... +box.execute('SELECT 2.3 & true;') + | --- + | - error: 'Type mismatch: can not convert 2.3 to integer' + | ... +box.execute('SELECT 2.3 & false;') + | --- + | - error: 'Type mismatch: can not convert 2.3 to integer' + | ... +box.execute('SELECT 2.3 | true;') + | --- + | - error: 'Type mismatch: can not convert 2.3 to integer' + | ... +box.execute('SELECT 2.3 | false;') + | --- + | - error: 'Type mismatch: can not convert 2.3 to integer' + | ... +box.execute('SELECT 2.3 << true;') + | --- + | - error: 'Type mismatch: can not convert 2.3 to integer' + | ... +box.execute('SELECT 2.3 << false;') + | --- + | - error: 'Type mismatch: can not convert 2.3 to integer' + | ... +box.execute('SELECT 2.3 >> true;') + | --- + | - error: 'Type mismatch: can not convert 2.3 to integer' + | ... +box.execute('SELECT 2.3 >> false;') + | --- + | - error: 'Type mismatch: can not convert 2.3 to integer' + | ... + +box.execute('SELECT true > 2.3;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT false > 2.3;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT true < 2.3;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT false < 2.3;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT 2.3 > true;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT 2.3 > false;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT 2.3 < true;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT 2.3 < false;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... + +box.execute('SELECT true >= 2.3;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT false >= 2.3;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT true <= 2.3;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT false <= 2.3;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT 2.3 >= true;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT 2.3 >= false;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT 2.3 <= true;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT 2.3 <= false;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... + +box.execute('SELECT true == 2.3;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT false == 2.3;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT true != 2.3;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT false != 2.3;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT 2.3 == true;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT 2.3 == false;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT 2.3 != true;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT 2.3 != false;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... + +box.execute('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] + | ... +box.execute('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] + | ... + +box.execute('SELECT true BETWEEN 0.1 and 9.9;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... +box.execute('SELECT false BETWEEN 0.1 and 9.9;') + | --- + | - error: 'Type mismatch: can not convert REAL to boolean' + | ... + +-- Check interaction of BOOLEAN and TEXT. +box.execute("SELECT true AND 'abc';") + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... +box.execute("SELECT false AND 'abc';") + | --- + | - metadata: + | - name: false AND 'abc' + | type: boolean + | rows: + | - [false] + | ... +box.execute("SELECT true OR 'abc';") + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... +box.execute("SELECT false OR 'abc';") + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... +box.execute("SELECT 'abc' AND true;") + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... +box.execute("SELECT 'abc' AND false;") + | --- + | - metadata: + | - name: '''abc'' AND false' + | type: boolean + | rows: + | - [false] + | ... +box.execute("SELECT 'abc' OR true;") + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... +box.execute("SELECT 'abc' OR false;") + | --- + | - error: 'Type mismatch: can not convert abc to boolean' + | ... + +box.execute("SELECT true > 'abc';") + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +box.execute("SELECT false > 'abc';") + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +box.execute("SELECT true < 'abc';") + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +box.execute("SELECT false < 'abc';") + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +box.execute("SELECT 'abc' > true;") + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +box.execute("SELECT 'abc' > false;") + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +box.execute("SELECT 'abc' < true;") + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... +box.execute("SELECT 'abc' < false;") + | --- + | - error: 'Type mismatch: can not convert TEXT to boolean' + | ... + +box.execute("SELECT true || 'abc';") + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +box.execute("SELECT false || 'abc';") + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +box.execute("SELECT 'abc' || false;") + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... +box.execute("SELECT 'abc' || true;") + | --- + | - error: 'Inconsistent types: expected TEXT or BLOB got BOOLEAN' + | ... + +-- Cleaning. +box.execute('DROP VIEW v;') + | --- + | - row_count: 1 + | ... +box.execute('DROP TABLE t5;') + | --- + | - row_count: 1 + | ... +box.execute('DROP TABLE t4;') + | --- + | - row_count: 1 + | ... +box.execute('DROP TABLE t3;') + | --- + | - row_count: 1 + | ... +box.execute('DROP TABLE t2;') + | --- + | - row_count: 1 + | ... +box.execute('DROP TABLE t1;') + | --- + | - row_count: 1 + | ... diff --git a/test/sql/boolean.test.lua b/test/sql/boolean.test.lua new file mode 100644 index 0000000..e402ad1 --- /dev/null +++ b/test/sql/boolean.test.lua @@ -0,0 +1,403 @@ +test_run = require('test_run').new() +engine = test_run:get_cfg('engine') +box.execute('pragma sql_default_engine=\''..engine..'\'') + +-- Create table for tests +box.execute('CREATE TABLE t (a BOOLEAN PRIMARY KEY);') +box.execute('INSERT INTO t VALUES (true), (false);') + +-- Create user-defined function. +function return_type(arg) return type(arg) end +box.internal.sql_create_function("return_type", "TEXT", return_type) + +-- Check boolean as WHERE argument. +box.execute('SELECT a FROM t WHERE a;') +box.execute('SELECT a FROM t WHERE a != true;') + +-- Check that we can create index on field of type BOOLEAN. +box.execute('CREATE INDEX i1 ON t(a);') + +-- Check boolean as LIMIT argument. +box.execute('SELECT * FROM t LIMIT true;') +box.execute('SELECT * FROM t LIMIT false;') + +-- Check boolean as OFFSET argument. +box.execute('SELECT * FROM t LIMIT 1 OFFSET true;') +box.execute('SELECT * FROM t LIMIT 1 OFFSET false;') + +-- Check involvance in index search. +box.execute('EXPLAIN QUERY PLAN SELECT a FROM t WHERE a = true;') + +-- Check that ephemeral tables are used with BOOLEAN. +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 + +-- Check BOOLEAN as argument of user-defined function. +box.execute('SELECT return_type(a) FROM t;') + +-- Check BOOLEAN as argument of scalar function. +box.execute('SELECT typeof(a) FROM t;') + +-- Check BOOLEAN as argument of aggregate function. +box.execute('SELECT count(a) FROM t GROUP BY a;') + +-- Check BOOLEAN as binding parameter. +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) + +-- Check interactions with CHECK constraint. +box.execute('CREATE TABLE t1 (i INT PRIMARY KEY, a BOOLEAN, CONSTRAINT ck CHECK(a != true));') +box.execute('INSERT INTO t1 VALUES (1, false);') +box.execute('INSERT INTO t1 VALUES (2, true);') + +-- Check interactions with FOREIGN KEY constraint. +box.execute('CREATE TABLE t2 (a BOOLEAN PRIMARY KEY, b BOOLEAN REFERENCES t2(a));') +box.execute('INSERT INTO t2 VALUES (false, true)') +box.execute('INSERT INTO t2 VALUES (true, false)') +box.execute('INSERT INTO t2 VALUES (true, true)') +box.execute('INSERT INTO t2 VALUES (false, true)') + +-- Check interactions with UNIQUE constraint. +box.execute('CREATE TABLE t3 (i INT PRIMARY KEY, a BOOLEAN, CONSTRAINT uq UNIQUE(a));') +box.execute('INSERT INTO t3 VALUES (1, true)') +box.execute('INSERT INTO t3 VALUES (2, false)') +box.execute('INSERT INTO t3 VALUES (3, true)') +box.execute('INSERT INTO t3 VALUES (4, false)') + +-- Check CAST from BOOLEAN to the other types. +box.execute('SELECT cast(true AS INTEGER), cast(false AS INTEGER);') +box.execute('SELECT cast(true AS REAL), cast(false AS REAL);') +box.execute('SELECT cast(true AS TEXT), cast(false AS TEXT);') +box.execute('SELECT cast(true AS BOOLEAN), cast(false AS BOOLEAN);') + +-- Check usage in trigger. +box.execute('CREATE TABLE t4 (i INT PRIMARY KEY, a BOOLEAN);') +box.execute('CREATE TABLE t5 (i INT PRIMARY KEY AUTOINCREMENT, b BOOLEAN);') +box.execute('CREATE TRIGGER r AFTER INSERT ON t4 FOR EACH ROW BEGIN INSERT INTO t5(b) VALUES(true); END;') +box.execute('INSERT INTO t4 VALUES (100, false);') +box.execute('DROP TRIGGER r;') +box.execute('SELECT * FROM t4;') +box.execute('SELECT * FROM t5;') + +-- Check UNION, UNION ALL AND INTERSECT. +box.execute('INSERT INTO t5 VALUES (100, false);') +box.execute('SELECT * FROM t4 UNION SELECT * FROM t5;') +box.execute('SELECT * FROM t4 UNION ALL SELECT * FROM t5;') +box.execute('SELECT * FROM t4 INTERSECT SELECT * FROM t5;') + +-- Check SUBSELECT. +box.execute('INSERT INTO t5(b) SELECT a FROM t4;') +box.execute('SELECT * FROM t5;') + +-- Check VIEW. +box.execute('CREATE VIEW v AS SELECT b FROM t5;') +box.execute('SELECT * FROM v;') + +-- Check DISTINCT. +box.execute('SELECT DISTINCT * FROM v;') + +-- Check logical, bitwise and arithmetical operations. +box.execute('SELECT NOT true;') +box.execute('SELECT NOT false;') +box.execute('SELECT true AND true;') +box.execute('SELECT true AND false;') +box.execute('SELECT false AND true;') +box.execute('SELECT false AND false;') +box.execute('SELECT true OR true;') +box.execute('SELECT true OR false;') +box.execute('SELECT false OR true;') +box.execute('SELECT false OR false;') + +box.execute('SELECT -true;') +box.execute('SELECT -false;') +box.execute('SELECT true + true;') +box.execute('SELECT true + false;') +box.execute('SELECT false + true;') +box.execute('SELECT false + false;') +box.execute('SELECT true - true;') +box.execute('SELECT true - false;') +box.execute('SELECT false - true;') +box.execute('SELECT false - false;') +box.execute('SELECT true * true;') +box.execute('SELECT true * false;') +box.execute('SELECT false * true;') +box.execute('SELECT false * false;') +box.execute('SELECT true / true;') +box.execute('SELECT true / false;') +box.execute('SELECT false / true;') +box.execute('SELECT false / false;') +box.execute('SELECT true % true;') +box.execute('SELECT true % false;') +box.execute('SELECT false % true;') +box.execute('SELECT false % false;') + +box.execute('SELECT true & true;') +box.execute('SELECT true & false;') +box.execute('SELECT false & true;') +box.execute('SELECT false & false;') +box.execute('SELECT true | true;') +box.execute('SELECT true | false;') +box.execute('SELECT false | true;') +box.execute('SELECT false | false;') +box.execute('SELECT true << true;') +box.execute('SELECT true << false;') +box.execute('SELECT false << true;') +box.execute('SELECT false << false;') +box.execute('SELECT true >> true;') +box.execute('SELECT true >> false;') +box.execute('SELECT false >> true;') +box.execute('SELECT false >> false;') + +-- Check concatenate. +box.execute('SELECT true || true;') +box.execute('SELECT true || false;') +box.execute('SELECT false || true;') +box.execute('SELECT false || false;') + +-- Check comparisons. +box.execute('SELECT true > true;') +box.execute('SELECT true > false;') +box.execute('SELECT false > true;') +box.execute('SELECT false > false;') +box.execute('SELECT true < true;') +box.execute('SELECT true < false;') +box.execute('SELECT false < true;') +box.execute('SELECT false < false;') + +box.execute('SELECT true >= true;') +box.execute('SELECT true >= false;') +box.execute('SELECT false >= true;') +box.execute('SELECT false >= false;') +box.execute('SELECT true <= true;') +box.execute('SELECT true <= false;') +box.execute('SELECT false <= true;') +box.execute('SELECT false <= false;') + +box.execute('SELECT true == true;') +box.execute('SELECT true == false;') +box.execute('SELECT false == true;') +box.execute('SELECT false == false;') +box.execute('SELECT true != true;') +box.execute('SELECT true != false;') +box.execute('SELECT false != true;') +box.execute('SELECT false != false;') + +box.execute('SELECT true IN (true);') +box.execute('SELECT false IN (true);') +box.execute('SELECT true IN (false);') +box.execute('SELECT false IN (false);') +box.execute('SELECT true IN (true, false);') +box.execute('SELECT false IN (true, false);') +box.execute("SELECT true IN (1, 1.2, 'true', false);") +box.execute("SELECT false IN (1, 1.2, 'true', false);") + +box.execute('SELECT true BETWEEN true AND true;') +box.execute('SELECT false BETWEEN true AND true;') +box.execute('SELECT true BETWEEN false AND false;') +box.execute('SELECT false BETWEEN false AND false;') +box.execute('SELECT true BETWEEN true AND false;') +box.execute('SELECT false BETWEEN true AND false;') +box.execute('SELECT true BETWEEN false AND true;') +box.execute('SELECT false BETWEEN false AND true;') + +-- Check interaction of BOOLEAN and INTEGER. +box.execute('SELECT true AND 2;') +box.execute('SELECT false AND 2;') +box.execute('SELECT true OR 2;') +box.execute('SELECT false OR 2;') +box.execute('SELECT 2 AND true;') +box.execute('SELECT 2 AND false;') +box.execute('SELECT 2 OR true;') +box.execute('SELECT 2 OR false;') + +box.execute('SELECT true + 2;') +box.execute('SELECT false + 2;') +box.execute('SELECT true - 2;') +box.execute('SELECT false - 2;') +box.execute('SELECT true * 2;') +box.execute('SELECT false * 2;') +box.execute('SELECT true / 2;') +box.execute('SELECT false / 2;') +box.execute('SELECT true % 2;') +box.execute('SELECT false % 2;') +box.execute('SELECT 2 + true;') +box.execute('SELECT 2 + false;') +box.execute('SELECT 2 - true;') +box.execute('SELECT 2 - false;') +box.execute('SELECT 2 * true;') +box.execute('SELECT 2 * false;') +box.execute('SELECT 2 / true;') +box.execute('SELECT 2 / false;') +box.execute('SELECT 2 % true;') +box.execute('SELECT 2 % false;') + +box.execute('SELECT true & 2;') +box.execute('SELECT false & 2;') +box.execute('SELECT true | 2;') +box.execute('SELECT false | 2;') +box.execute('SELECT true << 2;') +box.execute('SELECT false << 2;') +box.execute('SELECT true >> 2;') +box.execute('SELECT false >> 2;') +box.execute('SELECT 2 & true;') +box.execute('SELECT 2 & false;') +box.execute('SELECT 2 | true;') +box.execute('SELECT 2 | false;') +box.execute('SELECT 2 << true;') +box.execute('SELECT 2 << false;') +box.execute('SELECT 2 >> true;') +box.execute('SELECT 2 >> false;') + +box.execute('SELECT true > 2;') +box.execute('SELECT false > 2;') +box.execute('SELECT true < 2;') +box.execute('SELECT false < 2;') +box.execute('SELECT 2 > true;') +box.execute('SELECT 2 > false;') +box.execute('SELECT 2 < true;') +box.execute('SELECT 2 < false;') + +box.execute('SELECT true >= 2;') +box.execute('SELECT false >= 2;') +box.execute('SELECT true <= 2;') +box.execute('SELECT false <= 2;') +box.execute('SELECT 2 >= true;') +box.execute('SELECT 2 >= false;') +box.execute('SELECT 2 <= true;') +box.execute('SELECT 2 <= false;') + +box.execute('SELECT true == 2;') +box.execute('SELECT false == 2;') +box.execute('SELECT true != 2;') +box.execute('SELECT false != 2;') +box.execute('SELECT 2 == true;') +box.execute('SELECT 2 == false;') +box.execute('SELECT 2 != true;') +box.execute('SELECT 2 != false;') + +box.execute('SELECT true IN (0, 1, 2, 3);') +box.execute('SELECT false IN (0, 1, 2, 3);') + +box.execute('SELECT true BETWEEN 0 and 10;') +box.execute('SELECT false BETWEEN 0 and 10;') + +-- Check interaction of BOOLEAN and REAL. +box.execute('SELECT true AND 2.3;') +box.execute('SELECT false AND 2.3;') +box.execute('SELECT true OR 2.3;') +box.execute('SELECT false OR 2.3;') +box.execute('SELECT 2.3 AND true;') +box.execute('SELECT 2.3 AND false;') +box.execute('SELECT 2.3 OR true;') +box.execute('SELECT 2.3 OR false;') + +box.execute('SELECT true + 2.3;') +box.execute('SELECT false + 2.3;') +box.execute('SELECT true - 2.3;') +box.execute('SELECT false - 2.3;') +box.execute('SELECT true * 2.3;') +box.execute('SELECT false * 2.3;') +box.execute('SELECT true / 2.3;') +box.execute('SELECT false / 2.3;') +box.execute('SELECT true % 2.3;') +box.execute('SELECT false % 2.3;') +box.execute('SELECT 2.3 + true;') +box.execute('SELECT 2.3 + false;') +box.execute('SELECT 2.3 - true;') +box.execute('SELECT 2.3 - false;') +box.execute('SELECT 2.3 * true;') +box.execute('SELECT 2.3 * false;') +box.execute('SELECT 2.3 / true;') +box.execute('SELECT 2.3 / false;') +box.execute('SELECT 2.3 % true;') +box.execute('SELECT 2.3 % false;') + +box.execute('SELECT true & 2.3;') +box.execute('SELECT false & 2.3;') +box.execute('SELECT true | 2.3;') +box.execute('SELECT false | 2.3;') +box.execute('SELECT true << 2.3;') +box.execute('SELECT false << 2.3;') +box.execute('SELECT true >> 2.3;') +box.execute('SELECT false >> 2.3;') +box.execute('SELECT 2.3 & true;') +box.execute('SELECT 2.3 & false;') +box.execute('SELECT 2.3 | true;') +box.execute('SELECT 2.3 | false;') +box.execute('SELECT 2.3 << true;') +box.execute('SELECT 2.3 << false;') +box.execute('SELECT 2.3 >> true;') +box.execute('SELECT 2.3 >> false;') + +box.execute('SELECT true > 2.3;') +box.execute('SELECT false > 2.3;') +box.execute('SELECT true < 2.3;') +box.execute('SELECT false < 2.3;') +box.execute('SELECT 2.3 > true;') +box.execute('SELECT 2.3 > false;') +box.execute('SELECT 2.3 < true;') +box.execute('SELECT 2.3 < false;') + +box.execute('SELECT true >= 2.3;') +box.execute('SELECT false >= 2.3;') +box.execute('SELECT true <= 2.3;') +box.execute('SELECT false <= 2.3;') +box.execute('SELECT 2.3 >= true;') +box.execute('SELECT 2.3 >= false;') +box.execute('SELECT 2.3 <= true;') +box.execute('SELECT 2.3 <= false;') + +box.execute('SELECT true == 2.3;') +box.execute('SELECT false == 2.3;') +box.execute('SELECT true != 2.3;') +box.execute('SELECT false != 2.3;') +box.execute('SELECT 2.3 == true;') +box.execute('SELECT 2.3 == false;') +box.execute('SELECT 2.3 != true;') +box.execute('SELECT 2.3 != false;') + +box.execute('SELECT true IN (0.1, 1.2, 2.3, 3.4);') +box.execute('SELECT false IN (0.1, 1.2, 2.3, 3.4);') + +box.execute('SELECT true BETWEEN 0.1 and 9.9;') +box.execute('SELECT false BETWEEN 0.1 and 9.9;') + +-- Check interaction of BOOLEAN and TEXT. +box.execute("SELECT true AND 'abc';") +box.execute("SELECT false AND 'abc';") +box.execute("SELECT true OR 'abc';") +box.execute("SELECT false OR 'abc';") +box.execute("SELECT 'abc' AND true;") +box.execute("SELECT 'abc' AND false;") +box.execute("SELECT 'abc' OR true;") +box.execute("SELECT 'abc' OR false;") + +box.execute("SELECT true > 'abc';") +box.execute("SELECT false > 'abc';") +box.execute("SELECT true < 'abc';") +box.execute("SELECT false < 'abc';") +box.execute("SELECT 'abc' > true;") +box.execute("SELECT 'abc' > false;") +box.execute("SELECT 'abc' < true;") +box.execute("SELECT 'abc' < false;") + +box.execute("SELECT true || 'abc';") +box.execute("SELECT false || 'abc';") +box.execute("SELECT 'abc' || false;") +box.execute("SELECT 'abc' || true;") + +-- Cleaning. +box.execute('DROP VIEW v;') +box.execute('DROP TABLE t5;') +box.execute('DROP TABLE t4;') +box.execute('DROP TABLE t3;') +box.execute('DROP TABLE t2;') +box.execute('DROP TABLE t1;') -- 2.7.4