[MDEV-12491] JOIN: solve field name ambiguity when possible Created: 2017-04-12 Updated: 2017-05-24 |
|
| Status: | Open |
| Project: | MariaDB Server |
| Component/s: | Parser |
| Fix Version/s: | None |
| Type: | Task | Priority: | Minor |
| Reporter: | Federico Razzoli | Assignee: | Unassigned |
| Resolution: | Unresolved | Votes: | 0 |
| Labels: | join, semi-join | ||
| Description |
|
With JOINs, sometimes we get this error:
It happens because multiple tables share the same column name, and we did not reference it in the form `table`.`column`. But sometimes the user didn't because he doesn't think it's necessary, and from a purely logical point of view he's right. I can think at least 2 cases. 1) Think this ON:
If we are only joining t1 and t2, the values are identical. 2) Think about a semi-join:
Unless we are joining more tables, we mean ORDER BY t1.name. I know that the language allows us to order by a constant NULL value. But in a real case semi-join it seems to me perfectly safe to assume that we are referencing a column from t1. |
| Comments |
| Comment by Daniel Black [ 2017-04-12 ] | |||||||||||||||||||
|
Case 1) you can use USING Case 2) When SQL becomes a compiled language perhaps this can be optimised out, but until then CPU cycles are required to replace your programmers brain making stuff slower. Until then lets stick to Postel's Law | |||||||||||||||||||
| Comment by Federico Razzoli [ 2017-04-12 ] | |||||||||||||||||||
|
Honestly, I don't understand why this is a problem (the error message is clear). But it is, for a lot of developers. I won't insist on this, but before just saying "no", please take a look at StackOverflow or similar sites. | |||||||||||||||||||
| Comment by Elena Stepanova [ 2017-04-12 ] | |||||||||||||||||||
|
No matter how it works, there will always be complaints on resources like StackOverflow, because people only care about their scenario and ignore any other possibilities. Say you introduce the "optimization" for the first case. The first complaint will immediately be something like "why does the query work with t1.id = t2.id, but fails with t1.id = t2.id or t1.id is NULL?!" , especially if the query is generated by an application and can take various forms. Same with the second example. | |||||||||||||||||||
| Comment by Sergei Golubchik [ 2017-05-24 ] | |||||||||||||||||||
|
The first example, with ON is also ambiguous:
But even USING doesn't solve it:
So, I'd agree that ON can do the same as USING when resolving column names. |