[tarantool-patches] Re: [PATCH v1 1/1] sql: test suite for BOOLEAN

["n.pettik" <korablev@tarantool.org>]

> From 92649b7b53483d4bbb947e300acf569d85e25001 Mon Sep 17 00:00:00 2001
> Date: Tue, 16 Jul 2019 12:43:51 +0300
> Subject: [PATCH] sql: test suite for BOOLEAN
> 
> This patch provides a test suite that allows us to make sure that
> the SQL BOOLEAN type works as intended.
> 
> Part of #4228

Could you remove duplicate tests from sql/types.test.lua ?
That is let’s move all tests related to boolean to this suite.

You added a lot of tests checking work with literals - that’s OK,
but I’d like to see the same amount of tests with table columns.
I don’t ask you to duplicate these tests, but come up with new
ones. At the end of letter I suggest several ways to extend this suite.

I’d say it would be great job if you found a few bugs (at least one).

> diff --git a/test/sql/boolean.test.sql b/test/sql/boolean.test.sql
> new file mode 100644
> index 0000000..7a1a0dc
> --- /dev/null
> +++ b/test/sql/boolean.test.sql
> @@ -0,0 +1,405 @@
> +-- Create table for tests
> +CREATE TABLE t (a BOOLEAN PRIMARY KEY);
> +INSERT INTO t VALUES (true), (false);

I’d add cases involving secondary index comprising nulls.

> +
> +-- Create user-defined function.
> +\set language lua
> +function return_type(arg) return type(arg) end
> +box.internal.sql_create_function("return_type", "TEXT", return_type)
> +\set language sql
> +
> +-- Check boolean as WHERE argument.
> +SELECT a FROM t WHERE a;
> +SELECT a FROM t WHERE a != true;
> +
> +-- Check that we can create index on field of type BOOLEAN.
> +CREATE INDEX i1 ON t(a);
> +
> +-- Check boolean as LIMIT argument.
> +SELECT * FROM t LIMIT true;
> +SELECT * FROM t LIMIT false;
> +
> +-- Check boolean as OFFSET argument.
> +SELECT * FROM t LIMIT 1 OFFSET true;
> +SELECT * FROM t LIMIT 1 OFFSET false;
> +
> +-- Check involvance in index search.
> +EXPLAIN QUERY PLAN SELECT a FROM t WHERE a = true;
> +
> +-- Check that ephemeral tables are used with BOOLEAN.
> +\set language lua
> +result = box.execute('EXPLAIN SELECT * FROM (VALUES(true)), t;')
> +i = 0
> +for _,v in pairs(result.rows) do if (v[2] == 'OpenTEphemeral') then i = i + 1 end end
> +i > 0
> +\set language sql
> +
> +-- Check BOOLEAN as argument of user-defined function.
> +SELECT return_type(a) FROM t;

What about functions returning boolean type?

> +
> +-- Check BOOLEAN as argument of scalar function.
> +SELECT typeof(a) FROM t;
> +
> +-- Check BOOLEAN as argument of aggregate function.
> +SELECT count(a) FROM t GROUP BY a;

Why only one aggregate?

> +
> +-- Check UNION, UNION ALL AND INTERSECT.
> +INSERT INTO t5 VALUES (100, false);
> +SELECT * FROM t4 UNION SELECT * FROM t5;
> +SELECT * FROM t4 UNION ALL SELECT * FROM t5;
> +SELECT * FROM t4 INTERSECT SELECT * FROM t5;
> +
> +-- Check SUBSELECT.
> +INSERT INTO t5(b) SELECT a FROM t4;
> +SELECT * FROM t5;
> +
> +-- Check VIEW.
> +CREATE VIEW v AS SELECT b FROM t5;
> +SELECT * FROM v;
> +
> +-- Check DISTINCT.
> +SELECT DISTINCT * FROM v;

Add tests on CTE, joins, UPDATE, switch-case,
ORDER BY (which again requires check of OP_Sort),
more sophisticated subquery examples.