List:General Discussion« Previous MessageNext Message »
From:gerald_clark Date:April 15 2003 8:26pm
Subject:Re: bug or normal behaviour?
View as plain text  
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
>
>


Thread
bug or normal behaviour?Martijn Korse15 Apr
  • Re: bug or normal behaviour?gerald_clark15 Apr
Re: bug or normal behaviour?marty @ excudo15 Apr
  • Re: bug or normal behaviour?gerald_clark16 Apr
Re: bug or normal behaviour?marty @ excudo16 Apr