[jira] [Commented] (CALCITE-4357) DESCRIBE SCHEMA throws "Column not found" exception

["Louis Kuang (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-4357?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17255315#comment-17255315 ] 

Louis Kuang commented on CALCITE-4357:
--------------------------------------

For "DESCRIBE SCHEMA", I believe you are missing the "." in front of the "schemaName". You can execute
{noformat}
!describe schema .tpch{noformat}
in a shell. However, if you try to execute the statement "DESCRIBE SCHEMA .tpch" against the DB using JDBC, you will get a "Non-query expression encountered in illegal context" exception.

If you would like to get the meta data associated with a table, you can do something like "SELECT * FROM tpch.out_tpch_vw__lineitem" and obtain a `ResultSetMetaData` from the corresponding result set. So the complete example will be
{code:java}
Statement statement = connection.createStatement();
Resultset resultset = statement.executeQuery("SELECT * FROM tpch.out_tpch_vw__lineitem LIMIT 1");
ResultSetMetaData rsmd = resultSet.getMetaData();
for (int i = 0; i < rsmd.getColumnCount(); ++i) {
  flogger.atInfo().log(String.format("Column %d: %s", i+1, rsmd.getColumnName(i+1)));
}{code}
I only tried this with the example csv project but it should work for postgresql as well.

> DESCRIBE SCHEMA throws "Column not found" exception
> ---------------------------------------------------
>
>                 Key: CALCITE-4357
>                 URL: https://issues.apache.org/jira/browse/CALCITE-4357
>             Project: Calcite
>          Issue Type: Bug
>    Affects Versions: 1.27.0
>            Reporter: David Kubecka
>            Priority: Major
>
> I can succesfully connect to my Postgres DB via Calcite driver using this model:
> {code:java}
> {
>   "version": "1.0",
>   "defaultSchema": "tpch",
>   "schemas": [
>     {
>       "name": "tpch",
>       "type": "jdbc",
>       "jdbcUrl": "jdbc:postgresql://*",
>       "jdbcSchema": "tpch",
>       "jdbcUser": "*",
>       "jdbcPassword": "*"
>     }
>   ]
> }
> {code}
> I can execute SELECT queries but I get an unexpected and misleading error when I try {{DESCRIBE SCHEMA "tpch"}}:
> {code:java}
> [2020-10-26 18:32:22] Error while executing SQL "DESCRIBE SCHEMA "tpch"": From line 1, column 17 to line 1, column 22: Column 'tpch' not found in any table
> [2020-10-26 18:32:22] org.apache.calcite.sql.validate.SqlValidatorException: Column 'tpch' not found in any table
> {code}
> According to [Calcite SQL grammar|https://calcite.apache.org/docs/reference.html] I should have the syntax right:
> {code:java}
> DESCRIBE SCHEMA [ [ databaseName . ] catalogName ] . schemaName
> {code}
> Note that other DESCRIBE commands doesn't work as well, e.g.:
> {code:java}
> [2020-10-26 19:01:53] Error while executing SQL "DESCRIBE TABLE "out_tpch_vw__lineitem"": From line 1, column 16 to line 1, column 38: Column 'out_tpch_vw__lineitem' not found in any table
> [2020-10-26 19:01:53] org.apache.calcite.sql.validate.SqlValidatorException: Column 'out_tpch_vw__lineitem' not found in any table
> [2020-10-26 19:02:09] Error while executing SQL "DESCRIBE TABLE "tpch"."out_tpch_vw__lineitem"": From line 1, column 16 to line 1, column 21: Table 'tpch' not found
> [2020-10-26 19:02:09] org.apache.calcite.sql.validate.SqlValidatorException: Table 'tpch' not found
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)