questions about case sensitivity

[AndyGo]

What is the difference between IGNORE_CASE and CASE_INSENSITIVE_IDENTIFIERS in the connection string? 

H2 defaults to being case-sensitive. I prefer the opposite. Is it possible to set a permanent case-insensitivity option when a new database is initially created, and not have to rely on the connection string to specify case insensitivity upon every future connection?  (version 2.1.212)

Thank you!

[Evgenij Ryazanov]

Hello!

These settings aren't related to each other.

IGNORE_CASE is about data types and their values. It replaces CHARACTER VARYING (VARCHAR) columns in DDL commands with VARCHAR_IGNORECASE data type. This legacy setting should never be used, it is much better to specify some case-insensitive database collation if you need it.

DATABASE_TO_UPPER, DATABASE_TO_LOWER, and CASE_INSENSITIVE_IDENTIFIERS settings change treatment of identifiers. H2 by default is fully compliant with the SQL Standard here. It means all unquoted identifiers are converted to upper case. id, ID, Id, "ID" (and also non-standard MySQL-style `ID` and `id`) are equal to each other, but "id" is an another identifier. Some other database systems historically process identifiers in their own special way and sometimes this way also depends on their environment. That's why these settings can be set separately from compatibility modes. Values of these settings, however, aren't persisted and you must always specify them with the same values in JDBC URL.

It is possible to specify them in the default Regular compatibility mode too, but, again, the default behavior is already correct and compliant with the Standard.

[AndyGo]

Thanks. Appreciate the quick and comprehensive response. Can you explain this a bit more: "It is possible to specify them in the default Regular compatibility mode "

Just curious, since the settings can't persist... what would happen if, in default mode, I created MyTable and also "mytable" and then later, connected with CASE_INSENSITIVE_IDENTIFIERS and queried SELECT * FROM MYTABLE ?

[Andrei Tokar]

Hi Andy,

I wonder, what are your expectations in such scenario?

If you are looking for a way to shoot yourself in the foot, I am sure H2 will present multiple opportunities for that.

Does it really matter if it end up with "table not found" or will select from one or the other table, at random?
Of course, you are always welcome to try...

[AndyGo]

Thanks for the reply andrei.

It's not a matter of trying to shoot myself in the foot. If I'm writing all SQL by hand then personally, I'm going to always use unquoted values for table/view names. However, various ORMs and query tools/frameworks have all kinds of different behaviors.

I've used H2 successfully in different projects for over a decade. It's an awesome database with incredible breadth of use cases. And yet, just the other day when creating a client demo, I received a series of error messages that didn't appear to make sense -- even accounting for case-sensitivity, the table name of the SQL statement matched the table name, yet H2 couldn't find the table?

Exception in thread "main" org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "MYFIRSTTABLE" not found (candidates are: "myFirstTable"); SQL statement: SELECT id, name FROM myFirstTable

Eventually, the culprit was an ORM tool that quotes all of its object names, and then running queries with standard JDBC Prepared Statements that don't. However it was an embarrassing half-hour with the client trying to sort it all out with a database that I endorse but which the client was unfamiliar with. Obviously, this experience did not leave them with a great impression of H2.

[Andreas Reichel]

Greetings Andy.

On Sun, 2023-01-15 at 22:15 -0800, AndyGo wrote:

I'm going to always use unquoted values for table/view names. 

This is not RDBMS agnostic and will get you into big trouble when switching from Oracle to Sybase or MS SQL Server or vice versa. 

I've used H2 successfully in different projects for over a decade. It's an awesome database with incredible breadth of use cases. 

I full agree on that, however: who runs H2 in production should know exactly what he is doing.

Your issue is a rather simple one: at least you got an error!

At the same time, between releases there is a residual risk that perfect SQL:2016 compliant queries return wrong/different results ("WITH statement with parameters" since 210) -- and this is really dangerous because you will suddenly get wrong information without a chance of noticing.

Don't run software in production under scenarios, which you have not well tested and understood in the lab before.

And yet, just the other day when creating a client demo, I received a series of error messages that didn't appear to make sense -- even accounting for case-sensitivity, the table name of the SQL statement matched the table name, yet H2 couldn't find the table?

Exception in thread "main" org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "MYFIRSTTABLE" not found (candidates are: "myFirstTable"); SQL statement: SELECT id, name FROM myFirstTable 

I do not understand your example: if your table was created as "myFirstTable" (with explicit quotes), then of course myFirstTable == MYFIRSTTABLE == "MYFIRSTTABLE"  won't match. Same for Oracle and MS SQL Server or any other DB I know.

I do not see any H2 issue here, just the usual challenge of writing platform agnostic SQL.

My advice: stick to the SQL:2016 standard as much as possible and document well founded deviations (e.g. Oracle .nextval) when forced to use it.

Cheers

Andreas

[Andreas Reichel]

On Sun, 2023-01-15 at 22:15 -0800, AndyGo wrote:

If I'm writing all SQL by hand then personally, I'm going to always use unquoted values for table/view names.

Working with Oracle, H2, MS SQL Server and DB2, I think best practise was to write SQL Keywords "lower case" and Identifiers "upper case" (maybe with Functions spelled "camel case", if you are adventurous).

select COLUMN1, MY_COLUMN_2 from CFE.TABLE_NAME where Trim(COLUMN1)='something';

This will always work, with out without quoting. And its easy to read even when most SQL syntax highlighting is poor.

Cheers

Andreas