I'm setting SQLite logging a warning Warning 0x11C: automatic index on PackageIdByAll(_PackageID)
Whenever I run this SQL WITH PackageIdByUser(_PackageID) AS ( SELECT _PackageID FROM Package AS p INNER JOIN PackageUser AS pu ON pu.Package=p._PackageID WHERE pu.User=?1 AND p._WorkID=0 AND pu._WorkID=0 ), PackageIdByDefaultAccountForPackageFamilyNotUser(_PackageID) AS ( SELECT _PackageID FROM Package AS p INNER JOIN PackageUser AS pu ON pu.Package=p._PackageID WHERE ?4 AND pu.User IN ( SELECT _UserID FROM User AS u WHERE u.UserSid=?3 ) AND p.PackageFamily NOT IN ( SELECT DISTINCT PackageFamily FROM Package AS p INNER JOIN PackageUser AS pu ON pu.Package=p._PackageID WHERE pu.User=?1 AND p._WorkID=0 AND pu._WorkID=0 ) AND p._WorkID=0 AND pu._WorkID=0 ), PackageIdByAll(_PackageID) AS ( SELECT _PackageID FROM PackageIdByUser UNION ALL SELECT _PackageID FROM PackageIdByDefaultAccountForPackageFamilyNotUser ) SELECT * FROM Protocol AS p INNER JOIN ApplicationExtension AS ae ON ae._ApplicationExtensionID=p.Extension INNER JOIN Application AS a ON a._ApplicationID=ae.Application INNER JOIN Package AS pkg ON pkg._PackageID=a.Package INNER JOIN PackageFamily AS pf ON pf._PackageFamilyID=pkg.PackageFamily INNER JOIN PackageIdByAll AS cte_p ON cte_p._PackageID=pkg._PackageID WHERE p.ProgId=?2 AND p._WorkId=0 AND ae._WorkId=0 AND a._WorkId=0 AND pkg._WorkId=0 AND pf._WorkId=0; Why? PackageIdByAll is a CTE. I can't CREATE INDEX for it. _PackageID is the primary key of the Package table DROP TABLE IF EXISTS Package; CREATE TABLE Package( _PackageID INTEGER PRIMARY KEY /* primarykey */ NOT NULL, ... So indexing should be covered being the table's ROWID. I'm using SQLite 3.18.0 Bug or am I holding it wrong? * Howard _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users