Bug 70433 - EDITING: Embedded Firebird - List doesn't work in queries, works in external Firebird
Summary: EDITING: Embedded Firebird - List doesn't work in queries, works in external ...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.2.0.0.alpha0+ Master
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
QA Contact:
URL:
Whiteboard: target:5.4.0 target:5.3.1
Keywords:
Depends on:
Blocks:
 
Reported: 2013-10-13 19:53 UTC by robert
Modified: 2017-01-24 06:13 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Start the query of the database - shows only <object>, but works with external Firebird. (46.34 KB, application/vnd.oasis.opendocument.base)
2013-10-13 19:53 UTC, robert
Details
Version 3.0 of original file (73.98 KB, application/vnd.oasis.opendocument.database)
2016-11-12 00:03 UTC, Julien Nabet
Details
Firebird 3.0 of original file (4.12 KB, application/vnd.oasis.opendocument.database)
2016-12-15 12:09 UTC, Tamas Bunth
Details

Note You need to log in before you can comment on or make changes to this bug.
Description robert 2013-10-13 19:53:33 UTC
Created attachment 87567 [details]
Start the query of the database - shows only <object>, but works with external Firebird.

In the external version of Firebird exists a function called LIST. It lists all values of one field together. When you group another fields it works like "GROUP CONCAT" in MySQL. This functions works in the GUI, not only in direct SQL-mode.

When I start this function in the embedded Firebird it only recognizes an <OBJECT> inside the field. Also when I change to direct SQL-mode it doesn't work. Also direct input in Tools → SQL doesn't show anything.
Comment 1 Alex Thurgood 2013-10-21 06:57:22 UTC
Robert,

If I open your file in GUI Query Design mode, I see the following as the Field Name :

LIST( [Vorname], ' and ' )


shouldn't the function appear in the Function are of the UI ?


Alex
Comment 2 Alex Thurgood 2013-10-21 06:59:53 UTC
LIST doesn't appear in the dropdown list of supported functions, so I'm guessing that it is one that hasn't been implemented, or is a missing feature ? In which case, this would be a RFE ?



Alex
Comment 3 Alex Thurgood 2013-10-21 07:01:53 UTC
The SQL statement that I see in your file :

SELECT LIST( "Vorname", ' and ' ) FROM "Name_VarcharID"



Alex
Comment 4 robert 2013-10-21 08:24:33 UTC
(In reply to comment #3)
> The SQL statement that I see in your file :
> 
> SELECT LIST( "Vorname", ' and ' ) FROM "Name_VarcharID"
> 
Hi Alex,

there are two new functions in Firebird, which I missed in HSQLDB:
DATEADD and LIST. In the description of Firebird I found
LIST ([ALL | DISTINCT] expression [, separator])
The result type is a BLOB - could be this problem could be solved together with Bug70664.
When I put 
SELECT LIST( "Vorname", ' and ' ) FROM "Name_VarcharID"
in a query of the external Firebird database it shows in Base all Values of "Vorname" in one field - looks like
'Robert and Alex and Eva and Adam'
In MySQL/MariaDB this function is called "GROUP_CONCAT()".

When this function appears as a choice in the GUI it could be that users think it will work with all databases - especially with the internal databases of LO. Don't know if it should appear there anyway.
Comment 5 Lionel Elie Mamane 2013-10-21 09:26:04 UTC
The question whether the GUI shows the choice or not is independent on whether it should work when typed directly. If you want to discuss that enhancement, please fork it into another bug report.

For clarity, *this* bug is (to the best of my understanding) about the result of "LIST()" function being recognised as BLOB with internal firedb, but as varchar (string) using external firedb.

Robert: for "external" firedb, do you use the native connector or something like ODBC or JDBC?

Robert: try adding the explicit cast to varchar of the result; something like:
 SELECT CAST( LIST("Vorname", ' and ') AS VARCHAR) FROM "Name_VarcharID"
Comment 6 robert 2013-10-21 13:59:42 UTC
(In reply to comment #5)

> For clarity, *this* bug is (to the best of my understanding) about the
> result of "LIST()" function being recognised as BLOB with internal firedb,
> but as varchar (string) using external firedb.

When I read the description of Firebird 2.5, I see on p. 166
Result type: BLOB
The result is a text BLOB, except when expression is a BLOB of another subtype.
Firebird has different sub_types of BLOB. The sub_type of LIST is a text-BLOB

> Robert: for "external" firedb, do you use the native connector or something
> like ODBC or JDBC?

I have tried it with JDBC and ODBC. Both work as expected.
> 
> Robert: try adding the explicit cast to varchar of the result; something
> like:
>  SELECT CAST( LIST("Vorname", ' and ') AS VARCHAR) FROM "Name_VarcharID"

Tried this (it's a function implemented in Firebird), but it doesn't work. Gives a firebird_sdbc error.
Comment 7 Lionel Elie Mamane 2013-10-21 14:19:44 UTC
(In reply to comment #6)
> (In reply to comment #5)

>> For clarity, *this* bug is (to the best of my understanding) about the
>> result of "LIST()" function being recognised as BLOB with internal firedb,
>> but as varchar (string) using external firedb.

> When I read the description of Firebird 2.5, I see on p. 166
> Result type: BLOB
> The result is a text BLOB, except when expression is a BLOB of another
> subtype.
> Firebird has different sub_types of BLOB. The sub_type of LIST is a text-BLOB

I see. Then maybe the subtype has to be mapped to css::sdbc::DataType::CLOB, but currently is not (is mapped to BLOB instead). Andrzej? Does that ring a bell?

>> Robert: for "external" firedb, do you use the native connector or something
>> like ODBC or JDBC?

> I have tried it with JDBC and ODBC. Both work as expected.

Probably the JDBC / ODBC drivers do the above mapping.
Comment 8 Alex Thurgood 2015-01-03 17:39:35 UTC Comment hidden (no-value)
Comment 9 Julien Nabet 2016-11-12 00:03:43 UTC
Created attachment 128678 [details]
Version 3.0 of original file

On pc Debian x86-64 with master sources updated today, I could reproduce this.

I attached V3 version of the file

On console, I noticed these:
warn:connectivity.firebird:16904:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1485: Not yet implemented
warn:legacy.osl:16904:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1489: Not implemented yet!
warn:connectivity.firebird:16904:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1485: Not yet implemented
warn:legacy.osl:16904:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1489: Not implemented yet!
warn:connectivity.firebird:16904:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1485: Not yet implemented
warn:legacy.osl:16904:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1489: Not implemented yet!
warn:connectivity.firebird:16904:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1485: Not yet implemented
warn:legacy.osl:16904:1:connectivity/source/drivers/firebird/DatabaseMetaData.cxx:1489: Not implemented yet!
Comment 10 Julien Nabet 2016-11-12 00:14:49 UTC
I submitted https://gerrit.libreoffice.org/#/c/30789/ so LO uses subtype to distinguish BLOB/CLOB.

Now the field is empty (instead of display "OBJECT").
On console, I noticed this:
warn:connectivity.firebird:17270:1:connectivity/source/drivers/firebird/ResultSetMetaData.cxx:177: Column 'LIST' not found in database
Comment 11 Commit Notification 2016-11-12 09:17:24 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

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

Related tdf#70433: Use subtype to distinguish BLOB/CLOB for Firebird

It will be available in 5.3.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 12 Julien Nabet 2016-11-12 09:18:16 UTC
I don't know how to fix this, it's just a related patch.
So let's remove target.
Comment 13 Tamas Bunth 2016-12-15 12:09:57 UTC
Created attachment 129663 [details]
Firebird 3.0 of original file

I'm afraid the previous firebird 3.0 version of attachment was accidentally an attachment of another bug.
Comment 14 Tamas Bunth 2016-12-15 17:20:23 UTC
As I see, the result is an empty Clob right now, because the getClob() function in Resultset.cxx returns always a nullpointer. (not implemented).

Before Julien's patch, the type received from getColumnTypeFromFBType (in Util.cxx) was the type of a blob, instead of clob. That's why the result was "OBJECT" before, instead of and empty cell.
This experiment makes me assume, that the UI could display a non-empty clob (or text-Blob in case of Firebird, whatever) properly.

So we have two opportunities:
- Implement the XClob interface just as XBlob is implemented, and use it in getClob():
https://www.openoffice.org/api/docs/common/ref/com/sun/star/sdbc/XClob.html

- Use list with an extra CAST():

SELECT CAST (LIST( "Vorname", ' and ' )  as VARCHAR(32000) ) FROM "Name_VarcharID"

That should work. At least it works for me.
Comment 15 robert 2016-12-15 20:12:29 UTC
(In reply to Tamas Bunth from comment #14) 
> - Use list with an extra CAST():
> 
> SELECT CAST (LIST( "Vorname", ' and ' )  as VARCHAR(32000) ) FROM
> "Name_VarcharID"
> 
> That should work. At least it works for me.

Thanks Tamas! This is a good workaround for me. Works also here with LO 5.4.0.0.alpha0+ and with LO 5.2.4.1 (Firebird 2.5).

We shouldn't set this to WORKSFORME, but could wait a little bit with this bug.
Comment 16 Commit Notification 2017-01-14 12:14:20 UTC
Tamás Bunth committed a patch related to this issue.
It has been pushed to "master":

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

tdf#70433 tdf#104734 implement sdbc XClob

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 17 robert 2017-01-20 08:22:57 UTC
(In reply to Commit Notification from comment #16)
> Tamás Bunth committed a patch related to this issue.
> It has been pushed to "master":
> 
> http://cgit.freedesktop.org/libreoffice/core/commit/
> ?id=882db8709f4ebc9f170cf743f39434d791b34b14
> 
> tdf#70433 tdf#104734 implement sdbc XClob
> 
> It will be available in 5.4.0.

Patch works well in LO 5.4.0. Thanks Tamás!

Could it be also included to LO 5.3.0?
Comment 18 Tamas Bunth 2017-01-21 00:20:52 UTC
(In reply to robert from comment #17) 
> Could it be also included to LO 5.3.0?

I think so. But I'm not familiar with the aspects of including code to previous versions.
It doesn't depend on code created after LO 5.3.0.
Comment 19 Julien Nabet 2017-01-21 08:49:36 UTC
I cherry picked the patch on 5.3 branch, it's on review here:
https://gerrit.libreoffice.org/#/c/33364/
Comment 20 Commit Notification 2017-01-24 06:12:26 UTC
Tamás Bunth committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

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

tdf#70433 tdf#104734 implement sdbc XClob

It will be available in 5.3.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 Julien Nabet 2017-01-24 06:13:35 UTC
Let's put this one to FIXED now.