You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Juan Pan <pa...@apache.org> on 2020/01/06 07:42:44 UTC
[QUESTION] How could getTableName(columnIndex) return the correct
result?
Hi Calcite Community,
Thanks for your attention. After failing self-helping by debug source code, i sent this email for your help. :)
My query SQL is `SELECT count(*) a FROM test`, and i called JDBC interface, i.e, `ResultSet.getMetaData().getTableName(1)` to get table name, i.e, test, however the result of which is null.
I traced the process and found that if !(selectItem instanceof SqlIdentifier) then return null in `SqlValidatorImpl.java`. Is there any way to get the real table name, i.e, test?
Thanks in advance,
Trista
Juan Pan (Trista)
Senior DBA & PPMC of Apache ShardingSphere(Incubating)
E-mail: panjuan@apache.org
Re: [QUESTION] How could getTableName(columnIndex) return the correct result?
Posted by Julian Hyde <jh...@apache.org>.
I have logged https://issues.apache.org/jira/browse/CALCITE-3716. No
one is working on fixing it currently.
On Tue, Jan 7, 2020 at 2:14 AM Juan Pan <pa...@apache.org> wrote:
>
> Thanks your explanation, Julian. Does it mean the optimization of this JDBC interface may be included in next release of Calcite?
>
>
> Juan Pan (Trista)
>
> Senior DBA & PPMC of Apache ShardingSphere(Incubating)
> E-mail: panjuan@apache.org
>
>
>
>
> On 01/7/2020 11:17，Julian Hyde<jh...@apache.org> wrote：
> Yes, we should be returning “” rather than null.
>
> (Not an excuse, but that method is so old that I suspect that the authors of JDBC were still thinking in terms of ODBC. In C it’s difficult to return a null, it’s easier to return an empty string.)
>
> Julian
>
>
> On Jan 6, 2020, at 7:03 PM, Juan Pan <pa...@apache.org> wrote:
>
> FYI.
>
>
> The following information comes from `java.sql.ResultSetMetaData`.
>
>
> /**
> * Gets the designated column's table name.
> *
> * @param column the first column is 1, the second is 2, ...
> * @return table name or "" if not applicable
> * @exception SQLException if a database access error occurs
> */
> String getTableName(int column) throws SQLException;
>
>
> Juan Pan (Trista)
>
> Senior DBA & PPMC of Apache ShardingSphere(Incubating)
> E-mail: panjuan@apache.org
>
>
>
>
> On 01/7/2020 10:56，Juan Pan<pa...@apache.org> wrote：
> Hi Julian,
>
>
> You’re right. From my tests, since “a” is not from table test, getTableName(columnIndex) returns `empty string` from MySQL and H2 databases, and `null` from calcite. It makes sense.
> The scenario happened to me is that some of third-part applications or open-source projects would call some jdbc interfaces, like getTableName(columnIndex).
> As a result, when they call getTableName(columnIndex), the null result from calcite makes them throw NPE, but empty string from DBs avoid this case.
>
>
> Julian, very appreciated your help. :-)
>
>
> Best wishes,
> Trista
>
>
> Juan Pan (Trista)
>
> Senior DBA & PPMC of Apache ShardingSphere(Incubating)
> E-mail: panjuan@apache.org
>
>
>
>
> On 01/7/2020 04:12，Julian Hyde<jh...@apache.org> wrote：
> JDBC table names and column names are of limited use. They tell you where a particular column comes from, and your “a” column does not come (directly) from a table. I think you’ll find that Calcite is implementing the JDBC standard correctly, and is consistent with other databases.
>
> What do you need the table name for?
>
> If you want to understand the structure of the query - e.g. the fact that the query is sourced from the “test” table - then your might be better working with the SqlNode or RelNode representations. The RelNode representation of your query is
>
>
> Aggregate(count(*) as a)
> ^
> |
> TableScan(“test”)
>
> and that probably tells you what you need to know.
>
> Julian
>
>
> On Jan 5, 2020, at 11:42 PM, Juan Pan <pa...@apache.org> wrote:
>
>
>
> Hi Calcite Community,
>
>
> Thanks for your attention. After failing self-helping by debug source code, i sent this email for your help. :)
>
>
> My query SQL is `SELECT count(*) a FROM test`, and i called JDBC interface, i.e, `ResultSet.getMetaData().getTableName(1)` to get table name, i.e, test, however the result of which is null.
> I traced the process and found that if !(selectItem instanceof SqlIdentifier) then return null in `SqlValidatorImpl.java`. Is there any way to get the real table name, i.e, test?
>
>
> Thanks in advance,
>
>
> Trista
>
>
>
>
>
>
> Juan Pan (Trista)
>
> Senior DBA & PPMC of Apache ShardingSphere(Incubating)
> E-mail: panjuan@apache.org
>
>
>
Re: [QUESTION] How could getTableName(columnIndex) return the
correct result?
Posted by Juan Pan <pa...@apache.org>.
Thanks your explanation, Julian. Does it mean the optimization of this JDBC interface may be included in next release of Calcite?
Juan Pan (Trista)
Senior DBA & PPMC of Apache ShardingSphere(Incubating)
E-mail: panjuan@apache.org
On 01/7/2020 11:17，Julian Hyde<jh...@apache.org> wrote：
Yes, we should be returning “” rather than null.
(Not an excuse, but that method is so old that I suspect that the authors of JDBC were still thinking in terms of ODBC. In C it’s difficult to return a null, it’s easier to return an empty string.)
Julian
On Jan 6, 2020, at 7:03 PM, Juan Pan <pa...@apache.org> wrote:
FYI.
The following information comes from `java.sql.ResultSetMetaData`.
/**
* Gets the designated column's table name.
*
* @param column the first column is 1, the second is 2, ...
* @return table name or "" if not applicable
* @exception SQLException if a database access error occurs
*/
String getTableName(int column) throws SQLException;
Juan Pan (Trista)
Senior DBA & PPMC of Apache ShardingSphere(Incubating)
E-mail: panjuan@apache.org
On 01/7/2020 10:56，Juan Pan<pa...@apache.org> wrote：
Hi Julian,
You’re right. From my tests, since “a” is not from table test, getTableName(columnIndex) returns `empty string` from MySQL and H2 databases, and `null` from calcite. It makes sense.
The scenario happened to me is that some of third-part applications or open-source projects would call some jdbc interfaces, like getTableName(columnIndex).
As a result, when they call getTableName(columnIndex), the null result from calcite makes them throw NPE, but empty string from DBs avoid this case.
Julian, very appreciated your help. :-)
Best wishes,
Trista
Juan Pan (Trista)
Senior DBA & PPMC of Apache ShardingSphere(Incubating)
E-mail: panjuan@apache.org
On 01/7/2020 04:12，Julian Hyde<jh...@apache.org> wrote：
JDBC table names and column names are of limited use. They tell you where a particular column comes from, and your “a” column does not come (directly) from a table. I think you’ll find that Calcite is implementing the JDBC standard correctly, and is consistent with other databases.
What do you need the table name for?
If you want to understand the structure of the query - e.g. the fact that the query is sourced from the “test” table - then your might be better working with the SqlNode or RelNode representations. The RelNode representation of your query is
Aggregate(count(*) as a)
^
|
TableScan(“test”)
and that probably tells you what you need to know.
Julian
On Jan 5, 2020, at 11:42 PM, Juan Pan <pa...@apache.org> wrote:
Hi Calcite Community,
Thanks for your attention. After failing self-helping by debug source code, i sent this email for your help. :)
My query SQL is `SELECT count(*) a FROM test`, and i called JDBC interface, i.e, `ResultSet.getMetaData().getTableName(1)` to get table name, i.e, test, however the result of which is null.
I traced the process and found that if !(selectItem instanceof SqlIdentifier) then return null in `SqlValidatorImpl.java`. Is there any way to get the real table name, i.e, test?
Thanks in advance,
Trista
Juan Pan (Trista)
Senior DBA & PPMC of Apache ShardingSphere(Incubating)
E-mail: panjuan@apache.org
Re: [QUESTION] How could getTableName(columnIndex) return the correct
result?
Posted by Julian Hyde <jh...@apache.org>.
Yes, we should be returning “” rather than null.
(Not an excuse, but that method is so old that I suspect that the authors of JDBC were still thinking in terms of ODBC. In C it’s difficult to return a null, it’s easier to return an empty string.)
Julian
> On Jan 6, 2020, at 7:03 PM, Juan Pan <pa...@apache.org> wrote:
>
> FYI.
>
>
> The following information comes from `java.sql.ResultSetMetaData`.
>
>
> /**
> * Gets the designated column's table name.
> *
> * @param column the first column is 1, the second is 2, ...
> * @return table name or "" if not applicable
> * @exception SQLException if a database access error occurs
> */
> String getTableName(int column) throws SQLException;
>
>
> Juan Pan (Trista)
>
> Senior DBA & PPMC of Apache ShardingSphere(Incubating)
> E-mail: panjuan@apache.org
>
>
>
>
> On 01/7/2020 10:56，Juan Pan<pa...@apache.org> wrote：
> Hi Julian,
>
>
> You’re right. From my tests, since “a” is not from table test, getTableName(columnIndex) returns `empty string` from MySQL and H2 databases, and `null` from calcite. It makes sense.
> The scenario happened to me is that some of third-part applications or open-source projects would call some jdbc interfaces, like getTableName(columnIndex).
> As a result, when they call getTableName(columnIndex), the null result from calcite makes them throw NPE, but empty string from DBs avoid this case.
>
>
> Julian, very appreciated your help. :-)
>
>
> Best wishes,
> Trista
>
>
> Juan Pan (Trista)
>
> Senior DBA & PPMC of Apache ShardingSphere(Incubating)
> E-mail: panjuan@apache.org
>
>
>
>
> On 01/7/2020 04:12，Julian Hyde<jh...@apache.org> wrote：
> JDBC table names and column names are of limited use. They tell you where a particular column comes from, and your “a” column does not come (directly) from a table. I think you’ll find that Calcite is implementing the JDBC standard correctly, and is consistent with other databases.
>
> What do you need the table name for?
>
> If you want to understand the structure of the query - e.g. the fact that the query is sourced from the “test” table - then your might be better working with the SqlNode or RelNode representations. The RelNode representation of your query is
>
>
> Aggregate(count(*) as a)
> ^
> |
> TableScan(“test”)
>
> and that probably tells you what you need to know.
>
> Julian
>
>
> On Jan 5, 2020, at 11:42 PM, Juan Pan <pa...@apache.org> wrote:
>
>
>
> Hi Calcite Community,
>
>
> Thanks for your attention. After failing self-helping by debug source code, i sent this email for your help. :)
>
>
> My query SQL is `SELECT count(*) a FROM test`, and i called JDBC interface, i.e, `ResultSet.getMetaData().getTableName(1)` to get table name, i.e, test, however the result of which is null.
> I traced the process and found that if !(selectItem instanceof SqlIdentifier) then return null in `SqlValidatorImpl.java`. Is there any way to get the real table name, i.e, test?
>
>
> Thanks in advance,
>
>
> Trista
>
>
>
>
>
>
> Juan Pan (Trista)
>
> Senior DBA & PPMC of Apache ShardingSphere(Incubating)
> E-mail: panjuan@apache.org
>
>
>
Re: [QUESTION] How could getTableName(columnIndex) return the
correct result?
Posted by Juan Pan <pa...@apache.org>.
FYI.
The following information comes from `java.sql.ResultSetMetaData`.
/**
* Gets the designated column's table name.
*
* @param column the first column is 1, the second is 2, ...
* @return table name or "" if not applicable
* @exception SQLException if a database access error occurs
*/
String getTableName(int column) throws SQLException;
Juan Pan (Trista)
Senior DBA & PPMC of Apache ShardingSphere(Incubating)
E-mail: panjuan@apache.org
On 01/7/2020 10:56，Juan Pan<pa...@apache.org> wrote：
Hi Julian,
You’re right. From my tests, since “a” is not from table test, getTableName(columnIndex) returns `empty string` from MySQL and H2 databases, and `null` from calcite. It makes sense.
The scenario happened to me is that some of third-part applications or open-source projects would call some jdbc interfaces, like getTableName(columnIndex).
As a result, when they call getTableName(columnIndex), the null result from calcite makes them throw NPE, but empty string from DBs avoid this case.
Julian, very appreciated your help. :-)
Best wishes,
Trista
Juan Pan (Trista)
Senior DBA & PPMC of Apache ShardingSphere(Incubating)
E-mail: panjuan@apache.org
On 01/7/2020 04:12，Julian Hyde<jh...@apache.org> wrote：
JDBC table names and column names are of limited use. They tell you where a particular column comes from, and your “a” column does not come (directly) from a table. I think you’ll find that Calcite is implementing the JDBC standard correctly, and is consistent with other databases.
What do you need the table name for?
If you want to understand the structure of the query - e.g. the fact that the query is sourced from the “test” table - then your might be better working with the SqlNode or RelNode representations. The RelNode representation of your query is
Aggregate(count(*) as a)
^
|
TableScan(“test”)
and that probably tells you what you need to know.
Julian
On Jan 5, 2020, at 11:42 PM, Juan Pan <pa...@apache.org> wrote:
Hi Calcite Community,
Thanks for your attention. After failing self-helping by debug source code, i sent this email for your help. :)
My query SQL is `SELECT count(*) a FROM test`, and i called JDBC interface, i.e, `ResultSet.getMetaData().getTableName(1)` to get table name, i.e, test, however the result of which is null.
I traced the process and found that if !(selectItem instanceof SqlIdentifier) then return null in `SqlValidatorImpl.java`. Is there any way to get the real table name, i.e, test?
Thanks in advance,
Trista
Juan Pan (Trista)
Senior DBA & PPMC of Apache ShardingSphere(Incubating)
E-mail: panjuan@apache.org
Re: [QUESTION] How could getTableName(columnIndex) return the
correct result?
Posted by Juan Pan <pa...@apache.org>.
Hi Julian,
You’re right. From my tests, since “a” is not from table test, getTableName(columnIndex) returns `empty string` from MySQL and H2 databases, and `null` from calcite. It makes sense.
The scenario happened to me is that some of third-part applications or open-source projects would call some jdbc interfaces, like getTableName(columnIndex).
As a result, when they call getTableName(columnIndex), the null result from calcite makes them throw NPE, but empty string from DBs avoid this case.
Julian, very appreciated your help. :-)
Best wishes,
Trista
Juan Pan (Trista)
Senior DBA & PPMC of Apache ShardingSphere(Incubating)
E-mail: panjuan@apache.org
On 01/7/2020 04:12，Julian Hyde<jh...@apache.org> wrote：
JDBC table names and column names are of limited use. They tell you where a particular column comes from, and your “a” column does not come (directly) from a table. I think you’ll find that Calcite is implementing the JDBC standard correctly, and is consistent with other databases.
What do you need the table name for?
If you want to understand the structure of the query - e.g. the fact that the query is sourced from the “test” table - then your might be better working with the SqlNode or RelNode representations. The RelNode representation of your query is
Aggregate(count(*) as a)
^
|
TableScan(“test”)
and that probably tells you what you need to know.
Julian
On Jan 5, 2020, at 11:42 PM, Juan Pan <pa...@apache.org> wrote:
Hi Calcite Community,
Thanks for your attention. After failing self-helping by debug source code, i sent this email for your help. :)
My query SQL is `SELECT count(*) a FROM test`, and i called JDBC interface, i.e, `ResultSet.getMetaData().getTableName(1)` to get table name, i.e, test, however the result of which is null.
I traced the process and found that if !(selectItem instanceof SqlIdentifier) then return null in `SqlValidatorImpl.java`. Is there any way to get the real table name, i.e, test?
Thanks in advance,
Trista
Juan Pan (Trista)
Senior DBA & PPMC of Apache ShardingSphere(Incubating)
E-mail: panjuan@apache.org
Re: [QUESTION] How could getTableName(columnIndex) return the correct
result?
Posted by Julian Hyde <jh...@apache.org>.
JDBC table names and column names are of limited use. They tell you where a particular column comes from, and your “a” column does not come (directly) from a table. I think you’ll find that Calcite is implementing the JDBC standard correctly, and is consistent with other databases.
What do you need the table name for?
If you want to understand the structure of the query - e.g. the fact that the query is sourced from the “test” table - then your might be better working with the SqlNode or RelNode representations. The RelNode representation of your query is
Aggregate(count(*) as a)
^
|
TableScan(“test”)
and that probably tells you what you need to know.
Julian
> On Jan 5, 2020, at 11:42 PM, Juan Pan <pa...@apache.org> wrote:
>
>
>
> Hi Calcite Community,
>
>
> Thanks for your attention. After failing self-helping by debug source code, i sent this email for your help. :)
>
>
> My query SQL is `SELECT count(*) a FROM test`, and i called JDBC interface, i.e, `ResultSet.getMetaData().getTableName(1)` to get table name, i.e, test, however the result of which is null.
> I traced the process and found that if !(selectItem instanceof SqlIdentifier) then return null in `SqlValidatorImpl.java`. Is there any way to get the real table name, i.e, test?
>
>
> Thanks in advance,
>
>
> Trista
>
>
>
>
>
>
> Juan Pan (Trista)
>
> Senior DBA & PPMC of Apache ShardingSphere(Incubating)
> E-mail: panjuan@apache.org
>
>
>