Unexpected Result in Joining Virtual Tables
(1) By Jinsheng Ba (bajinsheng) on 2022-05-30 07:50:23 [source]
Again, the query with the WHERE clause returns more results than the same query without WHERE.
CREATE VIRTUAL TABLE vt0 USING fts4(c0);
CREATE VIRTUAL TABLE vt1 USING fts4(c0);
CREATE VIEW v0(c0) AS SELECT vt0.c0 FROM vt1 CROSS JOIN vt0 ON vt1.c0;
INSERT INTO vt1(c0) VALUES ('x');
INSERT INTO vt0 VALUES (1), (2);
SELECT * FROM vt1 LEFT OUTER JOIN v0 ON vt1.c0 LEFT OUTER JOIN vt0 ON v0.c0;
-- x||
SELECT * FROM vt1 LEFT OUTER JOIN v0 ON vt1.c0 LEFT OUTER JOIN vt0 ON v0.c0 WHERE vt0.c0;
-- x||1
-- x||2
This should be a regression issue which does not appear in the old version.
Environment:
Version: 3.39.0
Commit ID: 7e87892c
OS: Ubuntu 20.04
Configuration Options: ./configure --enable-all
Compiler: gcc-9
Client: CLI
(2) By Richard Hipp (drh) on 2022-05-30 17:34:17 in reply to 1 [link] [source]
Thanks for the bug report. Please try again with the latest trunk check-in and let us know if you find anything else.
(3) By Jinsheng Ba (bajinsheng) on 2022-05-31 08:52:41 in reply to 2 [link] [source]
Thanks! Yes, it has been fixed.