MySQL Bugs: #38392: SQL standard compliance "fix" causes problems

Bug #38392	SQL standard compliance "fix" causes problems
Submitted:	26 Jul 2008 15:52	Modified:	27 Sep 2008 23:08
Reporter:	Gareth Evans	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	compliance, duplicate, subquery

Description:
This probably isn't a bug report as such but I too would like to register my disappointment with the "fix" described at the link below for the handling (or otherwise, as the case may be) of duplicate column names in subqueries:

http://bugs.mysql.com/bug.php?id=6709

I understand that the original "fix" was applied to address an SQL standard compliance issue, but the "solution" seems somewhat barbaric - why not prepend the subquery alias to the column name in the form alias.columnname to generate a unique identifier, rather than to have the query fail?  If this conflicts somehow with the SQL specification (which, on limited reading, I don't think it does, the option of an implementation-dependent response being available), does it seem a reasonable adjustment/concession?

I only stumbled across this problem after developing a website with PHP and MySQL 4.1.2.2 on Windows, then moving it to MySQL 5 on Linux (Ubuntu, precise version unknown and not to hand to discover...) - even if the original "bug" needed to be fixed, the solution isn't very graceful.

...and I can't see any warning of it in the release notes... and as people move their websites and ISPs use different versions of MySQL, couldn't/shouldn't this have been foreseen?

Any chance of an "implementation-dependent" workaround to this (such, perhaps, as described above), rather than the "guillotine" approach as at present?

Many thanks.
Gareth

How to repeat:
See http://dev.mysql.com/doc/refman/5.0/en/unnamed-views.html

Suggested fix:
Prepend alias to column name instead of failing - allows for graceful fix by website developers and takes less time than substantially re-engineering whole (long) SQL queries.

Thank you for the report.

I am afraid this is not a bug: bug #6709 contains parts of SQL standard regarding to this problem. See comments by Trudy Pelzer.

Also I don't understand how alias in the result set can help in case of query like " select * from (select 1 as a, 1 as a) as t1, (select 1 as a, 1 as a) as t2;". I mean if MySQL server will do it. This can break many applications which don't expect any alias here.

Anyway why just don't use MySQL Proxy to rewrite queries?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".