Comparing a Text Column With the Number 0 Matches Codes Like abc on MySQL and Fails on PostgreSQL

Product codes are text, and a query that forgets the quotes, WHERE code = 0, still runs on MySQL and MariaDB. There it returns codes that have nothing to do with 0.

MySQL and MariaDB turn the text into a number, and text that does not start with digits becomes 0, so WHERE code = 0 finds every code like 'abc'. SQLite compares the number as text and finds nothing. PostgreSQL refuses to compare character varying with integer. '1' = 1 trips over the same thing in another way: false on SQLite, true on the other three.

What Each Engine Answered

On a table made with

CREATE TABLE items (code VARCHAR(10));
INSERT INTO items VALUES ('abc');

the example

SELECT COUNT(*) FROM items WHERE code = 0;

gave these answers:

Every Form BareSQL Checks

BareSQL Portable checks 3 forms of this, and each was run on all four engines. yes means the engine ran it and the engines that ran it agreed, no means the engine refused it, and a value is what the engine answered where they disagreed.

What to Write Instead

This form answers the same on every engine:

SELECT COUNT(*) FROM items WHERE code = '0';

Compare like with like: quote the number when the column holds text (code = '0'), or convert one side with CAST.

What BareSQL Portable Reports

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

$ baresql lint --all example.sql
example.sql:3:34: error: code = 0 compares a text column with a number: MySQL and MariaDB turn the text into a number, so 'abc' matches 0, SQLite compares it as text and finds nothing, and PostgreSQL refuses it. [text-number-compare]
    code = 0
    Compare like with like: quote the number when the column holds text (code = '0'), or convert one side with CAST.

1 problem: 1 error.

baresql rules text-number-compare prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.