Bug #38199 Wrong result: HAVING not observed when date key is compared to integer
Submitted: 17 Jul 2008 14:41 Modified: 7 Sep 2008 22:10
Reporter: Philip Stoev Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:6.0 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: regression, regression

[17 Jul 2008 14:41] Philip Stoev
Description:
This query, when executed with condition pushdown:

SELECT OUTR . `pk` AS X
FROM B AS OUTR
WHERE
 OUTR . `date_key` <> 100
HAVING pk <= 0
ORDER BY varchar_key;

returns results even though the HAVING clause is an impossible condition. Please note that the date_key is being compared to an integer.

May be related to #38011 (condition on a date key matches all rows) and/or bug #38072 (having not observed with an aggregate.

5.1 does not appear affected.

How to repeat:
--source include/have_innodb.inc
SET engine_condition_pushdown=1;

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `int_nokey` int(11) NOT NULL,
  `int_key` int(11) NOT NULL,
  `date_key` date NOT NULL,
  `date_nokey` date NOT NULL,
  `time_key` time NOT NULL,
  `time_nokey` time NOT NULL,
  `datetime_key` datetime NOT NULL,
  `datetime_nokey` datetime NOT NULL,
  `varchar_key` varchar(1) NOT NULL,
  `varchar_nokey` varchar(1) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `int_key` (`int_key`),
  KEY `date_key` (`date_key`),
  KEY `time_key` (`time_key`),
  KEY `datetime_key` (`datetime_key`),
  KEY `varchar_key` (`varchar_key`)
) ENGINE=InnoDB;

INSERT INTO t1 VALUES (1,8,5,'0000-00-00','0000-00-00','10:37:38','10:37:38','0000-00-00 00:00:00','0000-00-00 00:00:00','p','p'),
(2,0,9,'0000-00-00','0000-00-00','00:00:00','00:00:00','2007-10-14 00:00:00','2007-10-14 00:00:00','d','d');

SELECT pk
FROM t1
WHERE date_key <> 1
HAVING pk <= 0
ORDER BY varchar_key;
[17 Jul 2008 15:08] MySQL Verification Team
Thank you for the bug report.

Repeatable only with 6.0.6 source tree server.
Not repeatable with 5.1 and 6.0.4 released version.

mysql 6.0 >
mysql 6.0 > SELECT pk
    -> FROM t1
    -> WHERE date_key <> 1
    -> HAVING pk <= 0
    -> ORDER BY varchar_key;
+----+
| pk |
+----+
|  2 |
|  1 |
+----+
2 rows in set, 3 warnings (0.11 sec)

mysql 6.0 > alter table t1 engine MyISAM;
Query OK, 2 rows affected (0.31 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 6.0 > SELECT pk
    -> FROM t1
    -> WHERE date_key <> 1
    -> HAVING pk <= 0
    -> ORDER BY varchar_key;
Empty set, 3 warnings (0.03 sec)

mysql 6.0 > show variables like "%version%";
+-------------------------+--------------------------+
| Variable_name           | Value                    |
+-------------------------+--------------------------+
| protocol_version        | 10                       |
| version                 | 6.0.6-alpha-nt-debug-log |
| version_comment         | Source distribution      |
| version_compile_machine | ia32                     |
| version_compile_os      | Win32                    |
+-------------------------+--------------------------+
5 rows in set (0.05 sec)

mysql 6.0 >

mysql>
mysql> SELECT pk
    -> FROM t1
    -> WHERE date_key <> 1
    -> HAVING pk <= 0
    -> ORDER BY varchar_key;
Empty set, 3 warnings (0.06 sec)

mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| protocol_version        | 10                           |
| version                 | 6.0.4-alpha-community        |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | ia32                         |
| version_compile_os      | Win32                        |
+-------------------------+------------------------------+
5 rows in set (0.08 sec)

mysql>
[7 Sep 2008 22:10] Sergey Petrunya
Cannot repeat on 6.0 with fixes for BUG#38072 and BUG#37977. Changing status to Duplicate.