SQLite User Forum

Unexpected Result in Joining Virtual Tables
Login

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.