MySQL Bugs: #13496: SELECT COUNT(*) FROM dual WHERE 1=0 returns empty set but shouldn't

Bug #13496	SELECT COUNT(*) FROM dual WHERE 1=0 returns empty set but shouldn't
Submitted:	26 Sep 2005 20:43	Modified:	3 Nov 2008 6:08
Reporter:	Roland Bouman
Status:	Closed
Category:	Server: Optimizer	Severity:	S4 (Feature request)
Version:	5.0.12	OS:	Any
Assigned to:	Sergey Gluhov	Target Version:
Triage:	D5 (Feature request)

Description:
SELECT COUNT(*) col FROM dual WHERE 1=0 

returns no rows at all. It was expected that this query would return one row with the
value 0 (zero) for the col column.

SELECT COUNT(*) col FROM dual WHERE 1=1 

returns one row as expected. However, the value for the col column is 0 (zero), whereas
it should be 1 (one)

How to repeat:
SELECT COUNT(*) col FROM dual WHERE 1=0 
;
-- (returns an empty set, but should return one row with the value 0)

SELECT COUNT(*) col FROM dual WHERE 1=1 
;
-- (returns one row, with the value 0 for col)

-- Compare to:

create table mydual(
dummy enum('X') primary key
)
;
insert into mydual values 'X'
;

SELECT COUNT(*) col FROM mydual WHERE 1=0 
;
-- (returns one row, col has zero as value)

SELECT COUNT(*) col FROM dual WHERE 1=1 
;
-- (returns one row, col has one as value)

Suggested fix:
If DUAL is meant to be a faketable like in Oracle, this is undesired behaviour at the
least (for example, queries that use DUAL in subqueries could be severly broken). In this
case, the fix should ideally have dual mimic oracle's dual in the full. So, it should
conform to a table constructed like this (the following snippet is meant to be a
description, not an implementation):

create table DUAL (
    DUMMY ENUM('X') PRIMARY KEY
);

INSERT INTO DUAL VALUES 'X'
;

By having the column name and content conform to the Oracle DUAL table, chances that
existing Oracle code ports to MySQL will break are reduced to the minimum. Oracle
actually uses the VARCHAR2(1) datatype but code that relies would have to be revised
anyway because MySQL does not have such a datatype.

If DUAL is just a NOOP placeholder, it should be documented very clearly that DUAL
behaviour is incompatible with DUAL behaviour in Oracle (wich seems daft, because in
MySQL, DUAL is a compatibility feature). Especially
because DUAL does allow a WHERE clause. Allowing the WHERE clause suggests that DUAL is
not just a NOOP placeholder, and any out of the ordinary behaviour should be documented
extra clearly.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31205

After some discussion, it became clear that this is a "feature request" rather than a bug.
It is reasonable to consider Oracle compatibility here. However, it will be less
disruptive to put behaviour changes in at a point when we are changing versions.

Fixed in 6.0.7:

+-----------------------+
| version()             |
+-----------------------+
| 6.0.7-alpha-community |
+-----------------------+
1 row in set (0.30 sec)

mysql> SELECT COUNT(*) col FROM dual WHERE 1=0 ;
+-----+
| col |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) col FROM dual WHERE 1=1 ;
+-----+
| col |
+-----+
|   1 |
+-----+
1 row in set (0.01 sec)

Also fixed in 5.1.29 and 5.0.70:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -P3308 -uroot -proot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.70-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT COUNT(*) col FROM dual WHERE 1=0 ;
+-----+
| col |
+-----+
|   0 |
+-----+
1 row in set (0.17 sec)

mysql> SELECT COUNT(*) col FROM dual WHERE 1=1 ;
+-----+
| col |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

So, should be just closed.