Description:
# Set up data
DROP TABLE IF EXISTS invoice;
CREATE TABLE invoice (
id int(11) unsigned NOT NULL auto_increment,
text_id int(10) unsigned default NULL,
PRIMARY KEY (id)
);
INSERT INTO invoice VALUES("1", "0");
INSERT INTO invoice VALUES("2", "10");
DROP TABLE IF EXISTS text_table;
CREATE TABLE text_table (
text_id char(3) NOT NULL default '',
language_id char(3) NOT NULL default '',
text_data text,
PRIMARY KEY (text_id,language_id)
);
INSERT INTO text_table VALUES("0", "EN", "0-EN");
INSERT INTO text_table VALUES("0", "SV", "0-SV");
INSERT INTO text_table VALUES("10", "EN", "10-EN");
INSERT INTO text_table VALUES("10", "SV", "10-SV");
# Perform a query
SELECT invoice.id, invoice.text_id, text_table.text_data
FROM invoice
LEFT JOIN text_table ON invoice.text_id = text_table.text_id
AND text_table.language_id = 'SV'
WHERE (invoice.id LIKE '%' OR text_table.text_data LIKE '%');
The expected result is of course
+----+---------+-----------+
| id | text_id | text_data |
+----+---------+-----------+
| 1 | 0 | 0-SV |
| 2 | 10 | 10-SV |
+----+---------+-----------+
Though, the query above, run on MySQL 4.1.7 on Windows will provide the following result
+----+---------+-----------+
| id | text_id | text_data |
+----+---------+-----------+
| 1 | 0 | 0-EN |
| 1 | 0 | 0-SV |
| 1 | 0 | 10-EN |
| 1 | 0 | 10-SV |
| 2 | 10 | 0-EN |
| 2 | 10 | 0-SV |
| 2 | 10 | 10-EN |
| 2 | 10 | 10-SV |
+----+---------+-----------+
I noticed also, that if one of the two WHERE conditions are removed, the expected result will be provided. Charset is latin1 (latin1_swedish_ci).
How to repeat:
Se above.
Suggested fix:
Make it return the same result as 4.0 did.
Description: # Set up data DROP TABLE IF EXISTS invoice; CREATE TABLE invoice ( id int(11) unsigned NOT NULL auto_increment, text_id int(10) unsigned default NULL, PRIMARY KEY (id) ); INSERT INTO invoice VALUES("1", "0"); INSERT INTO invoice VALUES("2", "10"); DROP TABLE IF EXISTS text_table; CREATE TABLE text_table ( text_id char(3) NOT NULL default '', language_id char(3) NOT NULL default '', text_data text, PRIMARY KEY (text_id,language_id) ); INSERT INTO text_table VALUES("0", "EN", "0-EN"); INSERT INTO text_table VALUES("0", "SV", "0-SV"); INSERT INTO text_table VALUES("10", "EN", "10-EN"); INSERT INTO text_table VALUES("10", "SV", "10-SV"); # Perform a query SELECT invoice.id, invoice.text_id, text_table.text_data FROM invoice LEFT JOIN text_table ON invoice.text_id = text_table.text_id AND text_table.language_id = 'SV' WHERE (invoice.id LIKE '%' OR text_table.text_data LIKE '%'); The expected result is of course +----+---------+-----------+ | id | text_id | text_data | +----+---------+-----------+ | 1 | 0 | 0-SV | | 2 | 10 | 10-SV | +----+---------+-----------+ Though, the query above, run on MySQL 4.1.7 on Windows will provide the following result +----+---------+-----------+ | id | text_id | text_data | +----+---------+-----------+ | 1 | 0 | 0-EN | | 1 | 0 | 0-SV | | 1 | 0 | 10-EN | | 1 | 0 | 10-SV | | 2 | 10 | 0-EN | | 2 | 10 | 0-SV | | 2 | 10 | 10-EN | | 2 | 10 | 10-SV | +----+---------+-----------+ I noticed also, that if one of the two WHERE conditions are removed, the expected result will be provided. Charset is latin1 (latin1_swedish_ci). How to repeat: Se above. Suggested fix: Make it return the same result as 4.0 did.