[
https://issues.apache.org/jira/browse/CALCITE-5020?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Zhengqiang Duan updated CALCITE-5020:
-------------------------------------
Description:
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}
was:
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_idPostgreSQL:
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}
> 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)
