Trouble with the LIST() aggregate function [Firebird]


I just started using LibreOffice Base (total beginner). The version is (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?

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

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

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…

1 Like


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)

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?

1 Like

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

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.

1 Like

Still getting those weird squares :frowning:

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.

1 Like

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