NULLs get scrambled in an INSERT-SELECT

[Jan Dvorak]

I hope to have found the right place for a bug report,
finally.

Jan


-------- Original Message --------
From: Jan Dvorak <jan.dvorak@stripped>
Subject: [Fwd: NULLs get scrambled in an INSERT-SELECT]
To: developer@stripped

I'm not sure if this bug report hasn't slipped
the Developers' attention.
I think it's pretty urgent,
as a part of standard SQL functionality broke.

Jan Dvorak


-------- Original Message --------
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

-- 
---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"
before
posting. To request this thread, e-mail
mysql-thread28055@stripped

To unsubscribe, send a message to:
    <mysql-unsubscribe-jan.dvorak=veda.cz@stripped>

---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"
before
posting. To request this thread, e-mail
developer-thread800@stripped

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail developer-unsubscribe@stripped instead.