Only SQLite Lets WHERE Use a Name From the Select List

A query that names price * quantity as total in the select list and then filters on total in WHERE runs on SQLite. PostgreSQL, MySQL and MariaDB refuse it, since the table has no column named total.

WHERE is worked out before the select list, so in standard SQL the y of SELECT x * 2 AS y does not exist yet. SQLite looks it up anyway.

What Each Engine Answered

On a table made with

CREATE TABLE t (x INTEGER);
INSERT INTO t VALUES (1), (2);

the example

SELECT x * 2 AS y FROM t WHERE y > 2;

gave these answers:

What to Write Instead

This form answers the same on every engine:

SELECT x * 2 AS y FROM t WHERE x * 2 > 2;

Repeat the expression in WHERE (WHERE x * 2 > 2), or select from a subquery that defines the name.

What BareSQL Portable Reports

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

$ baresql lint --all example.sql
example.sql:3:32: error: SQLite lets WHERE use a name given in the select list; PostgreSQL, MySQL and MariaDB refuse it. [alias-in-where]
    y
    Repeat the expression in WHERE (WHERE x * 2 > 2), or select from a subquery that defines the name.

1 problem: 1 error.

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