| Bug #6482 | Simple query returns wrong result | ||
|---|---|---|---|
| Submitted: | 7 Nov 2004 8:23 | Modified: | 8 Nov 2004 9:18 |
| Reporter: | Nathan Cheng | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S1 (Critical) |
| Version: | 4.1.7-standard-log | OS: | pc-linux |
| Assigned to: | CPU Architecture: | Any | |
[7 Nov 2004 8:28]
Nathan Cheng
Note that this bug does NOT exist in MySQL 4.0.14-nt; i.e. the expected result +-------------+-------------+ | campaign_id | campaign_id | +-------------+-------------+ | 1108 | NULL | +-------------+-------------+ is returned just fine.
[7 Nov 2004 8:37]
Nathan Cheng
Note that the correct result is returned if you remove the first row from the table (campaign_id=1107) and leave only the one row.
[7 Nov 2004 8:42]
Nathan Cheng
Note that the following simpler query also returns the wrong result:
mysql> SELECT
-> a.campaign_id,
-> aa.campaign_id
-> FROM
-> meta_campaign a
-> LEFT JOIN
-> meta_campaign aa ON(
-> aa.campaign_id < a.campaign_id
-> AND
-> aa.start_date <= 1099814712
-> )
-> WHERE a.campaign_id = 1108
-> ;
+-------------+-------------+
| campaign_id | campaign_id |
+-------------+-------------+
| 1108 | 1107 |
| 1108 | 1108 |
+-------------+-------------+
2 rows in set (0.00 sec)
But this query returns the correct result:
mysql> SELECT
-> a.campaign_id,
-> aa.campaign_id
-> FROM
-> meta_campaign a
-> LEFT JOIN
-> meta_campaign aa ON(
-> aa.campaign_id < a.campaign_id
-> AND
-> aa.start_date >= a.start_date
-> )
-> WHERE a.campaign_id = 1108
-> ;
+-------------+-------------+
| campaign_id | campaign_id |
+-------------+-------------+
| 1108 | NULL |
+-------------+-------------+
1 row in set (0.00 sec)
[7 Nov 2004 8:46]
Nathan Cheng
And this is just plain fun:
mysql> SELECT
-> a.campaign_id,
-> aa.campaign_id,
-> aa.start_date <= 1099814712,
-> aa.campaign_id < a.campaign_id,
-> aa.start_date <= 1099814712 AND aa.campaign_id < a.campaign_id
-> FROM
-> meta_campaign a
-> LEFT JOIN
-> meta_campaign aa ON(
-> aa.start_date <= 1099814712
-> AND
-> aa.campaign_id < a.campaign_id
-> )
-> WHERE a.campaign_id = 1108
-> ;
+-------------+-------------+-----------------------------+--------------------------------+-----------------------------------------------------------------+
| campaign_id | campaign_id | aa.start_date <= 1099814712 | aa.campaign_id < a.campaign_id | aa.start_date <= 1099814712 AND aa.campaign_id < a.campaign_id |
+-------------+-------------+-----------------------------+--------------------------------+-----------------------------------------------------------------+
| 1108 | 1107 | 1 | 1 | 1 |
| 1108 | 1108 | 1 | 0 | 0 |
+-------------+-------------+-----------------------------+--------------------------------+-----------------------------------------------------------------+
2 rows in set (0.00 sec)
[8 Nov 2004 9:18]
Alexander Keremidarski
All queries return expected result with 4.1 build from
ChangeSet@1.2080, 2004-11-04 08:50:07+04:00, bar@mysql.com
SELECT
a.campaign_id,
aa.campaign_id
FROM
meta_campaign a
LEFT JOIN
meta_campaign aa ON(
aa.campaign_id < a.campaign_id
AND
aa.start_date <= 1099814712
AND
aa.start_date >= a.start_date
)
WHERE a.campaign_id = 1108;
+-------------+-------------+
| campaign_id | campaign_id |
+-------------+-------------+
| 1108 | 1107 |
+-------------+-------------+
SELECT
a.campaign_id,
aa.campaign_id
FROM
meta_campaign a
LEFT JOIN
meta_campaign aa ON(
aa.campaign_id < a.campaign_id
AND
aa.start_date <= 1099814712
)
WHERE a.campaign_id = 1108
;
+-------------+-------------+
| campaign_id | campaign_id |
+-------------+-------------+
| 1108 | 1107 |
+-------------+-------------+
SELECT
a.campaign_id,
aa.campaign_id
FROM
meta_campaign a
LEFT JOIN
meta_campaign aa ON(
aa.campaign_id < a.campaign_id
AND
aa.start_date >= a.start_date
)
WHERE a.campaign_id = 1108
;
+-------------+-------------+
| campaign_id | campaign_id |
+-------------+-------------+
| 1108 | NULL |
+-------------+-------------+
SELECT
a.campaign_id,
aa.campaign_id,
aa.start_date <= 1099814712,
aa.campaign_id < a.campaign_id,
aa.start_date <= 1099814712 AND aa.campaign_id < a.campaign_id
FROM
meta_campaign a
LEFT JOIN
meta_campaign aa ON(
aa.start_date <= 1099814712
AND
aa.campaign_id < a.campaign_id
)
WHERE a.campaign_id = 1108
;
+-------------+-------------+-----------------------------+--------------------------------+-----------------------------------------------------------------+
| campaign_id | campaign_id | aa.start_date <= 1099814712 | aa.campaign_id < a.campaign_id | aa.start_date <= 1099814712 AND aa.campaign_id < a.campaign_id |
+-------------+-------------+-----------------------------+--------------------------------+-----------------------------------------------------------------+
| 1108 | 1107 | 1 | 1 | 1 |
+-------------+-------------+-----------------------------+--------------------------------+-----------------------------------------------------------------+
Description: A trivial SELECT statement returns an obviously incorrect result, which is breaking our application. The necessary component "aa.campaign_id < a.campaign_id" of the ON condition in the LEFT JOIN of the following statement is being ignored: SELECT a.campaign_id, aa.campaign_id FROM meta_campaign a LEFT JOIN meta_campaign aa ON( aa.campaign_id < a.campaign_id AND aa.start_date <= 1099814712 AND aa.start_date >= a.start_date ) WHERE a.campaign_id = 1108; Running this returns the result; +-------------+-------------+ | campaign_id | campaign_id | +-------------+-------------+ | 1108 | 1108 | +-------------+-------------+ 1 row in set (0.00 sec) This obviously could NEVER be correct, since aa.campaign_id < a.campaign_id is a necessary condition! How to repeat: CREATE TABLE `meta_campaign` ( `campaign_id` int(11) NOT NULL auto_increment, `start_date` int(11) default NULL, PRIMARY KEY (`campaign_id`), KEY `idx_3` (`start_date`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `meta_campaign` VALUES (1107,0),(1108,1099800804); SELECT a.campaign_id, aa.campaign_id FROM meta_campaign a LEFT JOIN meta_campaign aa ON( aa.campaign_id < a.campaign_id AND aa.start_date <= 1099814712 AND aa.start_date >= a.start_date ) WHERE a.campaign_id = 1108; Suggested fix: Removing any of the three supposedly necessary sub-conditions of the ON condition returns the correct result. For some reason, MySQL is ignoring the condition involving the primary key when the two other conditions hold true. So I would look for the problem somewhere in code that is trying to optimize something or other based on the presence of a conditional expression involving the primary key of a single table on both sides of the expression.