MySQL Bugs: #9554: Insert Into ... Select incorrect result (sequel)

Bug #9554	Insert Into ... Select incorrect result (sequel)
Submitted:	1 Apr 2005 9:49	Modified:	25 Apr 2005 13:14
Reporter:	Sergei Kulakov (Candidate Quality Contributor)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	3.23.58	OS:	Unix/Windows
Assigned to:		CPU Architecture:	Any

Description:
This report continues report 9201 as I can't add a comment to it, while I was misunderstood. 

Working on a project I found the strange effect: when I executed a select query
and displayed its results I got correct results, but when I added "insert into
.." to it the result changed. 
I grabbed the tables and made them as small as possible to simplify debugging. I
removed all columns I could so that to keep the effect, I also removed some
parts of the query. Strangely, removing some columns/query parts made the effect
disappear, while removing others did not. 
So finally I got the table Hotels with just 5 columns (out of some 30) and 3
rows and the table Prices without much data either. Hotels stores hotels data
while Prices stores their room rates.

How to repeat:
Here are the queries to recreate the context:

1. Table Hotels
CREATE TABLE Hotels (
  Id smallint(5) unsigned NOT NULL auto_increment,
  Name varchar(128) NOT NULL default '',
  AreaId mediumint(8) unsigned NOT NULL default '0',
  Status enum('Pending','Approved','Declined','Suspended') NOT NULL default
'Pending',
  Page text NOT NULL,
  PRIMARY KEY  (Id),
  UNIQUE KEY LocationId (AreaId,Name)
) TYPE=MyISAM; 

2. Its data
INSERT INTO Hotels VALUES (62,'Flora Beach Hotel',21,'Pending',''),(65,'Hotel
Padma',21,'Suspended',''),(77,'Rama Gardens Hotel',21,'Pending','');

3. Table Prices
CREATE TABLE Prices (
  HotelId smallint(5) unsigned NOT NULL default '0',
  No tinyint(3) unsigned NOT NULL default '0',
  ServiceId smallint(5) unsigned NOT NULL default '0',
  Price float NOT NULL default '0',
  PRIMARY KEY  (HotelId,ServiceId)
) TYPE=MyISAM;

4. Its data
INSERT INTO Prices VALUES (62,1,232,35),(65,1,784,50),(77,1,313,35);

5. The next query is supposed to return hotels' id, min price and status. The
idea is suspended hotels should have no price displayed. AreaId means geographic
area. 

Insert Into tmpHotData
Select Hotels.Id as HotelId, Min(Price) as Price, Hotels.Status
From Hotels as Hotels
Left Join Prices On	(Status!='Suspended' And Prices.HotelId=Hotels.Id)
Where AreaId=21
Group By HotelId

Normally it returns this (correct):

HotelId  Price   Status  
62        35      Pending 
65        NULL   Suspended 
77        35      Pending

If we create a table to store the result

Create Table tmpHotData(HotelId SmallInt UnSigned Not Null, Price float Not
Null, Status varchar(32));

and execute this:

Insert Into tmpHotData
Select Hotels.Id as HotelId, Min(Price) as Price, Hotels.Status
From Hotels as Hotels
Left Join Prices On	(Status!='Suspended' And Prices.HotelId=Hotels.Id)
Where AreaId=21
Group By HotelId;

the table will have 
65 0 Pending
in the second row which is impossible: the hotel is suspended!

If the query left join condition is changed to 
Left Join Prices On	(Prices.HotelId=If(Status!='Suspended', Hotels.Id, Null))
the effect remains, but removing any other seemingly insignificant query part
like Where AreaId=21 or prices makes the effect disappear. 

This effect is very subtle and requires many conditions to take place.

Suggested fix:
-

[16 Mar 2:06pm] Hartmut Holzgraefe

This is expected behavior as you are trying to insert a NULL value into a NOT
NULL column. What happens in this case is that the columns default value is used
instead.

Starting with MySQL 4.1 this would also generate a warning:

> show warnings;
+---------+------+--------------------------------------------------------------
-----+
| Level   | Code | Message                                                      
    |
+---------+------+--------------------------------------------------------------
-----+
| Warning | 1263 | Data truncated; NULL supplied to NOT NULL column 'Price' at
row 2 |
+---------+------+--------------------------------------------------------------
-----+

I'm sorry, perhaps I didn't put much emphasis onto it, but the wrong column was Status which normally returns Suspended but becomes Pending when I insert the query into the table, not Price. It was not about using a column's default value in case a NULL value is inserted into it. The column Status belongs to the first table - Hotels - and returns "Suspended" for the 2nd row, not a NULL value. The first column - HotelId - belongs to the same table and is not NULL either (65). As I use Left Join all rows from the first table must be found and they are. Only the second column - Price - returns NULL in the 2nd row, and it is converted into 0 when I insert the query result into the table tmpHotData - this is absolutely alright. 
Another sign of  it is that the behavior changes back to normal if the column AreaId is removed from the querty/table, which shouldn't be the case if the behavior were expected. 
Note that MySql 4.1 only gives a warning about the Price column, not the Status column.

Also I found 4.1 works correct in this situation.

3.23 branch is not maintained any longer.