Description:
Suppose there's 3 tables, T1, T2, T3
You're SELECTing all the data from T1, you're joining T2 with a LEFT join on T1 (which does not restrict the data from T1, cause it's a left join) and you're joining T3 on T2 with an INNER JOIN (which should not restrict the data being selected from T1, but Should restrict the data being selected from T2.
This will only work if 'some' data from T3 is selected. In a case where you construct a WHERE clause for T3 that will exclude any information from T3, the amount of rows returned will be 0 - while it should return at least all the data from T1, (with NULL values for the fields from T2 and T3).
p.s.: i don't have shell access so i couldn't use the mysqlbug program
How to repeat:
*** bod ***
#
# Table structure for table 'tabel1'
#
CREATE TABLE tabel1 (
id int(10) unsigned NOT NULL auto_increment,
jaarweek mediumint(6) unsigned default NULL,
PRIMARY KEY (id),
UNIQUE KEY t1_id (id),
KEY jw (jaarweek)
) TYPE=MyISAM;
#
# Dumping data for table 'tabel1'
#
INSERT INTO tabel1 VALUES("1", "200301");
INSERT INTO tabel1 VALUES("2", "200302");
INSERT INTO tabel1 VALUES("3", "200303");
INSERT INTO tabel1 VALUES("4", "200304");
INSERT INTO tabel1 VALUES("5", "200305");
INSERT INTO tabel1 VALUES("6", "200306");
INSERT INTO tabel1 VALUES("7", "200307");
INSERT INTO tabel1 VALUES("8", "200308");
INSERT INTO tabel1 VALUES("9", "200309");
INSERT INTO tabel1 VALUES("10", "200310");
INSERT INTO tabel1 VALUES("11", "200311");
INSERT INTO tabel1 VALUES("12", "200312");
INSERT INTO tabel1 VALUES("13", "200313");
INSERT INTO tabel1 VALUES("14", "200314");
INSERT INTO tabel1 VALUES("15", "200315");
#
# Table structure for table 'tabel2'
#
CREATE TABLE tabel2 (
id int(10) unsigned NOT NULL auto_increment,
date_start date default NULL,
date_end date default NULL,
uren tinyint(3) unsigned default NULL,
PRIMARY KEY (id),
UNIQUE KEY t2_id (id),
KEY ds (date_start),
KEY de (date_end)
) TYPE=MyISAM;
#
# Dumping data for table 'tabel2'
#
INSERT INTO tabel2 VALUES("1", "2002-12-09", "2003-01-05", "20");
INSERT INTO tabel2 VALUES("2", "2002-09-12", "2003-03-03", "30");
INSERT INTO tabel2 VALUES("3", "2001-01-01", NULL, "40");
INSERT INTO tabel2 VALUES("4", "2003-02-02", "2003-04-05", "10");
#
# Table structure for table 'tabel3'
#
CREATE TABLE tabel3 (
id int(10) unsigned NOT NULL auto_increment,
tabel2_id int(10) unsigned default NULL,
beperk enum('true','false') default 'false',
PRIMARY KEY (id),
UNIQUE KEY id (id),
KEY tabel2_id (tabel2_id)
) TYPE=MyISAM;
#
# Dumping data for table 'tabel3'
#
INSERT INTO tabel3 VALUES("1", "1", "false");
INSERT INTO tabel3 VALUES("2", "2", "false");
INSERT INTO tabel3 VALUES("3", "3", "true");
INSERT INTO tabel3 VALUES("4", "4", "false");
*** eod ***
Now, the next query will produce normal results:
*** boq ***
SELECT t3.id AS id3, t2.id AS id2, t1.jaarweek, SUM(t2.uren) AS uren
FROM tabel1 AS t1
LEFT JOIN tabel2 AS t2 ON YEARWEEK(t2.date_start) <= t1.jaarweek AND
t2.date_start IS NOT NULL
AND (YEARWEEK(t2.date_end) >= t1.jaarweek OR t2.date_end IS NULL)
INNER JOIN tabel3 AS t3 ON t3.tabel2_id = t2.id AND t3.beperk != 'true'
GROUP BY t1.jaarweek
ORDER BY t1.jaarweek
*** eoq ***
Also, if we substitute the line with the INNER JOIN for any of the following
lines, it will still produce correct results:
INNER JOIN tabel3 AS t3 ON t3.tabel2_id = t2.id AND t3.beperk != 'true'
INNER JOIN tabel3 AS t3 ON t3.tabel2_id = t2.id AND t3.beperk = 'true'
INNER JOIN tabel3 AS t3 ON t3.tabel2_id = t2.id AND t3.beperk != 'false'
INNER JOIN tabel3 AS t3 ON t3.tabel2_id = t2.id AND t3.beperk = 'false'
HOWEVER, if you substitute it for this line:
INNER JOIN tabel3 AS t3 ON t3.tabel2_id = t2.id AND t3.beperk = 'neither'
MySQL starts to act weird.
I'm doing a LEFT JOIN with table2 on table1, which should not affect the
amount of rows of table 1. Even if there's nothing to join on, i should
still get 15 rows. I'm doing an INNER JOIN with table3 on table2, which
should restrict the data being joined from table2 on table1, but _not_ the
data pulled from table1. At least, it doesn't seem logical to me. However,
that's just what it does. Taking that last one as the INNER JOIN, the query
won't give back any rows. (and i expected 15)
p.s. i know t3.beperk can never be 'neither', but that is exactly my point: Even tho this is never the case - i still want the data from T1 returned
Description: Suppose there's 3 tables, T1, T2, T3 You're SELECTing all the data from T1, you're joining T2 with a LEFT join on T1 (which does not restrict the data from T1, cause it's a left join) and you're joining T3 on T2 with an INNER JOIN (which should not restrict the data being selected from T1, but Should restrict the data being selected from T2. This will only work if 'some' data from T3 is selected. In a case where you construct a WHERE clause for T3 that will exclude any information from T3, the amount of rows returned will be 0 - while it should return at least all the data from T1, (with NULL values for the fields from T2 and T3). p.s.: i don't have shell access so i couldn't use the mysqlbug program How to repeat: *** bod *** # # Table structure for table 'tabel1' # CREATE TABLE tabel1 ( id int(10) unsigned NOT NULL auto_increment, jaarweek mediumint(6) unsigned default NULL, PRIMARY KEY (id), UNIQUE KEY t1_id (id), KEY jw (jaarweek) ) TYPE=MyISAM; # # Dumping data for table 'tabel1' # INSERT INTO tabel1 VALUES("1", "200301"); INSERT INTO tabel1 VALUES("2", "200302"); INSERT INTO tabel1 VALUES("3", "200303"); INSERT INTO tabel1 VALUES("4", "200304"); INSERT INTO tabel1 VALUES("5", "200305"); INSERT INTO tabel1 VALUES("6", "200306"); INSERT INTO tabel1 VALUES("7", "200307"); INSERT INTO tabel1 VALUES("8", "200308"); INSERT INTO tabel1 VALUES("9", "200309"); INSERT INTO tabel1 VALUES("10", "200310"); INSERT INTO tabel1 VALUES("11", "200311"); INSERT INTO tabel1 VALUES("12", "200312"); INSERT INTO tabel1 VALUES("13", "200313"); INSERT INTO tabel1 VALUES("14", "200314"); INSERT INTO tabel1 VALUES("15", "200315"); # # Table structure for table 'tabel2' # CREATE TABLE tabel2 ( id int(10) unsigned NOT NULL auto_increment, date_start date default NULL, date_end date default NULL, uren tinyint(3) unsigned default NULL, PRIMARY KEY (id), UNIQUE KEY t2_id (id), KEY ds (date_start), KEY de (date_end) ) TYPE=MyISAM; # # Dumping data for table 'tabel2' # INSERT INTO tabel2 VALUES("1", "2002-12-09", "2003-01-05", "20"); INSERT INTO tabel2 VALUES("2", "2002-09-12", "2003-03-03", "30"); INSERT INTO tabel2 VALUES("3", "2001-01-01", NULL, "40"); INSERT INTO tabel2 VALUES("4", "2003-02-02", "2003-04-05", "10"); # # Table structure for table 'tabel3' # CREATE TABLE tabel3 ( id int(10) unsigned NOT NULL auto_increment, tabel2_id int(10) unsigned default NULL, beperk enum('true','false') default 'false', PRIMARY KEY (id), UNIQUE KEY id (id), KEY tabel2_id (tabel2_id) ) TYPE=MyISAM; # # Dumping data for table 'tabel3' # INSERT INTO tabel3 VALUES("1", "1", "false"); INSERT INTO tabel3 VALUES("2", "2", "false"); INSERT INTO tabel3 VALUES("3", "3", "true"); INSERT INTO tabel3 VALUES("4", "4", "false"); *** eod *** Now, the next query will produce normal results: *** boq *** SELECT t3.id AS id3, t2.id AS id2, t1.jaarweek, SUM(t2.uren) AS uren FROM tabel1 AS t1 LEFT JOIN tabel2 AS t2 ON YEARWEEK(t2.date_start) <= t1.jaarweek AND t2.date_start IS NOT NULL AND (YEARWEEK(t2.date_end) >= t1.jaarweek OR t2.date_end IS NULL) INNER JOIN tabel3 AS t3 ON t3.tabel2_id = t2.id AND t3.beperk != 'true' GROUP BY t1.jaarweek ORDER BY t1.jaarweek *** eoq *** Also, if we substitute the line with the INNER JOIN for any of the following lines, it will still produce correct results: INNER JOIN tabel3 AS t3 ON t3.tabel2_id = t2.id AND t3.beperk != 'true' INNER JOIN tabel3 AS t3 ON t3.tabel2_id = t2.id AND t3.beperk = 'true' INNER JOIN tabel3 AS t3 ON t3.tabel2_id = t2.id AND t3.beperk != 'false' INNER JOIN tabel3 AS t3 ON t3.tabel2_id = t2.id AND t3.beperk = 'false' HOWEVER, if you substitute it for this line: INNER JOIN tabel3 AS t3 ON t3.tabel2_id = t2.id AND t3.beperk = 'neither' MySQL starts to act weird. I'm doing a LEFT JOIN with table2 on table1, which should not affect the amount of rows of table 1. Even if there's nothing to join on, i should still get 15 rows. I'm doing an INNER JOIN with table3 on table2, which should restrict the data being joined from table2 on table1, but _not_ the data pulled from table1. At least, it doesn't seem logical to me. However, that's just what it does. Taking that last one as the INNER JOIN, the query won't give back any rows. (and i expected 15) p.s. i know t3.beperk can never be 'neither', but that is exactly my point: Even tho this is never the case - i still want the data from T1 returned