[tarantool-patches] Re: [PATCH v1 1/1] sql: test suite for BOOLEAN
Mergen Imeev
imeevma at tarantool.org
Tue Jul 16 12:57:09 MSK 2019
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 <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;
More information about the Tarantool-patches
mailing list