[tarantool-patches] Re: [PATCH v1 1/1] sql: test suite for BOOLEAN
n.pettik
korablev at tarantool.org
Thu Aug 8 18:30:57 MSK 2019
>
>> 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.
More information about the Tarantool-patches
mailing list