SELECT FROM DUAL Runs on MySQL and Fails on PostgreSQL and SQLite
Code written for Oracle and carried over to MySQL often checks the clock with SELECT NOW() FROM DUAL. MySQL and MariaDB answer, and PostgreSQL and SQLite look for a table called dual that isn’t there.
DUAL is Oracle’s one-row table, which MySQL and MariaDB keep so that code written for Oracle runs. SQLite and PostgreSQL don’t need a FROM to select values, and look for a real table named dual.
What Each Engine Answered
The example
SELECT 1 + 1 FROM DUAL;
gave these answers:
| Engine | Answer |
|---|---|
| SQLite 3.45.1 | error: no such table: DUAL |
| PostgreSQL 16.15 | error: relation "dual" does not exist |
| MySQL 8.0.46 | 2 |
| MariaDB 10.11.14 | 2 |
What to Write Instead
This form answers the same on every engine:
SELECT 1 + 1;
| Engine | Answer |
|---|---|
| SQLite 3.45.1 | 2 |
| PostgreSQL 16.15 | 2 |
| MySQL 8.0.46 | 2 |
| MariaDB 10.11.14 | 2 |
Leave FROM DUAL out: SELECT 1 + 1 runs on every engine.
What BareSQL Portable Reports
Saved as example.sql, the example gets this from baresql lint --all:
$ baresql lint --all example.sql
example.sql:1:14: error: SELECT ... FROM DUAL runs on MySQL and MariaDB only; SQLite and PostgreSQL have no table called DUAL. [from-dual]
FROM DUAL
Leave FROM DUAL out: SELECT 1 + 1 runs on every engine.
1 problem: 1 error.
baresql rules from-dual prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.