I suspect that the amount of confusion that this feature has caused in the
past really justifies going a step further. I've created an issue for this:
https://github.com/jOOQ/jOOQ/issues/4455
The interesting thing is really that there are still several levels of
approximation. For instance:
SELECT t.x, u.x
FROM (
SELECT some_table.x
FROM ...
) t, (
SELECT another_table.x
FROM ...
) u
In the above case, when looking for the some_table.x reference in the
result, intuitively there is no ambiguity. Specifically because t might
even be an auto-generated table alias, which the jOOQ API user doesn't
explicitly define as such.
This will need some further careful consideration before implementation.
Cheers,
Lukas
2015-07-31 16:28 GMT+02:00 <[email protected]>:
> Ah, thanks for the explanation.
>
> Without having looked much into the jooq source code, I guess one strategy
> would be when exact match has failed and falling back to approximate
> matching, throw in the case that the approximate match is ambiguous. A
> little bit of overhead to check for this case perhaps, however it would
> defend against subtle bugs such as the one I found in our code. Perhaps
> given that the overhead wouldn't exist if exact matching succeeded, it's
> justifiable?
>
> Cheers,
> Dave
>
> On Monday, 20 July 2015 16:38:15 UTC+1, Lukas Eder wrote:
>>
>> Oh, I see - thanks for the clarification, so I understood correctly.
>> The rationale behind the existing feature is to allow for "unknown" table
>> aliases to be applied (e.g. when using derived tables) and to still use the
>> generated column literals to extract values for columns of such tables.
>> Example:
>>
>> SELECT t.x, t.y
>> FROM (
>> SELECT some_table.x, some_table.y
>> FROM ...
>> ) t
>>
>>
>> In the above example, you don't need to construct a formal t.x or t.y
>> column expression to extract x or y values from the result. You can
>> continue using SOME_TABLE.A generated literals. The algorithm works such
>> that exact matches (table AND column names match) are preferred to
>> "approximate" matches (only column names match).
>>
>> As a side-effect, duplicate column names may produce unexpected behaviour
>> as you have observed.
>>
>> This issue has surfaced the user group on numerous occasions. It is
>> actually not trivial to find the exact set of rules when exceptions should
>> be thrown as ambiguous column names in top-level SELECTs are a tricky thing
>> in SQL already. For instance, they're not allowed in nested selects... I'm
>> very open to concrete suggestions for a set of rules, though
>>
>> Cheers
>> Lukas
>>
>> 2015-07-18 15:29 GMT+02:00 <[email protected]>:
>>
>>> Yes - although in my case both tables had a column called 'name'.
>>>
>>> The getValue call was iterating through the retrieved fields and
>>> returning the first thing called 'name'
>>>
>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "jOOQ User Group" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to [email protected].
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
