Tarantool development patches archive
 help / color / mirror / Atom feed
* [tarantool-patches] [PATCH v1 1/1] sql: test suite for BOOLEAN
@ 2019-07-15 13:49 imeevma
  2019-07-15 14:39 ` [tarantool-patches] " Konstantin Osipov
                   ` (2 more replies)
  0 siblings, 3 replies; 10+ messages in thread
From: imeevma @ 2019-07-15 13:49 UTC (permalink / raw)
  To: korablev; +Cc: tarantool-patches

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

^ permalink raw reply	[flat|nested] 10+ messages in thread

end of thread, other threads:[~2019-09-12  5:13 UTC | newest]

Thread overview: 10+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2019-07-15 13:49 [tarantool-patches] [PATCH v1 1/1] sql: test suite for BOOLEAN imeevma
2019-07-15 14:39 ` [tarantool-patches] " Konstantin Osipov
2019-07-15 18:02 ` n.pettik
2019-07-16  9:57   ` Mergen Imeev
2019-07-18 18:05     ` n.pettik
2019-07-24 11:52       ` Mergen Imeev
2019-08-08 15:30         ` n.pettik
2019-08-28 14:11           ` Mergen Imeev
2019-08-28 16:51             ` Nikita Pettik
2019-09-12  5:12 ` Kirill Yukhin

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox