View with CTE no longer works
(1) By José Miguel SA (sio2sio2) on 2021-08-30 18:00:18 [link] [source]
I have recently upgraded my Debian and sqlite3 was automatically upgraded to version 3.36.0. In previous versions this example works:
CREATE TABLE Foobar ( id INTEGER PRIMARY KEY ); INSERT INTO Foobar VALUES (1); -- "Factor" is not yet defined CREATE VIEW FoobarV AS SELECT id*(SELECT * FROM Factor) FROM Foobar; -- But now I define it WITH Factor AS (SELECT 8) SELECT * FROM FoobarV;
But now it doesn't:
Error: near line 9: no such table: main.Factor
Is it a bug?
(2) By Dan Kennedy (dan) on 2021-08-30 18:34:07 in reply to 1 [source]
The behaviour of earlier versions was deemed a bug and was fixed here:
(3) By rjp (zimpenfish) on 2022-05-30 20:35:32 in reply to 2 [link] [source]
In lieu of that change, is there any recommended way to use a CTE with a VIEW?
My use case is that I need queries with a parameters and the CTE/view combo is a simple way to work around the lack of them in the CLI.
with param as (select 23 q) select * from view_that_references_param_dot_q;
(4) By Keith Medcalf (kmedcalf) on 2022-05-30 21:19:58 in reply to 3 [link] [source]
You could use the third-party sqlite-statement-vtab extension to create a "parameterized query" with no need for a view at all.
create virtual table paramview using statement(( select id * :q as answer from ... whence it comes ... ));
select answer from paramview where q=23;
(5) By Keith Medcalf (kmedcalf) on 2022-05-31 05:03:11 in reply to 3 [link] [source]
Actually, you can use parameters in the CLI. https://sqlite.org/cli.html#sql_parameters
.param init .param set :param 37 .param set :param2 sqrt(2) select :param, :param2; ┌────────┬─────────────────┐ │ :param │ :param2 │ ├────────┼─────────────────┤ │ 37 │ 1.4142135623731 │ └────────┴─────────────────┘
(6) By rjp (zimpenfish) on 2022-05-31 11:03:19 in reply to 5 [link] [source]
Actually, you can use parameters in the CLI.
Alas, parameters are not allowed in views.
(7) By rjp (zimpenfish) on 2022-05-31 11:03:20 in reply to 4 [link] [source]
That might have to be the fallback plan - I'm not keen on adding an extension just for this though.
(8) By Ebow Halm (D00000000Y) on 2022-06-01 05:23:16 in reply to 2 [link] [source]
No, no, no, no.
That feature (which I didn't know about till this forum post) is an elegant solution to one of the remaining, most-requested features of SQLite: support for stored procedures.
The need that results in the persistent requests for stored procedures is brilliantly solved by the way the poster used an undefined-at-the-time-of-definition CTE name in the view definition.
Instead of removing that feature, please consider documenting it.
The benefit to the SQLite community would be significant; the pressure on the SQLite developers to support stored procedures would ease. Simply point them to the documentation of this feature and examples of how it solves common requests for stored procedure support. I volunteer to provide examples.
Kudos to the poster for discovering such an ingenious solution to the problem. I am reverting that patch on my SQLite build because I need that feature.
In the interest of not breaking existing code, "bugs" and misfeatures have been maintained and documented in SQLite. I cannot upvote enough that this is deserving of that treatment and this is a great feature, not a bug.
To sum, for this, please prefer documentation over removing the feature.
(9) By Ebow Halm (D00000000Y) on 2022-06-01 05:49:48 in reply to 4 [link] [source]
The checkin that would have to be reverted (https://sqlite.org/src/info/f7dcc4b5) added bView to the With struct. Nothing else depends on that addition, so reverting it will not violate other code's expectations of the With struct.
At a minimum, if the SQLite developers have a strong opinion that the feature is a bug that must be removed, how about introducing a compile time constant that allows a user to proactively toggle the behaviour the posted relied upon on.
#ifndef ENABLE_UNAMED_CTE_IN_VIEW_DEFINITION if( p->bView ) break;
(10) By rjp (zimpenfish) on 2022-06-02 08:29:35 in reply to 8 [link] [source]
I can see that it would be a problem if the CTE aliased an existing table name - which do you use in that case?
But like you say, that's better solved with documentation (say "in a view, a concrete table beats a virtual table beats a CTE with the same name") rather than flat out denying us this extremely useful ability to emulate stored procedures.
(The loadable extension solution is sadly not workable on macOS because the stock sqlite is compiled without loadable extension support.)