MySQL Bugs: #33391: INSERT into table SELECT fields from table2 giving inconsistent/corrupt data

Bug #33391	INSERT into table SELECT fields from table2 giving inconsistent/corrupt data
Submitted:	20 Dec 2007 5:38	Modified:	20 Jan 2008 11:38
Reporter:	Gaurav Kohli	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S2 (Serious)
Version:	Mysql 5.0.45	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	inconsistent data, insert into, SELECT

Description:
 Hi,

I have two tables ia_raw_mo and ia_intermediate_mo.The ia_raw_mo table has extensive data , so I use the select group by on this table and insert the data into the ia_intermediate_mo table using "insert into select table" clause

The query is
insert into ia_intermediate_mo SELECT days, hours, bannerid, zoneid, ifnull(channel,'') as channel,ifnull(os,'') as os, ifnull(browser,'') as browser, ifnull(countryid,'') as countryid, ifnull(cityid,'') as cityid , ifnull(bandwidth,'') as bandwidth,count(*) as mouseovers
FROM ia_raw_mo where days='2007-12-14' and hours >= 8 and hours < 9 group by bannerid,zoneid,channel,os,browser,countryid,cityid,bandwidth,days,hours;

the ia_raw_intermediate has 9825 records and after correct execution of select and group by on this table, I should get 45 records in ia_intermediate_mo table.

But the above command is giving me 9825 records sometimes(around once in 100 times).So in that one particular case,there is no error message thrown but the group by is not properly executed and the query returns exact numbers of records , as if no group by was there

What I am thinking is,that there may be some problem in query plan which mysql creates, because the same query is also returning corrent result most of the time.But I am not sure about this or is there any possible flaw in the above query.

Even the mysql query log is also showing the same query been executed and returning 9825 records.

If any one can help me in this, that would be great.

Thanks,
Gaurav

How to repeat:
The problem is coming once in 100-200 runs of same query.

Thank you for taking the time to report a problem.  Unfortunately you are not using a current version of the product you reported a problem with -- the problem might already be fixed. Please download a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions, please change the version on this bug report to the version you tested and change the status back to "Open".  Again, thank you for your continued support of MySQL.

On our client side we have 5.0.45, where the bug was first found and we then we tested it on our mysql server(5.0.27).Here also the problem was coming .

Now we have upgraded to 5.0.45 GA release and the bug is still coming.

Thank you for the feedback. Could you please provide a complete test case:
create table statements, insert data statements, offended queries, result
you got and expected result and the my.cnf too. Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".