Bug #12883 min/max inconsistent behaviour in amount of retuned rows

• Submitted: 30 Aug 2005 15:18
• Modified: 14 Sep 2005 13:44
• Reporter: Martin Friebe (Gold Quality Contributor) (OCA)
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 4.1.14
• OS: FreeBSD (freebsd, Windows)
• Assigned to: Assigned Account
• CPU Architecture: Any

[30 Aug 2005 15:18] Martin Friebe

Description:
the queries below, should return the same result.

they both query the min value of a constant from the same table, with a condition that is always false.

(for this bug, it does not matter if the table is empty or not)

How to repeat:
create table t1 (a int);
#Query OK, 0 rows affected (0.01 sec)

select 1, min(1) from t1 where a=99 ;
Empty set (0.00 sec)

select 1, min(1) from t1 where 1=99 ;
+---+--------+
| 1 | min(1) |
+---+--------+
| 1 |      1 |
+---+--------+
1 row in set (0.00 sec)

Suggested fix:
- decide which behaviour is wated

[31 Aug 2005 7:55] Valeriy Kravchuk

mysql> use test;
Database changed
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.12 sec)

mysql> select * from t1;
Empty set (0.02 sec)

mysql> select 1, min(1) from t1 where a=99 ;
Empty set (0.03 sec)

mysql> select 1, min(1) from t1 where 1=99 ;
+---+--------+
| 1 | min(1) |
+---+--------+
| 1 |      1 |
+---+--------+
1 row in set (0.00 sec)

mysql> explain select 1, min(1) from t1 where a=99 ;
+----+-------------+-------+------+---------------+------+---------+------+-----
-+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL |    NULL | NULL | NULL
 | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+------------------------------+
1 row in set (0.00 sec)

mysql> explain select 1, min(1) from t1 where 1=99 ;
+----+-------------+-------+------+---------------+------+---------+------+-----
-+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL |    NULL | NULL | NULL
 | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.14-nt |
+-----------+
1 row in set (0.00 sec)

Please, note my private comment - the behaviour changed in 4.1.14 as compared to 4.1.13!

[14 Sep 2005 13:44] Timour Katchaounov

This bug is duplicate of BUG#12882.

[14 Sep 2005 13:48] Timour Katchaounov

Notice that according to the comments to BUG#12882,
and SQL:2003, the results of both test queries should be:

mysql> select 1, min(1) from t1 where a=99 ;
+---+--------+
| 1 | min(1) |
+---+--------+
| 1 |   NULL |
+---+--------+

mysql> select 1, min(1) from t1 where 1=99 ;
+---+--------+
| 1 | min(1) |
+---+--------+
| 1 |   NULL |
+---+--------+

I also tested this on other commercial DBMS with the same result.