SQLite3: "CREATE VIEW" fails at "column-name"

Developing some app in Perl 5.18.2, I try to create a view as part of the database schema. Unfortunately the creation statement fails, and my program outputs:

[0] failed_execute: failed to execute: CREATE VIEW V_KEY_FLAT
(TYPE, INSTANCE, KEY_DATA, CREATOR, CREATED, MODIFIED, LOCKED, DK_DATA,
KEK_DATA) AS
SELECT
KEY_TYPE.NAME,
KEY_ID.INSTANCE,
KEY_DATA, CREATORS.NAME AS CREATOR, CREATED, MODIFIED, LOCKED,
KEY_PROT.DK_DATA, KEY_PROT.KEK_DATA
FROM KEY_TYPE
INNER JOIN KEY_ID ON (KEY_TYPE.ID = KEY_ID.TYPE_ID)
INNER JOIN KEY_DATA ON (KEY_DATA.ID = KEY_ID.ID)
INNER JOIN KEY_PROT ON (KEY_PROT.ID = KEY_ID.ID)
INNER JOIN CREATORS ON (CREATORS.ID = CREATOR_ID);: near "(": syntax error

I suspect that the ( is that of "[( column-name [, column-name]...)]" where the docs say:

The column-name list syntax was added in SQLite versions 3.9.0 (2015-10-14).

I'm using sqlite3-3.44.0, so that should be fine, right? Also I was able to create that view in an empty database manually, so it seems the syntax is accepted.

I'm also using perl-DBI-1.628 and perl-DBD-SQLite-1.40.

Als the schema creation happens in a transaction that fails, running my program ends with an empty database. After creating the view manually, I see another strange error; first the view creation (note that none of the tables exists):

SQLite version 3.44.0 2023-11-01 11:23:50
Enter ".help" for usage hints.
sqlite> CREATE VIEW V_KEY_FLAT
   ...> (TYPE, INSTANCE, KEY_DATA, CREATOR, CREATED, MODIFIED, LOCKED, DK_DATA,
(x1...> KEK_DATA) AS
   ...> SELECT
   ...> KEY_TYPE.NAME,
   ...> KEY_ID.INSTANCE,
   ...> KEY_DATA, CREATORS.NAME AS CREATOR, CREATED, MODIFIED, LOCKED,
   ...> KEY_PROT.DK_DATA, KEY_PROT.KEK_DATA
   ...> FROM KEY_TYPE
   ...> INNER JOIN KEY_ID ON (KEY_TYPE.ID = KEY_ID.TYPE_ID)
   ...> INNER JOIN KEY_DATA ON (KEY_DATA.ID = KEY_ID.ID)
   ...> INNER JOIN KEY_PROT ON (KEY_PROT.ID = KEY_ID.ID)
   ...> INNER JOIN CREATORS ON (CREATORS.ID = CREATOR_ID);
sqlite>

The error is see when running my program again (after having created the view manually) is:

[0] failed_prepare: prepare() of "SELECT EXISTS(
SELECT 1 FROM sqlite_master WHERE type='table' and name=?);" failed with "malformed database schema (V_KEY_FLAT) - near "(": syntax error"

Again, the command succeeds when I execute the commands manually (in a database with the view added manually):

SQLite version 3.44.0 2023-11-01 11:23:50
Enter ".help" for usage hints.
sqlite> SELECT 1 FROM sqlite_master WHERE type='table' and name='V_KEY_FLAT';
sqlite> SELECT 1 FROM sqlite_master WHERE type='view' and name='V_KEY_FLAT';
1
sqlite>

I'm not able to find the root cause of the issues seen.

Unfortunately the code involved is much too complex to show a "MWE", but essentially the code to execute the SQL commands is:

# execute SQL statement
sub _do_sql($$)
{
    my ($self, $sql) = @_;
    my $dbh = $self->dbh();

    if (defined(my $result = $dbh->do($sql))) {
        return $result;
    } else {
        $self->add_error(EB_DBI_FAILED_EXEC,
                         'failed to execute: ' . $sql . ': ' . $dbh->errstr);
        return $result;
    }
}

(and $dbh is the result of DBI->connect('dbi:SQLite:dbname=' . $filename))

Debug Session

It's really strange (printing $sql and $dbh->errstr in _do_sql):

Auth::KeyStore::SQLite::_do_sql(lib/Auth/KeyStore/SQLite.pm:164):
164:        if (defined(my $result = $dbh->do($sql))) {
auto(-1)  DB<8> p $sql, $dbh->errstr
CREATE TABLE METADATA (
PNAME   VARCHAR(20) PRIMARY KEY NOT NULL,
PVALUE  VARCHAR(20)
);
  DB<9> n
Auth::KeyStore::SQLite::_do_sql(lib/Auth/KeyStore/SQLite.pm:167):
167:            $self->add_error(EB_DBI_FAILED_EXEC,
168:                             'failed to execute: ' . $sql . ': ' . $dbh->errstr);
auto(-1)  DB<9> p $sql, $dbh->errstr
CREATE TABLE METADATA (
PNAME   VARCHAR(20) PRIMARY KEY NOT NULL,
PVALUE  VARCHAR(20)
);malformed database schema (V_KEY_FLAT) - near "(": syntax error

So it seems when creating a table, SQLite looks at the views and complains about the view created earlier.

Stepping into the database interface I found that compiled DBD::SQLite::st::_prepare($sth, $sql, @_) causes the error.

Solution

I found the answer in https://stackoverflow.com/a/36411540/6607497:

> perl -MDBD::SQLite -le'print $DBD::SQLite::sqlite_version'
3.7.17

So perl isn't using the system's SQLite (3.44.0), but its own version (3.7.17)! As "column-name" was added for 3.9, the DBD::SQLite does not know it!