SQLite Forum

Potential bug? = comparison on columns defined with no type
Login

Potential bug? = comparison on columns defined with no type

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

I ran into this while working with the [parlgov-development.db](http://www.parlgov.org/static/data/parlgov-development.db) (<- download link) database from [http://www.parlgov.org/](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](https://github.com/simonw/datasette-graphql/issues/43)

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

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][1] documentation page, and especially the 
"[Type Conversions Prior To Comparison][2]" 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.

[1]: https://www.sqlite.org/datatype3.html
[2]: https://www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison

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

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]

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
```