Bug #24531 BOOLEAN literal UNKNOWN is missing

• Submitted: 23 Nov 2006 3:32
• Modified: 23 Nov 2006 12:16
• Reporter: Siu Ching Pong (Asuka Kenji) (Basic Quality Contributor)
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S4 (Feature request)
• Version: 5.1.12-beta
• OS: Linux (Linux (Fedora Core 5))
• CPU Architecture: Any
• Tags: boolean, BOOLEAN Related, Data Type, literal, qc, UNKNOWN

[23 Nov 2006 3:32] Siu Ching Pong (Asuka Kenji)

Description:
The BOOLEAN literal "UNKNOWN" is missing.
　
　
　
Motivation of reporting this issue:
　
1. Non-nullable BOOLEANs should act like, well, normal BOOLEANs. Nullable BOOLEANS should act like a "tristate" / "tribool". If TRUE (BOOLEAN one) and FALSE (BOOLEAN zero) are allowed as literals, so should UNKNOWN (BOOLEAN NULL).
　
2. This prevents the BOOLEAN DEFAULT value UNKNOWN in a CREATE TABLE statement. Of course, NULL could be used, but it should be better if UNKNOWN could be used.
　
3. Fixing the BOOLEAN literals first is an important step towards "full boolean
type handling".
　
　
　
File Downloaded:
mysql-5.1.12-beta-linux-x86_64-icc-glibc23.tar.gz
　
Note:
I have already read
"http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html"

How to repeat:
Statements:

DROP TABLE IF EXISTS test.t1;
DROP TABLE IF EXISTS test.t2;

CREATE TABLE test.t1 SELECT TRUE, FALSE, NULL;
DESC test.t1;

CREATE TABLE test.t2 SELECT TRUE, FALSE, UNKNOWN;
-- DESC test.t2;

Results:

+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| TRUE  | int(1)    | NO   |     | 0       |       |
| FALSE | int(1)    | NO   |     | 0       |       |
| NULL  | binary(0) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

ERROR 1054 (42S22): Unknown column 'UNKNOWN' in 'field list'

Suggested fix:
Please make the BOOLEAN literal UNKNOWN available.

[23 Nov 2006 12:16] Sergei Golubchik

You forgot to mention that UNKNOWN boolean literal is part of the standard:

<boolean literal> ::=
    TRUE
  | FALSE
  | UNKNOWN

[5 Dec 2008 18:25] Valeriy Kravchuk

Bug #41257 was marked as a duplicate of this one. See additional comments on proper BOOLEAN data type support there.

[5 Dec 2008 18:29] Valeriy Kravchuk

Bug #11881 was marked as a duplciate of this one.

[30 Apr 2009 20:24] Karsten Wutzke

I'd really like to see that ASAP, that is before 6.x. MySQL Workbench already allows BOOLEAN and BOOL types.

[20 Oct 2009 9:50] Karsten Wutzke

After some investigation, I changed my mind. UNKNOWN isn't really needed.