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

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

> 
>> 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).
>> 
> Not sure if these are bugs:
> 
> 1) Different results in case of internal convertion from
> BOOLEAN to TEXT:
> 
> tarantool> CREATE TABLE t(a BOOLEAN PRIMARY KEY);
> ---
> - row_count: 1
> ...
> 
> tarantool> INSERT INTO t VALUES (true), (false);
> ---
> - row_count: 2
> ...
> 
> tarantool> SELECT GROUP_CONCAT(a, '+++') FROM t;
> ---
> - metadata:
>  - name: GROUP_CONCAT(a, '+++')
>    type: string
>  rows:
>  - ['false+++true']
> …

I’d say it’s a tiny bug.

> tarantool> SELECT GROUP_CONCAT(CAST(a AS TEXT), '+++') FROM t;
> ---
> - metadata:
>  - name: GROUP_CONCAT(CAST(a AS TEXT), '+++')
>    type: string
>  rows:
>  - ['FALSE+++TRUE']
> ...
> 
> 2) CAST() does not cast from BOOLEAN to FLOAT, but it works
> in case FLOAT casted to BOOLEAN:
> 
> tarantool> SELECT cast(0.123 AS BOOLEAN), cast(0.0 AS BOOLEAN);
> ---
> - metadata:
>  - name: cast(0.123 AS BOOLEAN)
>    type: boolean
>  - name: cast(0.0 AS BOOLEAN)
>    type: boolean
>  rows:
>  - [true, false]
> …

It’s OK by design.

> 3) Operator IN works in one case and doesn't work in
> another:
> 
> tarantool> SELECT true in (1,2,3);
> ---
> - metadata:
>  - name: true in (1,2,3)
>    type: boolean
>  rows:
>  - [false]
> ...
> 
> tarantool> SELECT true IN (VALUES(1), (2), (3));
> ---
> - error: 'Type mismatch: can not convert true to integer'
> ...
> 
> 4) Function LENGTH() returns NULL when argument is BOOLEAN:
> 
> tarantool> SELECT length(false);
> ---
> - metadata:
>  - name: length(false)
>    type: integer
>  rows:
>  - [null]
> …

Please, file one issue containing first, third and fourth
points.

> 
> 5) Operator AND works differently for these cases:
> 
> tarantool> SELECT 'abc' AND false;
> ---
> - metadata:
>  - name: '''abc'' AND false'
>    type: boolean
>  rows:
>  - [false]
> ...
> 
> tarantool> SELECT 'abc' AND a FROM t WHERE a == false;
> ---
> - error: 'Type mismatch: can not convert abc to boolean'
> …

Yep, I guess it is due to AND optimisation.
Not sure if we should care about it or not.

> diff --git a/test/sql/boolean.test.sql b/test/sql/boolean.test.sql
> new file mode 100644
> index 0000000..e45e554
> --- /dev/null
> +++ b/test/sql/boolean.test.sql
> 
> +-- Check SWITCH-CASE.
> +SELECT i, \
> +CASE \
> +	WHEN a == true AND i % 2 == 1 THEN false \
> +	WHEN a == true and i % 2 == 0 THEN true \
> +	WHEN a != true then false \
> +END AS a0 \
> +FROM t4;
> +
> +-- Check OPDER BY.

Nit: ORDER BY

> +SELECT * FROM t4 UNION SELECT * FROM t5 ORDER BY a, i;
> +
> +-- Check GROUP BY.
> +SELECT a, COUNT(*) FROM (SELECT * FROM t4 UNION SELECT * FROM t5) GROUP BY a;

The rest seems to be OK.