From: Jan Dvorak Date: February 18 2000 12:40pm Subject: NULLs get scrambled in an INSERT-SELECT List-Archive: http://lists.mysql.com/bugs/6 Message-Id: <38AD3DA8.9F71D66E@veda.cz> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I hope to have found the right place for a bug report, finally. Jan -------- Original Message -------- From: Jan Dvorak 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: --------------------------------------------------------------------- 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.