SQLite column explicit NULL creates Unknown Type Error

[caleblloyd]

Version

1.15.0

What happened?

In SQLite, when a column is designated with an explicit NULL modifier, such as id INTEGER NULL or id TEXT NULL then an error occurs, such as unknown SQLite type: integernull or unknown SQLite type: textnull

Relevant log output

sqlc generate failed.

2022/08/23 14:29:37 unknown SQLite type: integernull
2022/08/23 14:29:37 unknown SQLite type: integernull

Database schema

-- Example queries for sqlc
CREATE TABLE test
(
    id INTEGER NULL
);

SQL queries

-- name: TestList :many
SELECT * FROM test;

Configuration

{
  "version": "1",
  "packages": [
    {
      "path": "db",
      "engine": "sqlite",
      "schema": "query.sql",
      "queries": "query.sql"
    }
  ]
}

Playground URL

https://play.sqlc.dev/p/01df25ecce97214ab8073d8e4ac7c2487d9d11ed995c682b32913b1e09a8855f

What operating system are you using?

Linux

What database engines are you using?

SQLite

What type of code are you generating?

Go

[caleblloyd]

A workaround is to remove the explicit NULL and the column will implicitly accept NULL values.

I would be happy to offer a PR if someone could point me in the right direction.

[kyleconroy]

@caleblloyd I believe this is an issues with our SQLite parser. It's parsing the column type as integernull instead of a null integer.

[hakobera]

@kyleconroy @caleblloyd It's very difficult say this is bug of SQLite parser. I know NULL constraint is accepted by SQLite3 binary, but in the spec, NULL constraint is not valid syntax for column-constraint.

See https://www.sqlite.org/syntax/column-constraint.html

In my understanding, current SQLite parser is based on spec according to original repo: https://github.com/bkiers/sqlite-parser, not SQLite3 implementation.

So this is expected behavior of current SQLite parser.

[kyleconroy]

Should this be a parser error then?

[hakobera]

Yes, it should be parser error based on the spec.

For my personal use, I don't use NULL constraints because it's default behavior.

But if someone want to update SQLite parser to match SQLite's implement, I have no objection.