History of Database DUAL Pseudo Table

The humble DUAL table was originally a 2-row table used with views in Oracle, but now has 1 row.

“I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, seemed apt for the process of creating a pair of rows from just one.” – Chuck Weiss, Oracle (one of the first 25 Oracle employees)

DUAL is most often used nowadays because Oracle does not support SELECT without a FROM table clause.

It is also supported in MySQL. According to my reading of the MySQL bug database, some effort is made to exactly mimic Oracle’s DUAL table.

“From MySQL 4.1.0 on, you are permitted to specify DUAL as a dummy table name in situations where no tables are referenced. DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MySQL may ignore the clauses. MySQL does not require FROM DUAL if no tables are referenced.”

DUAL is valuable in MySQL when writing queries that need to be also compatible in Oracle, especially advanced queries that require a lot of testing.

Emulating DUAL in another database, or even documenting how it works, is not straightforward as there is no spec for it and behavior varies across databases and versions.

Logically it looks like this:

CREATE TABLE DUAL (DUMMY varchar(1) default ‘X’);
INSERT INTO DUAL VALUES (‘X’);

– add a GRANT to make DUAL public
– a database symbol alias is needed to allow DUAL to be used without a current database being selected in some databases (not available in MySQL)
– one could add a trigger to enforce how you want updates to affect DUAL (Oracle allows one to drop DUAL, for example.)

DELIMITER $$
CREATE TRIGGER trig_dual_update BEFORE UPDATE ON DUAL (and BEFORE INSERT, DELETE)
FOR EACH ROW BEGIN
SET DUAL.DUMMY = 1 / 0;
END $$

DELIMITER ;

– testing

SELECT DUMMY FROM DUAL; — works in Oracle, not MySQL 5.1

DUMMY
——
X

SELECT COUNT(*) FROM DUAL WHERE 1 != 0; — 0, works in both Oracle and MySQL
SELECT COUNT(*) FROM DUAL WHERE 1 = 1; — 1, works in both Oracle and MySQL

Oracle Magazine – The origin of the DUAL table (2004)
wikipedia: DUAL Table
O’Reilly: SQL Hacks
MySQL Manual: SELECT syntax
All about the DUAL table
OTN Thread: can we drop dual table?

This entry was posted in MySQL, Open Source, Oracle, Tech, Toys. Bookmark the permalink.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.