DELETE FROM ... AS ... in trigger causes parse error

[coreybutler]

I'm running into a parsing error when executing a CREATE TRIGGER statement including a DELETE FROM ... AS ... clause inside the trigger body.

I'm using v1.14.28 of this module.

_, err := db.Exec(`
  CREATE TABLE host (host TEXT);
  CREATE TABLE dsn (id INTEGER, host TEXT);

  CREATE TRIGGER test_trigger AFTER DELETE ON dsn
  BEGIN
    DELETE FROM host AS h
    WHERE h.host = OLD.host
      AND h.host NOT IN (
        SELECT DISTINCT d2.host
        FROM dsn AS d2
        WHERE d2.id != OLD.id
          AND d2.host = OLD.host
      );
  END;
`)

returns

near "AS": syntax error

This SQL is valid as of SQLite 3.33.0, which introduced support for DELETE FROM ... AS alias. This is using SQLite v3.49.1. The same SQL executes without issue using tools like DBeaver.

I can work around this by removing the alias and referencing the table name directly, i.e.

DELETE FROM host
WHERE host.host = OLD.host
  AND host.host NOT IN (...)

I'm also able to successfully parse this using rqlite/sql.

Could this be a bug or parser limitation in the handling of Exec() for DDL/trigger bodies?

---

For testing/reproduction, here is the code that works in rqlite/sql (and whose output runs in DBeaver) but fails in this module:

package main

import (
  "fmt"
  "strings"

  "github.com/rqlite/sql"
)

func main() {
  query := `
    CREATE TRIGGER IF NOT EXISTS tr_log_dsn_removal AFTER DELETE
	ON dsn
	FOR EACH ROW
    BEGIN
	INSERT INTO audit (id, nm, data, source_id, source_table)
	VALUES (
	  lower(hex(randomblob(16))),
	  'DSN_REMOVED',
	  json_object(
	    'id', OLD.id,
	    'name', OLD.nm
	  ),
	  OLD.id,
	  'dsn'
	);

	DELETE FROM host h
	WHERE h.host = OLD.host
	  AND h.host NOT IN (
	    SELECT distinct d2.host
	    FROM dsn d2
	    WHERE d2.id != OLD.id AND d2.host = OLD.host
	);
    END
  ;`

  parser := sql.NewParser(strings.NewReader(query))

  for {
    stmt, err := parser.ParseStatement()
    if err != nil {
      if err.Error() == "EOF" {
	break
      }
      panic(err)
    }

    fmt.Printf("-- Reconstructed statement as: \n%s\n", stmt.String())
  }
}

Output:

-- Reconstructed statement as: 
CREATE TRIGGER IF NOT EXISTS "tr_log_dsn_removal" AFTER DELETE ON "dsn" FOR EACH ROW BEGIN INSERT INTO "audit" ("id", "nm", "data", "source_id", "source_table") VALUES (lower(hex(randomblob(16))), 'DSN_REMOVED', json_object('id', "OLD"."id", 'name', "OLD"."nm"), "OLD"."id", 'dsn'); DELETE FROM "host" AS "h" WHERE "h"."host" = "OLD"."host" AND "h"."host" NOT IN (SELECT DISTINCT "d2"."host" FROM "dsn" AS "d2" WHERE "d2"."id" != "OLD"."id" AND "d2"."host" = "OLD"."host"); END

[rittneje]

I think this is a bug in SQLite itself. I get the exact same error with the CLI (version 3.50.1).

And this bug specifically affects trying to do this within a trigger. Directly invoking DELETE FROM host AS h works fine, in both the CLI and this wrapper library. Are you sure you issued this exact CREATE TRIGGER command in DBeaver?

I would suggest reporting this bug directly to the SQLite maintainers. https://sqlite.org/forum/forummain

[coreybutler]

@rittneje the exact command was the output from the rqlite/sql (i.e. last example in the OP)... I was trying to provide a simpler recreation. Unless I mistakenly pasted in the wrong code, it worked in DBeaver. I'll double-check that tomorrow and post back here to confirm.

[rittneje]

The code sample you have for rqlite is also missing the AS keyword, which is required by SQLite. This implies that the rqlite library has its own independent parser and is not rigorously adhering to the SQLite spec.

[coreybutler]

@rittneje I got a chance to followup tonight and it looks like I had a copy/paste error when I originally tried this in DBeaver. Testing with the output I specified above yields the same error in DBeaver, so it doesn't seem like this module is the culprit.

I did as you suggested and opened an issue at https://sqlite.org/forum/forumpost/d3451f1ea6.

Feel free to close (or keep open until the upstream issue is resolved). Thanks for looking into this and pointing me in the right direction.

[coreybutler]

@rittneje - just to be clear, this is the code I was testing (the rqlite output, formatted for visibility):

CREATE TRIGGER IF NOT EXISTS "tr_log_dsn_removal" 
  AFTER DELETE ON "dsn" 
  FOR EACH ROW 
BEGIN
  INSERT INTO "audit" ("id", "nm", "data", "source_id", "source_table") 
  VALUES (
      lower(hex(randomblob(16)))
    , 'DSN_REMOVED'
    , json_object(
        'id', "OLD"."id",
        'name', "OLD"."nm"
      )
    , "OLD"."id"
    , 'dsn'
   ); 

   --------- PROBLEM IS IN THIS STATEMENT ---------
   DELETE FROM "host" AS "h" 
   WHERE "h"."host" = "OLD"."host" AND "h"."host" NOT IN (
     SELECT DISTINCT "d2"."host" 
     FROM "dsn" AS "d2" 
     WHERE "d2"."id" != "OLD"."id" 
       AND "d2"."host" = "OLD"."host"
   );
END

The reconstructed query does have the AS keyword, which seems valid. I think you're right that this is an issue in SQLite itself.

Thanks again!