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

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

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 Is 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

Thread
bug or normal behaviourmarty @ excudo15 Apr