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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
