List:Bugs« Previous MessageNext Message »
From:Thimble Smith Date:February 18 2000 9:50am
Subject:NULLs get scrambled in an INSERT-SELECT
View as plain text  
----- Forwarded message from Jan Dvorak <jan.dvorak@stripped> -----
From: jan.dvorak@stripped
Subject: NULLs get scrambled in an INSERT-SELECT
To: mysql@stripped

>Description:
An INSERT-SELECT appears to change NULLs to 0's.

When the SELECT part is run by itself,
it produces the correct result,
with NULLs at the right places.

When I try to INSERT the result into a table,
the NULLs are changed to 0's,
even though the relevant target table column
does allow NULLs.

Same problem with CREATE TABLE ... SELECT.

>How-To-Repeat:

Create two tables to play with:
mysql> create table A ( id integer unsigned not null primary key );
mysql> create table B ( id integer unsigned not null primary key );
mysql> insert into A values (1), (2);
mysql> insert into B values (1);

An outer join works as expected:
mysql> select        
    ->     A.id as id_A,
    ->     B.id as id_B
    ->   from
    ->     A left join B using ( id );
+------+------+
| id_A | id_B |
+------+------+
|    1 |    1 |
|    2 | NULL |
+------+------+
2 rows in set (0.00 sec)

Now I want to store the result in a table:
mysql> create table AB_2 (
    ->    id_A integer unsigned not null,
    ->    id_B integer unsigned null        /* note the null */
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into AB_2
    -> select
    ->     A.id as id_A,
    ->     B.id as id_B
    ->   from
    ->     A left join B using ( id );
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from AB_2;
+------+------+
| id_A | id_B |
+------+------+
|    1 |    1 |
|    2 |    0 |     <--- NULL lost!
+------+------+
2 rows in set (0.00 sec)

The NULL was changed to 0.

It's the same with CREATE TABLE ... SELECT:
mysql> create table AB_1
    -> select
    ->     A.id as id_A,
    ->     B.id as id_B
    ->   from
    ->     A left join B using ( id );
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from AB_1;
+------+------+
| id_A | id_B |
+------+------+
|    1 |    1 |
|    2 |    0 |     <--- NULL lost!
+------+------+
2 rows in set (0.01 sec)

Althouth I'm running 3.23.9,
nothing in the Changes appendix of the Manual indicates 
that this misbehaviour should be fixed by 3.23.11
- or did I miss something?


>Fix:

>Submitter-Id:	jan.dvorak@stripped
>Originator:	Jan Dvorak
>Organization:	MathAn Praha, Ltd.; CZ
>MySQL support: none
>Synopsis:	NULLs get scrambled in an INSERT-SELECT
>Severity:	critical
>Priority:	high
>Category:	mysql
>Class:		sw-bug
>Release:	mysql-3.23.9-alpha (Official MySQL RPM)
>Server: /usr/bin/mysqladmin  Ver 7.14 Distrib 3.23.8-alpha, for pc-linux-gnu on i686
TCX Datakonsult AB, by Monty

Server version		3.23.9-alpha-log
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/lib/mysql/mysql.sock
Uptime:			6 days 21 hours 10 min 42 sec

Threads: 6  Questions: 20715  Slow queries: 6  Opens: 1329  Flush
tables: 67  Open tables: 57
>Environment:
System: Linux medved.mathan.cz 2.2.13 #2 SMP Thu Dec 16 09:47:45 CET
1999 i686 unknown
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from
/usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
Compilation info: CC='egcs'  CFLAGS='-O6 -fomit-frame-pointer
-mpentium'  CXX='egcs'  CXXFLAGS='-O6 -fomit-frame-pointer 	         
-felide-constructors -fno-exceptions -fno-rtti -mpentium'  LDFLAGS=''
LIBC: 
lrwxrwxrwx   1 root     root           13 Jun  9  1999 /lib/libc.so.6 ->
libc-2.1.1.so
-rwxr-xr-x   1 root     root      4016683 Apr 17  1999
/lib/libc-2.1.1.so
-rw-r--r--   1 root     root     19533408 Apr 17  1999 /usr/lib/libc.a
-rw-r--r--   1 root     root          178 Apr 17  1999 /usr/lib/libc.so
Configure command: ./configure  --disable-shared
--with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static
--enable-assembler --with-mysqld-user=mysql
--with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/
--exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc
--datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info
--includedir=/usr/include --mandir=/usr/man '--with-comment=Official
MySQL RPM'
Perl: This is perl, version 5.005_03 built for i386-linux
-- 
Tim Smith   < tim@stripped >  :MySQL Development Team:  Boone, NC  USA.
Thread
NULLs get scrambled in an INSERT-SELECTThimble Smith18 Feb