Choosing Ambiguous Columns about pgjdbc

[hiro2k]

Sorry this is a duplicate issue of my pull request. I thought I had to create an issue first and then associate it to the pull request.

[ringerc]

On 07/16/2014 06:49 AM, hiro2k wrote:

The JDBC spec says when you have duplicate columns names, the first one
should be returned but this behavior isn't useful to us. What we really
need is the ability to resolve those ambiguous cases in the client code.

Isn't that what query aliases are for?

SELECT table1.a AS t1a, table2.a AS t2a
FROM ...

I propose adding an additional string to the columnNameIndexMap that
would be in the format of tablename.column similar to how you would
reference the columns in SQL.

That's potentially sensible, but introduces a tricky conflict:

SELECT table1.a, table2."table1.a"
FROM ...

i.e. quoted identifiers may contain anything, including a period. In a
flat map like that there's no way to tell whether you're looking at a
field named "table1.a" or a field named "a" from table "table1".

Probably not a big issue in the real world - we'd just have to give
priority to unqualified identifiers in case of a conflict. Still worth
thinking about.

I've added a test case for this use case that shows that it works and
doesn't affect any existing test cases.

Heh. That mostly shows that our test coverage isn't ideal there.

That said, if the spec permits us to have >1 entry for a given index, I
don't have a big problem with doing what you suggest. I'd like to see a
lot more real-world app testing though.

Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

[hiro2k]

Hi ringerc, thanks for your feedback. You're correct that this is the use case for query aliases but this change helps when doing a rather stupid SELECT * FROM t1, t2, t3 ... and I'm not sure what to do about the quoted identifiers, other than stating that the current behavior is to load the map in reverse order so if it shows up first in the result set it will win and be present in the end.

This came up while I was porting an application from MySQL that has hundreds of those SELECT * queries. It was a big surprise when we found out that wasn't standard behavior in the jdbc spec and only the MySQL driver supported it, but we decided that it would be more work to change all of our queries than patching the postgres driver.

I've been maintaining this patch #172 for a long time, but I figured it would be better to try and get it accepted upstream and share this solution with anyone else trying to port a MySQL app.

[ringerc]

On 07/16/2014 10:58 PM, hiro2k wrote:

Hi ringerc, thanks for your feedback. You're correct that this is the
use case for query aliases but this change helps when doing a rather
stupid SELECT * FROM t1, t2, t3 ... and I'm not sure what to do about
the quoted identifiers, other than stating that the current behavior is
to load the map in reverse order so if it shows up first in the result
set it will win and be present in the end.

This came up while I was porting an application from MySQL that has
hundreds of those SELECT * queries. It was a big surprise when we found
out that wasn't standard behavior in the jdbc spec and only the MySQL
driver supported it, but we decided that it would be more work to change
all of our queries than patching the postgres driver.

I've been maintaining this patch #172
#172 for a long time, but I
figured it would be better to try and get it accepted upstream and share
this solution with anyone else trying to port a MySQL app.

That's a good reason to make such a change, for sure.

I think a good choice here might be to introduce a new parameter like
compatible= or emulate= that tells PgJDBC "try to support things that
this other database's driver does".

So that way we're not adding new params whenever someone wants to add a
corner case / quirk handler for some other DBMS's drivers too.

Thoughts?

I'm not sure how to name it though. I don't want to suggest that people
should expect the driver to emulate the database's syntax etc. We
won't be supporting quotes or [quotes] any time soon or returning true
for NULL = NULL.

driverpersonality=mysql ?

Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

[sehrope]

I don't foresee anybody enabling two compatibility modes at the same time as it makes no sense. Having a single property seems like a good idea. The embedded JDBC database H2 has a similar concept: http://www.h2database.com/html/features.html#compatibility

Couple alternate names to consider:

• drivermode
• compatibilitymode
• drivercompatibilitymode

Given that code that makes use of a property like this would be all over the place, we should make sure that the impact to non-usage isn't more than a boolean branch check.

For example in the case of this patch, putting aside whether it might break existing applications, the number of map insertions in findColumnIndex(...) doubles. That should be avoided.

[lordnelson]

I personally think the JDBC driver should stick to the spec rather than adding many vendor-specific flags and modes. As @ringerc mentioned, aliases are the standard way of handling this type of problem.