ambigious column name '...' [CORE6544]

[firebird-automations]

Submitted by: Alexander Muylaert (alexander_gonline.be)

Attachments:
image_2021_04_21T05_29_28_939Z.png

Votes: 1

Hi

We had an accidental performance degrade because of a calculated field that wasn't intended to be used in production. The problem behind it was the SQL that was approved (falsely). It contained an ambiguous column names. It would be magnificent if you could fail on compile time, since this is a very dangerous situation.

In my example, bit T_ADDRESS and T_PROJECT have a field F_PERS_ID and the code I'm showing is the computed by of a calculated field. The second (correct) implementation gives me a correct, fast result, while the first one, returns a full table scan. What it should do... But it would have saved us many stupid hours of debugging if the ambiguous warning would have been thrown. A bit like in MS-SQL.

kind regards

Alexander

[firebird-automations]

Commented by: Alexander Muylaert (alexander_gonline.be)

left is wrong statement, right is correct one.

[firebird-automations]

Modified by: Alexander Muylaert (alexander_gonline.be)

Attachment: image_2021_04_21T05_29_28_939Z.png [ 13591 ]

[firebird-automations]

Modified by: Alexander Muylaert (alexander_gonline.be)

description: Hi

We had an accidental performance degrade because of a calculated field that should wasn't intended to be used in production. The problem behind it was the SQL that was approved. It contained ambiguous column names between tables.
It would be magnificent if you could fail on compile time, since this is a very dangerous situation.

In my example, bit T_ADDRESS and T_PROJECT have a field F_PERS_ID and the code I'm showing is the computed by of a calculated field. The second (correct) implementation gives me a correct, fast result, while the first one, returns a full table scan. What it should do... But it would have saved us many stupid hours of debugging if the ambiguous warning would have been thrown. A bit like in MS-SQL.

kind regards

Alexander

=>

Hi

We had an accidental performance degrade because of a calculated field that wasn't intended to be used in production. The problem behind it was the SQL that was approved (falsely). It contained an ambiguous column names. It would be magnificent if you could fail on compile time, since this is a very dangerous situation.

In my example, bit T_ADDRESS and T_PROJECT have a field F_PERS_ID and the code I'm showing is the computed by of a calculated field. The second (correct) implementation gives me a correct, fast result, while the first one, returns a full table scan. What it should do... But it would have saved us many stupid hours of debugging if the ambiguous warning would have been thrown. A bit like in MS-SQL.

kind regards

Alexander

[firebird-automations]

Commented by: Lucas Schatz (arvanus)

Not 100% sure, but I don't see a reason to fail compile in the left statement, the first select "adr.f_pers_id = f_pers_id" could probably be translated as "1=1" or simply "true" implying a full table scan if you ignore the second part of the condition. So no error here (obviously this doesn't returned what you needed)
On the other side, I don't think your right SQL could compile, the way you used a outer table's column I cant reproduce it here, can you provide a DLL+SQL sample?
Thanks