Trouble with the LIST() aggregate function [Firebird]

[hedler]

Hello!

I just started using LibreOffice Base (total beginner). The version is 7.2.0.4 (x64 Windows) and I am using Firebird embebbed database.

Here is my table:

artist_ID | artist_name
1 | Stan Lee
2| Jack Kirby
3| Dick Ayers

I am trying to use the LIST function to produce a list of all rows in a column. So I made the following query:

SELECT LIST (“artist_name”) FROM “artist”

The desired result was to retrieve a table with the following content:

|Stan Lee, Jack Kirby, Dick Ayers|

However, there is something wrong. The result I got is:

|Stan Lee□□□□□□□□,□□□□□□□□□□□□□□□Jack Kirby□□□□□□,□□□□□□□□□□□□□□□Dick Ayers□□□□□□|

I have no idea why I got □ in between separators. I would also like to point out that if I try to use LIST on artist_ID it works as intended. Could someone please point out what I’m doing wrong?

[RobertG]

Have a look at the field properties of the table. Which property has “artist_name”? Is it really VARCHAR?

[hedler]

Yes, the field “artist_name” is set as VARCHAR. “artist_ID” is set as INTEGER.

[RobertG]

Tested it with a VARCHAR column and didn’t get such a result. Tested it with a CHAR column and I will get spaces between the values.

Could you upload the database or an example, which didn’t work as expected?

If it isn’t a VARCHAR-column, something like this will help:
SELECT LIST(TRIM(CAST("artist_name" AS VARCHAR(50))),', ') FROM "artist"
It will set the content to VARCHAR and deletes all following spaces. But what you got aren’t spaces…

[hedler]

Absolutely!

I’m uploading the database file. Could you try to get the list on the table artist? I’ll also upload a print screen from my results.

testdatabase.odb (8.5 KB)

[RobertG]

I tried it:
SELECT LIST("artist_name",', ') FROM "artist"
works here (OpenSUSE 64bit rpm Linux) without any problem.
The query runs also well with your code here.

Could this be a problem with Firebird, Windows and UTF8?

[hedler]

I have no idea, just started using Base a few days ago. Should I reinstall Libre Office?

[RobertG]

You could try to start LO in safe mode: Help → Restart in Safe Mode. This will show LO without the content of your user-folder of LO. In most cases reinstalling of LO won’t help.

[hedler]

Still getting those weird squares

[RobertG]

I have counted the squares a little bit:
There are for every field of artist 16 characters in front of the comma: the content of the field together with squares. And 15 squares are following the comma. Then the next field content follows.

Please write a bug for it: LO bugtracker. Add a screenshot and a little example database (only table “artist” will be enough). It seems to be a special buggy behavior together with windows.

[hedler]

Thank you! I will submit it at once! You’ve been very helpful!