Analyzing SQL queries with default catalog/database names

[Zheng Shao <zh...@uber.com.INVALID>]

Hi Calcite Dev,

What is the recommended way to analyze SQL queries with default
catalog/database names?

For example, for query like "SELECT * FROM foo" where foo maps to a table
in hive.my_database.

In Project Coral
<https://github.com/linkedin/coral/blob/master/coral-hive/src/main/java/com/linkedin/coral/hive/hive2rel/parsetree/ParseTreeBuilder.java#L579>,
the code tries to add catalog/database names right after the parsing stage,
but that would not work for cases like "WITH foo AS (SELECT * FROM mytable)
SELECT * from foo" in which case there is no need to prepend.

-- 
Zheng

Re: Analyzing SQL queries with default catalog/database names

[Julian Hyde <jh...@gmail.com>]

A key step in validation is to fully-quality table names. Thus in your example, mytable would become the string array [“hive”, “my_database”, “mytable”]. Any my_database2.mytable2 would become [“hive”, “my_database2”, “mytable2”]. But foo would remain foo, because it is in the query and is not a table.

The validator’s context is a path, which looks something like [[], [“hive”], [“hive”, “my_database”]]. Fully-, partially- and non-qualified table names are all looked up within that path.

Julian


> On Aug 17, 2021, at 12:02 AM, Zheng Shao <zh...@uber.com.INVALID> wrote:
> 
> Hi Calcite Dev,
> 
> What is the recommended way to analyze SQL queries with default
> catalog/database names?
> 
> For example, for query like "SELECT * FROM foo" where foo maps to a table
> in hive.my_database.
> 
> In Project Coral
> <https://github.com/linkedin/coral/blob/master/coral-hive/src/main/java/com/linkedin/coral/hive/hive2rel/parsetree/ParseTreeBuilder.java#L579>,
> the code tries to add catalog/database names right after the parsing stage,
> but that would not work for cases like "WITH foo AS (SELECT * FROM mytable)
> SELECT * from foo" in which case there is no need to prepend.
> 
> -- 
> Zheng