Looking at the query, in the CLI, what if you rename the three bolded "title" fields to something else? I think the system is getting messed up because a field in a table is called "title" and you're naming a query called "title" but you're not distinctly stating which "title" you want to query against, as in a table field or your query. Not to mention it looks like you're doing the SORT BY twice against the same field in the same manor. Mind you I have NOT tested this against anything.
sqlite> SELECT (CASE bms.title ISNULL WHEN 1 THEN moz_places.url ELSE (CASE bms.title WHEN '' THEN moz_places.url ELSE bms.title END) END) AS * title*, (CASE moz_places.favicon_id ISNULL WHEN 1 THEN '' ELSE (SELECT 'moz-anno:favicon:' || moz_favicons.url FROM moz_favicons WHERE moz_favicons.id = moz_places.favicon_id) END) AS favicon, moz_places.url AS url, moz_places.visit_count AS visit_count, moz_places.frecency AS frecency, bms.id AS id, bms.dateAdded AS dateAdded, bms.lastModified AS lastModified, (SELECT max(visit_date) FROM moz_historyvisits WHERE place_id = moz_places.id) AS visit_date FROM (SELECT fk AS id FROM moz_bookmarks WHERE parent IN (6068538) GROUP BY fk HAVING count(*) = 1) AS t, moz_places, moz_bookmarks AS folders, moz_bookmarks AS bms WHERE t.id = moz_places.id AND t.id = bms.fk AND bms.parent = folders.id AND folders.parent != 4 GROUP BY bms.id ORDER BY *title *COLLATE NOCASE ASC, *title *COLLATE NOCASE ASC; On Wed, Apr 10, 2013 at 3:32 PM, Perry Wagle <wa...@mac.com> wrote: > > On Apr 10, 2013, at 5:33 AM, Richard Hipp <d...@sqlite.org> wrote: > > > On Wed, Apr 10, 2013 at 4:39 AM, Perry Wagle <wa...@mac.com> wrote: > > > >> I have a problem with a field name being ambiguous when it wasn't > before. > >> Is this a bug or a feature? > >> > >> Firefox 19 uses Sqlite 3.7.14.1 which does what it should. Firefox 20 > >> uses Sqlite 3.7.15.2 which complains that "title" is ambiguous in the > ORDER > >> BY in the below query: > >> > >> SELECT (CASE bms.title ISNULL WHEN 1 THEN moz_places.url ELSE (CASE > >> bms.title WHEN '' THEN moz_places.url ELSE bms.title END) END) AS title, > >> (CASE moz_places.favicon_id ISNULL WHEN 1 THEN '' ELSE (SELECT > >> 'moz-anno:favicon:' || moz_favicons.url FROM moz_favicons WHERE > >> moz_favicons.id = moz_places.favicon_id) END) AS favicon, > moz_places.url > >> AS url, moz_places.visit_count AS visit_count, moz_places.frecency AS > >> frecency, bms.id AS id, bms.dateAdded AS dateAdded, bms.lastModified AS > >> lastModified, (SELECT max(visit_date) FROM moz_historyvisits WHERE > place_id > >> = moz_places.id) AS visit_date FROM (SELECT fk AS id FROM moz_bookmarks > >> WHERE parent IN (6068538) GROUP BY fk HAVING count(*) = 1) AS t, > >> moz_places, moz_bookmarks AS folders, moz_bookmarks AS bms WHERE t.id = > >> moz_places.id AND t.id = bms.fk AND bms.parent = folders.id AND > >> folders.parent != 4 GROUP BY bms.id ORDER BY title COLLATE NOCASE ASC, > >> title COLLATE NOCASE ASC > >> > >> I claim "title" shouldn't be ambiguous, since the first SELECT > expression > >> was named "title". What's the consensus? > >> > > > > There were enhancements to name resolution in 3.7.16. Did you try it > > there? > > See below: > > SQLite version 3.7.16.1 2013-03-29 13:44:34 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> SELECT (CASE bms.title ISNULL WHEN 1 THEN moz_places.url ELSE > (CASE bms.title WHEN '' THEN moz_places.url ELSE bms.title END) END) AS > title, (CASE moz_places.favicon_id ISNULL WHEN 1 THEN '' ELSE (SELECT > 'moz-anno:favicon:' || moz_favicons.url FROM moz_favicons WHERE > moz_favicons.id = moz_places.favicon_id) END) AS favicon, moz_places.url > AS url, moz_places.visit_count AS visit_count, moz_places.frecency AS > frecency, bms.id AS id, bms.dateAdded AS dateAdded, bms.lastModified AS > lastModified, (SELECT max(visit_date) FROM moz_historyvisits WHERE place_id > = moz_places.id) AS visit_date FROM (SELECT fk AS id FROM moz_bookmarks > WHERE parent IN (6068538) GROUP BY fk HAVING count(*) = 1) AS t, > moz_places, moz_bookmarks AS folders, moz_bookmarks AS bms WHERE t.id = > moz_places.id AND t.id = bms.fk AND bms.parent = folders.id AND > folders.parent != 4 GROUP BY bms.id ORDER BY title COLLATE NOCASE ASC, > title COLLATE NOCASE ASC; > Error: ambiguous column name: title > > > > > -- > > D. Richard Hipp > > d...@sqlite.org > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
