One Script, Four Engines: Where SQL Dialects Disagree

This track runs on PostgreSQL, but every SQL engine has its own dialect. Reading a list of differences is one thing. This lesson runs the same script on PostgreSQL 17, MySQL 8.4, SQL Server 2022 and SQLite 3.49, and shows what each one really returns. Each engine runs in its own Docker container:

# start.sh: the four engines used in this lesson (a local lab; the passwords are placeholders)
docker run -d --name sqlpg -e POSTGRES_PASSWORD=pg -e POSTGRES_DB=sqldemo postgres:17
docker run -d --name sqlmy -e MYSQL_ROOT_PASSWORD=pw -e MYSQL_DATABASE=demo mysql:8.4
docker run -d --name sqlms -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD='Lab-only-Pass1' \
  mcr.microsoft.com/mssql/server:2022-latest
# once SQL Server is up (about 20 seconds), create its database; -C trusts its self-signed certificate
docker exec sqlms /opt/mssql-tools18/bin/sqlcmd -C -U sa -P 'Lab-only-Pass1' -Q 'CREATE DATABASE demo'
docker run -d --name sqlite alpine:3.22 sleep infinity && docker exec sqlite apk add sqlite

The script creates two small tables, then runs nine probes. Each probe is a single statement, so an error in one doesn’t stop the rest.

-- dialects.sql: the same probes on every engine
CREATE TABLE nums (v INT);
INSERT INTO nums VALUES (1), (NULL), (2);
CREATE TABLE pets (owner VARCHAR(10), name VARCHAR(10));
INSERT INTO pets VALUES ('ana', 'rex'), ('ana', 'tom'), ('ben', 'max');
SELECT 5 / 2 AS division;
SELECT CASE WHEN 'a' = 'A' THEN 'equal' ELSE 'different' END AS a_vs_upper_a;
SELECT v FROM nums ORDER BY v;
SELECT 'sql' || 'lab' AS pipes;
SELECT CONCAT('sql', 'lab') AS concat_fn;
SELECT v FROM nums ORDER BY v LIMIT 1;
SELECT v FROM nums ORDER BY v OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY;
SELECT TOP 1 v FROM nums ORDER BY v;
SELECT owner, name FROM pets GROUP BY owner;

SQL Server’s sqlcmd runs a file as one batch, and a syntax error anywhere in a batch stops all of it. So the SQL Server copy is the same file with a GO line after every statement, made with sed 's/;$/;\nGO/' dialects.sql > dialects_mssql.sql. The commands below leave out the user and password flags, and sqlcmd’s -C.

PostgreSQL 17

$ psql -d sqldemo -f dialects.sql
CREATE TABLE
INSERT 0 3
CREATE TABLE
INSERT 0 3
 division 
----------
        2
(1 row)

 a_vs_upper_a 
--------------
 different
(1 row)

 v 
---
 1
 2
  
(3 rows)

 pipes  
--------
 sqllab
(1 row)

 concat_fn 
-----------
 sqllab
(1 row)

 v 
---
 1
(1 row)

 v 
---
 1
(1 row)

psql:dialects.sql:13: ERROR:  syntax error at or near "1"
LINE 1: SELECT TOP 1 v FROM nums ORDER BY v;
                   ^
psql:dialects.sql:14: ERROR:  column "pets.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT owner, name FROM pets GROUP BY owner;
                      ^

MySQL 8.4

$ mysql --table --force demo < dialects.sql
+----------+
| division |
+----------+
|   2.5000 |
+----------+
+--------------+
| a_vs_upper_a |
+--------------+
| equal        |
+--------------+
+------+
| v    |
+------+
| NULL |
|    1 |
|    2 |
+------+
+-------+
| pipes |
+-------+
|     0 |
+-------+
+-----------+
| concat_fn |
+-----------+
| sqllab    |
+-----------+
+------+
| v    |
+------+
| NULL |
+------+
ERROR 1064 (42000) at line 12: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY' at line 1
ERROR 1064 (42000) at line 13: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 v FROM nums ORDER BY v' at line 1
ERROR 1055 (42000) at line 14: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'demo.pets.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SQL Server 2022

$ sqlcmd -d demo -W -i dialects_mssql.sql

(3 rows affected)

(3 rows affected)
division
--------
2

(1 rows affected)
a_vs_upper_a
------------
equal

(1 rows affected)
v
-
NULL
1
2

(3 rows affected)
Msg 102, Level 15, State 1, Server 8e9d2674f647, Line 1
Incorrect syntax near '|'.
concat_fn
---------
sqllab

(1 rows affected)
Msg 102, Level 15, State 1, Server 8e9d2674f647, Line 1
Incorrect syntax near 'LIMIT'.
v
-
NULL

(1 rows affected)
v
-
NULL

(1 rows affected)
Msg 8120, Level 16, State 1, Server 8e9d2674f647, Line 1
Column 'pets.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Server 8e9d2674f647 is the container’s host name, so yours will differ. Line 1 counts lines within each GO batch, not the file.

SQLite 3.49

$ sqlite3 -header -column demo.db < dialects.sql
division
--------
2       
a_vs_upper_a
------------
different   
v
-
 
1
2
pipes 
------
sqllab
concat_fn
---------
sqllab   
v
-
 
Parse error near line 12: near "OFFSET": syntax error
  SELECT v FROM nums ORDER BY v OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY;
                  error here ---^
Parse error near line 13: near "1": syntax error
  SELECT TOP 1 v FROM nums ORDER BY v;
             ^--- error here
owner  name
-----  ----
ana    rex 
ben    max 

The empty line under v is the NULL. In -column mode SQLite prints it as nothing.

What Actually Differed

A few of these are worth a closer look:

• Integer division. Three engines drop the remainder, and MySQL doesn’t. A query that computes an average or a ratio with integers gives different numbers on different engines.
• Case. The equality test depends on the default collation, not on the SQL. MySQL’s connection default collation and SQL Server’s default SQL_Latin1_General_CP1_CI_AS are both case-insensitive (ci).
• || in MySQL is not concatenation. It’s logical OR: both strings convert to the number 0, so the result is 0, with only a warning (deprecated ||, truncated DOUBLE value) that the batch client doesn’t show. That’s the most dangerous row in the table.
• Limiting rows. The standard FETCH FIRST runs on PostgreSQL and SQL Server only. LIMIT covers the other three, and TOP is SQL Server’s own.
• GROUP BY. SQLite accepted a column that isn’t grouped or aggregated and returned the name from one of ana’s rows. Which one it picks isn’t defined. MySQL refuses only because ONLY_FULL_GROUP_BY is on by default since 5.7.

MySQL and SQLite were run in a terminal, so their errors appear in order with the results. Redirect their output to a file and the errors can land in a different place, because the clients write errors to a separate stream.

Practice

1. Make 5 / 2 return 2.5 on all four engines.

-- p1.sql
SELECT 5 / 2.0 AS division;

$ psql -d sqldemo -f p1.sql
      division      
--------------------
 2.5000000000000000
(1 row)

$ mysql --table --force demo < p1.sql
+----------+
| division |
+----------+
|   2.5000 |
+----------+
$ sqlcmd -d demo -W -i p1_mssql.sql
division
--------
2.500000

(1 rows affected)
$ sqlite3 -header -column demo.db < p1.sql
division
--------
2.5     

2. Sort nums with NULLs last on every engine, without NULLS LAST (only PostgreSQL and SQLite accept it here).

-- p2.sql
SELECT v FROM nums ORDER BY CASE WHEN v IS NULL THEN 1 ELSE 0 END, v;

$ psql -d sqldemo -f p2.sql
 v 
---
 1
 2
  
(3 rows)

$ mysql --table --force demo < p2.sql
+------+
| v    |
+------+
|    1 |
|    2 |
| NULL |
+------+
$ sqlcmd -d demo -W -i p2_mssql.sql
v
-
1
2
NULL

(3 rows affected)
$ sqlite3 -header -column demo.db < p2.sql
v
-
1
2
 

3. Rewrite the GROUP BY probe so it runs on all four engines and returns one defined pet per owner.

-- p3.sql
SELECT owner, MIN(name) AS first_pet FROM pets GROUP BY owner ORDER BY owner;

$ psql -d sqldemo -f p3.sql
 owner | first_pet 
-------+-----------
 ana   | rex
 ben   | max
(2 rows)

$ mysql --table --force demo < p3.sql
+-------+-----------+
| owner | first_pet |
+-------+-----------+
| ana   | rex       |
| ben   | max       |
+-------+-----------+
$ sqlcmd -d demo -W -i p3_mssql.sql
owner first_pet
----- ---------
ana rex
ben max

(2 rows affected)
$ sqlite3 -header -column demo.db < p3.sql
owner  first_pet
-----  ---------
ana    rex      
ben    max