IBX - Possible problem with non‑unique calculated column names

[maurog]

Hi,

a user of TurboBird pointed out a behavior to me.

The following query (from the user) shows wrong column contents and wrong order in the grid – most columns show the value of the first column, and the order does not match the SELECT list:

Code: Pascal  [Select][+][-]

1. SELECT
2.   '1' || ' = ' || ASCII_VAL('1'),
3.   '2' || ' = ' || ASCII_VAL('2'),
4.   '3' || ' = ' || ASCII_VAL('3'),
5.   '4' || ' = ' || ASCII_VAL('4'),
6.   '5' || ' = ' || ASCII_VAL('5'),
7.   '6' || ' = ' || ASCII_VAL('6'),
8.   '7' || ' = ' || ASCII_VAL('7'),
9.   '8' || ' = ' || ASCII_VAL('8'),
10.   '9' || ' = ' || ASCII_VAL('9'),
11.   '0' || ' = ' || ASCII_VAL('0'),
12.   'A' || ' = ' || ASCII_VAL('A'),
13.   'B' || ' = ' || ASCII_VAL('B'),
14.   'C' || ' = ' || ASCII_VAL('C'),
15.   'W' || ' = ' || ASCII_VAL('W'),
16.   'Y' || ' = ' || ASCII_VAL('Y'),
17.   'Z' || ' = ' || ASCII_VAL('Z')
18. FROM RDB$DATABASE;
19.  

In Flamerobin the query appears correctly. Firebird internally assigns unique names like CONCATENATION, CONCATENATION1 …, but in TurboBird's grid the described errors occur.

Workaround: Give unique alias names to each calculated column, and everything works:

Code: Pascal  [Select][+][-]

1. SELECT
2.   '1' || ' = ' || ASCII_VAL('1') AS COL_1,
3.   '2' || ' = ' || ASCII_VAL('2') AS COL_2,
4.   ...
5.   'Z' || ' = ' || ASCII_VAL('Z') AS COL_Z
6. FROM RDB$DATABASE;
7.  

Could this be related to IBX?

Regards
Mustafa.

[dsiders]

Hi,

a user of TurboBird pointed out a behavior to me.

The following query (from the user) shows wrong column contents and wrong order in the grid – most columns show the value of the first column, and the order does not match the SELECT list:

Code: Pascal  [Select][+][-]

1. SELECT
2.   '1' || ' = ' || ASCII_VAL('1'),
3.   '2' || ' = ' || ASCII_VAL('2'),
4.   '3' || ' = ' || ASCII_VAL('3'),
5.   '4' || ' = ' || ASCII_VAL('4'),
6.   '5' || ' = ' || ASCII_VAL('5'),
7.   '6' || ' = ' || ASCII_VAL('6'),
8.   '7' || ' = ' || ASCII_VAL('7'),
9.   '8' || ' = ' || ASCII_VAL('8'),
10.   '9' || ' = ' || ASCII_VAL('9'),
11.   '0' || ' = ' || ASCII_VAL('0'),
12.   'A' || ' = ' || ASCII_VAL('A'),
13.   'B' || ' = ' || ASCII_VAL('B'),
14.   'C' || ' = ' || ASCII_VAL('C'),
15.   'W' || ' = ' || ASCII_VAL('W'),
16.   'Y' || ' = ' || ASCII_VAL('Y'),
17.   'Z' || ' = ' || ASCII_VAL('Z')
18. FROM RDB$DATABASE;
19.  

In Flamerobin the query appears correctly. Firebird internally assigns unique names like CONCATENATION, CONCATENATION1 …, but in TurboBird's grid the described errors occur.

Workaround: Give unique alias names to each calculated column, and everything works:

Code: Pascal  [Select][+][-]

1. SELECT
2.   '1' || ' = ' || ASCII_VAL('1') AS COL_1,
3.   '2' || ' = ' || ASCII_VAL('2') AS COL_2,
4.   ...
5.   'Z' || ' = ' || ASCII_VAL('Z') AS COL_Z
6. FROM RDB$DATABASE;
7.  

Could this be related to IBX?

Regards
Mustafa.

I tried this in the IDE using IBX. Apparently, the SQL needs a column alias to get the correct order. Not sure if it's an IBX/FB issue or if the grid plays a role.
In any case... using this statement:

Code: MySQL  [Select][+][-]

1. SELECT
2.   '1' || ' = ' || ASCII_VAL('1') "COL1",
3.   '2' || ' = ' || ASCII_VAL('2') "COL2",
4.   '3' || ' = ' || ASCII_VAL('3') "COL3",
5.   '4' || ' = ' || ASCII_VAL('4') "COL4",
6.   '5' || ' = ' || ASCII_VAL('5') "COL5",
7.   '6' || ' = ' || ASCII_VAL('6') "COL6",
8.   '7' || ' = ' || ASCII_VAL('7') "COL7",
9.   '8' || ' = ' || ASCII_VAL('8') "COL8",
10.   '9' || ' = ' || ASCII_VAL('9') "COL9",
11.   '0' || ' = ' || ASCII_VAL('0') "COL10",
12.   'A' || ' = ' || ASCII_VAL('A') "COL11",
13.   'B' || ' = ' || ASCII_VAL('B') "COL12",
14.   'C' || ' = ' || ASCII_VAL('C') "COL13",
15.   'W' || ' = ' || ASCII_VAL('W') "COL14",
16.   'Y' || ' = ' || ASCII_VAL('Y') "COL15",
17.   'Z' || ' = ' || ASCII_VAL('Z') "COL16"
18. FROM RDB$DATABASE;
19.  

gets the result pictured in the attachment.
                       

[maurog]

Thanks for testing. TurboBird uses two different grid components (RxDBGrid in the QueryWindow and IBXDynamicGrid in the Script window). Both show the same behavior. I will add a warning in TurboBird when duplicate or non‑unique column names are detected.

Regards
Mustafa.

[tonyw]

Hi,

a user of TurboBird pointed out a behavior to me.

The following query (from the user) shows wrong column contents and wrong order in the grid – most columns show the value of the first column, and the order does not match the SELECT list:
<snip>

In Flamerobin the query appears correctly. Firebird internally assigns unique names like CONCATENATION, CONCATENATION1 …, but in TurboBird's grid the described errors occur.

Workaround: Give unique alias names to each calculated column, and everything works:

<snip>

Could this be related to IBX?

Regards
Mustafa.

When it comes to select queries in IBX, column names are always those assigned by Firebird using the metadata aliasname. This is either the underlying column name, or a generated name when the column is computed (e.g. using an aggregation). When columns come from different tables, there may also be a need for disambiguation i.e. adding a suffix the aliasname.

For update queries, the param name (e.g. :F1) is an IBX invention (actually fbintf). This is because Firebird only recognising '?' positional placeholders. IBX adds value here by allowing for named parameters which it maintains itself. It will also generate names if your query uses the positional parameter syntax. Otherwise, it uses the names you give it. If a param name is used more than once, setting that param sets all instances simultaneously .e.g

Update MyTable Set A=:X, B=:X where C = 1;

Setting the value of param X sets both A and B.

In the case of this example, I would always recommend setting your own alias names (which is what the AS <name> syntax does). While Firebird should be consistent about assigning generated names, I would not like to guarantee it. I don't know Flamerobin internals, but it may generate its own aliasnames when the user does not provide them. Hence you may see different behaviour.

[maurog]

@Tony

thank you very much for the detailed explanation!

I will also point to it on GitHub so that users there are informed.

Best regards,
Mustafa.

[rvk]

@tonyw

I do agree with @maurog that there seems to be something wrong here.

When doing this query in IBX on Delphi I get the correct column names AND values.

With IBX for Lazarus (31-05-2026) I get correct column names but ALL values, from 1 onwards, seem to be "1 = 49"

See images. (1 = Delphi IBX, 2 = Lazarus IBX)

Either way you slice it, IBX for Lazarus shouldn't return all same values in the columns. This is wrong.
(In the second image I used the IBDataGrid and the normal TDBGrid below it to see if that makes any difference. It doesn't)

[tonyw]

In the above, the column names are showing correctly and are hence being correctly reported.

It looks like somewhere, when field value lookup is performed, a comparison is being made that is using a truncated name.

This is unlikely to be in IBX. Fields are bound to columns when a dataset is opened and from then on fieldbyname lookup is in the FCL

[rvk]

In the above, the column names are showing correctly and are hence being correctly reported.

It looks like somewhere, when field value lookup is performed, a comparison is being made that is using a truncated name.

This is unlikely to be in IBX. Fields are bound to columns when a dataset is opened and from then on fieldbyname lookup is in the FCL

Well, this is tested against the same Firebird instance. So if Delphi IBX does this correctly, and IBX for Lazarus not, what else could it be?

Just to show the difference... I now switched to TSQLConnector, TSQLTransaction and TSQLQuery from sqldb from FPC/Lazarus and there it works correctly.
So it's not the FCL that's the cause. This really seems a IBX for Lazarus issue.

[tonyw]

In the above, the column names are showing correctly and are hence being correctly reported.

It looks like somewhere, when field value lookup is performed, a comparison is being made that is using a truncated name.

This is unlikely to be in IBX. Fields are bound to columns when a dataset is opened and from then on fieldbyname lookup is in the FCL

Well, this is tested against the same Firebird instance. So if Delphi IBX does this correctly, and IBX for Lazarus not, what else could it be?

Just to show the difference... I now switched to TSQLConnector, TSQLTransaction and TSQLQuery from sqldb from FPC/Lazarus and there it works correctly.
So it's not the FCL that's the cause. This really seems a IBX for Lazarus issue.

Ok. I'll set up a test- but not today - I only have my phone with me.

[tonyw]

I suppose this is a good example of not giving "definitive" replies when the code is not in front of you. What I had forgotten is that IBX does its own disambiguation of Firebird column aliasnames adding a numeric suffix to each duplicate aliasname. In the above example, Firebird gives both the fieldname and the aliasname as "CONCATENATION" for all columns derived from a concatenation. IBX adds the numeric suffix.

That in itself is not the problem. After investigation, I found that when the internal buffers are set up, IBX uses the (ambiguous) aliasname to link columns to dataset field names. Normally this is not a problem. However, the bug occurs  because the fieldname is derived from the disambiguated aliasname provided by IBX, and comparing it using the (ambiguous) aliasname will always return the first instance.

The solution is to use the disambiguated aliasname rather than the Firebird provided aliasname for the comparison. It's a one line change and you can try it for yourself by editing runtime/nongui/IBBufferedCursors.pas and patching line 1722 as follows

Code: Pascal  [Select][+][-]

1. -    field := aFields.FindField(colMetadata.GetAliasName);
2. +    field := aFields.FindField(colMetadata.GetName);
3.  

I'll update the release soon, but it would be nice to get some early feedback before I bother the OPM admin for the second time in a week.

Otherwise, I would always recommend providing your own aliasnames (using the AS <name> syntax) when using aggregations or concatenations, etc, This is because the disambiguated aliasnames generated by IBX use a simple sequence no and a change of field order or adding your own alias to a field will change the sequence no for all subsequent fields. That is relying on generated aliasnames will give you a maintenance issue. They are only really suitable for ad hoc queries.

[rvk]

Code: Pascal  [Select][+][-]

1. -    field := aFields.FindField(colMetadata.GetAliasName);
2. +    field := aFields.FindField(colMetadata.GetName);
3.  

I'll update the release soon, but it would be nice to get some early feedback before I bother the OPM admin for the second time in a week.

Yes, that does seem to fix it for me.

I always use alias names when using calculated fields and I mostly use Delphi at the moment, so it's not critical for me.
But I guess it was a regression on IBX4Laz's part 

[tonyw]

I always use alias names when using calculated fields and I mostly use Delphi at the moment, so it's not critical for me.
But I guess it was a regression on IBX4Laz's part 

Its been there since 2.6.0 and the new improved buffer management scheme. I presume that Delphi IBX still uses the old buffer management with its higher overheads.

[tonyw]

The update is now on github as release 2.7.11. It would be useful to have some more confirmations before rolling out on OPM.

[dsiders]

The update is now on github as release 2.7.11. It would be useful to have some more confirmations before rolling out on OPM.

I downoaded 2.7.11 from GitHub. I can confirm that it fixes the order of output columns for the original posted query.

[maurog]

I can also confirm that IBX 2.7.11 fixes the problem. I just tested it – it works.

Thank you to everyone who contributed – especially to Tony for the quick fix and to all for the thorough testing!

Best regards,
Mustafa.