Is there a way to stop SQL Optimizer from changing the alias of a table when rewriting the query? Compare tool shows false positives. (4229970)

Is there a way to stop SQL Optimizer from changing the alias of a table when rewriting the query? Compare tool shows false positives.

Is there a way to stop SQL Optimizer from changing the alias of a table when rewriting the query? When performing a compare of the original against an alternative, the compare tool highlights a lot of false positive changes when comparing the code.

It is by design to modify the alias name if there happens to have the same alias name used repeatedly in the SQL. Note that this only happen whent he same alias name is used again and no modification is done on alias that is unique.

For example, in a query "Select * from T1 A, T2 B, T3 A", then only the alias "A" is repeated and the final result should only have alias "A" modified and resulting in this: "Select * from T1 A1, T2 B, T3 A2". During rewriting of original SQL, we sometimes will do quite big changes that may require changing the table position so we need to make sure each table has a unique alias to avoid ambiguous referencing. It is also a good practice not to name tables with same alias.

WORKAROUND:

None.

STATUS:

Enhancement ID: ST118915 has been created for consideration to have options to either ignore the alias' during compare or modify the original SQL alias' to match alternatives.

ST118915