SQLite Forum

Bug report: strange error message in cte and window function
Login

Bug report: strange error message in cte and window function

(1) By Zu-Ming Jiang (jiang446079653) on 2021-05-17 07:30:44 [link]

Hello,

I used my fuzzing tool to test sqlite of latest version, and found that it returned stange error message when processing a sql.

Test command:

`rm test.db; ./sqlite3 test.db < fuzz.sql`

The content of fuzz.sql:

`create table t_sa ( 
c_muyat INTEGER NOT NULL,
c_d4u TEXT ,
c_lngdt TEXT NOT NULL,
c_c3v INTEGER ,
primary key(c_c3v),
unique(c_muyat),
check(1=1)
);`

`WITH 
cte_0 AS (select       
    ref_0.c_muyat as c1
  from 
    t_sa as ref_0
)
select  
  LAG(cast(cast(null as INTEGER) as INTEGER)) over gen8fjew as c0
from 
  t_sa as ref_5
window gen8fjew as ( partition by (select c1 from cte_0 order by c1 limit 1 offset 4));`

The returned error message is:

`Error: near line 11: no such table: cte_0`

It seems to be a bug since cte_0 has been created.

(2) By Dan Kennedy (dan) on 2021-05-18 10:48:22 in reply to 1 [link]

I think you're right - this is a bug. Now fixed, along with the triggers one that you posted, here:

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

Thanks for finding and reporting all these!

Dan.

(3) By ddevienne on 2021-05-18 11:16:31 in reply to 2 [link]

Hi Dan. Just curious regarding this commit:

The .test and .tcl files seem to overlap a lot, with one being a superset  
of the other, with expected rows added to it. Can you please explain why that is?

*(there's also a `breakpoint` added somewhere, as another difference).*

(4) By Dan Kennedy (dan) on 2021-05-18 20:47:26 in reply to 3

For the window*.test/tcl files, the *.tcl file is a script that generates the *.test file. It finds the expected results by running the queries against postgres.

The breakpoint was most likely added by mistake. 

Dan.