Query across schemas in same database

[Yogendra Sharma <sy...@live.com>]

Hi,

I have a couple of questions on calcite.

  1.   Assuming that I have a MySQL server which has two user databases: db1 and db2. If I wish to run a query "select * from db1.tab1 inner join db2.tab2 on db1.id = db2.id", do I need to add both databases in the root schema?
  2.
  3.  Secondly, even if I add one database say db1, the tableMap that we create has all the tables across all the databases in MySQL including system schema such as information schema and performance schema. Why?
  4.
  5.  Point (2) has created an issue for MySQL version 8 where a table name is common in mysql & information databases which leads to exception for duplicate key as we expect each table name to unique. I think I am reading something wrong here because Calcite shouldnt expect table names to be unique across catalogs/schemas?

Thanks,
Yogi

Re: Query across schemas in same database

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

I think you meant to write “tab1.id = tab2.id”. 

Based on your description I think there are a couple of things going wrong in the JDBC adapter. It should be putting the tables from different MySQL databases into different calcite schemas.  (A calcite schema is a namespace. It may map onto a database or a catalog or whatever in the underlying DBMS, but duplicate names should never be possible.)

I believe the JDBC adapter will currently regard your query as a federated query. There will be two JdbcConvention objects.  Therefore it will do the join in Calcite, not MySQL. To fix this, the JDBC adapter needs to be made smarter, so that it can look at tables that are in different schemas, know that they are local to each other, and know the correct way to generate their names so that they can be referenced in the same SQL query. 

Can you log a JIRA case please. Contributions welcome. 

> On Nov 7, 2021, at 10:56 PM, Yogendra Sharma <sy...@live.com> wrote:
> 
> Hi,
> 
> I have a couple of questions on calcite.
> 
>  1.   Assuming that I have a MySQL server which has two user databases: db1 and db2. If I wish to run a query "select * from db1.tab1 inner join db2.tab2 on db1.id = db2.id", do I need to add both databases in the root schema?
>  2.
>  3.  Secondly, even if I add one database say db1, the tableMap that we create has all the tables across all the databases in MySQL including system schema such as information schema and performance schema. Why?
>  4.
>  5.  Point (2) has created an issue for MySQL version 8 where a table name is common in mysql & information databases which leads to exception for duplicate key as we expect each table name to unique. I think I am reading something wrong here because Calcite shouldnt expect table names to be unique across catalogs/schemas?
> 
> Thanks,
> Yogi