2016-05-13 20:50 GMT+02:00 <[email protected]>: > > SQL doesn't have any problems with duplicate column names in top level > selects > > Top-level is the key. If you do duplicates in a subselect and try to > select them out, that's when you get the duplicate error. A simplified > case that produces an error in mysql: > > SELECT * FROM (SELECT `foo`.`id`, `bar`.`id` FROM `foo` JOIN `bar` ON > bar.foo_id = foo.id) >
Indeed, that is a common problem, especially when jOOQ emulates a feature by creating such a derived table. Perhaps, in order to find a solution here for you, could you explain how you generate these queries? It would help me better understand the use-case. I've often thought about a new feature that would allow aliasing all the columns automatically in an easy way... > As you can see, while there was, at some point, the notion of an ID > column that belongs to a MY_TABLE table, that notion cannot be maintained > throughout such expression transformations. > > In that particular case, yes, but I think the transform is fine returning > a Field, not a TableField. > > Field.as() can still return a Field, but I still think that > TableField.as() can return a TableField (albeit one with an alias in the > implementation). The transform that you're referring to could still return > a Field and be fine. eg the typing would look like this: > > TableField<?> joinedId = MY_TABLE.ID.as <http://my_table.id.as/>("joinedId"); > // TableField.as() is still a TableField, but with an alias > Table<?> derivedTable = > table(select(joinedId).from(...)).as("derivedTable"); > Field<?> derivedTableJoinedId = derivedTable.field(joinedId); // Becomes a > Field after the transform > > Wouldn't this still work? It seems to me that just because you alias a > TableField, there's no reason you should lose the ability to ask what table > it is from. That's of course not true for the general case of aliasing a > Field, but I'm saying Field.as() --> Field, TableField.as() --> TableField. > Yes it would still work. The example isn't perfect for what I'm trying to say here. The idea here is very clear, though. A TableField is a Field that generates a fully qualified column, e.g. "catalog"."schema"."table"."column", or "schema"."table"."column", or just "table"."column". There are other field types (e.g. DSL.field(Name)) that can also generate qualified column references. An aliased field (the object returned from the "as()" call) is *never* qualified. It does not have a table. In that sense, TableField.as() must not return a TableField, it would be wrong, conceptually. We have a composition over inheritance situation here. The relationship between types is: "aliased field" has-a "TableField", not "aliased field" is-a "TableField". The only thing that might be useful to add to a future jOOQ is the ability to: 1. Recognise aliased fields 2. Add API that exposes the original field (the one on the left of the AS clause), on which you could then call "getTable()" This would expose the "has-a" relationship to the public. It has been requested a couple of times before, not only for aliased fields. We're addressing this in jOOQ 4.0, when we open up more internals to allow for easier custom AST transformations. Having said so, there are many possible features / enhancements to explore. As far as your immediate problem is concerned, I'd prefer to better understand your use-case first, rather than discussing potential solutions. I think we're barking up the wrong tree with this TableField vs Field discussion. There's certainly a better solution for your immediate problem. -- 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.
