Issue 117214

Could you add example file with this data?

Created attachment 76007 [details]
Test ODB with illustrative data

Added test.odb with illustrative timestamp data

@r4zoli :
Test db file added.

Alex

@ wurzel
This odb file not contain any timestamp field in any table.

The DD/MM/YY date format is not exact the YY part is amigous, 99 could be 1899, 1999 etc. and depend on local settings.

In hsqldb inside stored in ISO format (YYYY-MM-DD), date/timestamp fields could be processed correctly with CAST. 

On win7 with my data no such problem, with cast and Hungarian locale.

@r4zoli :

I have just checked the file I uploaded to the issue from my hard disk called test.odb. It has 3 tables in it :

PayData_T
Table1
Table2
timeout

The table "timeout" is the one with the timestamp data in it.


I also have 2 queries in this file :
Query1
QueryDate

QueryDate is the query that shows the odd behaviour.


However, I also downloaded the same file from IZ just to check since I didn't understand why you couldn't see the data. When I opened this downloaded file in OOo 3.3.0, the table "timeout" was no longer visible !!! The data is there, but you can not see it, as a result of another separate bug that modifies the properties of the file (hsqldb version number). I noticed the same issue when I tried to open the file in NeoOffice 3.1.2 patch 4. 

Here is a copy of the SCRIPT file from the ODB :

SET DATABASE COLLATION "French"
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE CACHED TABLE "PayData_T"(ID INTEGER NOT NULL PRIMARY KEY,"Hours" DECIMAL(8,2),"PayRate" DECIMAL(8,2),"Dept" VARCHAR(1))
CREATE CACHED TABLE "Table1"("ID" INTEGER NOT NULL PRIMARY KEY,"id" DOUBLE NOT NULL,"Value" VARCHAR(10))
CREATE CACHED TABLE "Table2"("ID1" INTEGER NOT NULL PRIMARY KEY,"ID" VARCHAR(255),"name1" VARCHAR(255),"name2" VARCHAR(255),"integer" VARCHAR(255),"decimal" VARCHAR(255),"bool" VARCHAR(255),"date" VARCHAR(255),"time" VARCHAR(255))
CREATE CACHED TABLE "timeout"("id" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"dt" TIMESTAMP(0) NOT NULL)
SET TABLE "PayData_T" INDEX'88 0'
SET TABLE "Table1" INDEX'360 0'
SET TABLE "Table2" INDEX'680 0'
SET TABLE "timeout" INDEX'1472 5'
ALTER TABLE "timeout" ALTER COLUMN "id" RESTART WITH 5
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 60




Alex

I could have set the oooqa keyword myself, but there is obviously a problem with the file if no one else can see the data I have put it in.

As for needmoreinfo, what more info can I give, this is the file in which the problem was demonstrated, I didn't just make it up out thin air. I can see the complete data in OOo 3.3.0, but not in NeoOffice 3.1.2 patch 4, nor in OOo 3.2.


Alex

Please create another example file, this one not contain "timeout" table.
I checked the script of odb file.

This could be caused by uploading file without closing it, after you added the "timeout" table.

After you add new table, close file fully or close OOo before you upload it.
Sometimes the OOo keep files locked until OOo shut down fully, including quick-starter.

(In reply to comment #7)

> 
> This could be caused by uploading file without closing it, after you added the
> "timeout" table.
> 
> After you add new table, close file fully or close OOo before you upload it.
> Sometimes the OOo keep files locked until OOo shut down fully, including
> quick-starter.


There is no quick starter on Mac. I have been using StarOffice, then OOo or NeoOffice for over 15 years now on various OSes, have worked on QA for OOo on and off for quite a few years now, so I think I understand whether or not I've shut it down properly before messing about with file uploads.

If you can not see the data, and even the script file makes no mention of the table, then it is OOo that is causing that data not to show up in the file uploaded. Why OOo should store some of the table / data information somewhere else and not in the ODB file itself is a bug.

My understanding of the spec is that all of this is stored in memory until the database is saved and the file closed, then OOo closed. As I systematically work in this way with the HSQLDB databases, there shouldn't be any other data floating around in XCU files or in some configuration file of OOo which only gets loaded again when I reload the ODB file into OOo. But I digress, and this is cause for a separate bug issue. I know I am not the only one I have seen this happen to, since it has been reported on the user mailing lists, and I believe that even Frank Schoenheit mentioned something about cache configuration issues being a possible cause for this behaviour. Anyway, as I said, this is a separate issue.

I will recreate a db and upload it.

Alex

Created attachment 76031 [details]
another test db

added a second test db

Created attachment 76032 [details]
screenshot parse on results displayed

screenshot of results as displayed with SQL parser on

Created attachment 76033 [details]
screenshot parser off results displayed

screeenshot of results displayed with SQL parser off

I see that your main problem in that OOo Base query UI format query result as text, not date as required by CAST( some_timestamp_value AS DATE) function.

This not happens in SQL Direct mode. 

In second example file I not see the date difference mentioned in first post.

Created attachment 76037 [details]
another test db illustrating date miscalculation

another test db illustrating date miscalculation

Created attachment 76038 [details]
screenshot of table data in testdt.odb

screenshot of actual table data in testdt.odb

Created attachment 76039 [details]
screenshot of query with miscalculated date

screenshot of query with miscalculated date

@r4zoli :

I have provided another db, called testdt.odb in which the date miscalculation problem can be seen, as illustrated by the accompanying screenshots.



Alex

The secondly attached database query shows correctly the "timeout" table content.

If you format the table YYYY-MM-DD HH:MM:SS format you can see same results as you can see in screenshot.


How you added the timestamp data into dt table?
The 99// converted to 9999- - during data input.

getting rid of value "enhancement" for field "severity".
For enhancement the field "issue type" shall be used.

This Issue requires more information ('needmoreinfo'), but has not been updated
within the last year. Please provide feedback as requested and re-test with the the latest version of OpenOffice - the problem(s) may already be addressed. 

You can download Apache OpenOffice 3.4.1 from http://www.openoffice.org/download

Please report back the outcome of your testing, so this Issue may be closed or
progressed as necessary - otherwise the issue may be Resolved as Invalid in the
future.