> 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) > 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. On Friday, May 13, 2016 at 2:52:23 AM UTC-7, Lukas Eder wrote: > > Hi Daniel > > 2016-05-12 20:20 GMT+02:00 <[email protected] <javascript:>>: > >> I think I found a problem in the jooq Field type hierarchy. >> >> Lets say I start with a few TableFields. My SQL generation does some >> field filtering based off of fields' tables, so later code needs to be able >> to call .getTable() on them. >> >> Now, lets say I'm joining across two tables and have two columns called >> ID. In certain queries, this will create a "Duplicate column name 'id'" >> error (eg if these are part of an inner subselect that gets selected out). >> >> So, I need to alias one of my TableFields, for example MY_TABLE.ID.as >> ("joinedId"). >> >> However, because .as() is part of the Field interface and returns a >> Field, my code that depends on a list of TableFields to do the .getTable() >> breaks apart at the type level. I can't typecast back up to TableField >> because .as() actually returns a different implementation under the hood. >> > > That's correct. > > >> Is there any reason why the TableField interface can't override the >> inherited .as() function and declare it as returning a TableField instead >> of a Field? >> > > Yes, because the returned field (expression) is no longer a TableField. > When you declare an aliased field (in the SELECT clause), it does render > the underlying table on the left hand side of the AS keyword, but the > expression itself (e.g. as rendered in the ORDER BY clause) really does not > have any notion of being part of a table. Specifically, there's a use-case > when you use an alias to dereference a column from a nested select / > derived table: > > Field<?> joinedId = MY_TABLE.ID.as("joinedId"); > Table<?> derivedTable = > table(select(joinedId).from(...)).as("derivedTable"); > Field<?> derivedTableJoinedId = derivedTable.field(joinedId); // This is > now "derivedTable"."joinedId" > > > 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 the end, the only thing that > is really guaranteed is the fact that your field's name is "joinedId". > > May have to play with the underlying Impl's to get it right, but in >> theory, calling TableField.as() /should/ return a TableField, just with an >> added alias. >> > > No, because it would be wrong to fully qualify the alias with the original > table name. The original table name no longer exists. The only thing that > exists now is the alias. > > Note, this is different from aliasing a table, which produces a new table > with columns that reference the aliased table. > > Alternatively, is there a different way I can alias one of my TableFields >> while still being able to later call .getTable() on it? >> > > There are many ways to solve similar problems. I think it would help to > first better understand your use-case, e.g. why you want to disambiguate > (in a generic fashion) the duplicate ID columns. For instance, SQL doesn't > have any problems with duplicate column names in top level selects. Neither > does jOOQ. So, what's the use-case you're trying to solve here? > > 2016-05-12 20:20 GMT+02:00 <[email protected] <javascript:>>: > >> I think I found a problem in the jooq Field type hierarchy. >> >> Lets say I start with a few TableFields. My SQL generation does some >> field filtering based off of fields' tables, so later code needs to be able >> to call .getTable() on them. >> >> Now, lets say I'm joining across two tables and have two columns called >> ID. In certain queries, this will create a "Duplicate column name 'id'" >> error (eg if these are part of an inner subselect that gets selected out). >> >> So, I need to alias one of my TableFields, for example MY_TABLE.ID.as >> ("joinedId"). >> >> However, because .as() is part of the Field interface and returns a >> Field, my code that depends on a list of TableFields to do the .getTable() >> breaks apart at the type level. I can't typecast back up to TableField >> because .as() actually returns a different implementation under the hood. >> >> ----- >> >> Is there any reason why the TableField interface can't override the >> inherited .as() function and declare it as returning a TableField instead >> of a Field? May have to play with the underlying Impl's to get it right, >> but in theory, calling TableField.as() /should/ return a TableField, just >> with an added alias. >> >> Alternatively, is there a different way I can alias one of my TableFields >> while still being able to later call .getTable() on it? >> >> -- >> 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] <javascript:>. >> 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.
