Issue 97159 - Query doesn't work any more
Summary: Query doesn't work any more
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: DEV300m35
Hardware: All All
: P2 Trivial (vote)
Target Milestone: OOo 3.1
Assignee: marc.neumann
QA Contact: issues@dba
Keywords: regression, release_blocker
Depends on:
Blocks: 95768
  Show dependency tree
Reported: 2008-12-11 21:25 UTC by Mechtilde
Modified: 2009-02-25 09:00 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

SQL script to create the needed table structure in a MySQL database (1.88 KB, application/x-sql-script)
2009-02-03 10:56 UTC, Frank Schönheit
no flags Details
document to reproduce the bug case (4.19 KB, application/vnd.sun.xml.base)
2009-02-03 10:57 UTC, Frank Schönheit
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description Mechtilde 2008-12-11 21:25:29 UTC
A query working more than 3 1/2 year doesn't work any more with version DEV300_m35 

SELECT `Konten`.`Kontonummer`, `Konten`.`Kontoname`, SUM(
`Uberweisung`.`SollEUR` ), SUM( `Uberweisung`.`HabenEUR` ) FROM
`Hauptbuch2008`.`Uberweisung` AS `Uberweisung`, `Hauptbuch2008`.`Konten` AS
`Konten` WHERE `Uberweisung`.`Kontenbezeichnung` = `Konten`.`ID` AND
`Uberweisung`.`Uberweisungsdatum` >= :Anfangsdatum AND
`Uberweisung`.`Uberweisungsdatum` <= :Enddatum GROUP BY `Konten`.`Kontonummer`,
`Konten`.`Kontoname` HAVING ( ( `Konten`.`Kontonummer` <> 'Null' ) )

If I execute the query the parameters are not observed.
The parameter should collect all informations e.g for one month but this is ignored

At this time I have no idea to give more information
Please ask for them.
Comment 1 Mechtilde 2008-12-16 21:26:44 UTC
I tried to use this query with an example HSQL DB. But this doesn't work.

It seems that this is a Problem of using mysql with JDBC.
Comment 2 Frank Schönheit 2008-12-17 13:13:16 UTC
the table structure for the two tables Uberweisung and Konten would be helpful
in reproducing the problem.

Alternatively, if you can strip down this query to a shorter one, which also
fails ...
Comment 3 Frank Schönheit 2008-12-17 13:14:41 UTC
> I tried to use this query with an example HSQL DB. But this doesn't work.

What do you mean with "it doesn't work here"? Is it that the problem does not
happen with HSQLDB?
Comment 4 Frank Schönheit 2009-02-02 13:00:34 UTC
Comment 5 Mechtilde 2009-02-02 13:13:35 UTC
I know :(

I've not enough time to look for it :-(
Comment 6 Frank Schönheit 2009-02-03 10:56:49 UTC
Created attachment 59852 [details]
SQL script to create the needed table structure in a MySQL database
Comment 7 Frank Schönheit 2009-02-03 10:57:30 UTC
Created attachment 59853 [details]
document to reproduce the bug case
Comment 8 Frank Schönheit 2009-02-03 10:59:04 UTC
Thanks to the HSQL version you sent me, I was able to reproduce this with MySQL.
Attached is an SQL script to create and populate the necessary tables in a MySQL
database, and a database document accessing those tables. The query contained in
the DB doc shows the problem: In OOo 3.0, it produces a non-empty result, in
DV300.m40, it doesn't.
Comment 9 Frank Schönheit 2009-02-03 11:01:12 UTC
the problem here is that the parameter is not recognized as date parameter
anymore, but treated as string. This can also be observed in the parameter
dialog: In 3.0, an input such as "1.1.2000" is changed to "#01.01.2000#", which
is a correct date notation. In m40, it is changed to "'1.1.2000'", which
indicates it is treated as string, not as date.
Comment 10 Frank Schönheit 2009-02-03 11:22:06 UTC
fs->oj: as talked about: OSQLParseTreeIterator::traverseParameters does not
properly extract the column name / table range of the parameter columns, thus
the subsequent traverseParameter does not find the respective column, and falls
back to assuming a string parameter. Seems to be a regression of fixing issue 91208.
Comment 11 ocke.janssen 2009-02-03 11:42:33 UTC
Fixed in cws dba31i.
Comment 12 Frank Schönheit 2009-02-03 13:46:02 UTC
extended test case complex.dbaccess.Parser with method 'checkParameterTypes', to
catch this kind of errors in the future.
Comment 13 ocke.janssen 2009-02-11 07:54:32 UTC
Please verify. Thanks.
Comment 14 marc.neumann 2009-02-11 13:50:50 UTC
verified in CWS dba31i

find more information about this CWS, like when it is available in the master
builds, in EIS, the Environment Information System:
Comment 15 marc.neumann 2009-02-25 09:00:31 UTC

this is fixed in master OOO310_m2.

I close this issue now.

Bye Marc