Potential bug? = comparison on columns defined with no type

(1) By Simon Willison (simonw) on 2020-08-08 23:36:39 [source]

I ran into this while working with the parlgov-development.db (<- download link) database from http://www.parlgov.org/

If you create a table where the columns do not have types (which isn't something I would ever do, but I write software that works with databases created by other people) the following SQL queries return different results:

select * from no_column_types where country_id = 44;

Compared to:

select * from no_column_types where country_id = '44';

Here's a full demonstration session:

SQLite version 3.28.0 2019-04-15 14:49:49
sqlite> create table no_column_types (id, name, country_id);
sqlite> insert into no_column_types (id, name, country_id) values (1, 'Bob', 44);
sqlite> insert into no_column_types (id, name, country_id) values (2, 'Karl', 44);
sqlite> select * from no_column_types;
1|Bob|44
2|Karl|44
sqlite> select * from no_column_types where country_id = 44;
1|Bob|44
2|Karl|44
sqlite> select * from no_column_types where country_id = '44';
sqlite>

Is this a SQLite bug, or is this intended behaviour?

Here's a full issue thread where I explore this bug: Figure out why an {eq: value} fails where where: "x = value" succeeds

(2) By Richard Hipp (drh) on 2020-08-09 00:19:35 in reply to 1 [link] [source]

Intended behavior. '44' is text and 44 is a number. They are not equal.

    SELECT '44'==44;

The above returns false.

If you give an affinity to the column (either text or integer) then the constant will be coerced into the correct type and it will work more like you expect. But because no type was specified for the "country_id" column, no type coercion occurs. See the whole Datatypes documentation page, and especially the "Type Conversions Prior To Comparison" section.

Bottom line: If you give datatypes to your table columns, SQLite works just like PostgreSQL and other "traditionally typed" RDBMSes. The extra rules for type affinity only really come into play when you have typeless columns or start mixing datatypes in ways that traditional RDBMSes do not allow.

(3) By Simon Willison (simonw) on 2020-08-09 03:25:52 in reply to 2 [link] [source]

That makes sense, thanks for the explanation! I was confused because I'd been benefiting from type coercion on my other projects, since I was comparing values to a column with a defined type affinity.

(4) By luuk on 2020-08-09 07:40:08 in reply to 3 [link] [source]

The no_column_types also has:

sqlite> insert into no_column_types (id, name, country_id) values (3, 'Richard', '1');
sqlite> select * from no_column_types order by country_id;
id          name        country_id
----------  ----------  ----------
1           Bob         44
2           Karl        44
3           Richard     1

and, of course a simple solution:

sqlite> select * from no_column_types order by 0+country_id;
id          name        country_id
----------  ----------  ----------
3           Richard     1
1           Bob         44
2           Karl        44