Presto is not able to read table in MySql with NAME as CAPITAL or having _.

[isonusingh]

I am also facing the same issue.
Database - RETAIL5M
Tables - ITEM, SALES, STORE, ONLINE_SALES, SALES_TEST.

Presto is not able to read this metadata, Please help.

[mbasmanova]

This is a known limitation in Presto. The only workaround I know of is to rename the table to use lower case.

[mbasmanova]

Here is an earlier discussion: #10288

[isonusingh]

Thanks, Maria. Can we start fixing this?

[mbasmanova]

@SonuSingh200190 Are you proposing to come up with a design that would address this issue and work on executing it?

[isonusingh]

@mbasmanova yes I think we can fix this.

[mbasmanova]

@mbasmanova yes I think we can fix this.

Please, go ahead. Looking forward to a design proposal and/or prototype.

[agrawalreetika]

I tried with presto latest version. It seems to be working fine.
This is how I have tables in my mysql instance-

Here is table list from presto cli-

Please correct me if I missing something here?

[marcejohnson]

@agrawalreetika what do you get when you type
show tables from mysql.presto.item_test;
?
For me, that is where it will fail on the case conversion and returns "does not exist".
Should be same failure with a select * from mysql.presto.item_test; in your example above.

[agrawalreetika]

@marcejohnson item_test is itself a table in schema presto(In mysql) .
I tried SELECT operation on the table from presto-cli:

[marcejohnson]

@agrawaldevesh what output do you get when you type in mysql (not presto) client:
show variables like 'lower_case_%';

If latter returns non-zero for lower_case_table_names then mysql itself is doing case-insensitive compares, and explains why it works for you. My mysql server setting returns 0, which is a case sensitive compare, hence does not work well with presto (which strips the case).

https://mariadb.com/products/skysql/docs/reference/es/system-variables/lower_case_table_names/