Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

cast of a SQL boolean value to a string or clob or (var)char is wrong #6110

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed
Labels
bug Something isn't working normal SQL

Comments

@monetdb-team
Copy link

Date: 2016-10-27 17:39:26 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.23.13 (Jun2016-SP2)
CC: @njnes

Last updated: 2017-05-01 13:32:45 +0200

Comment 24638

Date: 2016-10-27 17:39:26 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:49.0) Gecko/20100101 Firefox/49.0
Build Identifier:

boolean values true and false are not converted to strings: "true" and "false" when casting or converting to string, clob, char or varchar.

Reproducible: Always

Steps to Reproduce:

  1. Start mserver5
  2. Start mclient
  3. Execute SQL commands:
    CREATE TABLE t_boolean (val BOOLEAN, valstr VARCHAR(5));
    INSERT INTO t_boolean VALUES (true, 'True');
    INSERT INTO t_boolean VALUES (false, 'False');
    INSERT INTO t_boolean VALUES (Null, 'Null');

SELECT val, valstr, cast(val as bool) as cast2bool FROM t_boolean order by val;
SELECT val, valstr, cast(valstr as bool) as caststr2bool FROM t_boolean where val is not null order by val;

-- the next conversions to char strings are wrong
SELECT val, valstr, cast(val as string) as cast2str FROM t_boolean order by val;
SELECT val, valstr, cast(val as char(5)) as cast2char5 FROM t_boolean order by val;
SELECT val, valstr, cast(val as varchar(5)) as cast2varchar5 FROM t_boolean order by val;
SELECT val, valstr, cast(val as clob) as cast2clob FROM t_boolean order by val;

SELECT val, valstr, convert(val, string) as convert2str FROM t_boolean order by val;
SELECT val, valstr, convert(val, char(5)) as convert2char5 FROM t_boolean order by val;
SELECT val, valstr, convert(val, varchar(5)) as convert2varchar5 FROM t_boolean order by val;
SELECT val, valstr, convert(val, clob) as convert2clob FROM t_boolean order by val;

-- the next conversions are okay
SELECT val, valstr, cast(val as int) as cast2int FROM t_boolean order by val;
SELECT val, valstr, cast(val as smallint) as cast2smallint FROM t_boolean order by val;
SELECT val, valstr, cast(val as tinyint) as cast2tinyint FROM t_boolean order by val;
SELECT val, valstr, cast(val as bigint) as cast2bigint FROM t_boolean order by val;
SELECT val, valstr, cast(val as hugeint) as cast2hugeint FROM t_boolean order by val;

-- next casting should fail
SELECT val, valstr, cast(val as real) as cast2real FROM t_boolean order by val;
SELECT val, valstr, cast(val as float) as cast2float FROM t_boolean order by val;
SELECT val, valstr, cast(val as double) as cast2double FROM t_boolean order by val;
SELECT val, valstr, cast(val as time) as cast2time FROM t_boolean order by val;
SELECT val, valstr, cast(val as date) as cast2date FROM t_boolean order by val;

DROP TABLE t_boolean;

Actual Results:

bash-4.3$ mclient
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.26.0 (unreleased), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE TABLE t_boolean (val BOOLEAN, valstr VARCHAR(5));
operation successful (26.123ms)
sql>INSERT INTO t_boolean VALUES (true, 'True');
1 affected row (7.397ms)
sql>INSERT INTO t_boolean VALUES (false, 'False');
1 affected row (7.853ms)
sql>INSERT INTO t_boolean VALUES (Null, 'Null');
1 affected row (8.000ms)
sql>
sql>SELECT val, valstr, cast(val as bool) as cast2bool FROM t_boolean order by val;
+-------+--------+-----------+
| val | valstr | cast2bool |
+=======+========+===========+
| null | Null | null |
| false | False | false |
| true | True | true |
+-------+--------+-----------+
3 tuples (0.753ms)
sql>SELECT val, valstr, cast(valstr as bool) as caststr2bool FROM t_boolean where val is not null order by val;
+-------+--------+--------------+
| val | valstr | caststr2bool |
+=======+========+==============+
| false | False | false |
| true | True | true |
+-------+--------+--------------+
2 tuples (3.137ms)
sql>
sql>-- the next conversions to char strings are wrong
sql>SELECT val, valstr, cast(val as string) as cast2str FROM t_boolean order by val;
+-------+--------+----------+
| val | valstr | cast2str |
+=======+========+==========+
| null | Null | null |
| false | False | 0 |
| true | True | 1 |
+-------+--------+----------+
3 tuples (2.900ms)
sql>SELECT val, valstr, cast(val as char(5)) as cast2char5 FROM t_boolean order by val;
+-------+--------+------------+
| val | valstr | cast2char5 |
+=======+========+============+
| null | Null | null |
| false | False | 0 |
| true | True | 1 |
+-------+--------+------------+
3 tuples (2.177ms)
sql>SELECT val, valstr, cast(val as varchar(5)) as cast2varchar5 FROM t_boolean order by val;
+-------+--------+---------------+
| val | valstr | cast2varchar5 |
+=======+========+===============+
| null | Null | null |
| false | False | 0 |
| true | True | 1 |
+-------+--------+---------------+
3 tuples (0.925ms)
sql>SELECT val, valstr, cast(val as clob) as cast2clob FROM t_boolean order by val;
+-------+--------+-----------+
| val | valstr | cast2clob |
+=======+========+===========+
| null | Null | null |
| false | False | 0 |
| true | True | 1 |
+-------+--------+-----------+
3 tuples (0.708ms)
sql>
sql>SELECT val, valstr, convert(val, string) as convert2str FROM t_boolean order by val;
+-------+--------+-------------+
| val | valstr | convert2str |
+=======+========+=============+
| null | Null | null |
| false | False | 0 |
| true | True | 1 |
+-------+--------+-------------+
3 tuples (2.842ms)
sql>SELECT val, valstr, convert(val, char(5)) as convert2char5 FROM t_boolean order by val;
+-------+--------+---------------+
| val | valstr | convert2char5 |
+=======+========+===============+
| null | Null | null |
| false | False | 0 |
| true | True | 1 |
+-------+--------+---------------+
3 tuples (0.720ms)
sql>SELECT val, valstr, convert(val, varchar(5)) as convert2varchar5 FROM t_boolean order by val;
+-------+--------+------------------+
| val | valstr | convert2varchar5 |
+=======+========+==================+
| null | Null | null |
| false | False | 0 |
| true | True | 1 |
+-------+--------+------------------+
3 tuples (3.000ms)
sql>SELECT val, valstr, convert(val, clob) as convert2clob FROM t_boolean order by val;
+-------+--------+--------------+
| val | valstr | convert2clob |
+=======+========+==============+
| null | Null | null |
| false | False | 0 |
| true | True | 1 |
+-------+--------+--------------+
3 tuples (2.021ms)
sql>
sql>-- the next conversions are okay
sql>SELECT val, valstr, cast(val as int) as cast2int FROM t_boolean order by val;
+-------+--------+----------+
| val | valstr | cast2int |
+=======+========+==========+
| null | Null | null |
| false | False | 0 |
| true | True | 1 |
+-------+--------+----------+
3 tuples (0.659ms)
sql>SELECT val, valstr, cast(val as smallint) as cast2smallint FROM t_boolean order by val;
+-------+--------+---------------+
| val | valstr | cast2smallint |
+=======+========+===============+
| null | Null | null |
| false | False | 0 |
| true | True | 1 |
+-------+--------+---------------+
3 tuples (0.680ms)
sql>SELECT val, valstr, cast(val as tinyint) as cast2tinyint FROM t_boolean order by val;
+-------+--------+--------------+
| val | valstr | cast2tinyint |
+=======+========+==============+
| null | Null | null |
| false | False | 0 |
| true | True | 1 |
+-------+--------+--------------+
3 tuples (0.731ms)
sql>SELECT val, valstr, cast(val as bigint) as cast2bigint FROM t_boolean order by val;
+-------+--------+-------------+
| val | valstr | cast2bigint |
+=======+========+=============+
| null | Null | null |
| false | False | 0 |
| true | True | 1 |
+-------+--------+-------------+
3 tuples (2.801ms)
sql>SELECT val, valstr, cast(val as hugeint) as cast2hugeint FROM t_boolean order by val;
+-------+--------+--------------+
| val | valstr | cast2hugeint |
+=======+========+==============+
| null | Null | null |
| false | False | 0 |
| true | True | 1 |
+-------+--------+--------------+
3 tuples (0.666ms)
sql>
sql>-- next casting should fail
sql>SELECT val, valstr, cast(val as real) as cast2real FROM t_boolean order by val;
types boolean(1,0) and real(24,0) are not equal for column 'val'
sql>SELECT val, valstr, cast(val as float) as cast2float FROM t_boolean order by val;
types boolean(1,0) and double(53,0) are not equal for column 'val'
sql>SELECT val, valstr, cast(val as double) as cast2double FROM t_boolean order by val;
types boolean(1,0) and double(53,0) are not equal for column 'val'
sql>SELECT val, valstr, cast(val as time) as cast2time FROM t_boolean order by val;
types boolean(1,0) and time(1,0) are not equal for column 'val'
sql>SELECT val, valstr, cast(val as date) as cast2date FROM t_boolean order by val;
types boolean(1,0) and date(0,0) are not equal for column 'val'
sql>
sql>DROP TABLE t_boolean;
operation successful (7.385ms)
sql>

Expected Results:

sql>SELECT val, valstr, cast(val as string) as cast2str FROM t_boolean order by val;
+-------+--------+----------+
| val | valstr | cast2str |
+=======+========+==========+
| null | Null | null |
| false | False | false |
| true | True | true |
+-------+--------+----------+
3 tuples (2.900ms)
sql>SELECT val, valstr, cast(val as char(5)) as cast2char5 FROM t_boolean order by val;
+-------+--------+------------+
| val | valstr | cast2char5 |
+=======+========+============+
| null | Null | null |
| false | False | false |
| true | True | true |
+-------+--------+------------+
3 tuples (2.177ms)
sql>SELECT val, valstr, cast(val as varchar(5)) as cast2varchar5 FROM t_boolean order by val;
+-------+--------+---------------+
| val | valstr | cast2varchar5 |
+=======+========+===============+
| null | Null | null |
| false | False | false |
| true | True | true |
+-------+--------+---------------+
3 tuples (0.925ms)
sql>SELECT val, valstr, cast(val as clob) as cast2clob FROM t_boolean order by val;
+-------+--------+-----------+
| val | valstr | cast2clob |
+=======+========+===========+
| null | Null | null |
| false | False | false |
| true | True | true |
+-------+--------+-----------+
3 tuples (0.708ms)
sql>
sql>SELECT val, valstr, convert(val, string) as convert2str FROM t_boolean order by val;
+-------+--------+-------------+
| val | valstr | convert2str |
+=======+========+=============+
| null | Null | null |
| false | False | false |
| true | True | true |
+-------+--------+-------------+
3 tuples (2.842ms)
sql>SELECT val, valstr, convert(val, char(5)) as convert2char5 FROM t_boolean order by val;
+-------+--------+---------------+
| val | valstr | convert2char5 |
+=======+========+===============+
| null | Null | null |
| false | False | false |
| true | True | true |
+-------+--------+---------------+
3 tuples (0.720ms)
sql>SELECT val, valstr, convert(val, varchar(5)) as convert2varchar5 FROM t_boolean order by val;
+-------+--------+------------------+
| val | valstr | convert2varchar5 |
+=======+========+==================+
| null | Null | null |
| false | False | false |
| true | True | true |
+-------+--------+------------------+
3 tuples (3.000ms)
sql>SELECT val, valstr, convert(val, clob) as convert2clob FROM t_boolean order by val;
+-------+--------+--------------+
| val | valstr | convert2clob |
+=======+========+==============+
| null | Null | null |
| false | False | false |
| true | True | true |
+-------+--------+--------------+
3 tuples (2.021ms)

output boolean true as "true" when converting to character string
output boolean false as "false" when converting to character string

Comment 24961

Date: 2017-02-03 14:28:53 +0100
From: @sjoerdmullender

The reason for the values '0' and '1' is given in changeset fc4bf3655bd0

Comment 25082

Date: 2017-03-02 16:46:28 +0100
From: Martin van Dinther <<martin.van.dinther>>

In case the target is (var)char of 5 or more (or clob or text or string) it would be better to return 'true' or 'false'.
Only when target is (var)char 1..4 it should:

  • give an error
    or
  • truncate the string value to first 1..4 characters (this is preferred)
    or
  • return '0' or '1' (as currently implemented).

Note that if you wanted to convert a boolean to an int (or bit) you should use:
cast(boolean_col as tinyint)
Which can be converted to char(1) using:
cast(cast(boolean_col as tinyint) as char(1))

Comment 25083

Date: 2017-03-02 17:11:50 +0100
From: MonetDB Mercurial Repository <>

Changeset 5164261b899a made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=5164261b899a

Changeset description:

Adding test script for bug #6110

Comment 25208

Date: 2017-04-08 15:36:44 +0200
From: @njnes

solved, ie we use t/f for small char strings (len 1-5) and full true/false for longer strings

Comment 25209

Date: 2017-04-08 15:37:27 +0200
From: MonetDB Mercurial Repository <>

Changeset 1697b26f2155 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=1697b26f2155

Changeset description:

fix bug #6110, if use t/f and true/false when possible, when converting boolean
values to string
@monetdb-team monetdb-team added bug Something isn't working normal SQL labels Nov 30, 2020
@sjoerdmullender sjoerdmullender added this to the Ancient Release milestone Feb 7, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working normal SQL
Projects
None yet
Development

No branches or pull requests

2 participants