when i use this query:
Code: Select all
SELECT DISTINCT CURRENT_USER, CURRENT_TIMESTAMP FROM INFORMATION_SCHEMA.SYSTEM_USERS
R
Code: Select all
SELECT DISTINCT CURRENT_USER, CURRENT_TIMESTAMP FROM INFORMATION_SCHEMA.SYSTEM_USERS
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
Edit: the image below shows HSQLDB 2.3.4 |
Edit: the image below shows 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 ?chrisb wrote:there is a similar issue with UNION when using hsqldb 2.4.0.
My queries using UNION and UNION ALL work fine using hsqldb 2.3.x but using 2.4.0 they return no results.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
Code: Select all
SELECT DISTINCT CURRENT_USER, CURRENT_TIMESTAMP FROM INFORMATION_SCHEMA.SYSTEM_USERS
'---------------------------------------------------------
SA,2017-06-19 12:46:19.660000+2:00,
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 .
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
Code: Select all
Select A.* From
(select "Name", "ID" from "tSeason"
union
select "Swim", ID from "tSwim") AS A
Luckily there are people like you who try harder than others.Sliderule wrote:The second Query is an example of Base Parser with a Parameter Query, while also using UNION ALL.
Thank-you very much for posting the bug report at Apache OpenOffice Bugzilla. The web site is: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.