HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Discuss the database features

HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Postby F3K Total » Sun Jun 18, 2017 11:59 am

Hello,
when i use this query:
Code: Select all   Expand viewCollapse view
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 5118 times
Using newest HSQLDB 2.4.0, the TIMESTAMP is missing:
TS2.PNG
TS2.PNG (939 Bytes) Viewed 5118 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
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Postby Villeroy » Sun Jun 18, 2017 12:29 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27733
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Postby Sliderule » Sun Jun 18, 2017 3:21 pm

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

Code: Select all   Expand viewCollapse view
-- 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   Expand viewCollapse view
-- 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.
User avatar
Sliderule
Volunteer
 
Posts: 1205
Joined: Thu Nov 29, 2007 9:46 am

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Postby chrisb » Sun Jun 18, 2017 4:28 pm

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 5085 times

 Edit: the image below shows HSQLDB 2.4.0 
2.4.0.PNG
2.4.0.PNG (14.28 KiB) Viewed 5085 times
Last edited by chrisb on Sun Jun 18, 2017 4:49 pm, edited 1 time in total.
open office 4.1.7 & LibreOffice 6.3.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 207
Joined: Mon Jun 07, 2010 4:16 pm

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Postby Sliderule » Sun Jun 18, 2017 4:39 pm

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
User avatar
Sliderule
Volunteer
 
Posts: 1205
Joined: Thu Nov 29, 2007 9:46 am

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Postby chrisb » Sun Jun 18, 2017 4:52 pm

the result is always the same whether using UNION or UNION ALL.
open office 4.1.7 & LibreOffice 6.3.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 207
Joined: Mon Jun 07, 2010 4:16 pm

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Postby UnklDonald418 » Sun Jun 18, 2017 5:34 pm

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.7 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1286
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Postby Sliderule » Sun Jun 18, 2017 6:07 pm

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
Sliderule
Volunteer
 
Posts: 1205
Joined: Thu Nov 29, 2007 9:46 am

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Postby Villeroy » Mon Jun 19, 2017 12:47 pm

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   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27733
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Postby eremmel » Mon Jun 19, 2017 7:31 pm

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
eremmel
 
Posts: 1041
Joined: Tue Dec 30, 2008 1:15 am

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Postby Villeroy » Mon Jun 19, 2017 7:48 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27733
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Postby F3K Total » Sat Jun 24, 2017 11:35 am

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
F3K Total
Volunteer
 
Posts: 950
Joined: Fri Dec 16, 2011 8:20 pm

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Postby Sliderule » Mon Jun 26, 2017 5:24 pm

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   Expand viewCollapse view
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   Expand viewCollapse view
-- 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   Expand viewCollapse view
Select A.* From
(select "Name", "ID" from "tSeason"
union
select "Swim", ID from "tSwim") AS A

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1205
Joined: Thu Nov 29, 2007 9:46 am

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Postby Villeroy » Mon Jun 26, 2017 8:11 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27733
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Postby chrisb » Mon Jul 03, 2017 3:54 pm

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.7 & LibreOffice 6.3.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 207
Joined: Mon Jun 07, 2010 4:16 pm

Re: HSQLDB 2.4.0 problem with CURRENT_TIMESTAMP

Postby Sliderule » Mon Jul 03, 2017 7:25 pm

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
User avatar
Sliderule
Volunteer
 
Posts: 1205
Joined: Thu Nov 29, 2007 9:46 am


Return to Base

Who is online

Users browsing this forum: No registered users and 2 guests