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
Created attachment 46309 [details] Base with SQL properly saved
Created attachment 46310 [details] Base with SQL not properly saved
[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
Hello Still persists with Windows 7 & LOdev 3.5.0beta2+ Build ID: 62b9587-7ef74e0-7bf1c81 Regards PYS
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.
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.
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
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?
(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
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".
Hello Still occurs with Windows 7 64bits & LibO Version 3.6.0.0.beta2 (Build ID: f010139) Regards Pierre-Yves
Hi Lionel, Do you think you could take a look ? Alex
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.
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
Fixed in features/base-preview branch. Porting to all the various active branches...
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
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.
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.
(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
Setting to "VERIFIED FIXED" as per test by Pierre-Yves.