Home » Bugs » BUG 67 : avoid duplicate column names with associations
 Bug 67 : avoid duplicate column names with associations
Priority 
High
 Reported Version 
 
 Logged By 
imario
Status 
Fixed
 Fixed Version 
1.0.3
 Assigned To 
 
Product 
Ebean - core
 Duplicate Of 
 
 Created 
14/02/2009
Updated 
14/02/2009
 Type 
Bug
  
Attachments 
No attachments

This patch will alias each and every column so that, even if columns are duplicate in the sql due to associations, the query will work.


Index: ../ebean/src/com/avaje/ebean/server/query/DefaultDbSqlContext.java
===================================================================
--- ../ebean/src/com/avaje/ebean/server/query/DefaultDbSqlContext.java (revision 127)
+++ ../ebean/src/com/avaje/ebean/server/query/DefaultDbSqlContext.java Sat Feb 14 12:38:39 CET 2009
@@ -18,6 +18,7 @@
private final String tableAliasPlaceHolder;

private final StringBuilder sb = new StringBuilder();
+ private int columnIndex;

private final Stack tableAliasStack = new Stack();

@@ -118,6 +119,9 @@
sb.append(tableAlias);
sb.append(PERIOD);
sb.append(column);
+
+ sb.append(" as c_" + columnIndex);
+ columnIndex++;
}

public String toString() {
 
Rob 16 Feb 09:59
Hmmm, not sure...

I think I want to know how the problem occurs... in that "... even if columns are duplicate in the sql due to associations...".

I'm not sure how the columns can be duplicate in the sql... in that the columns get a table alias prefix. So, can you give me an example of how to recreate the problem.

I'm not keen on the code change suggested as the tableAlias.column must be unique to enable predicate parsing for where clauses and order by clauses etc... so alias'ing the column is not as simple as the code change (the way I see it).

Thanks, Rob.

Rob 17 Feb 10:52
Making this a BUG...

I'm going to change this from an ENH to a BUG... in that it relates to Bug 66 and Oracle doesn't like 'ambiguous' column names for that scenario (of concatinated keys and multiple associations etc).

Also, I'm keen to tweak this a little... by default the column prefix to just "c" rather than "as c_" ... and make it configurable so that people can change it via ebean.properties if they need to.

So by default the SQL generated looks like:

select i.customer c0, i.itemNumber c1, i.description c2, i.type c3, i.region c4, i.version c5, i.customer c6, i.type c7, i.customer c8, i.region c9
from Item i
where customer = ? AND itemNumber = ?

And you can put in ebean.properties:

# change the column alias prefix in ebean.properties
# for the "h2" ebean server
ebean.h2.columnAliasPrefix=as column_


Resulting in SQL:

select i.customer as column_0, i.itemNumber as column_1, i.description as column_2, i.type as column_3, i.region as column_4, i.version as column_5, i.customer as column_6, i.type as column_7, i.customer as column_8, i.region as column_9
from Item i

Rob 17 Feb 10:54
Similarly...

ebean.columnAliasPrefix=as c_

Results in SQL as per the original suggestion:

select i.customer as c_0, i.itemNumber as c_1, i.description as c_2, i.type as c_3, i.region as c_4, i.version as c_5, i.customer as c_6, i.type as c_7, i.customer as c_8, i.region as c_9
from Item i

Rob 17 Feb 10:57
Let me know if the default is not so good

I prefer the default I have chosen as I find it less distracting in terms of reading the sql.

If someone objects I'm all ears about what to change it to. Yes I think there maybe people out there who use column names like "c0" etc but if there are then they can use the ebean.columnAliasPrefix setting in ebean.properties.

How does that sound?

Fixed in v1.0.3.

Rob 18 Feb 10:32
Thanks Mario

Thanks Mario.

woResponse

Upload a file