ambigious column name '...' [CORE6544] #6770
Comments
|
Commented by: Alexander Muylaert (alexander_gonline.be) left is wrong statement, right is correct one. |
Modified by: Alexander Muylaert (alexander_gonline.be)Attachment: image_2021_04_21T05_29_28_939Z.png [ 13591 ] |
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. 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 |
|
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) |
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
The text was updated successfully, but these errors were encountered: