Issue 107104

Summary:

query design change SQL string (was:Subform binding fails with stamp field.)

Product:

Base

Reporter:

villeroy <villeroy>

Component:

code

Assignee:

AOO issues mailing list <issues>

Status:

CONFIRMED ---

QA Contact:

Severity:

Trivial

Priority:

CC:

issues, r4zoli

Version:

OOo 3.1.1

Keywords:

oooqa

Target Milestone:

---

Hardware:

Unknown

OS:

All

Issue Type:

DEFECT

Latest Confirmation in:

---

Developer Difficulty:

---

Attachments:

Description	Flags
List of integer values with running totals	none

Description villeroy 2009-11-21 22:54:07 UTC

Refer to the attached database.
- Form1 lets you add integers to a list with auto-ID and auto-stamp.
- Form2 fails to show the running total based on the order of stamps (subform on
Query1).
- Form3 demonstrates that it works with running totals based on the order of IDs
(subform on Query2).

Comment 1 villeroy 2009-11-21 22:55:33 UTC

Created attachment 66232 [details]
List of integer values with running totals

Comment 2 r4zoli 2009-11-22 07:58:32 UTC

This is possibly problem around query.
I found problems with Query1:

Query1, open in SQL view:
SELECT "a1"."Stamp", SUM( "a2"."Value" ) AS "Running_Total" FROM "Stamps" AS
"a1", "Stamps" AS "a2" WHERE "a1"."Stamp" >= "a2"."Stamp" GROUP BY "a1"."Stamp"
ORDER BY "a1"."Stamp" ASC
Run Query without problem.

Switch design view on run Query, it fails with: The data content can not be loaded.
Switch design view off, the Query1 changes, to:
SELECT "a1"."Stamp", SUM( "a2"."Value" ) AS "Running_Total" FROM "Stamps" AS
"a1", "Stamps" AS "a2" GROUP BY "a1"."Stamp" HAVING ( ( "a1"."Stamp" >=
"a2"."Stamp" ) ) ORDER BY "a1"."Stamp" ASC
Run Query1, it fails with: The data content can not be loaded.

Query 2, open for edit in SQL view:
SELECT "a1"."ID", SUM( "a2"."Value" ) AS "Running_Total" FROM "Stamps" AS "a1",
"Stamps" AS "a2" WHERE "a1"."ID" >= "a2"."ID" GROUP BY "a1"."ID" ORDER BY
"a1"."ID" ASC
It runs without problem.

Switch design view on, the query2 runs, shows only last calculation results.

Switch design view off the Query2 changes, to:
SELECT "a1"."ID", SUM( "a2"."Value" ) AS "Running_Total" FROM "Stamps" AS "a1",
"Stamps" AS "a2" GROUP BY "a1"."ID" HAVING ( ( "a1"."ID" >= "a2"."ID" ) ) ORDER
BY "a1"."ID" ASC
It runs, shows only last calculation results.

Same results in OOO320_m5, on winXP.

Comment 3 marc.neumann 2009-11-23 07:21:48 UTC

confirm, set target and send to the right developer. The same error happens also
in OOO 3.0.1.

Comment 4 ocke.janssen 2009-11-23 09:33:39 UTC

Fixed in cws dba33d

Comment 5 ocke.janssen 2009-12-21 11:04:53 UTC

Please verify. Thanks.

@msc: The query does not mix where and having anymore. There seems to be a
problem with filtering date/times. Could you please check if this also exists
for other databases as well. Thanks.

Comment 6 marc.neumann 2010-01-20 09:15:18 UTC

reopen because it's not fixed.

The Form2 still not work as described in the issue.

Comment 7 marc.neumann 2010-01-20 09:16:46 UTC

reassign to oj

Comment 8 ocke.janssen 2010-01-21 10:58:29 UTC

I have to change the target to 3.x because currently it isn't possible to handle
DateTime correctly. The struct com.sun.star.util.DateTime can only handle
hundredth of seconds which isn't precise enough. We need mili seconds. So we
have to change the struct or do something totally different. But to change the
struct would imply to make the UNO API incompatible.

The 2nd issue inside this one (th eproblem with the query) is fixed.