MySQL Bugs: #2012: INSERT ... SELECT writes zeros into all fields instead of real data

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:	Victor Vagin	CPU Architecture:	Any

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!

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.

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.

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

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