The Double Pipe Operator Joins Text on PostgreSQL and SQLite and Means OR on MySQL

Code that builds labels with first_name || ' ' || last_name works on PostgreSQL and SQLite. Move it to MySQL or MariaDB and every label comes back as 0, with no error to say why.

The SQL standard joins strings with ||, and SQLite and PostgreSQL follow it. MySQL and MariaDB read || as a logical OR unless the server runs in the PIPES_AS_CONCAT mode, so both strings are turned into numbers and the answer is 0. The warnings they give for it seldom reach the application.

What Each Engine Answered

The example

SELECT 'a' || 'b';

gave these answers:

These answers need SQLite 3.44 or later.

What to Write Instead

This form answers the same on every engine:

SELECT CONCAT('a', 'b');

Use CONCAT(a, b), which all four engines have (SQLite from version 3.44). CONCAT treats NULL differently on different engines; see CONCAT with a NULL argument.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:1:8: warning: || joins text on SQLite and PostgreSQL. On MySQL and MariaDB it means OR, so 'a' || 'b' is 0. [pipes-concat]
    'a' || 'b'
    Use CONCAT(a, b), which all four engines have (SQLite from version 3.44). CONCAT treats NULL differently on different engines; see concat-null.

1 problem: 1 warning.

baresql rules pipes-concat prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.