From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CB53C23067 for ; Thu, 8 Aug 2019 11:30:59 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 940sgPcB2QxJ for ; Thu, 8 Aug 2019 11:30:59 -0400 (EDT) Received: from smtpng2.m.smailru.net (smtpng2.m.smailru.net [94.100.179.3]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 8A4EA22FB5 for ; Thu, 8 Aug 2019 11:30:59 -0400 (EDT) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 12.4 \(3445.104.11\)) Subject: [tarantool-patches] Re: [PATCH v1 1/1] sql: test suite for BOOLEAN From: "n.pettik" In-Reply-To: <20190724115237.GA22402@tarantool.org> Date: Thu, 8 Aug 2019 18:30:57 +0300 Content-Transfer-Encoding: quoted-printable Message-Id: References: <2ecdecf63e68a73815c48d9ff369cbd129ad28d6.1563198457.git.imeevma@gmail.com> <20190716095708.GA29957@tarantool.org> <20190724115237.GA22402@tarantool.org> Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-Subscribe: List-Owner: List-post: List-Archive: To: tarantool-patches@freelists.org Cc: Imeev Mergen >=20 >> I don=E2=80=99t 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. >>=20 >> I=E2=80=99d say it would be great job if you found a few bugs (at = least one). >>=20 > Not sure if these are bugs: >=20 > 1) Different results in case of internal convertion from > BOOLEAN to TEXT: >=20 > tarantool> CREATE TABLE t(a BOOLEAN PRIMARY KEY); > --- > - row_count: 1 > ... >=20 > tarantool> INSERT INTO t VALUES (true), (false); > --- > - row_count: 2 > ... >=20 > tarantool> SELECT GROUP_CONCAT(a, '+++') FROM t; > --- > - metadata: > - name: GROUP_CONCAT(a, '+++') > type: string > rows: > - ['false+++true'] > =E2=80=A6 I=E2=80=99d say it=E2=80=99s 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'] > ... >=20 > 2) CAST() does not cast from BOOLEAN to FLOAT, but it works > in case FLOAT casted to BOOLEAN: >=20 > 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] > =E2=80=A6 It=E2=80=99s OK by design. > 3) Operator IN works in one case and doesn't work in > another: >=20 > tarantool> SELECT true in (1,2,3); > --- > - metadata: > - name: true in (1,2,3) > type: boolean > rows: > - [false] > ... >=20 > tarantool> SELECT true IN (VALUES(1), (2), (3)); > --- > - error: 'Type mismatch: can not convert true to integer' > ... >=20 > 4) Function LENGTH() returns NULL when argument is BOOLEAN: >=20 > tarantool> SELECT length(false); > --- > - metadata: > - name: length(false) > type: integer > rows: > - [null] > =E2=80=A6 Please, file one issue containing first, third and fourth points. >=20 > 5) Operator AND works differently for these cases: >=20 > tarantool> SELECT 'abc' AND false; > --- > - metadata: > - name: '''abc'' AND false' > type: boolean > rows: > - [false] > ... >=20 > tarantool> SELECT 'abc' AND a FROM t WHERE a =3D=3D false; > --- > - error: 'Type mismatch: can not convert abc to boolean' > =E2=80=A6 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 >=20 > +-- Check SWITCH-CASE. > +SELECT i, \ > +CASE \ > + WHEN a =3D=3D true AND i % 2 =3D=3D 1 THEN false \ > + WHEN a =3D=3D true and i % 2 =3D=3D 0 THEN true \ > + WHEN a !=3D 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.