SQL differences
Double Dash Comments Need a Space After Them on MySQL and MariaDB
In a line like SELECT price --discount, left over from debugging, SQLite and PostgreSQL read --discount as a comment. MySQL and MariaDB add the discount to the price …
Posts
SQL differences
Double Quotes Name a Column on PostgreSQL and Make a String on MySQL
A query copied from PostgreSQL that puts a column name in double quotes runs on MySQL without an error, and never reads the column. Standard SQL quotes names with double …
SQL differences
FETCH FIRST ROWS ONLY Works on PostgreSQL and MariaDB and Fails on MySQL and SQLite
FETCH FIRST 10 ROWS ONLY is the standard way to ask for the top ten, and PostgreSQL and MariaDB accept it. SQLite and MySQL refuse the query. MariaDB added FETCH FIRST in …
SQL differences
FULL OUTER JOIN Works on PostgreSQL and SQLite and Fails on MySQL and MariaDB
Matching two lists, such as invoices and payments, to see what is missing on either side calls for a FULL OUTER JOIN. PostgreSQL and SQLite run it, and MySQL and MariaDB …
SQL differences
GREATEST Skips NULL on PostgreSQL and Returns NULL on MySQL
GREATEST(sale_price, list_price) picks the larger of two prices. When one of them is NULL, PostgreSQL still picks the other price, and no other engine does. GREATEST(1, …
SQL differences
Hash Comments Work on MySQL and MariaDB and Become an Operator on PostgreSQL
MySQL and MariaDB skip everything from a # to the end of the line. On SQLite and PostgreSQL that # is still part of the statement. Scripts written for the mysql client …
BareSQL Audit
How BareSQL Audit Finds Every Column a PostgreSQL Login Can Read
PostgreSQL keeps the answer to “who can read this column?” in many places. Grants sit in access lists on tables and on single columns. Role memberships nest, and since …
testing
How BareSQL Audit Was Tested Against Planted Leaks on Six PostgreSQL Versions
An access audit can be wrong in two directions. If it misses a route, a leak goes unnoticed because the report said all was well. If it invents one, people spend a day …
BareSQL Portable
How BareSQL Portable Finds SQL That Answers Differently on Four Engines
Most SQL that fails on another engine fails loudly, with a syntax error on the first run. The expensive kind runs everywhere and answers differently. On the sample shop’s …
testing
How BareSQL Portable Was Tested on Four Real Database Engines
Every message baresql lint prints is a claim about four database engines. “GROUP_CONCAT() works on SQLite, MySQL and MariaDB; PostgreSQL refuses it.” Claims like that go …