Bug Hunting Session
Bug 36824 - embedded hsqldb; in view, computed row without alias makes whole file unusable
Summary: embedded hsqldb; in view, computed row without alias makes whole file unusable
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: high critical
Assignee: Lionel Elie Mamane
URL:
Whiteboard: target:3.7.0 target:3.6.0.0.beta3 tar...
Keywords:
Depends on:
Blocks:
 
Reported: 2011-05-03 23:57 UTC by pierre-yves samyn
Modified: 2012-07-06 06:57 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Error Screenshot (10.17 KB, image/png)
2011-05-03 23:57 UTC, pierre-yves samyn
Details
Base with SQL properly saved (32.97 KB, application/vnd.oasis.opendocument.database)
2011-05-03 23:59 UTC, pierre-yves samyn
Details
Base with SQL not properly saved (32.84 KB, application/vnd.oasis.opendocument.database)
2011-05-04 00:02 UTC, pierre-yves samyn
Details

Note You need to log in before you can comment on or make changes to this bug.
Description pierre-yves samyn 2011-05-03 23:57:24 UTC
Created attachment 46308 [details]
Error Screenshot

Hello

My environment LibO 3.3.2 buildid=330m19(Build:8) - XP 
Reproduced with OOo 3.2.1

The problem occurs with a HSQL database, when using a function (sum, min, etc..) in a view without defining an alias to the column.

The first attached database (BaseOK.odb) includes a view generating a correct sql:

SELECT SUM( "prix" ) AS "Total" FROM "Instruments"

The second database (BaseKO.odb) includes a view generating a incorrect sql: 

SELECT SUM( "prix" ) FROM "Instruments"

- There is no alias for SUM( "prix" )
- This SQL is OK, you can run it and get good result. It is during the save of the database that the software turns it into incorrect statement.

Steps to reproduce 
1. Open BaseOK.odb
2. Menu View > Database Objects > Tables
3. Right clik on "View1" > Edit
4. Delete the column alias "Total" 
5. Save and close the view
6. Close the database
7. Re-open the database
8. Menu View > Database Objects > Tables

Error message (cf. attached screenshot): The connection to the data source could not be established
Error in script file... requires AS in statement [CREATE VIEW "View1" (SUM("Instruments"."prix"))]

Expected Results: connection to the data source and display tables / views

Note : The problem is the same if you create a new view. 
I propose to test  with an existing database only  to facilitate the reproduction of the problem.

At this point the only workaround seems to be to unzip the archive to edit the script file (database\script)

Replace :
CREATE VIEW "View1" ( SUM("Instruments"."prix")) AS SELECT SUM( "prix" ) FROM "Instruments"

With :
CREATE VIEW "View1" ("Total") AS SELECT SUM( "prix" ) AS "Total" FROM "Instruments"

or :
CREATE VIEW "View1" AS SELECT SUM( "prix" ) AS "Total" FROM "Instruments"

Best regards
Comment 1 pierre-yves samyn 2011-05-03 23:59:32 UTC
Created attachment 46309 [details]
Base with SQL properly saved
Comment 2 pierre-yves samyn 2011-05-04 00:02:42 UTC
Created attachment 46310 [details]
Base with SQL not properly saved
Comment 3 Björn Michaelsen 2011-12-23 12:05:52 UTC
[This is an automated message.]
This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it
started right out as NEW without ever being explicitly confirmed. The bug is
changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back
to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases.
Details on how to test the 3.5.0 beta1 can be found at:
http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1

more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
Comment 4 pierre-yves samyn 2011-12-24 02:57:54 UTC
Hello

Still persists with Windows 7 & LOdev 3.5.0beta2+ Build ID: 62b9587-7ef74e0-7bf1c81

Regards
PYS
Comment 5 Julien Nabet 2011-12-27 06:25:15 UTC
I reproduced the same problem with 3.5 branch and master branch (future 3.6).
Pc Debian x86-64.
I had no error/warning messages in console only the same popup as the one already attached.
Comment 6 Drew Jensen 2012-01-25 23:47:58 UTC
Checked this w/ Ubuntu 11.04 (amd64) and LibO 3.5 RC2

I can not reproduce creation of the error - of course any database created with the older binary will still not open as the create view statement written in the ODB file is in fact erroneous.

However, those files can be salvaged reasonably easily by un-zipping the ODB file and then directly deleting the bad statement.

So - I would say this is now a worksforme situation and would close the issue.
Comment 7 pierre-yves samyn 2012-01-26 01:46:50 UTC
Hello

(In reply to comment #6)
> Checked this w/ Ubuntu 11.04 (amd64) and LibO 3.5 RC2
> 
> I can not reproduce creation of the error
>...
> So - I would say this is now a worksforme situation and would close the issue.

Please don't... : still occurs with Windows 7 and LibreOffice 3.5.0rc2 
Version ID : e371a95-bf68a13-5a1aa2b-d3c1ae9-b938258 (even with a new database created with this version)

Regards
PYS
Comment 8 Drew Jensen 2012-01-26 05:01:52 UTC
Hi pierre-yves,

Alright - well, I'll lay my hands on a system with Win 7 (64bit home basic) later today then and give it a try also.

Can you let me know which version of the JRE you are running?
Comment 9 pierre-yves samyn 2012-01-26 09:06:49 UTC
(In reply to comment #8)
> 
> Can you let me know which version of the JRE you are running?

good question of course...

I'm a little confused :

C:\Users\Pierre-Yves>java -version
java version "1.6.0_20"
Java(TM) SE Runtime Environment (build 1.6.0_20-b02)
Java HotSpot(TM) 64-Bit Server VM (build 16.3-b01, mixed mode)

But :

C:\Program Files (x86)\Java\jre6\bin>java -version
java version "1.6.0_30"
Java(TM) SE Runtime Environment (build 1.6.0_30-b12)
Java HotSpot(TM) Client VM (build 20.5-b03, mixed mode, sharing)

So, Tools> Options> Java> 1.6.0_30 (location : C:\Program Files (x86)\Java\jre6)

I hope it could help
Comment 10 Robert Großkopf 2012-06-07 02:00:05 UTC
Have tested this with OpenSuSE 11.4, LO 3.3.4 und LO 3.5.4 with JRE 6_u22 (only java that works well with Base in SuSE).
The error appears with both LO-versions with new created databases in every version.
It's very important, becouse it could, for nomal users, destroy the database.
Set Importance to "high".
Comment 11 pierre-yves samyn 2012-07-01 04:07:07 UTC
Hello

Still occurs with Windows 7 64bits & LibO Version 3.6.0.0.beta2 (Build ID: f010139)

Regards
Pierre-Yves
Comment 12 Alex Thurgood 2012-07-02 00:39:33 UTC
Hi Lionel,

Do you think you could take a look ?

Alex
Comment 13 Lionel Elie Mamane 2012-07-02 10:42:38 UTC
Could reproduce with  3.5.5.1 (official .deb x86-64 binaries).

At first sight, I don't understand where the "column alias list" part of the CREATE VIEW statement is set; all HSQL-specific "CREATE VIEW" statements in the source code seem not to not possibly set any column alias. I'll have to take a closer look under gdb.
Comment 14 Lionel Elie Mamane 2012-07-03 08:52:45 UTC
Aha, this is a HSQLDB bug, as can be tested as follows:


$ java -cp hsqldb.jar org.hsqldb.util.SqlTool --inlineRC URL=jdbc:hsqldb:file:/tmp/db,user=SA
Enter password for SA:  

sql> CREATE TABLE foo (num integer);
sql> CREATE VIEW vFoo AS SELECT SUM(num) FROM foo;                                 
sql> SHUTDOWN;
sql> \q
$ java -cp hsqldb.jar org.hsqldb.util.SqlTool --inlineRC URL=jdbc:hsqldb:file:/tmp/db,user=SA
Enter password for SA:  
Failed to get a connection to 'jdbc:hsqldb:file:/tmp/db' as user "SA".
Cause: error in script file line: 3 Unexpected token: SUM in statement [CREATE VIEW VFOO ( SUM]

$ cat /tmp/db.script 
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE FOO(NUM INTEGER)
CREATE VIEW VFOO ( SUM(FOO.NUM)) AS SELECT SUM(num) FROM foo
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10
Comment 15 Lionel Elie Mamane 2012-07-03 11:05:49 UTC
Fixed in features/base-preview branch. Porting to all the various active branches...
Comment 16 Not Assigned 2012-07-03 11:27:10 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=bed3049c4c04a202ff288189d225ca6e5941d69b

fdo#36824 hsqldb properly escape automatically built names
Comment 17 Not Assigned 2012-07-03 11:27:37 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-3-6":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=3286e849e5b5f8809e92219bdf07e737c81cf42c&g=libreoffice-3-6

fdo#36824 hsqldb properly escape automatically built names


It will be available in LibreOffice 3.6.
Comment 18 Not Assigned 2012-07-04 00:29:46 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-3-5":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c395f40ee81aa6419dc5ff8d0b6c7ea46a807038&g=libreoffice-3-5

fdo#36824 hsqldb properly escape automatically built names


It will be available in LibreOffice 3.5.6.
Comment 19 pierre-yves samyn 2012-07-06 06:34:27 UTC
(In reply to comment #17)
> Lionel Elie Mamane committed a patch related to this issue.
> It will be available in LibreOffice 3.6.

Verified Ok with Version 3.6.0.0.beta3 (Build ID: 3e2b862) Windows7 64 bits

thank you (and also for "defrag" on closing ...)

Regards
Pierre-Yves
Comment 20 Lionel Elie Mamane 2012-07-06 06:57:54 UTC
Setting to "VERIFIED FIXED" as per test by Pierre-Yves.