This mailing list disallows attachments as a anti-spam measure. You
can send attachments directly to me, if you like.
On 5/1/19, Stephen Hunt <sh...@zaber.com> wrote:
> 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
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
