After laboriously translating your schema and query into something
readable, I get script shown below.
We (all SQLite developers have been consulted and agree) conclude that the
result of the query is undefined. You are sorting on the rowid of a
subquery. But the rowid of a subquery is undefined, so anything can happen
with there.
Was SQLite 3.7.17 really giving you the answer you expected? If so, we
think that was by luck.
Can you recast the query in such a way that it does not sort on the rowid
of a subquery?
Note that an ORDER BY clause without a LIMIT clause in a subquery is
pointless and might be removed by the query optimizer.
CREATE TABLE Items(
uid INTEGER PRIMARY KEY ASC,
id TEXT DEFAULT '',
type TEXT DEFAULT track,
kind TEXT DEFAULT '',
error INT DEFAULT 0,
name TEXT DEFAULT '',
album TEXT DEFAULT '',
artist TEXT DEFAULT '',
genre TEXT DEFAULT '',
composer TEXT DEFAULT '',
description TEXT DEFAULT '',
popularity REAL DEFAULT 0,
frequency REAL DEFAULT 0,
band TEXT DEFAULT '',
call_letters TEXT DEFAULT '',
city TEXT DEFAULT '',
state TEXT DEFAULT '',
country TEXT DEFAULT '',
provider TEXT DEFAULT '',
label TEXT DEFAULT '',
copyright TEXT DEFAULT '',
is_explicit TEXT DEFAULT false,
is_protected TEXT DEFAULT false,
is_purchased TEXT DEFAULT false,
bpm INT DEFAULT 0,
bit_rate INT DEFAULT 0,
sample_rate INT DEFAULT 0,
format TEXT DEFAULT '',
size INT DEFAULT 0,
time INT DEFAULT 0,
url TEXT DEFAULT '',
can_play TEXT DEFAULT false,
can_pause TEXT DEFAULT false,
can_seek TEXT DEFAULT false,
can_next TEXT DEFAULT false,
can_previous TEXT DEFAULT false,
can_queue TEXT DEFAULT false,
can_order TEXT DEFAULT false,
can_like TEXT DEFAULT false,
can_rate TEXT DEFAULT false,
can_star TEXT DEFAULT false,
modified_date INT DEFAULT 0,
released_date INT DEFAULT 0,
purchased_date INT DEFAULT 0,
skipped_date INT DEFAULT 0,
track_number INT DEFAULT 0,
disc_number INT DEFAULT 0,
track_count INT DEFAULT 0,
disc_count INT DEFAULT 0,
listener_count INT DEFAULT 0,
play_count INT DEFAULT 0,
skip_count INT DEFAULT 0
);
CREATE TABLE Relationships(
parent INTEGER,
child INTEGER,
instance INT DEFAULT 0,
owner DEFAULT '',
relationship DEFAULT ''
);
CREATE TABLE Defaults(
type TEXT DEFAULT '',
may_play TEXT DEFAULT true,
may_pause TEXT DEFAULT true,
may_seek TEXT DEFAULT true,
may_next TEXT DEFAULT true,
may_previous TEXT DEFAULT true,
may_queue TEXT DEFAULT false,
is_queued TEXT DEFAULT true,
may_order TEXT DEFAULT true,
may_like TEXT DEFAULT false,
is_liked TEXT DEFAULT '',
may_rate TEXT DEFAULT false,
rating REAL DEFAULT 0.0,
may_star TEXT DEFAULT false,
is_starred TEXT DEFAULT false,
added_date INT DEFAULT 0,
played_date INT DEFAULT 0
);
CREATE TABLE Attributes(
parent INTEGER,
child INTEGER,
instance INT,
owner TEXT,
may_play TEXT,
may_pause TEXT,
may_seek TEXT,
may_next TEXT,
may_previous TEXT,
may_queue TEXT,
is_queued TEXT,
may_order TEXT,
may_like TEXT,
is_liked TEXT,
may_rate TEXT,
rating REAL,
may_star TEXT,
is_starred TEXT,
added_date INT,
played_date INT
);
CREATE INDEX dtypes ON Defaults (type);
CREATE INDEX types ON Items (type);
CREATE INDEX albums ON Items (album);
CREATE INDEX genres ON Items (genre);
CREATE UNIQUE INDEX guids ON Items (id);
CREATE UNIQUE INDEX relations ON Relationships (parent, child, instance);
CREATE UNIQUE INDEX attribs ON Attributes (parent, child, instance, owner);
CREATE INDEX parents ON Relationships (parent);
CREATE INDEX childs ON Relationships (child);
CREATE INDEX instances ON Relationships (instance);
CREATE INDEX owners ON Relationships (owner);
CREATE INDEX relation ON Relationships (relationship);
CREATE INDEX kinds ON Items (kind);
CREATE INDEX attrib_parent ON Attributes (parent);
CREATE INDEX attrib_child ON Attributes (child);
CREATE INDEX attrib_instance ON Attributes (instance);
CREATE INDEX attrib_owner ON Attributes (owner);
CREATE INDEX names ON Items (name collate nocase);
CREATE INDEX artists ON Items (artist collate nocase);
explain query plan
SELECT R.child,
R.instance,
R.owner,
R.relationship,
I.*,
NS.rowid AS sort,
COALESCE(L1.may_play, L2.may_play, L3.may_play, L4.may_play,
L5.may_play,
L6.may_play, L7.may_play, L8.may_play, D.may_play) AS
may_play,
COALESCE(L1.may_pause, L2.may_pause, L3.may_pause, L4.may_pause,
L5.may_pause, L6.may_pause, L7.may_pause, L8.may_pause,
D.may_pause) AS may_pause,
COALESCE(L1.may_seek, L2.may_seek, L3.may_seek, L4.may_seek,
L5.may_seek,
L6.may_seek, L7.may_seek, L8.may_seek, D.may_seek) AS
may_seek,
COALESCE(L1.may_next, L2.may_next, L3.may_next, L4.may_next,
L5.may_next,
L6.may_next, L7.may_next, L8.may_next, D.may_next) AS
may_next,
COALESCE(L1.may_previous, L2.may_previous, L3.may_previous,
L4.may_previous, L5.may_previous, L6.may_previous,
L7.may_previous, L8.may_previous, D.may_previous)
AS may_previous,
COALESCE(L1.may_queue, L2.may_queue, L3.may_queue, L4.may_queue,
L5.may_queue, L6.may_queue, L7.may_queue, L8.may_queue,
D.may_queue) AS may_queue,
COALESCE(L1.is_queued, L2.is_queued, L3.is_queued, L4.is_queued,
L5.is_queued, L6.is_queued, L7.is_queued, L8.is_queued,
D.is_queued) AS is_queued,
COALESCE(L1.may_order, L2.may_order, L3.may_order, L4.may_order,
L5.may_order, L6.may_order, L7.may_order, L8.may_order,
D.may_order) AS may_order,
COALESCE(L1.may_like, L2.may_like, L3.may_like, L4.may_like,
L5.may_like,
L6.may_like, L7.may_like, L8.may_like, D.may_like) AS
may_like,
COALESCE(L1.is_liked, L2.is_liked, L3.is_liked, L4.is_liked,
L5.is_liked,
L6.is_liked, L7.is_liked, L8.is_liked, D.is_liked) AS
is_liked,
COALESCE(L1.may_rate, L2.may_rate, L3.may_rate, L4.may_rate,
L5.may_rate,
L6.may_rate, L7.may_rate, L8.may_rate, D.may_rate)
AS may_rate,
COALESCE(L1.rating, L2.rating, L3.rating, L4.rating, L5.rating,
L6.rating, L7.rating, L8.rating, D.rating) AS rating,
COALESCE(L1.may_star, L2.may_star, L3.may_star, L4.may_star,
L5.may_star,
L6.may_star, L7.may_star, L8.may_star, D.may_star) AS
may_star,
COALESCE(L1.is_starred, L2.is_starred, L3.is_starred, L4.is_starred,
L5.is_starred, L6.is_starred, L7.is_starred, L8.is_starred,
D.is_starred) AS is_starred,
COALESCE(L1.played_date, L2.played_date, L3.played_date,
L4.played_date,
L5.played_date, L6.played_date, L7.played_date,
L8.played_date,
D.played_date) AS played_date,
COALESCE(L1.added_date, L2.added_date, L3.added_date, L4.added_date,
L5.added_date, L6.added_date, L7.added_date, L8.added_date,
D.added_date) AS added_date
FROM items I
INNER JOIN defaults D
ON I.type = D.type
INNER JOIN relationships R
ON R.child = I.uid
INNER JOIN (SELECT uid
FROM items
ORDER BY name COLLATE nocase ASC) NS
ON NS.uid = I.uid
LEFT OUTER JOIN attributes L1
ON L1.parent = R.parent
AND L1.child = R.child
AND L1.instance = R.instance
AND L1.owner = ''
LEFT OUTER JOIN attributes L2
ON L2.parent = R.parent
AND L2.child = R.child
AND L2.instance = R.instance
AND L2.owner IS NULL
LEFT OUTER JOIN attributes L3
ON L3.parent = R.parent
AND L3.child = R.child
AND L3.instance IS NULL
AND L3.owner = ''
LEFT OUTER JOIN attributes L4
ON L4.parent = R.parent
AND L4.child = R.child
AND L4.instance IS NULL
AND L4.owner IS NULL
LEFT OUTER JOIN attributes L5
ON L5.parent = R.parent
AND L5.child IS NULL
AND L5.instance IS NULL
AND L5.owner = ''
LEFT OUTER JOIN attributes L6
ON L6.parent = R.parent
AND L6.child IS NULL
AND L6.instance IS NULL
AND L6.owner IS NULL
LEFT OUTER JOIN attributes L7
ON L7.parent IS NULL
AND L7.child = R.child
AND L7.instance IS NULL
AND L7.owner = ''
LEFT OUTER JOIN attributes L8
ON L8.parent IS NULL
AND L8.child = R.child
AND L8.instance IS NULL
AND L8.owner IS NULL
WHERE R.parent = (SELECT parent
FROM relationships
WHERE rowid = 2)
ORDER BY sort ASC
LIMIT 100;
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
