SUBSTR From Position 0 Returns Text on SQLite and PostgreSQL and Nothing on MySQL

Code ported from a language that counts from 0 often ends up with SUBSTR(code, 0, 3). SQLite and PostgreSQL return the first two characters. MySQL and MariaDB return an empty string.

Positions start at 1. SQLite and PostgreSQL count position 0 as part of the requested length, while MySQL and MariaDB return nothing for a start of 0. Negative starts differ as well: SQLite, MySQL and MariaDB count them from the end, and PostgreSQL counts them before the first character.

SELECT SUBSTR('abc', 0, 2), run on four engines: SQLite 3.45.1 'a'; PostgreSQL 16.15 'a'; MySQL 8.0.46 ''; MariaDB 10.11.14 ''.

What Each Engine Answered

The example

SELECT SUBSTR('abc', 0, 2);

gave these answers:

Engine	Answer
SQLite 3.45.1	`'a'`
PostgreSQL 16.15	`'a'`
MySQL 8.0.46	`''`
MariaDB 10.11.14	`''`

What to Write Instead

This form answers the same on every engine:

SELECT SUBSTR('abc', 1, 1);

Engine	Answer
SQLite 3.45.1	`'a'`
PostgreSQL 16.15	`'a'`
MySQL 8.0.46	`'a'`
MariaDB 10.11.14	`'a'`

Start at 1 or later. For the last n characters, SUBSTR(x, LENGTH(x) - n + 1) works on all four for ASCII text (LENGTH counts bytes on MySQL and MariaDB).

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: SUBSTR('abc', 0, 2) is 'a' on SQLite and PostgreSQL and an empty string on MySQL and MariaDB. [substring-start]
    SUBSTR('abc', 0, 2)
    Start at 1 or later. For the last n characters, SUBSTR(x, LENGTH(x) - n + 1) works on all four for ASCII text (LENGTH counts bytes on MySQL and MariaDB).

1 problem: 1 warning.

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