HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Discuss the database features
Post Reply
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Post by F3K Total »

Hello,
when i use this query:

Code: Select all

SELECT DISTINCT CURRENT_USER, CURRENT_TIMESTAMP FROM INFORMATION_SCHEMA.SYSTEM_USERS
using HSQLDB 2.3.4, i get with AOO 4.1.3, jre1.8.0_131 the following result, as expected:
TS.PNG
TS.PNG (1.29 KiB) Viewed 11042 times
Using newest HSQLDB 2.4.0, the TIMESTAMP is missing:
TS2.PNG
TS2.PNG (939 Bytes) Viewed 11042 times
Does anyone have the same problem? What do i do wrong?
R
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Post by Villeroy »

I can confirm the problem with the latest version of HSQL.
NOW() works for me.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Post by Sliderule »

Just as an FYI ( For Your Information ) . . . in to the wise advice given by Villerory . . . using other functions will also work:

Code: Select all

-- This works WITH or WITHOUT the Base Parser ( Run SQL command directly )
SELECT DISTINCT 
   CURRENT_USER, 
   CAST(CURRENT_TIMESTAMP AS TIMESTAMP) AS "CAST_CUR_TS", 
   NOW() AS "NOW"
FROM INFORMATION_SCHEMA.SYSTEM_USERS

Code: Select all

-- This ONLY works WITHOUT the Base Parser ( Run SQL command directly )
SELECT DISTINCT 
   CURRENT_USER, 
   CAST(CURRENT_TIMESTAMP AS TIMESTAMP) AS "CAST_CUR_TS", 
   LOCALTIMESTAMP AS "LOCALTIMESTAMP", -- Because using this function MUST Run SQL command directly
   NOW() AS "NOW"
FROM INFORMATION_SCHEMA.SYSTEM_USERS
Explanation: Since CURRENT_TIMESTAMP , with HSQL 2.4.0 is returning a TIMESTAMP with TIMEZONE per SQL / Java standards, Base parser does not accept this.

Work arounds include:
  1. As Villeroy suggested, use the NOW() HSQL function
  2. Use CAST and CURRENT_TIMESTAMP to a TIMESTAMP , for example: CAST(CURRENT_TIMESTAMP AS TIMESTAMP)
  3. Use LOCALTIMESTAMP , but, since Base Parser does not recognise this command, it can only be used withOUT the Base Parser
Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Last edited by Sliderule on Sun Jun 18, 2017 4:34 pm, edited 1 time in total.
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Post by chrisb »

there is a similar issue with UNION when using hsqldb 2.4.0.
 Edit: the image below shows HSQLDB 2.3.4 
2.3.4.PNG
2.3.4.PNG (17.2 KiB) Viewed 11009 times
 Edit: the image below shows HSQLDB 2.4.0 
2.4.0.PNG
2.4.0.PNG (14.28 KiB) Viewed 11009 times
Last edited by chrisb on Sun Jun 18, 2017 4:49 pm, edited 1 time in total.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Post by Sliderule »

chrisb wrote:there is a similar issue with UNION when using hsqldb 2.4.0.
I am not clear, with your images . . . which graphic is HSQL 2.3.4 vs HSQL 2.4.0 . . . but . . . ¿ what happens if you change the Query by making it UNION ALL rather than just UNION ?

Sliderule
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Post by chrisb »

the result is always the same whether using UNION or UNION ALL.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Post by UnklDonald418 »

I can confirm the claim by chrisb
there is a similar issue with UNION when using hsqldb 2.4.0
the result is always the same whether using UNION or UNION ALL
My queries using UNION and UNION ALL work fine using hsqldb 2.3.x but using 2.4.0 they return no results.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Post by Sliderule »

chrisb:

I would like to confirm you comment above about HSQL 2.4.0 and the use of both UNION and UNION ALL Queries not returning data as it should.

Just as an FYI ( For Your Information ) . . . using HSQL 2.4.0 with other database front-ends, all work well with UNION commands, including:
  1. HSQL Database Manager
  2. HSQL SqlTool.jar
  3. SQL Workbench/J
Put another way, I have no idea why the database back-end works as it should with other database back-end tools, but, NOT with either OpenOffice nor LibreOffice . :knock:

Just as a curious ( AKA - Also Known As - odd ) item when using LibreOffice . . . when running the Select statement with UNION ALL or UNION, . . . NOT . . . with the Base Query sub-system, but, rather from the Menu: Tools -SQL... and with Show output of "select" statements ticked . . . the results are displayed as it should be. :crazy:

Sliderule
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Post by Villeroy »

LibreOffice has an option "Show output of SELECT statements" in its SQL tool. It displays a textual representation of selected data. There you can see what you expect to see in the Base widgets.

Code: Select all

SELECT DISTINCT CURRENT_USER, CURRENT_TIMESTAMP FROM INFORMATION_SCHEMA.SYSTEM_USERS
'---------------------------------------------------------
SA,2017-06-19 12:46:19.660000+2:00,
indeed, there is a timezone info I never noticed before.

Oh, and then I tried to create a report from a UNION SELECT with LO 5.2 and HSQL 2.4. A simple 3-click report from the wiz shows all records. The same UNION SELECT stored in a direct mode query raises an error on report creation.
The view displays all the records.
The direct SQL query displays no records.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Post by eremmel »

Interesting in case of CURRENT_TIMESTAMP is bugfix 1450, it talks about a parameter hsqldb.translate_dti_types. Not sure if that might help.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Post by Villeroy »

translate_dti_types=true|false has no visible effect when used as argument in the connection-URL
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Post by F3K Total »

Hello,
thank you all for your suggestions an workarounds!
But this is the main issue:
Sliderule wrote:Just as an FYI ( For Your Information ) . . . using HSQL 2.4.0 with other database front-ends, all work well with UNION commands, including:

HSQL Database Manager
HSQL SqlTool.jar
SQL Workbench/J

Put another way, I have no idea why the database back-end works as it should with other database back-end tools, but, NOT with either OpenOffice nor LibreOffice . :knock:
:( :( :(
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Post by Sliderule »

Just as an FYI ( For Your Information ) . . . I found a work-around, using either OpenOffice Query, and/or LibreOffice Query with HSQL Version 2.4.0 ( requires Java 1.8 ). Consider the following Query . . . AND . . . it can be run ( used ) either with OR withOUT the Base Parser ( therefore, a Parameter Query could be used - see the second Query). :super:

Try running the following Query . . .

Code: Select all

Select 
   CAST(CURRENT_TIMESTAMP AS TIMESTAMP WITHOUT TIME ZONE) AS "CURRENT_TIMESTAMP", 
   A.* 
From (
      Select * From INFORMATION_SCHEMA.SYSTEM_TABLES Where TABLE_SCHEM = CURRENT_SCHEMA
         UNION ALL 
      Select * From INFORMATION_SCHEMA.SYSTEM_TABLES Where TABLE_SCHEM = 'INFORMATION_SCHEMA'
     ) AS A

Code: Select all

-- This is a Parameter Query with UNION ALL - answer PUBLIC when prompted
Select 
   CAST(CURRENT_TIMESTAMP AS TIMESTAMP WITHOUT TIME ZONE) AS "CURRENT_TIMESTAMP", 
   A.* 
From (
      Select * From INFORMATION_SCHEMA.SYSTEM_TABLES Where TABLE_SCHEM = :Enter_Schema_Name
         UNION ALL 
      Select * From INFORMATION_SCHEMA.SYSTEM_TABLES Where TABLE_SCHEM = 'INFORMATION_SCHEMA'
     ) AS A
Explanation: Note the following:
  1. In order for it ( Base Query sub-system ) to accept CURRENT_TIMESTAMP, use CAST for it to return the value withOUT TIME ZONE
  2. In order for it (Base Query sub-system ) to return results of either a UNION OR UNION ALL statement, define it with an alias table name ( above example, the alias table name is A )
  3. The second Query is an example of Base Parser with a Parameter Query, while also using UNION ALL. :lol:
Question: I have NOT reported this bug to either the OpenOffice, NOR the LibreOffice developers. ¿ Have you ? :?:

To user chrisb . . . in your post above about UNION . . . please try the following Query with HSQL 2.4.0:

Code: Select all

Select A.* From 
(select "Name", "ID" from "tSeason"
union
select "Swim", ID from "tSwim") AS A
Sliderule
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Post by Villeroy »

Sliderule wrote:The second Query is an example of Base Parser with a Parameter Query, while also using UNION ALL. :lol:
Luckily there are people like you who try harder than others.
This could be a solution for certain "impossible" queries when used with parent and child forms. Until now I throught there is no way to use a UNION or any other kind of direct SQL with subforms.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Post by chrisb »

Sliderule, sorry for the late reply (been on holiday).

your work around when using UNION or UNION ALL is effective (well done).

i have filed a bug report with Apache OpenOffice (AOO) Bugzilla. ref: 127465 3rd July 2017.
i have not reported the issue to Libre Office as i do not use it & have no way of testing their product.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Post by Sliderule »

chrisb wrote: i have filed a bug report with Apache OpenOffice (AOO) Bugzilla. ref: 127465 3rd July 2017.
i have not reported the issue to Libre Office as i do not use it & have no way of testing their product.
Thank-you very much for posting the bug report at Apache OpenOffice Bugzilla. The web site is:

https://bz.apache.org/ooo/show_bug.cgi?id=127465

I added a comment, and, hope others will do the same.

Sliderule
Post Reply