E0029 implicit-conversion

Type mismatch MySQL or SQLite converts implicitly.

What it reports

With --dialect mysql or --dialect sqlite, the type mismatches that PostgreSQL rejects as E0003 or E0007 but these databases run: they convert the values at run time instead. MySQL turns 'abc' into 0 (with a warning) when comparing it with a number, and SQLite compares values of different storage classes and stores any value in any column. The query runs, but rarely does what was meant.

Reported here: comparisons and JOIN ... ON conditions between mismatched types, arithmetic on text, CASE branches and set operation columns of different types, a comparison with a value that isn't one of an ENUM's labels, and (SQLite, or MySQL for a value that isn't a string literal) INSERT / UPDATE values that don't fit the column type.

Example

-- --dialect mysql
SELECT id FROM users WHERE id = 'abc';

warning[E0029]: Type mismatch: comparing integer with text
  --> q.sql:1:28
    |
  1 | SELECT id FROM users WHERE id = 'abc';
    |                            ^^
    = help: MySQL converts the values at run time, so this runs, but rarely does what was meant. Use an explicit CAST if the conversion is intended.

Fixed:

SELECT id FROM users WHERE id = 42;

Notes

MySQL in its default strict mode rejects a string literal that isn't a value of the column's type when it is assigned (UPDATE users SET id = 'abc', an invalid ENUM label in INSERT): that stays an error (E0003). A numeric string is fine in both databases (id = '42', id = '1.5').

SQLite STRICT tables reject type mismatches at run time on INSERT and UPDATE, so assignments of non-convertible values into a STRICT table are reported as E0003. Comparisons in STRICT tables still follow SQLite's usual conversions and are reported here.

Configuring

# sqlsift.toml
[rules]
implicit-conversion = "error"   # or "off"

Or for a single line: -- sqlsift:disable E0029. The examples use the schema on the Rules page.