Hi Richard,
I just realized I didn't include the mailing list in my previous response
to you, so I'm doing that now with the contents of the SQL file I sent you
previously. I've also included a copy of my original email in plaintext as
the HTML formatted SQL queries got mangled when it was processed for the
mailing list. Sorry for any confusion.
As always, let me know if you need any more information with regard to this.
Regards,
Steve
==== demo.sql START ====
DROP TABLE Data_Demo;
CREATE TABLE Data_Demo(
-- Synthetic key
Id INTEGER NOT NULL PRIMARY KEY,
-- A Name
Name TEXT NOT NULL);
INSERT INTO Data_Demo VALUES (1, 'john');
INSERT INTO Data_Demo VALUES (2, 'james');
INSERT INTO Data_Demo VALUES (3, 'jingle');
INSERT INTO Data_Demo VALUES (4, 'himer');
INSERT INTO Data_Demo VALUES (5, 'smith');
DROP VIEW IF EXISTS View_Proto;
CREATE VIEW View_Proto
AS
WITH Temp(Name) AS (VALUES ('A'), ('B'))
SELECT Name Name FROM Temp;
DROP VIEW IF EXISTS View_Demo;
CREATE VIEW View_Demo
AS
WITH Temp(Att, Val, Act) AS (VALUES
('C', 'D', 'E'),
('F', 'G', 'H')
)
SELECT D.Id Id, P.Name Protocol, T.Att Att, T.Val Val, T.Act Act
FROM Data_Demo D
CROSS JOIN View_Proto P
CROSS JOIN Temp T;
==== demo.sql END ====
On 5/1/19, Stephen Hunt 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_Proto
> AS
> WITH Temp2(Name) AS (VALUES ('A'), ('B'))
> SELECT Name Name FROM Temp2;
>
> OR if they have a different number of rows:
>
> CREATE VIEW View_Proto
> AS
> WITH 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_Proto
> AS
> WITH 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
