Bug 70506 - EDITING: Embedded Firebird - Alias in queries are ignored
Summary: EDITING: Embedded Firebird - Alias in queries are ignored
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.2.0.0.alpha0+ Master
Hardware: Other All
: medium normal
Assignee: Tamas Bunth
QA Contact:
URL:
Whiteboard: target:5.4.0 target:5.3.0.1
Keywords:
: 105195 (view as bug list)
Depends on:
Blocks: Database-Firebird
  Show dependency treegraph
 
Reported: 2013-10-15 18:44 UTC by robert
Modified: 2017-01-09 11:29 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Open the query - one field without fieldname (42.54 KB, application/vnd.oasis.opendocument.database)
2014-11-08 19:20 UTC, robert
Details
Backtrace of query edit crash (10.45 KB, text/plain)
2014-11-09 11:08 UTC, Buovjaga
Details
Version 3.0 of original file (73.98 KB, application/vnd.oasis.opendocument.database)
2016-11-11 23:45 UTC, Julien Nabet
Details

Note You need to log in before you can comment on or make changes to this bug.
Description robert 2013-10-15 18:44:58 UTC
Open an internal Firebird database. Edit a new query. Try to chose an alias for one field. The alias would be ignored - the old name of the field will appear.
It's a special problem when you try to use functions, which only work in the direct SQL-mode:
SELECT "date", DATEADD(DAY,'5',"date") AS "date_new" FROM "table"
In such a query there isn't shown any name for the field, which shows the added date. The fieldname will be empty.
Take https://bugs.freedesktop.org/attachment.cgi?id=87557 for testing.

Same works with the internal database in the same version of LO.
Same works with the external Firebird database in LO 4.1.1.
Comment 1 robert 2014-11-08 19:20:20 UTC
Created attachment 109137 [details]
Open the query - one field without fieldname

When you run the query1, you could see one field without any fieldname. This field shows the same content as the next field.

Open the query for editing. There are two fields with an alias: "Datum" AS "Date" and DATEADD(DAY, '5', "Datum") AS "NewDate". No alias is shown. "Datum" is shown for the third column and nothing for the fourth column.
Comment 2 Buovjaga 2014-11-08 21:02:55 UTC
(In reply to robert from comment #1)
> Created attachment 109137 [details]
> Open the query - one field without fieldname
> 
> When you run the query1, you could see one field without any fieldname. This
> field shows the same content as the next field.
> 
> Open the query for editing. There are two fields with an alias: "Datum" AS
> "Date" and DATEADD(DAY, '5', "Datum") AS "NewDate". No alias is shown.
> "Datum" is shown for the third column and nothing for the fourth column.

Sorry for my newbieness, but maybe it will be good to have "explain like I'm five" -style instructions for this: when I open the db and try to edit the query I get this error:

The connection to the data source "Firebird_intern_query_no_alias" could not be established. SQL Status: HY000  The connection to the external data source could not be established. No SDBC driver was found for the URL 'sdbc:embedded:firebird'.

What should I do to make it work?
Comment 3 robert 2014-11-09 07:07:44 UTC
(In reply to Beluga from comment #2)
> 
> The connection to the data source "Firebird_intern_query_no_alias" could not
> be established. SQL Status: HY000  The connection to the external data
> source could not be established. No SDBC driver was found for the URL
> 'sdbc:embedded:firebird'.

Could be you haven't enabled internal firebird. Go to 
Tools → Options → LibreOffice → Advanced → Enable experimatenal features
This should start the internal firebird for LO.
Comment 4 Buovjaga 2014-11-09 11:08:44 UTC
Created attachment 109146 [details]
Backtrace of query edit crash

I enabled experimental features, but now I'm getting a crash, when trying to edit the query :)

Win 7 64-bit Version: 4.4.0.0.alpha2+
Build ID: 9229170920ab770624415c4330da57af5b1b5398
TinderBox: Win-x86@39, Branch:master, Time: 2014-11-09_03:16:43
Comment 5 Buovjaga 2014-11-09 11:18:28 UTC
(In reply to robert from comment #1)
> Created attachment 109137 [details]
> Open the query - one field without fieldname
> 
> When you run the query1, you could see one field without any fieldname. This
> field shows the same content as the next field.
> 
> Open the query for editing. There are two fields with an alias: "Datum" AS
> "Date" and DATEADD(DAY, '5', "Datum") AS "NewDate". No alias is shown.
> "Datum" is shown for the third column and nothing for the fourth column.

Ok, I ran the query on Linux and I see what you are describing: Datum for 3rd column and nothing for 4th.

Version: 4.3.2.2.0+
Build ID: 4.3.2.2 Arch Linux build-1
Comment 6 Alex Thurgood 2015-01-03 17:39:40 UTC Comment hidden (no-value)
Comment 7 QA Administrators 2016-01-17 20:05:22 UTC Comment hidden (obsolete)
Comment 8 robert 2016-01-31 15:07:35 UTC
Bug still exists with LO 5.1.0.3, OpenSUSE 42.1 Leap, 64bit rpm Linux.
Comment 9 robert 2016-11-06 19:12:54 UTC
Have tested it again while creating a view:
The alias for a field will be created, if I create a view from a query, which doesn't show the alias.
See bug76072.
... but the alias wouldn't be shown through the query-GUI.

Tested with:
Version: 5.3.0.0.alpha1+
Build ID: 5d39c2013374727b1c8f147b8b99d54402a7ff02
CPU Threads: 4; OS Version: Linux 4.1; UI Render: default; VCL: gtk2; Layout Engine: old; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2016-11-02_00:55:24
Locale: de-DE (de_DE.UTF-8); Calc: group
Comment 10 Julien Nabet 2016-11-11 23:06:18 UTC
Robert: would it be possible to attach a file with Firebird 3.0 format?
Indeed, I tried gbak conversion suggested in https://wiki.documentfoundation.org/Development/Base/FirebirdSQL
but gbak version provided by Debian is from Firebird 3.0 (not 2.5 one)
I suppose it's because of this version mismatch that
gbak -backup firebird.fdb firebird.fbk
gives:
gbak: ERROR:Unable to complete network request to host "localhost".
gbak: ERROR:    Failed to establish a connection.
gbak:Exiting before completion due to errors
Comment 11 Julien Nabet 2016-11-11 23:11:53 UTC
After having installed firebird-server, I got the same initial error with gbak:
gbak: ERROR:unsupported on-disk structure for file /home/julien/lo/bugs/70506_firebirdrobertalias/test/database/firebird.fdb; found 11.2, support 12.0
gbak:Exiting before completion due to errors
Comment 12 Julien Nabet 2016-11-11 23:30:53 UTC
I installed Firebird 2.5 (superclassic) from Jessie Debian repo.

Now I've got this:
gbak: ERROR:operating system directive open failed
gbak: ERROR:    Permission denied
gbak:Exiting before completion due to errors

I gave a try to http://www.firebirdfaq.org/faq102/ but nothing.

I confirm a webservice which would convert a odb with Fb 2.5 to an odb with Fb 3.0 could be very useful! :-)
Comment 13 Julien Nabet 2016-11-11 23:45:43 UTC
Created attachment 128677 [details]
Version 3.0 of original file

I finally succeeded!
I runned the command as root in /tmp

So the complete procedure is:
- install Firebird 2.5
- uncompress odb file
- gbak -backup firebird.fdb firebird.fbk
=> so firebird.fdb is 2.5 and firebird.fbk is 2.5
- uninstall Firebird 2.5
- install Firebird 3.0
- gbak -c -v firebird.fbk firebird.fdb
=> so firebird.fdk is still 2.5 but firebird.fdb is 3.0
- change owner and rights of the file firebird.fdb
- recompress all the elements (with the brand new firebird.fdb)
- change extension from zip to odb

Hope I haven't forgotten anything but above all, hope there's a simpler way! :-)
Comment 14 Julien Nabet 2016-11-11 23:50:52 UTC
If I run the query, here is the header line:
ID Name Datum DATEADD DATEADD
And here are the console logs:
warn:connectivity.firebird:12464:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1485: Not yet implemented
warn:legacy.osl:12464:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1489: Not implemented yet!
warn:connectivity.firebird:12464:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1485: Not yet implemented
warn:legacy.osl:12464:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1489: Not implemented yet!
warn:connectivity.firebird:12464:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1485: Not yet implemented
warn:legacy.osl:12464:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1489: Not implemented yet!
warn:connectivity.firebird:12464:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1485: Not yet implemented
warn:legacy.osl:12464:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1489: Not implemented yet!
warn:connectivity.firebird:12464:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1485: Not yet implemented
warn:legacy.osl:12464:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1489: Not implemented yet!
warn:connectivity.firebird:12464:1:connectivity/source/drivers/firebird/ResultSetMetaData.cxx:177: Column 'DATEADD' not found in database
warn:connectivity.firebird:12464:1:connectivity/source/drivers/firebird/ResultSetMetaData.cxx:177: Column 'DATEADD' not found in database
Comment 15 Lionel Elie Mamane 2016-11-12 08:53:07 UTC
(In reply to Julien Nabet from comment #13)
> Created attachment 128677 [details]
> Version 3.0 of original file
> 
> I finally succeeded!
> I runned the command as root in /tmp
> 
> So the complete procedure is:
> - install Firebird 2.5
> - uncompress odb file
> - gbak -backup firebird.fdb firebird.fbk
> => so firebird.fdb is 2.5 and firebird.fbk is 2.5
> - uninstall Firebird 2.5
> - install Firebird 3.0
> - gbak -c -v firebird.fbk firebird.fdb
> => so firebird.fdk is still 2.5 but firebird.fdb is 3.0
> - change owner and rights of the file firebird.fdb
> - recompress all the elements (with the brand new firebird.fdb)
> - change extension from zip to odb
> 
> Hope I haven't forgotten anything but above all, hope there's a simpler way!
> :-)

In the odb file, remove the fdb, and put the fbk in its place. That should suffice :)

Something like:

 - install Firebird 2.5
 - uncompress odb file
 - gbak -backup firebird.fdb firebird.fbk
 - rm firebird.fdb
 - change owner and rights of the file firebird.bk
 - recompress all the elements (with the brand new firebird.fbk)
 - change extension from zip to odb
Comment 16 Julien Nabet 2016-11-12 09:26:45 UTC
(In reply to Lionel Elie Mamane from comment #15)
> (In reply to Julien Nabet from comment #13)
> > Created attachment 128677 [details]
> > Version 3.0 of original file
> > 
> > I finally succeeded!
> > I runned the command as root in /tmp
> > ...
> 
> In the odb file, remove the fdb, and put the fbk in its place. That should
> suffice :)
> 
> Something like:
> 
>  - install Firebird 2.5
>  - uncompress odb file
>  - gbak -backup firebird.fdb firebird.fbk
>  - rm firebird.fdb
>  - change owner and rights of the file firebird.bk
>  - recompress all the elements (with the brand new firebird.fbk)
>  - change extension from zip to odb
Indeed, it works! Thank you for the tip, it saves some time! :-)
Comment 17 Commit Notification 2016-12-20 18:18:48 UTC
Wastack committed a patch related to this issue.
It has been pushed to "master":

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

tdf#70506 implement getColumnLabel function

It will be available in 5.4.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 18 Julien Nabet 2016-12-20 18:24:18 UTC
Backport has been done too (see https://gerrit.libreoffice.org/#/c/32250/)
So let's put this one to FIXED.
Comment 19 Julien Nabet 2016-12-20 18:25:04 UTC
(backport to 5.3 branch only)
Comment 20 Commit Notification 2016-12-20 18:26:54 UTC
Wastack committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

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

tdf#70506 implement getColumnLabel function

It will be available in 5.3.0.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 21 robert 2016-12-22 20:47:56 UTC
Could confirm it works now with Version: 5.4.0.0.alpha0+
Build ID: 2a4cd80abcf9e515d1ce3b3a944b573bdc42bff2
CPU Threads: 4; OS Version: Linux 4.1; UI Render: default; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2016-12-22_00:18:04
Locale: de-DE (de_DE.UTF-8); Calc: group

Thanks a lot, Wastack.
Comment 22 robert 2017-01-09 11:29:48 UTC
*** Bug 105195 has been marked as a duplicate of this bug. ***