Apache OpenOffice (AOO) Bugzilla – Issue 121492
Base can not filter by dates
Last modified: 2016-08-30 21:27:30 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.
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.
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.
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
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
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())
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.
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' )
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.
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??
(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.
Problem persists with version 4.0.0. FWIW same problem with LibreOffice 3.5.7.2 on Ubuntu 12.04.
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.
Thank you, hanya. This looks good, but how do I use/add the patch?
Someone to commit this patch?
So this patch didn't make it into 4.1.0 by the looks of it :(
Not commited in 4.1.1 Too bad :-/
I think if you set review flag on attachment to "?" it will bring more attention to the patch.
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.
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.
Fixed with hanya's patch.
(In reply to Kay from comment #20) > Fixed with hanya's patch. Thanks Kay for testing Could you set a target milestone?
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!
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
(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.
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.
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.
@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
(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.
Accepted as blocker for 4.1.2. The standard use case has been analyzed, discussed and confirmed resolved.
"kschenk" committed SVN revision 1695343 into branches/AOO410: #i121492# Merge r1626099 to AOO410 branch.
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.
@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.
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.
Created attachment 84915 [details] Database document (embedded HSQL) to test filtering by dates This is my test document (embedded HSQL) related to comment #33
it works with Version 4.1.2 RC 1 on 64 RPM
Closing.