You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@openoffice.apache.org by bu...@apache.org on 2012/12/17 09:16:29 UTC
[Bug 121492] New: Base can not filter by dates
https://issues.apache.org/ooo/show_bug.cgi?id=121492
Bug ID: 121492
Issue Type: DEFECT
Summary: Base can not filter by dates
Classification: Application
Product: database access
Version: AOO 3.4.1
Hardware: All
OS: Linux
Status: UNCONFIRMED
Severity: normal
Priority: P3
Component: code
Assignee: issues@openoffice.apache.org
Reporter: villeroy@t-online.de
CC: issues@openoffice.apache.org
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.
--
You are receiving this mail because:
You are on the CC list for the bug.
You are the assignee for the bug.
[Bug 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
hanya <ha...@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |hanya.runo@gmail.com
--- Comment #4 from hanya <ha...@gmail.com> ---
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
--
You are receiving this mail because:
You are on the CC list for the bug.
You are the assignee for the bug.
[Bug 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
--- Comment #11 from Graham Horner <g...@hornercs.co.uk> ---
Problem persists with version 4.0.0. FWIW same problem with LibreOffice
3.5.7.2 on Ubuntu 12.04.
--
You are receiving this mail because:
You are on the CC list for the bug.
You are the assignee for the bug.
You are watching all bug changes.
[Issue 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
oooforum <oo...@free.fr> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |oooforum@free.fr
--- Comment #14 from oooforum <oo...@free.fr> ---
Someone to commit this patch?
--
You are receiving this mail because:
You are on the CC list for the issue.
You are the assignee for the issue.
You are watching all issue changes.
[Bug 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
gg43 <ge...@yahoo.it> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|UNCONFIRMED |CONFIRMED
CC| |george.gentili@yahoo.it
Latest|--- |AOO 3.4.1
Confirmation on| |
QA Contact| |george.gentili@yahoo.it
Ever confirmed|0 |1
OS|Linux |All
--- Comment #3 from gg43 <ge...@yahoo.it> ---
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
--
You are receiving this mail because:
You are on the CC list for the bug.
You are the assignee for the bug.
[Issue 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
Kay <ks...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Latest|3.4.1 |4.1.1
Confirmation on| |
Target Milestone|--- |4.2.0
--
You are receiving this mail because:
You are on the CC list for the issue.
You are the assignee for the issue.
You are watching all issue changes.
[Bug 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
--- Comment #5 from hanya <ha...@gmail.com> ---
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())
--
You are receiving this mail because:
You are on the CC list for the bug.
You are the assignee for the bug.
[Bug 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
--- Comment #8 from hanya <ha...@gmail.com> ---
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.
--
You are receiving this mail because:
You are on the CC list for the bug.
You are the assignee for the bug.
[Bug 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
--- Comment #9 from Graham Horner <g...@hornercs.co.uk> ---
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??
--
You are receiving this mail because:
You are on the CC list for the bug.
You are the assignee for the bug.
[Issue 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
gg43 <ge...@yahoo.it> changed:
What |Removed |Added
----------------------------------------------------------------------------
QA Contact|george.gentili@yahoo.it |
--
You are receiving this mail because:
You are on the CC list for the issue.
You are the assignee for the issue.
You are watching all issue changes.
[Issue 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
--- Comment #18 from Kay <ks...@apache.org> ---
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.
--
You are receiving this mail because:
You are on the CC list for the issue.
You are the assignee for the issue.
You are watching all issue changes.
[Issue 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
Kay <ks...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|CONFIRMED |RESOLVED
Resolution|--- |FIXED
--- Comment #20 from Kay <ks...@apache.org> ---
Fixed with hanya's patch.
--
You are receiving this mail because:
You are on the CC list for the issue.
You are the assignee for the issue.
You are watching all issue changes.
[Bug 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
--- Comment #7 from Andreas Säger <vi...@t-online.de> ---
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' )
--
You are receiving this mail because:
You are on the CC list for the bug.
You are the assignee for the bug.
[Bug 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
y.siclon@gmail.com changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |y.siclon@gmail.com
--- Comment #2 from y.siclon@gmail.com ---
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.
--
You are receiving this mail because:
You are on the CC list for the bug.
You are the assignee for the bug.
[Issue 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
--- Comment #21 from oooforum <oo...@free.fr> ---
(In reply to Kay from comment #20)
> Fixed with hanya's patch.
Thanks Kay for testing
Could you set a target milestone?
--
You are receiving this mail because:
You are on the CC list for the issue.
You are the assignee for the issue.
You are watching all issue changes.
[Issue 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
--- Comment #15 from Graham Horner <g...@hornercs.co.uk> ---
So this patch didn't make it into 4.1.0 by the looks of it :(
--
You are receiving this mail because:
You are on the CC list for the issue.
You are the assignee for the issue.
You are watching all issue changes.
[Issue 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
--- Comment #16 from oooforum <oo...@free.fr> ---
Not commited in 4.1.1
Too bad :-/
--
You are receiving this mail because:
You are on the CC list for the issue.
You are the assignee for the issue.
You are watching all issue changes.
[Bug 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
Graham <g...@hornercs.co.uk> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |g@hornercs.co.uk
--- Comment #1 from Graham <g...@hornercs.co.uk> ---
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.
--
You are receiving this mail because:
You are on the CC list for the bug.
You are the assignee for the bug.
[Bug 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
--- Comment #6 from Andreas Säger <vi...@t-online.de> ---
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.
--
You are receiving this mail because:
You are on the CC list for the bug.
You are the assignee for the bug.
[Bug 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
ralph@timms1.net changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |ralph@timms1.net
--- Comment #10 from ralph@timms1.net ---
(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.
--
You are receiving this mail because:
You are on the CC list for the bug.
You are the assignee for the bug.
[Issue 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
Kay <ks...@apache.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |kschenk@apache.org
--- Comment #17 from Kay <ks...@apache.org> ---
I think if you set review flag on attachment to "?" it will bring more
attention to the patch.
--
You are receiving this mail because:
You are on the CC list for the issue.
You are the assignee for the issue.
You are watching all issue changes.
[Bug 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
--- Comment #13 from ralph@timms1.net ---
Thank you, hanya. This looks good, but how do I use/add the patch?
--
You are receiving this mail because:
You are on the CC list for the bug.
You are the assignee for the bug.
You are watching all bug changes.
[Bug 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
--- Comment #12 from hanya <ha...@gmail.com> ---
Created attachment 82423
--> https://issues.apache.org/ooo/attachment.cgi?id=82423&action=edit
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.
--
You are receiving this mail because:
You are on the CC list for the bug.
You are the assignee for the bug.
You are watching all bug changes.
[Issue 121492] Base can not filter by dates
Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=121492
--- Comment #19 from Kay <ks...@apache.org> ---
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.
--
You are receiving this mail because:
You are on the CC list for the issue.
You are the assignee for the issue.
You are watching all issue changes.