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 18:43	Modified:	3 Nov 2008 5:08
Reporter:	Roland Bouman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	5.0.12	OS:	Any
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

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.