SQLite Forum

CTEs in triggers

CTEs in triggers

(1) By Joshua Netterfield (jnetterf) on 2020-11-01 21:41:04 [link] [source]

The documentation states that:

Common table expression are not supported for statements inside of triggers.

However, I seem to be able to use CTEs in triggers in INSERT INTO statements so long as the CTE is nested. For example, the following silly code seems to work:

CREATE TABLE src(content);
CREATE TABLE dst(content);
    WITH loophole AS (SELECT new.content)
    SELECT * FROM loophole

I would appreciate some clarification. Why are CTEs restricted in some cases? Will nesting CTEs in SELECT() in triggers cause terrible things to happen?

(2) By Larry Brasfield (LarryBrasfield) on 2020-11-02 00:56:43 in reply to 1 [link] [source]

The phrase "not supported" is a term of art meaning "may do something strange and undesirable under some conditions, may not perform consistently across versions, and should not be relied upon by code purported to be reliable." It need not lead to terrible consequences consistently, or ever depending upon the local meaning of "terrible". But when anybody uses unsupported features, they are choosing to forego any support for whatever results from such use.

You might buy some rappelling line and hardware rated for 5000 Newtons of tension strength, and find that the first 10 articles test to 3 times that. If you then decide to use it to lower yourself in your car down a cliff, you might live to tell about it. Or, if the line parts partway down, in your final few seconds you can tell yourself, "I guess nothing more than 5000 Newtons is 'supported'."

(3) By Joshua Netterfield (jnetterf) on 2020-11-02 01:37:49 in reply to 2 [link] [source]

If that is the case, perhaps the documentation should be reworded for clarity, as "not supported" can read "does not ever work". Indeed, WITH before an INSERT statement in a trigger results in a syntax error. I was surprised when the above workaround ran.

Is the restriction of CTEs in triggers due to difficulty parsing them or because it "may do something strange and undesirable under some conditions"? If so, which?

(7) By Richard Hipp (drh) on 2020-11-02 11:41:10 in reply to 3 [link] [source]

Is the restriction of CTEs in triggers due to difficulty parsing them

Yes. It's a lot more logic that has to be written, tested, debugged, and maintained for 30 years, and that is mostly unused. (You are the first person to complain that it is not present.)

(9) By Joshua Netterfield (jnetterf) on 2020-11-02 15:32:54 in reply to 7 [link] [source]

I appreciate your response and continue to be amazed by SQLite. No complaints, only confusion.

As this is a parsing issue, would it make sense to amend the documentation to state that CTEs in subqueries and SELECT expressions work, even in triggers?

(11) By miasuji on 2021-05-14 20:23:23 in reply to 7 [link] [source]

Just chiming in here in support of this feature. I'm making very heavy use of triggers for data modification, and have frequently wished I could use CTE's in them.

Usually there are 2 strategies to work around the restriction:

  1. Use the more verbose spelling. However, there are cases where this makes the query much harder to write, read, and maintain:

    • The verbose spelling is much more verbose, ie. you have to spell out the same long SELECT multiple times, and edit them each if you need to make changes.
    • The verbose spelling is "inside out" in a way that makes it difficult to write, read, and maintain. I usually have to write the CTE first, then refactor it to a nested SELECT.
  2. Use a named view. However, there are cases where being able to do WHERE x = NEW.x dramatically speeds up the query. In my case, this occurs when using window functions in more complicated CTE's that the optimization resulting from my earlier post doesn't handle.

(1) seems like the raison d'etre of non-recursive CTE's, and would be nice to have in triggers.

(2) seems like an implementation detail, though I'm not sure it's tractable to get the WHERE push-down optimization to work in all cases. In these cases, I can resort to the "inside-out" spelling, but again this is tedious for the reasons mentioned above.

In my case, I think it would be sufficient to make the above "nesting" trick explicitly supported, if that's easier than supporting top-level CTE's.

Disclaimer: None of this should be construed as a complaint. I continue to be in love with SQLite. Thanks! :-)

(8) By JFMcLuggage (mccon01) on 2020-11-02 12:22:22 in reply to 2 [link] [source]

Mr. Brasfeld's reply, made suitably generic, should be inserted in all technical documentation.

(4) By Keith Medcalf (kmedcalf) on 2020-11-02 02:03:54 in reply to 1 [source]

Of course, the statement you have specified in the trigger (the INSERT statement), once the syntactic sugar is removed, is simply:

INSERT INTO dst SELECT new.content;

In all usage cases of the WITH statement, other than recursive usage, is merely "syntactic sugar" for alternate spellings of the same thing that does not use the WITH keyword.

In other words, there is no particular reason why a WITH statement will not WORK in a trigger, though if you use one and it happens to work today but not tomorrow then that is YOUR problem, not the SQLite3 authors problem. Nor is it their problem it you use one that you think should work but does not.

"Not supported" does not mean "does not work". In the more common case the phrase is a marketing term which means "make money from". That is, when Microsoft says "Windows XP is no longer supported", that has nothing whatsoever to do with "working" but rather with the speaker "making money from" whatever they are disavowing. That is, Microsoft is saying that "Microsoft no longer makes money from Windows XP" and that you should give them money to transition to a product which they do "make money from" on an ongoing basis.

In the case of SQLite3, this usage makes no sense. Rather it means that if it fails to work as YOU intend, then that is YOUR problem, not MINE.

It is merely an advice that if you desire "guaranteed working" you should explore alternate spellings which achieve the same objective result but do not use the WITH syntax because although it may appear to work today, tomorrow that might not be the case and that no "special effort" will be made to ensure that it does work.

(5) By doug (doug9forester) on 2020-11-02 03:19:51 in reply to 4 [link] [source]

I disagree vigorously with the implementation of Sqlite which allows one to use SQL constructs which are "not supported". I think SQL is sufficiently complex that I should not have to pull out a manual (or search deeply) every time I use a construct. The parser/interpreter should surface an error when a "not supported" construct is used. To say it's on me to know everything there is to know about Sqlite's version of SQL, is wrong.

(6) By Keith Medcalf (kmedcalf) on 2020-11-02 06:09:45 in reply to 5 [link] [source]

Be that as it may, the following are identical semantics with different spellings, yet one is an error and the other is not.

sqlite> create trigger xx after insert on x begin
   ...> with t as (select * from new) insert into y select x from t;
   ...> end;
Error: near "insert": syntax error
sqlite> create trigger xx after insert on x begin
   ...> insert into y select x from (select * from new);
   ...> end;

So you are saying that the use of WITH (CTE's) where they are "rejected" are not supported but uses where they are not rejected are supported, despite documentation to the contrary?

And that you would have (somehow) a different expectation because the following spelling is not rejected:

sqlite> create trigger xx after insert on x begin
   ...> insert into y select x from (with t as (select * from new) select x from t);
   ...> end;

(10) By anonymous on 2020-11-04 19:43:43 in reply to 1 [link] [source]

I think that use of CTEs (including recursive CTEs) in triggers can be useful (I have sometimes used it); like you, I have just found that it doesn't work if it isn't nested. I think this is an acceptable restriction for now.