[upstream] view cannot be a UNION SELECT in trusty base

[Rolf Leggewie]

This is either an upstream bug already fixed in the version in wily or a regression introduced in Ubuntu. I run trusty.

https://bugs.documentfoundation.org/show_bug.cgi?id=92987

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 in trusty base 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."

Verifications whether this affects later versions than trusty appreciated.

[Libreoffice-4 (libreoffice-4)]

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."

[Iplaw67-h (iplaw67-h)]

@Rolf : which db backend are you using ?

[Libreoffice-4 (libreoffice-4)]

HSQLDB embedded

[Nv4y-robert (nv4y-robert)]

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

[Iplaw67-h (iplaw67-h)]

@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

[Libreoffice-4 (libreoffice-4)]

I can actually save the view when I choose to run SQL directly even in 4.2.8.2. But open opening the view in 4.2.8.2 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.

[Marcus Tomlinson (marcustomlinson)]

This release of Ubuntu is no longer receiving maintenance updates. If this is still an issue on a maintained version of Ubuntu please let us know.

[Marcus Tomlinson (marcustomlinson)]

You have acknowledged this to be fixed in the upstream report.