ON CONFLICT UPDATE not working on drift files about drift

[simolus3]

It complains that column appSettings doesn't exist

Really? It doesn't do that for me and the column hasn't even been mentioned in the query.

I've pasted the definition and the query into separate drift files in the examples/modular package from this repository and there is no warning from the generator.

[JCKodel]

@simolus3 I'm sorry, I meant authProvider column.

I'm not talking about the generator, I'm talking about actually executing the code

For example:

This is the exception:

(same as above: no such column: authProvider)

But this is the drift_db_viewer:

The column is there!

There are no warnings whatsoever after a clean and a run build_runner.

Copying and pasting all queries on sqliteonline.com works just fine.

Changing that query to INSERT OR REPLACE solves the problem (so, the column IS really there), but other updates stop working, for instance:

DriftRemoteException (SqliteException(1): while preparing statement, no such column: name, SQL logic error (code 1)
  Causing statement: UPDATE "Users" SET "name" = ?, "originalName" = ?, "avatarUrl" = ?, "originalAvatarUrl" = ? WHERE "id" = ? AND "deleted" = ?;)

At first run, this code is executed:

await into(users).insert(
            UsersCompanion.insert(
              id: principal.id,
              name: principal.name,
              email: principal.email,
              avatarUrl: principal.avatarUrl,
            ),
          );

On some next run, this code is executed (there is an if to determine if the insert or update is executed, they are in the same method)

  await (update(users)
                ..where(
                    (u) => u.id.equals(principal.id) & u.deleted.equals(false)))
              .write(
            UsersCompanion(
              name: Value(principal.name),
              originalName: Value(originalName),
              avatarUrl: Value(principal.avatarUrl),
              originalAvatarUrl: Value(originalAvatarUrl),
            ),
          );

It is not possible for the column to exist in INSERT but not on UPDATE.

O.o

[simolus3]

It only seems to happen with the trigger but I don't see what's wrong. I'll try to minimize the example and post an issue to the sqlite3 forum.

[simolus3]

It works with WHEN new."authProvider" <> old."authProvider" (note the explicit new). Or just use AFTER UPDATE OF "authProvider" ON "UserLogins" and drop the WHEN. I think we should reject unqualified references in triggers since they seem to cause problems.

[simolus3]

I've fixed sqlparser to be consistent with what sqlite3 is doing in f9fa123.

The solution is to fix the definition of the trigger to use a qualified reference or UPDATE OF. You'll have to write a migration dropping and re-creating the trigger afterwards if a version of the database with that trigger has already been published.