Bug #2012 INSERT ... SELECT writes zeros into all fields instead of real data
Submitted: 4 Dec 2003 6:58 Modified: 17 Dec 2003 2:01
Reporter: Sergei Kulakov (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:3.23.58 OS:FreeBSD (Free BSD)
Assigned to: Bugs System CPU Architecture:Any

[4 Dec 2003 6:58] Sergei Kulakov
Description:
I have some stats and I had to get numbers of records which is simply done by selecting all data plus null into a similar table with an extra auto_increment column. But I missed "primary key auto_increment" when creating the table and was surprised to see zeros in ALL fields instead of just the one for record numbers. Even though it was my mistake I do report the bug because the behaviour is not expected from MySQL. 

How to repeat:
1. This is my stat table

CREATE TABLE StatQuery(
  Month date NOT NULL,
  Type tinyint(3) unsigned NOT NULL auto_increment,
  Field int(10) unsigned NOT NULL,
  Count int(10) unsigned NOT NULL,
  UNIQUE KEY Month (Month,Type,Field)
) 

2. Populate it with some data

insert into StatQuery Values
(20030901, 1, 1, 100),
(20030901, 1, 2, 100),
(20030901, 2, 1, 100),
(20030901, 2, 2, 100),
(20030901, 3, 1, 100)

3. Try to select - you'll get the data

Select null, Field, Count
From StatQuery
Where Month=20030901 and Type=2

4. Now create the table missing auto_increment to store the result

create table Result(No int not null, Field int not null, Count int not null)

5. And insert the same select into it

insert into Result
Select null, Field, Count
From StatQuery
Where Month=20030901 and Type=2

6. Get zeros in ALL fields!

select * from Result

Suggested fix:
No idea - I'm not a MySQL developer. Plus too many conditions have to be met for the bug to show. For example, if there were only one condition in where (either "Month=20030901" or "Type=2") the bug would not show!
[4 Dec 2003 7:25] Dean Ellis
Verified in 3.23.58; works correctly in 4.0.17.

If, rather than inserting NULL for the first column, one inserts 0 (or some other non-NULL value), the query works.

Thank you.
[4 Dec 2003 7:30] Sergei Kulakov
Thanks for veryfing.
Of course I know it works with non-nulls. The point is I needed nulls because I intended to use it with an auto_increment field.
[16 Dec 2003 6:06] Victor Vagin
thank you very much for catching such a seldom bug

patch was sent (
To: dev-public@mysql.com
Subject: bk commit into 3.23 tree (1.1425)
) for review
[17 Dec 2003 2:01] Victor Vagin
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

It fixed in 3.23.59