SQL query returns zero results in Zotero, multiple results from command line

[Emiliano Heyns]

When I send the following query to Zotero using Zotero.DB.queryAsync, I get a null returned (which I assume means "no results"), but when I run it from the command line using sqlite3 against the DB (after Zotero was stopped of course), I do get results. Any ideas?

WITH duplicates AS ( SELECT parentItemID, contentType, COUNT(*) as duplicates FROM itemAttachments WHERE linkMode <> 3 AND itemID NOT IN (select itemID from deletedItems) GROUP BY parentItemID, contentType ) SELECT item.itemID, attachment.path, COALESCE(duplicates.duplicates, 1) as duplicates FROM items item LEFT JOIN itemAttachments attachment ON attachment.itemID = item.itemID LEFT JOIN duplicates on attachment.parentItemID = duplicates.parentItemID AND attachment.contentType = duplicates.contentType WHERE item.itemTypeID = 14 AND item.itemID NOT IN (select itemID from deletedItems) AND ( attachment.path IS NULL OR (attachment.linkMode IS NOT NULL AND attachment.linkMode <> 3) )

[Emiliano Heyns]

On Tuesday, February 6, 2018 at 10:24:34 PM UTC+1, Emiliano Heyns wrote:

When I send the following query to Zotero using Zotero.DB.queryAsync, I get a null returned (which I assume means "no results"), but when I run it from the command line using sqlite3 against the DB (after Zotero was stopped of course), I do get results. Any ideas?

WITH duplicates AS ( SELECT parentItemID, contentType, COUNT(*) as duplicates FROM itemAttachments WHERE linkMode <> 3 AND itemID NOT IN (select itemID from deletedItems) GROUP BY parentItemID, contentType ) SELECT item.itemID, attachment.path, COALESCE(duplicates.duplicates, 1) as duplicates FROM items item LEFT JOIN itemAttachments attachment ON attachment.itemID = item.itemID LEFT JOIN duplicates on attachment.parentItemID = duplicates.parentItemID AND attachment.contentType = duplicates.contentType WHERE item.itemTypeID = 14 AND item.itemID NOT IN (select itemID from deletedItems) AND ( attachment.path IS NULL OR (attachment.linkMode IS NOT NULL AND attachment.linkMode <> 3) )

Never mind -- looks like the sqlite inside Firefox doesn't understand CTEs, but a subquey works.