431670 - RESULTS_AS_DATE_QUERY and RESULTS_AS_DATE_SITE_QUERY create SQL Statement with syntax error when NavHistoryQuery attributes specified

Status: RESOLVED INACTIVE

(Firefox :: Bookmarks & History, defect)

Description

[cmtalbert]

Attachment: Test case demonstrating the problem

Running a RESULTS_AS_DATE_QUERY returns a SQL Statment that doesn't pass muster.  The DbConnection returns it with a Syntax Error

Here is the Output from NSPR:
BEGIN DEFERRED
-1610559488[1c07310]: Sqlite statement prepare error: 1 'near "ASCWHERE": syntax error'
-1610559488[1c07310]: Statement was: 'SELECT null, 'place:type=0&sort=1&beginTime='||beginTime||'&endTime='||endTime, dayTitle, null, null, endTime, null, null, null, null FROM (SELECT * FROM (SELECT 0 dayOrder, '0' dayRange, :dayTitle0 dayTitle, 1209625200000000 beginTime, 1209711600000000 endTime FROM  moz_historyvisits WHERE visit_date >= 1209625200000000 AND visit_date < 1209711600000000   AND visit_type NOT IN (0,4) LIMIT 1) TUNION0 UNION SELECT * FROM (SELECT 1 dayOrder, '1' dayRange, :dayTitle1 dayTitle, 1209538800000000 beginTime, 1209625200000000 endTime FROM  moz_historyvisits WHERE visit_date >= 1209538800000000 AND visit_date < 1209625200000000   AND visit_type NOT IN (0,4) LIMIT 1) TUNION1 UNION SELECT * FROM (SELECT 2 dayOrder, '2' dayRange, :dayTitle2 dayTitle, 1209452400000000 beginTime, 1209538800000000 endTime FROM  moz_historyvisits WHERE visit_date >= 1209452400000000 AND visit_date < 1209538800000000   AND visit_type NOT IN (0,4) LIMIT 1) TUNION2 UNION SELECT * FROM (SELECT 3 dayOrder, '3' dayRange, :dayTitle3 dayTitle, 1209366000000000 beginTime, 1209452400000000 endTime FROM  moz_historyvisits WHERE visit_date >= 1209366000000000 AND visit_date < 1209452400000000   AND visit_type NOT IN (0,4) LIMIT 1) TUNION3 UNION SELECT * FROM (SELECT 4 dayOrder, '4' dayRange, :dayTitle4 dayTitle, 1209279600000000 beginTime, 1209366000000000 endTime FROM  moz_historyvisits WHERE visit_date >= 1209279600000000 AND visit_date < 1209366000000000   AND visit_type NOT IN (0,4) LIMIT 1) TUNION4 UNION SELECT * FROM (SELECT 5 dayOrder, '5' dayRange, :dayTitle5 dayTitle, 1209193200000000 beginTime, 1209279600000000 endTime FROM  moz_historyvisits WHERE visit_date >= 1209193200000000 AND visit_date < 1209279600000000   AND visit_type NOT IN (0,4) LIMIT 1) TUNION5 UNION SELECT * FROM (SELECT 6 dayOrder, '6' dayRange, :dayTitle6 dayTitle, 1209106800000000 beginTime, 1209193200000000 endTime FROM  moz_historyvisits WHERE visit_date >= 1209106800000000 AND visit_date < 1209193200000000   AND visit_type NOT IN (0,4) LIMIT 1) TUNION6 UNION SELECT * FROM (SELECT 7 dayOrder, '7+' dayRange, :dayTitle7 dayTitle, 1 beginTime, 1209106800000000 endTime FROM  moz_historyvisits WHERE visit_date < 1209106800000000   AND visit_type NOT IN (0,4) LIMIT 1) TUNIONLAST ) TOUTER ORDER BY dayOrder ASCWHERE ( v.visit_date >=  :begin_time  AND  v.visit_date <=  :end_time  AND  h.visit_count >=  :min_visits )'
-1610559488[1c07310]: WARNING: NS_ENSURE_SUCCESS(rv, rv) failed with result 0x80004005: file /Users/clint/code/fx-trunk/mozilla/toolkit/components/places/src/nsNavHistory.cpp, line 3550
-1610559488[1c07310]: ROLLBACK TRANSACTION
-1610559488[1c07310]: WARNING: NS_ENSURE_SUCCESS(rv, rv) failed with result 0x80004005: file /Users/clint/code/fx-trunk/mozilla/toolkit/components/places/src/nsNavHistoryResult.cpp, line 2347
-1610559488[1c07310]: WARNING: NS_ENSURE_SUCCESS(rv, rv) failed with result 0x80004005: file /Users/clint/code/fx-trunk/mozilla/toolkit/components/places/src/nsNavHistoryResult.cpp, line 2142

I'll attach the test I wrote that contains the query I used to create this.

Repro:
Run this command on a test-enabled debug build (my build is from April 30, 2008), you'll have to modify it for your environment though.
/Users/clint/code/fx-trunk/mozilla/dbg/i386/dist/MinefieldDebug.app/Contents/MacOS/xpcshell -f ~/code/fx-trunk/mozilla/tools/test-harness/xpcshell-simple/head.js -f ~/code/fx-trunk/mozilla/toolkit/components/places/tests/queries/head_queries.js -f ~/code/fx-trunk/mozilla/toolkit/components/places/tests/queries/test_results-as-date-query.js -f ~/code/fx-trunk/mozilla/tools/test-harness/xpcshell-simple/tail.js -f ~/code/fx-trunk/mozilla/tools/test-harness/xpcshell-simple/execute_test.js

Comment 1

[cmtalbert]

Hmmm...It's not completely broken.

It works if:
* There are no attributes specified on the nsINavHistoryQuery object.

It doesn't work if:
* There are any query attributes specified.  Even querying over a date range doesn't work-throws the same error. You'd expect that you'd need to include a date range in order to use the DATE-QUERY result type based on the description in the IDL.

Since it's not a completely broken feature, downgrading to NORMAL.

Comment 2

[cmtalbert]

This same syntax error (or at least it appears to be the same) occurs with the RESULTS_AS_DATE_SITE_QUERY.  This is likely the same issue in both cases.  Here is the log excerpt from the DATE_SITE_QUERY:

-1610559488[1c07310]: COMMIT TRANSACTION
-1610559488[1c07310]: BEGIN DEFERRED
-1610559488[1c07310]: Sqlite statement prepare error: 1 'near "ASCWHERE": syntax error'
-1610559488[1c07310]: Statement was: 'SELECT null, 'place:type=4&sort=1&beginTime='||beginTime||'&endTime='||endTime, dayTitle, null, null, endTime, null, null, null, null FROM (SELECT * FROM (SELECT 0 dayOrder, '0' dayRange, :dayTitle0 dayTitle, 1209625200000000 beginTime, 1209711600000000 endTime FROM  moz_historyvisits WHERE visit_date >= 1209625200000000 AND visit_date < 1209711600000000   AND visit_type NOT IN (0,4) LIMIT 1) TUNION0 UNION SELECT * FROM (SELECT 1 dayOrder, '1' dayRange, :dayTitle1 dayTitle, 1209538800000000 beginTime, 1209625200000000 endTime FROM  moz_historyvisits WHERE visit_date >= 1209538800000000 AND visit_date < 1209625200000000   AND visit_type NOT IN (0,4) LIMIT 1) TUNION1 UNION SELECT * FROM (SELECT 2 dayOrder, '2' dayRange, :dayTitle2 dayTitle, 1209452400000000 beginTime, 1209538800000000 endTime FROM  moz_historyvisits WHERE visit_date >= 1209452400000000 AND visit_date < 1209538800000000   AND visit_type NOT IN (0,4) LIMIT 1) TUNION2 UNION SELECT * FROM (SELECT 3 dayOrder, '3' dayRange, :dayTitle3 dayTitle, 1209366000000000 beginTime, 1209452400000000 endTime FROM  moz_historyvisits WHERE visit_date >= 1209366000000000 AND visit_date < 1209452400000000   AND visit_type NOT IN (0,4) LIMIT 1) TUNION3 UNION SELECT * FROM (SELECT 4 dayOrder, '4' dayRange, :dayTitle4 dayTitle, 1209279600000000 beginTime, 1209366000000000 endTime FROM  moz_historyvisits WHERE visit_date >= 1209279600000000 AND visit_date < 1209366000000000   AND visit_type NOT IN (0,4) LIMIT 1) TUNION4 UNION SELECT * FROM (SELECT 5 dayOrder, '5' dayRange, :dayTitle5 dayTitle, 1209193200000000 beginTime, 1209279600000000 endTime FROM  moz_historyvisits WHERE visit_date >= 1209193200000000 AND visit_date < 1209279600000000   AND visit_type NOT IN (0,4) LIMIT 1) TUNION5 UNION SELECT * FROM (SELECT 6 dayOrder, '6' dayRange, :dayTitle6 dayTitle, 1209106800000000 beginTime, 1209193200000000 endTime FROM  moz_historyvisits WHERE visit_date >= 1209106800000000 AND visit_date < 1209193200000000   AND visit_type NOT IN (0,4) LIMIT 1) TUNION6 UNION SELECT * FROM (SELECT 7 dayOrder, '7+' dayRange, :dayTitle7 dayTitle, 1 beginTime, 1209106800000000 endTime FROM  moz_historyvisits WHERE visit_date < 1209106800000000   AND visit_type NOT IN (0,4) LIMIT 1) TUNIONLAST ) TOUTER ORDER BY dayOrder ASCWHERE ( h.visit_count >=  :min_visits )'
-1610559488[1c07310]: WARNING: NS_ENSURE_SUCCESS(rv, rv) failed with result 0x80004005: file /Users/clint/code/fx-trunk/mozilla/toolkit/components/places/src/nsNavHistory.cpp, line 3550
-1610559488[1c07310]: ROLLBACK TRANSACTION
-1610559488[1c07310]: WARNING: NS_ENSURE_SUCCESS(rv, rv) failed with result 0x80004005: file /Users/clint/code/fx-trunk/mozilla/toolkit/components/places/src/nsNavHistoryResult.cpp, line 2347
-1610559488[1c07310]: WARNING: NS_ENSURE_SUCCESS(rv, rv) failed with result 0x80004005: file /Users/clint/code/fx-trunk/mozilla/toolkit/components/places/src/nsNavHistoryResult.cpp, line 2142

Once again, if you remove all the nsINavHistoryQuery attributes from the query, it works.

Comment 3

[Marco Bonardo [:mak] (Away Apr 25 - May 5)]

that's because it's trying to apply where clauses to the query, that is a containers query, instead it should propagate the options down to the child queries.

Comment 4

[Gervase Markham [:gerv]]

Bug 451915 - move Firefox/Places bugs to Firefox/Bookmarks and History. Remove all bugspam from this move by filtering for the string "places-to-b-and-h".

In Thunderbird 3.0b, you do that as follows:
Tools | Message Filters
Make sure the correct account is selected. Click "New"
Conditions: Body   contains   places-to-b-and-h
Change the action to "Delete Message".
Select "Manually Run" from the dropdown at the top.
Click OK.

Select the filter in the list, make sure "Inbox" is selected at the bottom, and click "Run Now". This should delete all the bugspam. You can then delete the filter.

Gerv

Comment 5

[BMO Automation]

Per policy at https://wiki.mozilla.org/Bug_Triage/Projects/Bug_Handling/Bug_Husbandry#Inactive_Bugs. If this bug is not an enhancement request or a bug not present in a supported release of Firefox, then it may be reopened.