schema names in Superset

[M11VCS]

Hello,
I'm using Apache Superset through sqlalchemy-ibmi.
I found that schema names are returned in lower case to superset and this causes the SQL to fail.
Schemas which include a special character in its name , like a £ or §, are returned in upper case.
Is there a workaround to return schema names in uppercase ?
I've already got in touch with Superset support but they confirm that they are showing schema names as they are returned from the driver.

Thanks
Giovanni

[kadler]

AFAIK, neither sqlalchemy-ibmi nor the ODBC driver do any case conversion of schema names. I'm not sure what would be causing this.

[kadler]

Nevermind, I forgot that SQLAlchemy normalizes names to lowercase unless they need quoting.

Looks like the default normalization checks for names that need quoting here: https://github.com/sqlalchemy/sqlalchemy/blob/rel_2_0/lib/sqlalchemy/engine/default.py#L1032-L1034

This checks for illegal characters which require quoting here: https://github.com/sqlalchemy/sqlalchemy/blob/rel_2_0/lib/sqlalchemy/sql/compiler.py#L7744

Which defaults to only A-Z, 0-9, _, and $: https://github.com/sqlalchemy/sqlalchemy/blob/rel_2_0/lib/sqlalchemy/sql/compiler.py#L247

Based on https://www.ibm.com/docs/en/i/7.5.0?topic=identifiers-sql I think this is basically correct, though I think we should actually exclude $ to be 100% correct:

An ordinary identifier is an uppercase letter followed by zero or more characters, each of which is an uppercase letter, a digit, or the underscore character.

So I think sqlalchemy-ibmi is working as designed here, more or less (since SQLAlchemy wants ordinary identifiers lower-cased). I would assume that Superset needs to handle this SQLAlchemy behavior already so there's something getting lost in translation between SQLAlchemy and Superset.

[kadler]

@M11VCS do you know what the query being run is?

[M11VCS]

I had to create all my views in a IBM I library named §SUPERSET , with § as first letter. This causes SQL Alchemy not to lowercase library name and so everything works fine. But it's a really bad workaround.

[kadler]

Yeah, I'm trying to investigate further but I'd need to know what query it was running initially before you applied the workaround.

[M11VCS]

initial query was : select * from m11d00_dbe.vw_users and failed.
I just moved the view to §superset library:

second query : select * from §superset.vw_users and it worked.

[wAVeckx]

I have tried to replicate this on superset 4 and 6 environment, and have yet to do so using sqllab or in a dataset creation. Both are still using 9.3.0 of this driver however. I can run "SELECT * FROM sysibm.sysdummy1" in upper case and in lowercase and get the same result. I assume in your connection URI in susperset you are using ibmi:// ?
What version of superset, python, sqlalchemy-ibmi, and IBMi server are you running?