Issue 121492 - Base can not filter by dates
Summary: Base can not filter by dates
Status: CLOSED FIXED
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: 3.4.1
Hardware: All All
: P3 Normal with 12 votes (vote)
Target Milestone: 4.1.2
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2012-12-17 08:16 UTC by Andreas Säger
Modified: 2016-08-30 21:27 UTC (History)
11 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: 4.1.1
Developer Difficulty: ---
pescetti: 4.1.2_release_blocker+


Attachments
Proposed patch to decompose and unquote column name before to find column (4.34 KB, patch)
2014-01-28 17:29 UTC, hanya
no flags Details | Diff
Database document (embedded HSQL) to test filtering by dates (63.25 KB, application/vnd.sun.xml.base)
2015-09-14 15:04 UTC, Andreas Säger
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description Andreas Säger 2012-12-17 08:16:29 UTC
The built-in filter methods (form based, dialog based) do not work with dates. You can only compare some date field with one date value and the equal operator (preferably per auto-filter method). It does not even recognize manually entered ISO date criteria.
Comment 1 Graham Horner 2013-01-23 18:04:53 UTC
Issue applies also to 3.4.1 in Win 7 64-bit, HSQL engine.  Entering date in any format in the Standard Filter dialog does not filter a table, even using the = operator. All records are returned.  On re-opening the Standard Filter dialog, all criteria are blank. Entering date in any format in the query wizard does not create a corresponding condition.  Only entering [operator]#dd/mm/yyyy# in a query design form has the expected result.

Exception to the above: when using the jdbc driver to MySQL, the Filter Criterion becomes '{"D yyyy-mm-dd"}' and sticks in the value box, but the table is still not filtered correctly.  No records are returned, even though there are matching dates.

Version 3.3 behaved as expected on XP and Win 7 64-bit.
Comment 2 y.siclon 2013-01-30 14:36:00 UTC
The date filter don't work in 3.4.1 on all platforms linux and win7 regardless 32 or 64 bit It's a very important bug for us I don't know we could use AOO for long time if this bug persist.
Comment 3 gg43 2013-02-19 00:29:58 UTC
Platform: 
Operating System: Windows 7 Home Premium Version 6.1.7601 Service Pack 1 Build 7601 (x64)
Apache Open Office 3.4.1 AOO341m1 Build 9593 Rev 1372282


I confirm. The standard filter on date fields does not work, it works  AutoFilter.
Set the standard filter in an identical manner to That generated by automatic filter does not execute any filter, the same behavior with a numeric field and value set to a string
.

Changed Platform to All
Comment 4 hanya 2013-02-19 15:11:23 UTC
As note: 
I got the following exception in ODatabaseForm::executeRowSet

Wrong data type: java.lang.IllegalArgumentException in statement [SELECT * FROM "Table1" WHERE ( "Table1"."DATE" = '{D ''2013-01-10'' }' )], 
SQLState: 37000
ErrorCode: -16
Comment 5 hanya 2013-02-20 16:41:52 UTC
I do not know '{D ''2013-01-10'' }' is correct SQL statement but 
once I remove braces and D it worked with HSQLDB.

Index: connectivity/source/parse/sqlnode.cxx
===================================================================
--- connectivity/source/parse/sqlnode.cxx	(revision 1447781)
+++ connectivity/source/parse/sqlnode.cxx	(working copy)
@@ -1016,10 +1016,10 @@
 {
     ::rtl::OUString aEmptyString;
     OSQLParseNode* pNewNode = new OSQLInternalNode(aEmptyString, SQL_NODE_RULE,OSQLParser::RuleID(OSQLParseNode::set_fct_spec));
-	pNewNode->append(new OSQLInternalNode(::rtl::OUString::createFromAscii("{"), SQL_NODE_PUNCTUATION));
+	//pNewNode->append(new OSQLInternalNode(::rtl::OUString::createFromAscii("{"), SQL_NODE_PUNCTUATION));
 	OSQLParseNode* pDateNode = new OSQLInternalNode(aEmptyString, SQL_NODE_RULE,OSQLParser::RuleID(OSQLParseNode::odbc_fct_spec));
 	pNewNode->append(pDateNode);
-	pNewNode->append(new OSQLInternalNode(::rtl::OUString::createFromAscii("}"), SQL_NODE_PUNCTUATION));
+	//pNewNode->append(new OSQLInternalNode(::rtl::OUString::createFromAscii("}"), SQL_NODE_PUNCTUATION));
 
 	switch (nType)
 	{
@@ -2464,8 +2464,17 @@
             if (rString.getLength())
                 rString.appendAscii(" ");
 
-            const ::rtl::OString sT = OSQLParser::TokenIDToStr(m_nNodeID, rParam.bInternational ? &rParam.m_rContext :  NULL);
-            rString.append(::rtl::OUString(sT,sT.getLength(),RTL_TEXTENCODING_UTF8));
+            switch ( m_nNodeID )
+            {
+                case SQL_TOKEN_D:
+                case SQL_TOKEN_T:
+                case SQL_TOKEN_TS:
+                    break;
+                default:
+                    const ::rtl::OString sT = OSQLParser::TokenIDToStr(m_nNodeID, rParam.bInternational ? &rParam.m_rContext :  NULL);
+                    rString.append(::rtl::OUString(sT,sT.getLength(),RTL_TEXTENCODING_UTF8));
+                    break;
+            }
         }   break;
         case SQL_NODE_STRING:
             if (rString.getLength())
Comment 6 Andreas Säger 2013-05-31 10:09:41 UTC
Hanya wrote:
> I do not know '{D ''2013-01-10'' }' is correct SQL statement but 
> once I remove braces and D it worked with HSQLDB.

According to the F1-help on queries (unaltered since version 1) his is what is supposed to work with Base:
<quote>
Date fields are represented as #Date# to clearly identify them as dates. The date condition will be reproduced in the resulting SQL statement in the following ODBC - compliant way:
Date
{D'YYYY-MM-DD'}
Date time
{D'YYYY-MM-DD HH:MM:SS'}
Time
{D'HH:MM:SS'}

OpenOffice.org also supports the following Escape sequences known from ODBC and JDBC:
Date
{d 'YYYY-MM-DD'}
Time
{t 'HH:MI:SS[.SS]'} - [ ] optional
DateTime
{ts 'YYYY-MM-DD HH:MI:SS[.SS]'} - [ ] optional
</quote>
The #Date# syntax is for the GUI only. It supports locale specific input.
Comment 7 Andreas Säger 2013-05-31 10:13:04 UTC
SELECT * FROM "Table1" WHERE ( "Table1"."DATE" = '{D ''2013-01-10'' }' )

has 2 pairs of quotes too many. It should be

SELECT * FROM "Table1" WHERE ( "Table1"."DATE" = {D '2013-01-10'} )

or simply

SELECT * FROM "Table1" WHERE ( "Table1"."DATE" = '2013-01-10' )
Comment 8 hanya 2013-06-01 12:45:43 UTC
Thank you for the explanation.

When the filter is used in the table view, new SQL statement having WHERE clause 
constructed in dbaui::DlgFilterCrit::BuildWherePart method.
getCondition method construct correct value for Date type field like: {D'2000/01/01'}.
Then the constructed criteria is passed to css.sdb.XSingleSelectQueryComposer::setStructuredFilter method. 
The passed argument is parsed in lcl_getCondition of SingleSelectQueryComposer imple. 
These instance of PropertyValues contains their field name as "TABLE_NAME"."FIELD_NAME", 
so the column can not be found to get correct data type of the field. 
If column is not found, parse::OParseColumn provides wrong data type, VARCHAR is chosen 
and single quotes are escaped with ''.

The candidate to fix is: 
to make the column found in lcl_getCondition function that helps to get 
correct type of the field from Type property.
Comment 9 Graham Horner 2013-06-01 15:58:14 UTC
Come on, guys.  Can this be so difficult?  It worked in 3.3.  Why was it changed, and why can't it be changed back??
Comment 10 Ralph 2013-06-18 11:41:03 UTC
(In reply to Graham Horner from comment #9)
> Come on, guys.  Can this be so difficult?  It worked in 3.3.  Why was it
> changed, and why can't it be changed back??

Yes, I entirely agree with this.  Date filtering is so important that I can't believe the problem exists in the first place, but having found the bug surely it should be fixed as soon as possible.  Please give this the highest priority.  Having not long 'upgrade' to 3.4 I now find a fundamental bug like this is messing up my mail-merges, so I have to develop work arounds which are annoying for users.
Comment 11 Graham Horner 2013-09-07 17:47:04 UTC
Problem persists with version 4.0.0.  FWIW same problem with LibreOffice 3.5.7.2 on Ubuntu 12.04.
Comment 12 hanya 2014-01-28 17:29:18 UTC
Created attachment 82423 [details]
Proposed patch to decompose and unquote column name before to find column

::dbtools::quoteName function does not escape the quote string and 
composing concatenates schema, table and column names with dot separator.
Extracting the required column name to get column from the index container, 
remove quote string and dot separator, last quoted item is the column name.
Comment 13 Ralph 2014-01-28 18:22:44 UTC
Thank you, hanya.  This looks good, but how do I use/add the patch?
Comment 14 oooforum (fr) 2014-05-12 14:03:35 UTC
Someone to commit this patch?
Comment 15 Graham Horner 2014-07-24 12:25:26 UTC
So this patch didn't make it into 4.1.0 by the looks of it :(
Comment 16 oooforum (fr) 2014-08-26 07:40:44 UTC
Not commited in 4.1.1
Too bad :-/
Comment 17 Kay 2014-09-15 23:14:51 UTC
I think if you set review flag on attachment to "?" it will bring more attention to the patch.
Comment 18 Kay 2014-09-16 22:53:53 UTC
More from me on this one.

* the patch is not applied in trunk so I will do this and test

* My date format is dd/mm/yy. 

* Using "Use Wizard to Create Query" against the date and just using a string like 01/01/01 as a date comparison without any other modifiers works fine.

* Using "Create Query in SQL" view ONLY works with comparison of formatting comparison date in "standard' format in quotes, i.e. '2001-01-01', even though my date field is defined differently.

* Using TO_DATE function in "Create Query in SQL" to attempt to use 01/01/01 instead of 2001-01-01 yields Access denied (????)

* Using filters in table view doesn't do anything.
Comment 19 Kay 2014-09-18 22:56:27 UTC
Good news! hanya's patch fixed the date filter problem for filters in Table view and has now been committed.

Once the buildbot's pick this up and process, you can verify from one of the builds from: http://ci.apache.org/projects/openoffice/

Please provide feedback so we can close this one.

Closing this out as resolved. And will be changing the help file as well since I'm not sure how the {D '2013-01-10'} syntax came about but this isn't typically how a date for comparison is specified for SQL nowadays.
Comment 20 Kay 2014-09-18 23:00:03 UTC
Fixed with hanya's patch.
Comment 21 oooforum (fr) 2014-09-23 13:40:44 UTC
(In reply to Kay from comment #20)
> Fixed with hanya's patch.
Thanks Kay for testing
Could you set a target milestone?
Comment 22 Andrea Pescetti 2015-07-24 21:18:51 UTC
For better evaluation for inclusion in 4.1.2, it would help very much if someone tested this with https://ci.apache.org/projects/openoffice/install/win/ (recent Windows development builds; we have Linux builds available too, see link by Kay above for full list) and confirmed that everything works as expected. Thanks!
Comment 23 mickey69 2015-07-27 08:50:16 UTC
I installed the 4.2.0 version proposed in https://ci.apache.org/projects/openoffice/install/win/ (O.S. Win 7 - 64 bit)
I installed the postgresql-sdbc-0.7.6b extension too, to connecting to my Postgresql 9.3 DBMS

In the query designer (Design View) I build a query like "select DateField from MyTable where DateField = '2015-01-01' " (I select by double click the field 'DateField' in the table 'MyTable' and I write '2015-01-01' in the Criterion cell)
Traslation in SQL code by OOBase is "SELECT "DateField" FROM "MyTable" WHERE "DateField" = {D '2015-01-01' }. Same traslation if in the query designer I write #2015-01-01#
Postgresql doesen't recognise this syntax ( {D '2015-01-01' } ); correct syntax may be '2015-01-01' or date '2015-01-01' 

Do I write something wrong in the designer query?
Maybe I can set some Base option?
Thank you in advance

Michela Piva
Comment 24 Kay 2015-07-27 22:28:19 UTC
(In reply to mickey69 from comment #23)
> I installed the 4.2.0 version proposed in
> https://ci.apache.org/projects/openoffice/install/win/ (O.S. Win 7 - 64 bit)
> I installed the postgresql-sdbc-0.7.6b extension too, to connecting to my
> Postgresql 9.3 DBMS
> 
> In the query designer (Design View) I build a query like "select DateField
> from MyTable where DateField = '2015-01-01' " (I select by double click the
> field 'DateField' in the table 'MyTable' and I write '2015-01-01' in the
> Criterion cell)
> Traslation in SQL code by OOBase is "SELECT "DateField" FROM "MyTable" WHERE
> "DateField" = {D '2015-01-01' }. Same traslation if in the query designer I
> write #2015-01-01#
> Postgresql doesen't recognise this syntax ( {D '2015-01-01' } ); correct
> syntax may be '2015-01-01' or date '2015-01-01' 
> 
> Do I write something wrong in the designer query?
> Maybe I can set some Base option?
> Thank you in advance
> 
> Michela Piva

hmmm...well this is not good.  Can you check the settings for your date field in the table you're using? When you open your DB, the tables should show up in the bottom pane. Just highlight (one click) on the table name, then use Edit from the toolbar to look at how your date field is defined. The syntax you used for your selection should work fine if your date field is defined this way. On tests I have done, no translation of the date as you indicate takes place.

I have no experience with postgresql or the sdbc extension you're using, though.  I hope this is not a factor.
Comment 25 Ralph 2015-07-28 10:08:04 UTC
I have downloaded the new OO 4.2.0.
My issue was when filtering by date in Writer when doing mail-merge/form letters.  After version 3.4 this no longer worked.
I am pleased to say that the problem is now fixed and date filtering in Writer now works OK.  Thank you for the fix.
Comment 26 Andreas Säger 2015-07-28 12:03:03 UTC
Some "parsed SQL" expression works in your test cases but fails when the backend is PostgreSQL? This would be bad indeed.

Originally, the "parsed SQL mode" was designed to work regardless of the database backend whereas "direct SQL mode" passed the literal SQL statement to the underlying database driver.

In parsed mode any of the following tokens used to be a literal date:
'2015-07-31' (quoted ISO string)
{D'2015-07-31'}
#07/31/2015# (with US locale)
#31/07/2015# (with other locales)
or any other expression between hashes according to the current locale including dates with 2-digit year.

Missing hashes in the GUI were complemented in the query designer, filter dialog, form based filter and parameter query input box. Likewise any missing single quotes are complemented when you type a literal string argument.
Comment 27 mickey69 2015-07-28 14:31:24 UTC
@Kay: Field name: DateField
Field type: Date [ date ] (entry required: no, Format example: 01/01/00, no default value)
If I open the table, I see correct date value.

@Andreas Säger: I try the same query in a Base DBMS table (no sdbc to another DBMS) and it works fine.
In the query designer the "parsed SQL mode" is always on... In effects parsed mode may work regardless of the database backend...
If I miss hashes writing the date in the GUI, they are correctly complemented (it works)
Maybe a problem in the postgresql sdbc extension??
Some advice about the best Open Office <-> postgresql connector?

When I run the query I receive this error: 
Error code: 1
pq_driver: [PGRES_FATAL_ERROR]ERRORE:  errore di sintassi a o presso "{" al carattere 83
 (caused by statement 'SELECT "DateField" FROM "base"."MyTable" AS "MyTable" WHERE "DateField" = {D '2015-01-01' }')

(Traslation: ERROR: syntax error at or near "{" at character 83..."

The query written with 'Design view mode' off, 'parsed SQL mode' on

SELECT "DateField" FROM "base"."MyTable" AS "MyTable" WHERE "DateField" = '2015-01-01'

works fine.

Thanks for your answers
Comment 28 Kay 2015-07-28 20:12:48 UTC
(In reply to mickey69 from comment #27)
> @Kay: Field name: DateField
> Field type: Date [ date ] (entry required: no, Format example: 01/01/00, no
> default value)
> If I open the table, I see correct date value.
> 
> @Andreas Säger: I try the same query in a Base DBMS table (no sdbc to
> another DBMS) and it works fine.
> In the query designer the "parsed SQL mode" is always on... In effects
> parsed mode may work regardless of the database backend...
> If I miss hashes writing the date in the GUI, they are correctly
> complemented (it works)
> Maybe a problem in the postgresql sdbc extension??
> Some advice about the best Open Office <-> postgresql connector?
> 
> When I run the query I receive this error: 
> Error code: 1
> pq_driver: [PGRES_FATAL_ERROR]ERRORE:  errore di sintassi a o presso "{" al
> carattere 83
>  (caused by statement 'SELECT "DateField" FROM "base"."MyTable" AS "MyTable"
> WHERE "DateField" = {D '2015-01-01' }')
> 
> (Traslation: ERROR: syntax error at or near "{" at character 83..."
> 
> The query written with 'Design view mode' off, 'parsed SQL mode' on
> 
> SELECT "DateField" FROM "base"."MyTable" AS "MyTable" WHERE "DateField" =
> '2015-01-01'
> 
> works fine.
> 
> Thanks for your answers

About the only way to check if it is a problem with the postgresql SDBC extension is to try to connect to your DB using another form of connector -- jdbc, etc. and see if the same problem occurs.

Thanks for your investigation on this.
Comment 29 Andrea Pescetti 2015-08-10 07:37:39 UTC
Accepted as blocker for 4.1.2. The standard use case has been analyzed, discussed and confirmed resolved.
Comment 30 SVN Robot 2015-08-11 18:04:12 UTC
"kschenk" committed SVN revision 1695343 into branches/AOO410:
#i121492# Merge r1626099 to AOO410 branch.
Comment 31 Andreas Säger 2015-09-12 15:28:34 UTC
One thing I encountered today in the mail merge dialog and in the data source window of AOO4.1.1:
Click a date value and then the magic filter button. The result is OK. It shows records of the same date as the selected value. When you call the standard filter dialog, you see the current filter criterion set by the magic filter as 

Date = #01.03.2015# (German locale).

Now edit the date value of this filter, click OK and the result set is empty. The filter criterion changed to something invalid like this: 
'{D ''2015-03-01''}' 
with 6 single quotes. 
I hope that this has been fixed as well.
Comment 32 Andrea Pescetti 2015-09-14 08:42:59 UTC
@Andreas: I can't check if the fix for this issue solves the other problem too, but you (or anyone who has some time for doing so) can check with the builds at
https://ci.apache.org/projects/openoffice/install/win/
to verify it. Otherwise, a separate issue must be opened for that.
Comment 33 Andreas Säger 2015-09-14 14:54:26 UTC
Thank you very much for fixing this nasty one.

My tests with AOO420m1(Build:9800)  -  Rev. 1702819
2015-09-14_03:50:18 - Rev. 1702851 and Linux(64)

Filtering dates works well with a registerd data source in the data source window and in the mail merge dialog.

1.7.99 snaps in as #01.07.1999# with German locale
7/1/99 snaps in as #07/01/1999# with US locale

It works equally well with grid views opened from the database window, with form based filters and dialog based filters ("standard filter") and when entering date arguments to the query designer.

Hard coded ISO date strings work well as in:
SELECT * FROM "TBL" WHERE "D" BETWEEN '1999-12-01' AND '2000-03-01'

BUT NOT any hashed dates:
SELECT * FROM "TBL" WHERE "D" BETWEEN #1999-12-01# AND #2000-03-01#
(I think this is perfectly fine since localized (hashed) dates should follow the locale of the current GUI and should not be hard coded in SQL)

I can substitute date parameters of parameter queries with localized dates and ISO dates as in
SELECT * FROM "TBL" WHERE "D" BETWEEN :Date1 AND :Date2
Parameter substitution works with subforms and with the built-in dialog.

Function call with parameter:
SELECT "TBL".* FROM "TBL" WHERE Month("D") = Month(:Param)

I can create reports from the above queries.

Right now, I don't know what else could be tested.
Comment 34 Andreas Säger 2015-09-14 15:04:06 UTC
Created attachment 84915 [details]
Database document (embedded HSQL) to test filtering by dates

This is my test document (embedded HSQL) related to comment #33
Comment 35 Mechtilde 2015-10-04 08:30:06 UTC
it works with Version 4.1.2 RC 1 on 64 RPM
Comment 36 Kay 2016-08-30 21:27:30 UTC
Closing.