SQLite Forum

View with CTE no longer works
Login

View with CTE no longer works

(1) By José Miguel SA (sio2sio2) on 2021-08-30 18:00:18

I have recently upgraded my Debian and sqlite3 was automatically upgraded to version 3.36.0. In previous versions this example works:

```sql
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 [link]

The behaviour of earlier versions was deemed a bug and was fixed here:

[](https://sqlite.org/src/info/f7dcc4b5)

Dan.

(3) By rjp (zimpenfish) on 2022-05-30 20:35:32 in reply to 2 [link]

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.

eg. `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]

You could use the third-party [sqlite-statement-vtab extension](https://github.com/0x09/sqlite-statement-vtab) 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 ...
));
```

You then `select answer from paramview where q=23;`

(7) By rjp (zimpenfish) on 2022-05-31 11:03:20 in reply to 4 [link]

That might have to be the fallback plan - I'm not keen on adding an extension just for this though.

(9) By Ebow Halm (D00000000Y) on 2022-06-01 05:49:48 in reply to 4 [link]

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;
#endif

(5) By Keith Medcalf (kmedcalf) on 2022-05-31 05:03:11 in reply to 3 [link]

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]

> Actually, you can use parameters in the CLI.

Alas, parameters are not allowed in views.

(8) By Ebow Halm (D00000000Y) on 2022-06-01 05:23:16 in reply to 2 [link]

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.

(10) By rjp (zimpenfish) on 2022-06-02 08:29:35 in reply to 8 [link]

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.)