[
https://issues.apache.org/jira/browse/CALCITE-5020?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17497839#comment-17497839
]
Julian Hyde commented on CALCITE-5020:
--------------------------------------
I think the problem is that Calcite's validator makes aliases unique. The JDBC
driver is just reflecting the aliases that the validator has assigned.
If we were to stop making aliases unique, the following query would fail:
{noformat}
> select deptno0 from (select * from emps join depts on emps.deptno =
> depts.deptno);
+---------+
| DEPTNO0 |
+---------+
| 10 |
| 20 |
| 20 |
+---------+
3 rows selected (0.082 seconds)
{noformat}
It fails in Postgres:
{noformat}
select * from (select * from emp,dept);
ERROR: subquery in FROM must have an alias Hint: For example, FROM (SELECT ...)
[AS] foo. Position: 15
{noformat}
I am open to changing the validator to not make the names unique. But it would
have significant impact, because some sub-queries that are currently valid
(because we make the names unique) would fail.
For what it's worth, I think the only thing we should fix is that we make names
unique. We are correct to use the names assigned by the validator, rather than
naively looking for 'AS' as the JBDC spec suggests. And I think the rules by
which we derive labels are just fine. Postgres does the same as Calcite in both
of these regards, as the next two examples show.
In this Postgres query, there is no top-level 'AS', but the first "deptno"
column is derived from a column in a subquery:
{noformat}
select * from (select lower(dname) as deptno from dept) as t,dept
deptno deptno dname loc
------ ------ ----- ---
accounting,10,ACCOUNTING,NEW YORK
...
{noformat}
In another Postgres query, the alias is derived from a function name, "lower",
not a column name:
{noformat}
select lower from (select lower(dname) from dept) as t
lower
------------
accounting
research
sales
operations
{noformat}
> ResultSetMetaData.getColumnLabel should return columnName when not use column
> alias
> -----------------------------------------------------------------------------------
>
> Key: CALCITE-5020
> URL: https://issues.apache.org/jira/browse/CALCITE-5020
> Project: Calcite
> Issue Type: Bug
> Components: jdbc-driver
> Affects Versions: 1.27.0
> Reporter: Zhengqiang Duan
> Priority: Major
>
> I have two tables as below, t_order and t_order_item.
> {code:java}
> CREATE TABLE `t_order` (
> `order_id` bigint(20) unsigned NOT NULL,
> `user_id` int(11) DEFAULT NULL,
> `content` varchar(100) DEFAULT NULL,
> `creation_date` date DEFAULT NULL,
> PRIMARY KEY (`order_id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
> CREATE TABLE `t_order_item` (
> `item_id` int(11) DEFAULT NULL,
> `order_id` int(11) NOT NULL,
> `user_id` int(11) DEFAULT NULL,
> `content` varchar(100) DEFAULT NULL,
> PRIMARY KEY (`order_id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; {code}
> Since they have columns order_id and user_id with the same name, the
> ResultSetMetaData returned using calcite is as follows:
> {code:java}
> TableName: t_order, ColumnName: order_id, ColumnLabel: order_id
> TableName: t_order, ColumnName: user_id, ColumnLabel: user_id
> TableName: t_order, ColumnName: content, ColumnLabel: content
> TableName: t_order, ColumnName: creation_date, ColumnLabel: creation_date
> TableName: t_order_item, ColumnName: item_id, ColumnLabel: item_id
> TableName: t_order_item, ColumnName: order_id, ColumnLabel: order_id0
> TableName: t_order_item, ColumnName: user_id, ColumnLabel: user_id0 {code}
> The column label corresponding to orderId and userId in the t_order_item
> table returned orderId0 and userId0, which seems to be inconsistent with the
> [JDBC
> specification|https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSetMetaData.html#getColumnLabel-int-[].].]
> As described in the JDBC specification, if a SQL AS is not specified, the
> value returned from getColumnLabel will be the same as the value returned by
> the getColumnName method.
> For comparison, I tested the results returned by MySQL and PostgreSQL, both
> of which follow the JDBC specification.
> {code:java}
> MySQL:
> TableName: t_order, ColumnName: order_id, ColumnLabel: order_id
> TableName: t_order, ColumnName: user_id, ColumnLabel: user_id
> TableName: t_order, ColumnName: content, ColumnLabel: content
> TableName: t_order, ColumnName: creation_date, ColumnLabel: creation_date
> TableName: t_order_item, ColumnName: item_id, ColumnLabel: item_id
> TableName: t_order_item, ColumnName: order_id, ColumnLabel: order_id
> TableName: t_order_item, ColumnName: user_id, ColumnLabel: user_id
> PostgreSQL:
> TableName: t_order, ColumnName: order_id, ColumnLabel: order_id
> TableName: t_order, ColumnName: user_id, ColumnLabel: user_id
> TableName: t_order, ColumnName: content, ColumnLabel: content
> TableName: t_order, ColumnName: creation_date, ColumnLabel: creation_date
> TableName: t_order_item, ColumnName: item_id, ColumnLabel: item_id
> TableName: t_order_item, ColumnName: order_id, ColumnLabel: order_id
> TableName: t_order_item, ColumnName: user_id, ColumnLabel: user_id{code}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
