Description:
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:
Please fix!
Description: 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: Please fix!