Because t3.bperk is never 'neither'.
Martijn Korse wrote:
> I think i've found a bug. Or at least, it's strange behaviour. Here's
> a dump with some data to recreate it:
>
> *** 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 just what it does. Taking that last
> one as the INNER JOIN, the query won't give back any rows. (and i
> expected 15)
>
> Can anyone tell me if this is indeed a bug? Ot otherwise explain to me
> why this is normal behaviour
>
> MaRTy
>
> _________________________________________________________________
> The new MSN 8: smart spam protection and 2 months FREE*
> http://join.msn.com/?page=features/junkmail
>
>
