Apache OpenOffice (AOO) Bugzilla – Issue 97159
Query doesn't work any more
Last modified: 2009-02-25 09:00:31 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.
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.
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 ...
> 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?
ping
I know :( I've not enough time to look for it :-(
Created attachment 59852 [details] SQL script to create the needed table structure in a MySQL database
Created attachment 59853 [details] document to reproduce the bug case
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.
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.
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.
Fixed in cws dba31i.
extended test case complex.dbaccess.Parser with method 'checkParameterTypes', to catch this kind of errors in the future.
Please verify. Thanks.
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: http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=DEV300%2Fdba31i
Hi, this is fixed in master OOO310_m2. I close this issue now. Bye Marc