Bug 92987 - view cannot be a UNION SELECT
Summary: view cannot be a UNION SELECT
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected) release
Hardware: Other All
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2015-07-28 17:12 UTC by Rolf Leggewie
Modified: 2015-08-03 22:53 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description Rolf Leggewie 2015-07-28 17:12:58 UTC
SELECT "company_name",
 DATEDIFF( 'DD', '1899-12-30', TO_CHAR( CURDATE( ), 'YYYY-MM-' ) + "integer" ) AS "Zahltag"
 FROM "company", "integer"
UNION SELECT ALL "company_name",
 DATEDIFF( 'DD', '1899-12-30', TO_CHAR( CURDATE( ), 'YYYY-MM-' ) + "payday2" ) AS "Zahltag"
 FROM "company"

The above works fine as a query but only the first select will be accepted as a view.  The UNION SELECT will trigger the error "The given command is not a select statement. Only queries are allowed."
Comment 1 Alex Thurgood 2015-07-28 17:35:14 UTC
@Rolf : which db backend are you using ?
Comment 2 Rolf Leggewie 2015-07-28 17:41:03 UTC
HSQLDB embedded
Comment 3 Robert Großkopf 2015-07-28 18:18:51 UTC
Have tested this also.
Build two tables for the content I guessed by the given example.
Copied the query in a Base-query.
Could only execute the query in direct SQL-Mode, not in GUI-Mode. Think this is what you mean with "query works fine ...".
Now I took the query and created a view from this query. 
View works the same way the query works.

So I could not confirm the described bug.

My System: OpenSUSE 13.2, 64bit rpm Linux, different LO-versions
Comment 4 Alex Thurgood 2015-07-30 08:22:26 UTC
@Rolf : as with other bugs you have reported, you are using 4.2.8 provided by Ubuntu. TDF builds of the 4.2.8 line are EOL since Jan 2015.

Please update your version of LO to a current TDF production release, test again and report back here, or else close this bug and file a report against the Ubuntu provided version
Comment 5 Rolf Leggewie 2015-08-03 13:00:26 UTC
I can actually save the view when I choose to run SQL directly even in  But open opening the view in produces an error of "The content could not be loaded - Wrong datatype: java.lang.illegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff] in statement [SELECT * FROM "VIEW1"]

The UNION SELECT opens fine even as a view in 4.4.5, so this problem is indeed fixed in the latest release. Thanks.