Mysql BUG with selecting NULLs
am 07.07.2003 14:57:17 von Jaunius Belickas------=_NextPart_000_0015_01C34498.0F0ACE30
Content-Type: text/plain;
charset="windows-1257"
Content-Transfer-Encoding: 7bit
# uname -a
FreeBSD kelmas.secure.lt 4.7-STABLE FreeBSD 4.7-STABLE #2: Wed Nov 6
23:42:06 EET 2002
congo@mentas.justnet.lt:/usr/obj/usr/local/cvsup/src/sys/FAB NET i386
# mysql -V
mysql Ver 13.5 Distrib 4.1.0-alpha, for portbld-freebsd4.7 (i386)
Context:
mysql> explain folder;
+-------------+--------------+-------------------+------+--- --+---------+---
-------------+
| Field | Type | Collation | Null | Key | Default |
Extra |
+-------------+--------------+-------------------+------+--- --+---------+---
-------------+
| id | int(11) | binary | | PRI | NULL |
auto_increment |
| pid | int(11) | binary | YES | MUL | NULL |
|
| category | int(11) | binary | YES | | NULL |
|
| name | varchar(255) | latin1_swedish_ci | | MUL | |
|
| description | text | latin1_swedish_ci | YES | | NULL |
|
+-------------+--------------+-------------------+------+--- --+---------+---
-------------+
mysql> select * from folder;
+----+------+----------+------------------+-------------+
| id | pid | category | name | description |
+----+------+----------+------------------+-------------+
| 1 | NULL | 8 | Zuikiai | NULL |
| 2 | NULL | 8 | Organizer | NULL |
| 3 | NULL | 8 | Karjeros centras | NULL |
| 5 | 3 | 8 | Darbdaviai | NULL |
......
Action: (select NULL columns from table)
SELECT pid, name FROM folder WHERE pid = NULL;
Expected: selected columns with NULL pid values;
Result: _EMPTY SET_ instead of 3 result rows.
------=_NextPart_000_0015_01C34498.0F0ACE30
Content-Type: text/plain;
name="testcase.sql"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="testcase.sql"
CREATE TABLE folder (
id int(11) NOT NULL auto_increment,
pid int(11) default NULL,
category int(11) default NULL,
name varchar(255) NOT NULL default '',
description text,
PRIMARY KEY (id),
UNIQUE KEY pid_2 (pid,category,name),
KEY id (id),
KEY pid (pid),
KEY name (name)
) TYPE=3DMyISAM
LOCK TABLES folder WRITE;
INSERT INTO folder VALUES =
(1,NULL,8,'Zuikiai',NULL),(2,NULL,8,'Organizer',NULL),(3,NUL L,8,'Karjeros=
=
centras',NULL),(5,3,8,'Darbdaviai',NULL),(6,3,8,'Studentai', NULL),(7,2,8,=
'Funkcionalumas',NULL),(8,2,8,'Skyriai',NULL),(9,8,8,'To =
do',NULL),(10,8,8,'Bookmarks',NULL),(11,8,8,'Code snapshots',NULL);
UNLOCK TABLES;
-- demonstrate that we DO HAVE NULLs !
select pid, name from folder;
-- try to select NULLs and fail.
select pid, name from folder where pid =3D NULL;
------=_NextPart_000_0015_01C34498.0F0ACE30
Content-Type: text/plain; charset=us-ascii
--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org
------=_NextPart_000_0015_01C34498.0F0ACE30--