Hi,
We use SQLite extensively here at Zaber and are quite pleased with it.
However, we recently added a view that incorrectly returns incorrect/NULL
data. It appears to be cause by CTE names leaking outside of the view and
being confused with another CTE with the same name but only if both CTEs
produce the same number of rows.
I have attached some simple SQL and a procedure to reproduce the issue.
1. Create a new database
2. Read the attached SQL in demo.sql (which creates one table, Data_Demo,
and two views, View_Proto and View_Demo, each of which have a CTE named
Temp that produces 2 rows)
3. Run SELECT * FROM View_Demo; which incorrectly produces (note the
incorrect/NULL values for columns Att, Val, and Act):
Id Protocol Att Val Act
---------- ---------- ---------- ---------- ----------
1 A A
1 A B
1 B A
1 B B
2 ...
4. Run SELECT * FROM View_Demo WHERE Val IS NOT NULL; which correctly
produces:
Id Protocol Att Val Act
---------- ---------- ---------- ---------- ----------
1 A C D E
1 A F G H
1 B C D E
1 B F G H
2 ...
Note that this problem disappears if the two Temp CTEs are given different
names:
CREATE VIEW View_ProtoASWITH Temp2(Name) AS (VALUES ('A'),
('B'))SELECT Name Name FROM Temp2;
OR if they have a different number of rows:
CREATE VIEW View_ProtoASWITH Temp(Name) AS (VALUES ('A'), ('B'),
('X'))SELECT Name Name FROM Temp;
Also if one of the CTEs is a SELECT statement instead of a VALUES
statement, the problem also disappears, even if the CTE names and number of
rows returned are the same:
CREATE VIEW View_ProtoASWITH Temp(Name) AS (SELECT Name FROM Data_Demo
LIMIT 2)SELECT Name Name FROM Temp;
I have confirmed this on both versions 3.22 and 3.28. I’m running Ubuntu
18.04.
Thanks for looking into this. I really appreciate it! Please let me know if
you need any more information.
Regards,
Steve
--
Stephen Hunt
Zaber Technologies Inc.
#2 - 605 West Kent Ave. N.
Vancouver, British Columbia
Canada, V6P 6T7
Toll free (Canada and USA): 1-888-276-8033
Phone: +1-604-569-3780 ext. 134
Fax: +1-604-648-8033
www.zaber.com
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
