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

[Mergen Imeev]

Hi! Thank you for review! My answers below.

On Thu, Aug 08, 2019 at 06:30:57PM +0300, n.pettik wrote:
> > 
> >> 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.
> 
Done:
https://github.com/tarantool/tarantool/issues/4462

> > 
> > 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
> 
Fixed.

> > +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.
>