Bug #30371 Warning in LeftJoin-Condition leaves condition unevaluated
Submitted: 11 Aug 2007 11:49 Modified: 9 Dec 2007 12:22
Reporter: Georg Kapeller Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0.45 OS:Windows (sp2)
Assigned to: CPU Architecture:Any
Tags: condition, left join, not evaluated, user-defined-function, warning

[11 Aug 2007 11:49] Georg Kapeller 
Description:
Apparently this changed for 5.0.45, in recent version it worked without trouble.
Condition in LeftJoin doesn't get evaluated, if it causes a warning.

SELECT 
zi_id
SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) AS ov
 
FROM zimmer
LEFT JOIN zuweisungen ON zi_id = zu_ref_zi_id 
	AND SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) = '1'

Result:
+-------+-------+-------+-------+------+
| ob_id | wo_id | zi_id | zu_id | ov   |
+-------+-------+-------+-------+------+
|     2 |   282 |  1316 |   168 | 5    |
|     2 |   282 |  1316 |  2414 | 1    |
|     2 |   282 |  1316 |  4698 | 1    |
|     2 |   282 |  1316 |  5359 | 1    |
|     2 |   282 |  1317 |   172 | 5    |
|     2 |   282 |  1317 |   175 | 5    |
|     2 |   282 |  1317 |   179 | 2    |
|     2 |   282 |  1317 |  2588 | 1    |
|     2 |   282 |  1317 |  5360 | 1    |
|     2 |   282 |  1317 |  6185 | 1    |
|     2 |   282 |  1318 |   186 | 5    |
|     2 |   282 |  1318 |   190 | 5    |
|     2 |   282 |  1318 |  1493 | 5    |
|     2 |   282 |  1318 |  2557 | 5    |
|     2 |   282 |  1318 |  2692 | 2    |
|     2 |   282 |  1318 |  3016 | 1    |
|     2 |   282 |  1318 |  5361 | 1    |
|     2 |   282 |  1318 |  6184 | 1    |
+-------+-------+-------+-------+------+
18 rows in set, 36 warnings (0.00 sec)

OVERLAP is a user-defined function, causing the warning.

Expected Result:
+-------+-------+-------+-------+------+
| ob_id | wo_id | zi_id | zu_id | ov   |
+-------+-------+-------+-------+------+
|     2 |   282 |  1316 |  2414 | 1    |
|     2 |   282 |  1316 |  4698 | 1    |
|     2 |   282 |  1316 |  5359 | 1    |
|     2 |   282 |  1317 |  2588 | 1    |
|     2 |   282 |  1317 |  5360 | 1    |
|     2 |   282 |  1317 |  6185 | 1    |
|     2 |   282 |  1318 |  3016 | 1    |
|     2 |   282 |  1318 |  5361 | 1    |
|     2 |   282 |  1318 |  6184 | 1    |
+-------+-------+-------+-------+------+

If the condition shows up in the WHERE-clause, everything workes alright.

How to repeat:
create a user defined-function that causes an error and try selecting something invoking it with a leftJoin in the ON-clause.
[11 Aug 2007 11:51] Georg Kapeller 
actually the query was:

SELECT 
ob_id, wo_id, zi_id, zu_id,
SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) AS ov
 
FROM [some tables], zimmer
LEFT JOIN zuweisungen ON zi_id = zu_ref_zi_id 
	AND SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) = '1'

WHERE
[some conditions to join the tables]
[11 Aug 2007 11:56] Georg Kapeller 
mysql> show warnings;
+---------+------+------------------------------------+
| Level   | Code | Message                            |
+---------+------+------------------------------------+
| Warning | 1292 | Truncated incorrect date value: '' |
| Warning | 1292 | Truncated incorrect date value: '' |

[...]

| Warning | 1292 | Truncated incorrect date value: '' |
+---------+------+------------------------------------+
36 rows in set (0.00 sec)
[13 Aug 2007 8:00] Sveta Smirnova 
Thank you for the report.

Please provide output of SHOW CREATE TABLE zimmer, SHOW CREATE TABLE zuweisungen and SHOW VARIABLES LIKE 'char%'
[28 Aug 2007 23:32] Georg Kapeller 
sorry for the delay, i've been on vacations.

CREATE TABLE `zimmer` (
  `zi_id` int(11) NOT NULL auto_increment,
  `zi_zimmer_code` varchar(20) default NULL,
  `zi_name` varchar(200) default NULL,
  `zi_ref_wo_id` int(11) NOT NULL default '0',
  `zi_cod_geschlecht` tinyint(4) NOT NULL default '3',
  `zi_cod_raucher` tinyint(4) NOT NULL default '3',
  `zi_cod_typ_zuweisung` tinyint(4) NOT NULL default '0',
  `zi_cod_kategorie_wr` int(11) NOT NULL default '0',
  `zi_f_inkasso_01` tinyint(4) NOT NULL default '1',
  `zi_f_inkasso_02` tinyint(4) NOT NULL default '1',
  `zi_f_inkasso_03` tinyint(4) NOT NULL default '1',
  `zi_f_inkasso_04` tinyint(4) NOT NULL default '1',
  `zi_f_inkasso_05` tinyint(4) NOT NULL default '1',
  `zi_f_inkasso_06` tinyint(4) NOT NULL default '1',
  `zi_f_inkasso_07` tinyint(4) NOT NULL default '1',
  `zi_f_inkasso_08` tinyint(4) NOT NULL default '1',
  `zi_f_inkasso_09` tinyint(4) NOT NULL default '1',
  `zi_f_inkasso_10` tinyint(4) NOT NULL default '1',
  `zi_f_inkasso_11` tinyint(4) NOT NULL default '1',
  `zi_f_inkasso_12` tinyint(4) NOT NULL default '1',
  `zi_f_buch_01` tinyint(4) NOT NULL default '1',
  `zi_f_buch_02` tinyint(4) NOT NULL default '1',
  `zi_f_buch_03` tinyint(4) NOT NULL default '1',
  `zi_f_buch_04` tinyint(4) NOT NULL default '1',
  `zi_f_buch_05` tinyint(4) NOT NULL default '1',
  `zi_f_buch_06` tinyint(4) NOT NULL default '1',
  `zi_f_buch_07` tinyint(4) NOT NULL default '1',
  `zi_f_buch_08` tinyint(4) NOT NULL default '1',
  `zi_f_buch_09` tinyint(4) NOT NULL default '1',
  `zi_f_buch_10` tinyint(4) NOT NULL default '1',
  `zi_f_buch_11` tinyint(4) NOT NULL default '1',
  `zi_f_buch_12` tinyint(4) NOT NULL default '1',
  `zi_kategoriepreisabweichung` decimal(5,2) NOT NULL defaul
  `zi_ausstattung_deu` text NOT NULL,
  `zi_ausstattung_eng` text NOT NULL,
  `zi_komm` text,
  `zi_betten_zuweisbar` tinyint(4) NOT NULL default '1',
  `zi_f_exklusiv` tinyint(4) NOT NULL default '1',
  `zi_grosse` int(11) NOT NULL default '0',
  `zi_stiege` varchar(10) NOT NULL default '',
  `zi_f_zusatzbett` tinyint(4) NOT NULL default '0',
  `zi_dat_in` date default NULL,
  `zi_dat_update` date default NULL,
  `zi_f_inaktiv` tinyint(4) NOT NULL default '0',
  `zi_who_update` tinyint(4) default NULL,
  `zi_f_valid` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`zi_id`),
  KEY `zi_ref_objekt` (`zi_ref_wo_id`),
  KEY `zi_cod_geschlecht` (`zi_cod_geschlecht`),
  KEY `zi_cod_raucher` (`zi_cod_raucher`),
  KEY `zi_cod_typ_zuweisung` (`zi_cod_typ_zuweisung`),
  KEY `zi_cod_kategorie` (`zi_cod_kategorie_wr`)
) ENGINE=MyISAM AUTO_INCREMENT=1719 DEFAULT CHARSET=latin1

CREATE TABLE `zuweisungen` (
  `zu_id` int(11) NOT NULL auto_increment,
  `zu_cod_typ_zuweisung` tinyint(4) NOT NULL default '0',
  `zu_cod_status_zuweisung` tinyint(4) NOT NULL default '0',
  `zu_ref_an_id` int(11) NOT NULL default '0',
  `zu_ref_as_id` int(11) NOT NULL default '0',
  `zu_ref_ob_id` int(11) NOT NULL default '0',
  `zu_ref_wo_id` int(11) NOT NULL default '0',
  `zu_ref_zi_id` int(11) NOT NULL default '0',
  `zu_ref_be_id` int(11) NOT NULL default '0',
  `zu_proz_abweichung_nominalbetrag` decimal(7,4) NOT NULL default '100.0000',
  `zu_absolutpreis` decimal(10,2) NOT NULL default '0.00',
  `zu_betrag_vwk` decimal(10,2) NOT NULL default '0.00',
  `zu_reinigung` decimal(10,2) NOT NULL default '0.00',
  `zu_dat_sign` date default NULL,
  `zu_dat_start` date default NULL,
  `zu_dat_end` date default NULL,
  `zu_dat_end_vormals` date default NULL,
  `zu_dat_start_vormals` date NOT NULL default '0000-00-00',
  `zu_dat_storniert_mit` date default NULL,
  `zu_storno_von_zu_id` int(11) NOT NULL default '0',
  `zu_dat_reservierungsbrief_geschickt` date default NULL,
  `zu_dat_visumsbestaetigung_geschickt` date default NULL,
  `zu_dat_mietvertrag_gedruckt` date NOT NULL default '0000-00-00',
  `zu_dat_reserviert_bis` date NOT NULL default '0000-00-00',
  `zu_dat_verlaengert_mit` date NOT NULL default '0000-00-00',
  `zu_cod_department` tinyint(4) NOT NULL default '0',
  `zu_cod_geschlecht` tinyint(4) NOT NULL,
  `zu_cod_raucher` tinyint(4) NOT NULL,
  `zu_dat_aend` date default NULL,
  `zu_komm` char(255) default NULL,
  `zu_komm_heim` char(255) default NULL,
  `zu_alternativer_name_bestaetigung` char(50) NOT NULL,
  `zu_status_inventar` enum('','ok','beschõdigt') NOT NULL default '',
  `zu_schaden_details` char(255) NOT NULL default '',
  `zu_f_ruhend` tinyint(4) NOT NULL default '0',
  `zu_dat_in` date default NULL,
  `zu_who_in` tinyint(4) NOT NULL default '0',
  `zu_dat_update` date default NULL,
  `zu_who_update` tinyint(4) default NULL,
  `zu_f_korrespondiert` tinyint(4) NOT NULL,
  `zu_f_reinigungspauschale_verrechnen` tinyint(4) NOT NULL default '1',
  `zu_f_stornoforderungen_erzeugt` tinyint(4) NOT NULL default '0',
  `zu_f_vwks_im_voraus_gefordert` tinyint(4) NOT NULL default '0',
  `zu_f_vertrag_unterschrieben` tinyint(4) NOT NULL default '0',
  `zu_f_schluessel_ausgegeben` tinyint(4) NOT NULL default '0',
  `zu_f_valid` tinyint(4) NOT NULL default '1',
  `zu_f_neu` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`zu_id`),
  KEY `zu_ref_antrag` (`zu_ref_an_id`),
  KEY `zu_ref_antragsteller` (`zu_ref_as_id`),
  KEY `zu_ref_ob_id` (`zu_ref_ob_id`),
  KEY `zu_ref_zi_id` (`zu_ref_zi_id`),
  KEY `zu_ref_be_id` (`zu_ref_be_id`),
  KEY `zu_ref_wo_id` (`zu_ref_wo_id`),
  KEY `zu_id` (`zu_id`,`zu_cod_department`,`zu_f_valid`),
) ENGINE=MyISAM AUTO_INCREMENT=6237 DEFAULT CHARSET=latin1

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+-----------------------------------+
| Variable_name            | Value                             |
+--------------------------+-----------------------------------+
| character_set_client     | latin1                            |
| character_set_connection | latin1                            |
| character_set_database   | latin1                            |
| character_set_filesystem | binary                            |
| character_set_results    | latin1                            |
| character_set_server     | latin1                            |
| character_set_system     | utf8                              |
| character_sets_dir       | C:\Inetpub\mysql5\share\charsets\ |
+--------------------------+-----------------------------------+
[29 Aug 2007 7:07] Sveta Smirnova 
thank you for the feedback.

Please also provide definition of function OVERLAP.
[29 Aug 2007 8:35] Georg Kapeller 
DELIMITER //
CREATE FUNCTION overlap (base_begin DATE, base_end DATE, duration_begin DATE, duration_end DATE)
	RETURNS VARCHAR(50)
	DETERMINISTIC
BEGIN
	DECLARE strReturn VARCHAR(50) DEFAULT 'error';
	SET strReturn = '';

	IF base_begin = '' OR base_begin = "0000-00-00" OR base_begin is null THEN SET base_begin = "1111-11-11"; END IF;
	IF base_end = '' OR base_end = "0000-00-00" OR base_end is null THEN SET base_end = "2111-11-11"; END IF;
	
	
	IF (base_begin > base_end) THEN
		SELECT CONCAT('7(full):', DATEDIFF(base_end, duration_end), ':', DATEDIFF(duration_begin, base_begin)) INTO strReturn;
		RETURN strReturn;
	END IF;
	
	/* G A P */	
	IF (duration_begin > base_begin) AND (duration_end < base_end) THEN
		SELECT CONCAT('1(gap):', DATEDIFF(duration_begin,base_begin), ':', DATEDIFF(base_end,duration_end)) INTO strReturn;
		RETURN strReturn;
	END IF;
	
	/* F U L L */
	IF (duration_begin < base_begin) AND (duration_end < base_begin) THEN
		SELECT CONCAT('5(full_left):', DATEDIFF(duration_begin,base_begin), ':', DATEDIFF(base_end,duration_end)) INTO strReturn;
		RETURN strReturn;
	END IF;
	IF (duration_begin > base_end) AND (duration_end > base_end) THEN
		SELECT CONCAT('6(full_right):', DATEDIFF(duration_begin,base_begin), ':', DATEDIFF(base_end,duration_end)) INTO strReturn;
		RETURN strReturn;
	END IF;

	/* O V E R L A P */
	IF (duration_begin <= base_begin) AND (duration_end >= base_begin) THEN
		SELECT CONCAT('2(overlap_left):', DATEDIFF(duration_begin,base_begin), ':', DATEDIFF(base_end,duration_end)) INTO strReturn;
	END IF;
	IF (duration_begin > base_begin) AND (duration_end >= base_end) THEN
		SELECT CONCAT('3(overlap_right):', DATEDIFF(duration_begin,base_begin), ':', DATEDIFF(base_end,duration_end)) INTO strReturn;
	END IF;
	IF (duration_begin <= base_begin) AND (duration_end >= base_end) THEN
		SELECT CONCAT('4(overlap_leftright):', DATEDIFF(duration_begin,base_begin), ':', DATEDIFF(base_end,duration_end)) INTO strReturn;
	END IF;
	RETURN strReturn;
END //
DELIMITER ;
[9 Dec 2007 9:31] Sveta Smirnova 
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Try with 1=2 instead of OVERLAP call and you get same results.
[9 Dec 2007 9:33] Sveta Smirnova 
See also at http://dev.mysql.com/doc/refman/5.0/en/join.html:

If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table.
[9 Dec 2007 10:34] Georg Kapeller 
1) As mentioned, Behavior changed when version changed. As you are referring to the simple LEFT-JOIN documentation, i suppose, this part of the docu didn't change.

2) 
mysql> SELECT
    -> zi_id,
    -> SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) AS ov
    ->
    -> FROM zimmer
    -> LEFT JOIN zuweisungen ON zi_id = zu_ref_zi_id
    ->  AND 1=2
    -> WHERE zi_ref_wo_id = 282;
+-------+------+
| zi_id | ov   |
+-------+------+
|  1316 |      |
|  1317 |      |
|  1318 |      |
+-------+------+
3 rows in set, 6 warnings (0.00 sec)

This is actually not the same result!
Left join works as expected here. The 3 Rooms on the left, but as the LEFT-JOIN condition always evaluates to untrue, nothing is shown on the right.

3)
Querying only the table which above is the right side of the left join WITHOUT overlap condition

mysql> SELECT
    -> zu_id AS primary_key, zu_ref_zi_id AS zi_id, zu_ref_wo_id AS wo_id,
    -> SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) AS ov
    ->
    -> FROM zuweisungen
    -> WHERE zu_ref_wo_id = 282;
+-------------+-------+-------+------+
| primary_key | zi_id | wo_id | ov   |
+-------------+-------+-------+------+
|         168 |  1316 |   282 | 5    |
|         172 |  1317 |   282 | 5    |
|         175 |  1317 |   282 | 5    |
|         179 |  1317 |   282 | 2    |
|         186 |  1318 |   282 | 5    |
|         190 |  1318 |   282 | 5    |
|        1493 |  1318 |   282 | 5    |
|        2414 |  1316 |   282 | 1    |
|        2557 |  1318 |   282 | 5    |
|        2588 |  1317 |   282 | 1    |
|        2692 |  1318 |   282 | 2    |
|        3016 |  1318 |   282 | 1    |
|        4698 |  1316 |   282 | 1    |
|        5359 |  1316 |   282 | 1    |
|        5360 |  1317 |   282 | 1    |
|        5361 |  1318 |   282 | 1    |
|        6184 |  1318 |   282 | 1    |
|        6185 |  1317 |   282 | 1    |
|        6235 |  1316 |   282 | 1    |
+-------------+-------+-------+------+
19 rows in set, 38 warnings (0.00 sec)

You see the all the 3 Rooms (1316, 1317, 1318) are available here.

4)
Querying only the table which above is the right side of the left join WITH overlap condition

mysql> SELECT
    -> zu_id AS primary_key, zu_ref_zi_id AS zi_id, zu_ref_wo_id AS wo_id,
    -> SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) AS ov
    ->
    -> FROM zuweisungen
    -> WHERE zu_ref_wo_id = 282
    -> AND SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) = '1';
+-------------+-------+-------+------+
| primary_key | zi_id | wo_id | ov   |
+-------------+-------+-------+------+
|        2414 |  1316 |   282 | 1    |
|        2588 |  1317 |   282 | 1    |
|        3016 |  1318 |   282 | 1    |
|        4698 |  1316 |   282 | 1    |
|        5359 |  1316 |   282 | 1    |
|        5360 |  1317 |   282 | 1    |
|        5361 |  1318 |   282 | 1    |
|        6184 |  1318 |   282 | 1    |
|        6185 |  1317 |   282 | 1    |
|        6235 |  1316 |   282 | 1    |
+-------------+-------+-------+------+
10 rows in set, 58 warnings (0.02 sec)

You see the here also all the 3 Rooms (1316, 1317, 1318) are available. Overlap-Condition gets evaluated,

5)
Now rewriting the original-query by replacing the left-join by the result, that should result of the left join:

mysql> SELECT
    -> zi_id,
    -> ov
    ->
    -> FROM zimmer
    -> LEFT JOIN (
    ->
    -> SELECT
    -> zu_ref_zi_id,
    -> SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) AS ov
    ->
    -> FROM zuweisungen
    -> WHERE zu_ref_wo_id = 282
    -> AND SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) = '1'
    ->
    -> ) AS zu_derived ON zi_id = zu_ref_zi_id
    -> WHERE zi_ref_wo_id = 282;
+-------+------+
| zi_id | ov   |
+-------+------+
|  1316 | 1    |
|  1316 | 1    |
|  1316 | 1    |
|  1316 | 1    |
|  1317 | 1    |
|  1317 | 1    |
|  1317 | 1    |
|  1318 | 1    |
|  1318 | 1    |
|  1318 | 1    |
+-------+------+
10 rows in set, 58 warnings (0.00 sec)

This is the expected result!

6)
mysql> SELECT
    -> zi_id,
    -> SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) AS ov
    ->
    -> FROM zimmer
    -> LEFT JOIN zuweisungen ON zi_id = zu_ref_zi_id
    ->  AND SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) = '1'
    -> WHERE zi_ref_wo_id = 282;
+-------+------+
| zi_id | ov   |
+-------+------+
|  1316 | 5    |
|  1316 | 1    |
|  1316 | 1    |
|  1316 | 1    |
|  1316 | 1    |
|  1317 | 5    |
|  1317 | 5    |
|  1317 | 2    |
|  1317 | 1    |
|  1317 | 1    |
|  1317 | 1    |
|  1318 | 5    |
|  1318 | 5    |
|  1318 | 5    |
|  1318 | 5    |
|  1318 | 2    |
|  1318 | 1    |
|  1318 | 1    |
|  1318 | 1    |
+-------+------+
19 rows in set, 38 warnings (0.01 sec)

This just can't be true.
On the left there are the 3 Rooms (1316,1317,1318). All extra lines are generated by the LEFT-JOIN.
In my LEFT-JOIN Condition i specify, that only rows, where
SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) equals to '1' should be considered.
But as you see (SUBSTRING(OVERLAP(...)) AS ov) this is NOT what's happening. Lines where SUBSTRING(OVERLAP(...)) equals to '5' are also considered, which is definitely wrong.
Bug reopend.
[9 Dec 2007 10:49] Sveta Smirnova 
Thank you for the feedback.

Please provide output of SELECT zi_id, zu_dat_start, zu_dat_end, SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) AS ov FROM zimmer LEFT JOIN zuweisungen ON zi_id = zu_ref_zi_id  AND SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) = '1'
[9 Dec 2007 10:58] Georg Kapeller 
I suppose you want only the relevant rows, so i added the WHERE-clause (as in my examples in the last comment).

mysql> SELECT zi_id, zu_dat_start, zu_dat_end,
    -> SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) AS ov
    -> FROM
    -> zimmer LEFT JOIN zuweisungen ON zi_id = zu_ref_zi_id AND
    -> SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) = '1'
    -> WHERE zi_ref_wo_id = 282;
+-------+--------------+------------+------+
| zi_id | zu_dat_start | zu_dat_end | ov   |
+-------+--------------+------------+------+
|  1316 | 2006-09-25   | 2006-12-29 | 5    |
|  1316 | 2007-01-02   | 2007-05-31 | 1    |
|  1316 | 2007-10-01   | 2007-11-29 | 1    |
|  1316 | 2007-07-09   | 2007-09-02 | 1    |
|  1316 | 2007-09-16   | 2007-10-01 | 1    |
|  1317 | 2006-09-01   | 2006-09-29 | 5    |
|  1317 | 2006-10-02   | 2006-10-31 | 5    |
|  1317 | 2006-11-02   | 2007-01-31 | 2    |
|  1317 | 2007-01-31   | 2007-06-29 | 1    |
|  1317 | 2007-07-09   | 2007-09-02 | 1    |
|  1317 | 2007-09-03   | 2008-08-29 | 1    |
|  1318 | 2006-11-02   | 2006-11-30 | 5    |
|  1318 | 2006-10-02   | 2006-10-31 | 5    |
|  1318 | 2006-09-15   | 2006-09-29 | 5    |
|  1318 | 2006-12-01   | 2006-12-29 | 5    |
|  1318 | 2006-12-29   | 2007-02-28 | 2    |
|  1318 | 2007-03-01   | 2007-06-29 | 1    |
|  1318 | 2007-07-09   | 2007-09-02 | 1    |
|  1318 | 2007-09-03   | 2008-08-31 | 1    |
+-------+--------------+------------+------+
19 rows in set, 38 warnings (0.00 sec)
[9 Dec 2007 12:12] Sveta Smirnova 
Thank you for the feedback.

You are right: it was a bug.

I could repeat this with version 5.0.45:

SELECT zi_id, zu_dat_start, zu_dat_end,
SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) AS ov FROM zimmer
LEFT JOIN zuweisungen ON zi_id = zu_ref_zi_id ;
zi_id   zu_dat_start    zu_dat_end      ov
1316    2006-09-25      2006-12-29      5
1316    2007-01-02      2007-05-31      1
1316    2007-10-01      2007-11-29      1
1316    2007-07-09      2007-09-02      1
1316    2007-09-16      2007-10-01      1

But can not with version 5.0.50:

SELECT zi_id, zu_dat_start, zu_dat_end,
SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) AS ov FROM zimmer
LEFT JOIN zuweisungen ON zi_id = zu_ref_zi_id
AND SUBSTRING(OVERLAP('2007-01-01','2008-12-31', zu_dat_start, zu_dat_end),1,1) = '1'
        WHERE zi_ref_wo_id = 282
;
zi_id   zu_dat_start    zu_dat_end      ov
1316    2007-01-02      2007-05-31      1
1316    2007-10-01      2007-11-29      1
1316    2007-07-09      2007-09-02      1
1316    2007-09-16      2007-10-01      1

Please upgrade.
[9 Dec 2007 12:22] Georg Kapeller 
perfect, another 'somehow it got fixed' ... :)

thanks for the support!