BareSQL Portable

BareSQL Portable checks SQL that has to run on SQLite, PostgreSQL, MySQL and MariaDB. It knows 58 differences between those engines: 35 where at least one engine refuses the statement, 15 where every engine runs it and the answers differ, and 8 where the answers differ only for some data. Each one was run on all four engines, and each comes with a form that answers the same everywhere, where one exists.

It has three commands. baresql lint reads SQL files and points at the differences without connecting to anything. baresql compare runs a script on real engines and shows every answer that differs. baresql rules explains each difference with the answer every engine gave.

lint

baresql lint schema.sql queries.sql

Lint learns the column types from the CREATE TABLE statements it reads, so give it the schema along with the queries. That’s how it tells an integer division from a decimal one, or a text column compared with a number. This is its report on the sample shop’s queries:

shop.sql:6:8: warning: || joins text on SQLite and PostgreSQL. On MySQL and MariaDB it means OR, so 'a' || 'b' is 0. [pipes-concat]
    name || ' (' || stock || ')'
    Use CONCAT(a, b), which all four engines have (SQLite from version 3.44). CONCAT treats NULL differently on different engines; see concat-null.
shop.sql:7:8: warning: Dividing an integer by an integer drops the fraction on SQLite and PostgreSQL; MySQL and MariaDB keep it. [integer-division]
    SUM(stock) / COUNT(*)
    Multiply one side by 1.0 for the fraction and round it to the places you need (see decimal-division): ROUND(7 * 1.0 / 2, 2) is 3.5 on every engine. For a whole number on every engine, take the remainder off first: (7 - 7 % 2) / 2.
shop.sql:9:37: error: PostgreSQL has no = between a boolean and a number. [boolean-as-number]
    active = 1
    Compare and insert TRUE and FALSE (active = TRUE, or simply WHERE active). All four engines accept them.
shop.sql:10:8: error: A selected column that is neither grouped nor aggregated takes its value from any row of the group on SQLite and MariaDB; PostgreSQL and MySQL refuse the query. [ungrouped-column]
    name
    Add the column to GROUP BY, or say which value you want with an aggregate such as MAX(b).
shop.sql:10:14: error: A selected column that is neither grouped nor aggregated takes its value from any row of the group on SQLite and MariaDB; PostgreSQL and MySQL refuse the query. [ungrouped-column]
    price
    Add the column to GROUP BY, or say which value you want with an aggregate such as MAX(b).
shop.sql:12:8: error: IFNULL() works on SQLite, MySQL and MariaDB; PostgreSQL refuses it. [function-not-everywhere]
    IFNULL(stock, 0)
    Use COALESCE(a, b).

6 problems: 4 errors, 2 warnings. 4 notes not shown (differences that depend on the data); --all shows them.

Every problem has a severity. An error means at least one engine refuses the statement. A warning means every engine runs it and the answers differ. A note means the answers differ only for some data, such as text in mixed case or a column that holds NULL; notes are left out unless you pass --all.

The list of differences further down gives each rule’s usual severity. Lint reports the severity of what it actually found, so one rule can be an error in one statement and a note in another. Dividing by a column is a note, since the answers differ only when the column holds 0, but 1 / 0 written out is an error, because PostgreSQL refuses it.

Most projects use only two of the four engines. --engines sqlite,postgres keeps only the differences between those two, judged by the answers recorded for the exact construct lint found, so a MySQL-only problem drops out of a project that tests on SQLite and runs on PostgreSQL. A comment -- baresql:ignore integer-division on the line of a problem, or the line before it, silences that rule there.

compare

baresql compare --sqlite --postgres postgres://dev@localhost/scratch \
    --mysql mysql://dev@localhost:3306/ --mariadb mariadb://dev@localhost:3307/ shop.sql

compare runs the script statement by statement on every engine you name and prints the statements whose answers differ. Answers are compared by meaning: 3.5 equals 3.5000, true equals 1, dates match however they’re printed, and row order counts only under an ORDER BY. Text is compared exactly, so 'Mug' differs from 'mug', and text never equals a number. Here is the shop’s script on all four engines:

Compared on SQLite 3.45.1, PostgreSQL 16.15, MySQL 8.0.46, MariaDB 10.11.14.

shop.sql:5  SELECT name FROM products WHERE name = 'mug'
  0 rows on SQLite and PostgreSQL, 1 on MySQL and MariaDB
  SQLite and PostgreSQL: no rows
  MySQL and MariaDB: 'Mug'
  lint: text-compare-case

shop.sql:6  SELECT name || ' (' || stock || ')' FROM products ORDER BY id
  SQLite and PostgreSQL: 3 rows
      'Mug (10)'
      'mug lid (0)'
      NULL
  MySQL and MariaDB: 3 rows
      1
      0
      NULL
  MySQL warned: '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR ins...; Truncated incorrect DOUBLE value: 'Mug'; Truncated incorrect DOUBLE value: ' (' (and 3 more)
  MariaDB warned: Truncated incorrect DECIMAL value: 'Mug'; Truncated incorrect DOUBLE value: ' ('; Truncated incorrect DECIMAL value: 'mug lid' (and 2 more)
  lint: pipes-concat

shop.sql:7  SELECT SUM(stock) / COUNT(*) FROM products
  SQLite and PostgreSQL: 3
  MySQL and MariaDB: 3.3333
  lint: integer-division, division-by-zero

shop.sql:8  SELECT name FROM products ORDER BY stock
  the same rows in a different order (NULL sorts differently, or rows that tie in ORDER BY came in another order)
  SQLite, MySQL and MariaDB: 3 rows
      'Teapot'
      'mug lid'
      'Mug'
  PostgreSQL: 3 rows
      'mug lid'
      'Mug'
      'Teapot'
  lint: null-order

shop.sql:9  SELECT COUNT(*) FROM products WHERE active = 1
  refused by PostgreSQL, answered by SQLite, MySQL and MariaDB
  SQLite, MySQL and MariaDB: 2
  PostgreSQL: error: operator does not exist: boolean = integer
  lint: boolean-as-number

shop.sql:10  SELECT name, price FROM products GROUP BY active
  refused by PostgreSQL and MySQL, answered by SQLite and MariaDB
  SQLite and MariaDB: 2 rows
      'mug lid', 2.25
      'Mug', 7.5
  PostgreSQL: error: column "products.name" must appear in the GROUP BY clause or be used in an aggregate function
  MySQL: error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'baresql_compare.products.name' which is not functionally dependent ...
  lint: ungrouped-column

shop.sql:12  SELECT IFNULL(stock, 0) FROM products ORDER BY id
  refused by PostgreSQL, answered by SQLite, MySQL and MariaDB
  SQLite, MySQL and MariaDB: 3 rows
      10
      0
      0
  PostgreSQL: error: function ifnull(integer, integer) does not exist
  lint: function-not-everywhere

11 statements: 2 answered alike, 7 differ, 2 skipped.
baresql rules <code> explains each lint code above.

Each engine works in a throwaway space: a transaction rolled back at the end on PostgreSQL, a scratch database dropped at the end on MySQL and MariaDB, and a temporary file on SQLite, run through sqlite3 -safe. compare skips the statements that would leave that space, such as transaction control, statements about databases and users, and SET GLOBAL. On MySQL and MariaDB it refuses any statement that names another database. It is no sandbox against a script written to get out, so point it at servers meant for testing.

rules

baresql rules lists every difference, and baresql rules CODE shows one: what differs and why, a minimal example with the answer each engine gave, the form that answers the same everywhere and what to write instead. --format json gives the whole catalog with its answers.

The 58 Differences

Each difference has its own post with the recorded answers, every form BareSQL checks and what lint prints for it.

Numbers

• Integer Division Drops the Fraction on SQLite and PostgreSQL and Keeps It on MySQL integer-division, answers differ
• Dividing Decimals Gives Six Decimal Places on MySQL and Sixteen on PostgreSQL decimal-division, answers differ
• CAST to INTEGER Fails on MySQL and Rounds Differently on SQLite and PostgreSQL cast-to-number, fails somewhere
• Division by Zero Is NULL on SQLite and MySQL and an Error on PostgreSQL division-by-zero, depends on the data
• SQLite Keeps DECIMAL Values as Floats and Sums of Prices Pick Up Rounding Errors decimal-as-float, depends on the data
• REAL on PostgreSQL and FLOAT on MySQL Are 4-Byte Floats That Drop Digits float-precision, answers differ
• LOG With One Argument Is Base 10 on PostgreSQL and the Natural Logarithm on MySQL log-base, answers differ
• The Caret Operator Is a Power on PostgreSQL and a Bitwise XOR on MySQL caret-operator, fails somewhere

Text

• The Double Pipe Operator Joins Text on PostgreSQL and SQLite and Means OR on MySQL pipes-concat, answers differ
• CONCAT With a NULL Argument Returns NULL on MySQL and Skips It on PostgreSQL concat-null, answers differ
• MySQL Compares Text Without Regard to Letter Case and PostgreSQL Compares It Exactly text-compare-case, depends on the data
• LIKE Ignores Letter Case on SQLite and MySQL and Respects It on PostgreSQL like-case, depends on the data
• Comparing a Text Column With the Number 0 Matches Codes Like abc on MySQL and Fails on PostgreSQL text-number-compare, fails somewhere
• CAST to CHAR Cuts a Value to One Character on PostgreSQL cast-to-text, answers differ
• CHAR Columns Come Back Padded With Spaces on PostgreSQL char-padding, depends on the data
• LENGTH Counts Bytes on MySQL and MariaDB and Characters on PostgreSQL and SQLite length-bytes, depends on the data
• SQLite LOWER and UPPER Change Only the Letters A to Z lower-non-ascii, depends on the data
• SUBSTR From Position 0 Returns Text on SQLite and PostgreSQL and Nothing on MySQL substring-start, answers differ
• A Backslash Is an Escape Character in MySQL Strings and a Plain Character in PostgreSQL backslash-escapes, answers differ
• A Backslash in LIKE Escapes Percent and Underscore Everywhere Except SQLite like-escape, answers differ

Dates

• CAST to DATE Returns the Number 2024 on SQLite Because It Has No Date Type cast-to-date, answers differ
• Adding 1 to a Date Gives the Next Day on PostgreSQL and a Number on MySQL and SQLite date-arithmetic, answers differ

NULL and Sorting

• NULL Sorts First on SQLite and MySQL and Last on PostgreSQL null-order, depends on the data
• NULLS FIRST and NULLS LAST Fail on MySQL and MariaDB nulls-first-last, fails somewhere
• GREATEST Skips NULL on PostgreSQL and Returns NULL on MySQL greatest-least, fails somewhere

Queries

• Columns Missing From GROUP BY Work on SQLite and MariaDB and Fail on PostgreSQL and MySQL ungrouped-column, fails somewhere
• Comparing a Boolean With 1 Works on MySQL and SQLite and Fails on PostgreSQL boolean-as-number, fails somewhere
• LIMIT With a Comma Works on MySQL and SQLite and Fails on PostgreSQL limit-comma, fails somewhere
• FETCH FIRST ROWS ONLY Works on PostgreSQL and MariaDB and Fails on MySQL and SQLite fetch-first, fails somewhere
• Only SQLite Lets WHERE Use a Name From the Select List alias-in-where, fails somewhere
• PostgreSQL Refuses Select List Names in HAVING alias-in-having, fails somewhere
• MySQL and MariaDB Refuse LIMIT Inside an IN Subquery limit-in-subquery, fails somewhere
• FULL OUTER JOIN Works on PostgreSQL and SQLite and Fails on MySQL and MariaDB full-outer-join, fails somewhere
• DISTINCT ON Works on PostgreSQL Only distinct-on, fails somewhere
• COUNT DISTINCT With Two Columns Works on MySQL and Fails on PostgreSQL and SQLite count-distinct-columns, fails somewhere

Inserts and Updates

• MySQL Refuses an UPDATE Whose Subquery Reads the Same Table update-same-table, fails somewhere
• UPDATE With a Join Needs Different Syntax on MySQL and PostgreSQL update-join, fails somewhere
• INSERT IGNORE Becomes INSERT OR IGNORE on SQLite and ON CONFLICT DO NOTHING on PostgreSQL insert-ignore, fails somewhere
• Upsert Needs ON CONFLICT on PostgreSQL and ON DUPLICATE KEY UPDATE on MySQL upsert, fails somewhere
• MySQL Refuses INSERT RETURNING While PostgreSQL and SQLite Accept It returning, fails somewhere
• SQLite Has No TRUNCATE Statement truncate, fails somewhere

Schemas

• MySQL and MariaDB on Linux Treat Table Names as Case Sensitive table-name-case, fails somewhere
• SQLite Stores Text in INTEGER Columns and Ignores VARCHAR Lengths type-not-enforced, fails somewhere
• No Auto Increment Column Syntax Works on Every SQL Engine auto-increment, fails somewhere
• VARCHAR Without a Length Fails on MySQL and MariaDB varchar-length, fails somewhere
• Column Types Like DATETIME and BYTEA Exist on Some SQL Engines Only type-not-everywhere, fails somewhere
• MySQL Table Options Like ENGINE and CHARSET Fail on PostgreSQL and SQLite table-options, fails somewhere
• MySQL Refuses a Plain DEFAULT Value on TEXT Columns text-default, fails somewhere
• MySQL Refuses a Key on a TEXT Column Without a Prefix Length index-on-text, fails somewhere
• MySQL Refuses CREATE INDEX IF NOT EXISTS index-if-not-exists, fails somewhere
• SQLite Has No CREATE OR REPLACE VIEW create-or-replace, fails somewhere

Syntax

• Double Quotes Name a Column on PostgreSQL and Make a String on MySQL double-quotes, answers differ
• Double Dash Comments Need a Space After Them on MySQL and MariaDB dash-comment, answers differ
• PostgreSQL Returns Column Names in Lower Case Unless You Quote Them column-name-case, answers differ
• Backticks Around Names Work on MySQL and SQLite and Fail on PostgreSQL backticks, fails somewhere
• Hash Comments Work on MySQL and MariaDB and Become an Operator on PostgreSQL hash-comment, fails somewhere

Functions and Operators

• SQL Functions That Exist on Some Engines Only and What to Write Instead function-not-everywhere, fails somewhere
• SQL Operators That Exist on Some Engines Only and What to Write Instead operator-not-everywhere, fails somewhere

How the Answers Were Recorded

Every example, rewrite and form in the catalog was run on SQLite 3.45.1, PostgreSQL 16.15, MySQL 8.0.46 and MariaDB 10.11.14, and the 1,212 answers are kept in the code repository. On every change, CI runs the whole catalog again on PostgreSQL 13 to 18, MySQL 8.0, 8.4 and 9.7, MariaDB 10.11, 11.4, 11.8 and 12.3, and the sqlite3 of its Linux and macOS machines, and fails when an answer changes. Where a version really answers differently, the catalog keeps that answer too, and lint’s messages say so (“MD5() works on PostgreSQL, MySQL (before 9.6) and MariaDB; SQLite refuses it”). Some builds of sqlite3 leave out its math functions or fold case beyond A to Z; the catalog says which answers assume the usual build.

Lint’s messages for functions, operators and types come from those runs, and so does each problem’s severity. The testing post has the details.

Limits

Lint reads tokens and recognizes the shapes of expressions. It doesn’t parse SQL fully, so it can miss a construct written in an unusual way. JSON counts only where it decides whether a statement runs at all, as with JSON_EXTRACT, ->> and the JSONB type. The rest of JSON, stored procedures and triggers are left out, since their syntax differs too much between engines to compare. Answers are recorded with each engine’s default settings: server modes such as MySQL’s sql_mode, and collations, change some of them.

Some differences have no rule yet: ALTER TABLE syntax, names that are reserved words on some engines (user, key, rank), aggregate FILTER, FROM DUAL, the digits of AVG over integers and ROUND of floating point halves among them. compare shows them when a script runs into one.