Description:
using GREATEST(table1.field, table2.field) in the "where" clause of a select statement in 4.0.14 has changed from 4.0.13.
(This is also a great example on how to utilize GREATEST to select different tables based on a key..., I'm proud of my solution, and impressed by MySQL's flexibility, but not of the desing of the datamodel... which is not my own...)
For details, see the "How to repeat" section of this bugreport.
Simplified example:
Select BOOKING.ID, USER.NAME, GREATEST(TABLE1.ID, TABLE2.ID) as table_id
from BOOKING, USER, PERM
left outer join TABLE1 on TABLE1.TEXT=BOOKING.TEXT and BOOKING.TABLEID=1
left outer join TABLE2 on TABLE2.TEXT=BOOKING.TEXT and BOOKING.TABLEID=2
where USER.UID=BOOKING.UID
and PERM.TYPEID=BOOKING.TABLEID
and PERM.TABLEID=GREATEST(TABLE1.ID, TABLE2.ID)
and PERM.UID=232;
The idea behind the statement is rather complicated, and due to historical data, we cannot bypass the limitations in our tables, or change the datamodel.
The BOOKING table contains TEXT, which is the unique booking object (here is one flaw in our design, it should have been an UNIQUE ID instead...)
Anyway the booking objects themselves is spread around 4 tables (using 2 here for simplification, TABLE1, TABLE2)
first we want to look up the row in TABLE1 or TABLE2 based on TEXT field and TABLEID field in BOOKING using this:
Select BOOKING.ID, GREATEST(TABLE1.ID, TABLE2.ID) as table_id
from BOOKING,
left outer join TABLE1 on TABLE1.TEXT=BOOKING.TEXT and BOOKING.TABLEID=1
left outer join TABLE2 on TABLE2.TEXT=BOOKING.TEXT and BOOKING.TABLEID=2
....
So far, so good, this works in both 4.0.13 and 4.0.14
Then, we have a complicating issue, we have a table PERM which describes permsissions on access to records in TABLE1, TABLE2 and so on... based on an UID
which we then incorporates into our statement:
Select BOOKING.ID, USER.NAME, GREATEST(TABLE1.ID, TABLE2.ID) as table_id
from BOOKING, USER, PERM
left outer join TABLE1 on TABLE1.TEXT=BOOKING.TEXT and BOOKING.TABLEID=1
left outer join TABLE2 on TABLE2.TEXT=BOOKING.TEXT and BOOKING.TABLEID=2
where USER.UID=BOOKING.UID
and PERM.TYPEID=BOOKING.TABLEID
and PERM.TABLEID=GREATEST(TABLE1.ID, TABLE2.ID)
and PERM.UID=232;
The final goal of all this spaghetti, is to get a recordset from BOOKING, where the records should match the PERMISSIONS of the current user! ( an ugly mess, but blazingly fast when it worked in 4.0.13 .... MySQL does (did) the job excellent) And perfectly legal and correct....
So, to say it in some other words:
I, UID 232, wants to look up all records in BOOKING, but I'm not allowed to see records which contains objects in TABLE1 or TABLE2 where I dont have matching PERM -records (TYPEID, TABLEID). The result should show the uid of other users...
The design flaw is obious, if BOOKING had direct relation to the ID in TABLE1, TABLE2 and so on... this wouldnt have to be so complicated, I could have matched PERM.TABLEID against that..., but unfortunately it have only the TEXT field and TABLEID, which I have to look up first... to find the TABLE(x).ID to resolve in the PERM table..
In other news... MySQL 4.0.14 fixes some of the table corruption bugs(se bug #563), but due to this statement bug, we are unable to use 4.0.14 and continue testing and provide feedback on _that_ issue... so for our sake, and for your sake, it would be nice if you found, and fixed this bug in the next release...
Regards, Håkon.
How to repeat:
create database mysqlbug;
use mysqlbug;
create table BOOKING ( ID int, TEXT varchar(50), TABLEID int, UID int, PRIMARY KEY (ID) );
create table USER ( UID int, NAME varchar(50), PRIMARY KEY (UID) );
create table TABLE1 ( ID int, TEXT varchar(50), PRIMARY KEY(ID) );
create table TABLE2 ( ID int, TEXT varchar(50), PRIMARY KEY(ID) );
create table PERM ( TYPEID int, TABLEID int, UID int );
insert into BOOKING set ID=1, TEXT="test1", TABLEID=1, UID=232;
insert into BOOKING set ID=2, TEXT="test2", TABLEID=2, UID=232;
insert into TABLE1 set ID=1, TEXT="test1";
insert into TABLE2 set ID=1, TEXT="test2";
insert into USER set UID=232, NAME="haakon";
insert into PERM set TYPEID=1, TABLEID=1, UID=232;
insert into PERM set TYPEID=2, TABLEID=1, UID=232;
Select BOOKING.ID, USER.NAME, GREATEST(TABLE1.ID, TABLE2.ID) as table_id
from BOOKING, USER, PERM
left outer join TABLE1 on TABLE1.TEXT=BOOKING.TEXT and BOOKING.TABLEID=1
left outer join TABLE2 on TABLE2.TEXT=BOOKING.TEXT and BOOKING.TABLEID=2
where USER.UID=BOOKING.UID
and PERM.TYPEID=BOOKING.TABLEID
and PERM.TABLEID=GREATEST(TABLE1.ID, TABLE2.ID)
and PERM.UID=232;
Should give this value ( and it is correct on MySQL 4.0.13)
+----+--------+----------+
| ID | NAME | table_id |
+----+--------+----------+
| 1 | haakon | 1 |
| 2 | haakon | 1 |
+----+--------+----------+
2 rows in set (0.00 sec)
Running the above data on MySQL 4.0.14 gives an empty result:
Empty set (0.00 sec)
Description: using GREATEST(table1.field, table2.field) in the "where" clause of a select statement in 4.0.14 has changed from 4.0.13. (This is also a great example on how to utilize GREATEST to select different tables based on a key..., I'm proud of my solution, and impressed by MySQL's flexibility, but not of the desing of the datamodel... which is not my own...) For details, see the "How to repeat" section of this bugreport. Simplified example: Select BOOKING.ID, USER.NAME, GREATEST(TABLE1.ID, TABLE2.ID) as table_id from BOOKING, USER, PERM left outer join TABLE1 on TABLE1.TEXT=BOOKING.TEXT and BOOKING.TABLEID=1 left outer join TABLE2 on TABLE2.TEXT=BOOKING.TEXT and BOOKING.TABLEID=2 where USER.UID=BOOKING.UID and PERM.TYPEID=BOOKING.TABLEID and PERM.TABLEID=GREATEST(TABLE1.ID, TABLE2.ID) and PERM.UID=232; The idea behind the statement is rather complicated, and due to historical data, we cannot bypass the limitations in our tables, or change the datamodel. The BOOKING table contains TEXT, which is the unique booking object (here is one flaw in our design, it should have been an UNIQUE ID instead...) Anyway the booking objects themselves is spread around 4 tables (using 2 here for simplification, TABLE1, TABLE2) first we want to look up the row in TABLE1 or TABLE2 based on TEXT field and TABLEID field in BOOKING using this: Select BOOKING.ID, GREATEST(TABLE1.ID, TABLE2.ID) as table_id from BOOKING, left outer join TABLE1 on TABLE1.TEXT=BOOKING.TEXT and BOOKING.TABLEID=1 left outer join TABLE2 on TABLE2.TEXT=BOOKING.TEXT and BOOKING.TABLEID=2 .... So far, so good, this works in both 4.0.13 and 4.0.14 Then, we have a complicating issue, we have a table PERM which describes permsissions on access to records in TABLE1, TABLE2 and so on... based on an UID which we then incorporates into our statement: Select BOOKING.ID, USER.NAME, GREATEST(TABLE1.ID, TABLE2.ID) as table_id from BOOKING, USER, PERM left outer join TABLE1 on TABLE1.TEXT=BOOKING.TEXT and BOOKING.TABLEID=1 left outer join TABLE2 on TABLE2.TEXT=BOOKING.TEXT and BOOKING.TABLEID=2 where USER.UID=BOOKING.UID and PERM.TYPEID=BOOKING.TABLEID and PERM.TABLEID=GREATEST(TABLE1.ID, TABLE2.ID) and PERM.UID=232; The final goal of all this spaghetti, is to get a recordset from BOOKING, where the records should match the PERMISSIONS of the current user! ( an ugly mess, but blazingly fast when it worked in 4.0.13 .... MySQL does (did) the job excellent) And perfectly legal and correct.... So, to say it in some other words: I, UID 232, wants to look up all records in BOOKING, but I'm not allowed to see records which contains objects in TABLE1 or TABLE2 where I dont have matching PERM -records (TYPEID, TABLEID). The result should show the uid of other users... The design flaw is obious, if BOOKING had direct relation to the ID in TABLE1, TABLE2 and so on... this wouldnt have to be so complicated, I could have matched PERM.TABLEID against that..., but unfortunately it have only the TEXT field and TABLEID, which I have to look up first... to find the TABLE(x).ID to resolve in the PERM table.. In other news... MySQL 4.0.14 fixes some of the table corruption bugs(se bug #563), but due to this statement bug, we are unable to use 4.0.14 and continue testing and provide feedback on _that_ issue... so for our sake, and for your sake, it would be nice if you found, and fixed this bug in the next release... Regards, Håkon. How to repeat: create database mysqlbug; use mysqlbug; create table BOOKING ( ID int, TEXT varchar(50), TABLEID int, UID int, PRIMARY KEY (ID) ); create table USER ( UID int, NAME varchar(50), PRIMARY KEY (UID) ); create table TABLE1 ( ID int, TEXT varchar(50), PRIMARY KEY(ID) ); create table TABLE2 ( ID int, TEXT varchar(50), PRIMARY KEY(ID) ); create table PERM ( TYPEID int, TABLEID int, UID int ); insert into BOOKING set ID=1, TEXT="test1", TABLEID=1, UID=232; insert into BOOKING set ID=2, TEXT="test2", TABLEID=2, UID=232; insert into TABLE1 set ID=1, TEXT="test1"; insert into TABLE2 set ID=1, TEXT="test2"; insert into USER set UID=232, NAME="haakon"; insert into PERM set TYPEID=1, TABLEID=1, UID=232; insert into PERM set TYPEID=2, TABLEID=1, UID=232; Select BOOKING.ID, USER.NAME, GREATEST(TABLE1.ID, TABLE2.ID) as table_id from BOOKING, USER, PERM left outer join TABLE1 on TABLE1.TEXT=BOOKING.TEXT and BOOKING.TABLEID=1 left outer join TABLE2 on TABLE2.TEXT=BOOKING.TEXT and BOOKING.TABLEID=2 where USER.UID=BOOKING.UID and PERM.TYPEID=BOOKING.TABLEID and PERM.TABLEID=GREATEST(TABLE1.ID, TABLE2.ID) and PERM.UID=232; Should give this value ( and it is correct on MySQL 4.0.13) +----+--------+----------+ | ID | NAME | table_id | +----+--------+----------+ | 1 | haakon | 1 | | 2 | haakon | 1 | +----+--------+----------+ 2 rows in set (0.00 sec) Running the above data on MySQL 4.0.14 gives an empty result: Empty set (0.00 sec)