SNOW-1621535: DatabaseMetaData - handling of escape characters

[bauer-at-work]

1. What version of JDBC driver are you using?
   v3.18

2. What operating system and processor architecture are you using?
   Fedora 40, amd64

3. What version of Java are you using?
   OpenJDK 64-Bit Server VM (Red_Hat-21.0.4.0.7-2) (build 21.0.4+7, mixed mode, sharing)

4. What did you do?

   Run DataBaseMetadata.getSchemas() with two kinds of JDBC URL parameters:

   1. schema="UNDER_SCORE"
   2. schema="UNDER\\_SCORE"

   Then, I compared the results. Both queries yield one line of result (the only matching schema is "UNDER_SCORE")
   However, I realized a difference in field IS_CURRENT:

   In case (i), IS_CURRENT equals 'Y'.
   In case (ii), IS_CURRENT equals 'N'.

5. What did you expect to see?

   In case (ii), IS_CURRENT equalling 'Y'.

   It seems however that either the driver or the Snowflake query engine don't properly handle the escape characters \\.

Given that this is the second issue I encountered in regards to DatabaseMetaData operations on schemas with special characters,
please closely examine the driver implementation.
We're struggling to get decent performance on metadata operations on our Snowflake-to-SAP-HANA interface.

Thank you!

[sfc-gh-sghosh]

Hello @bauer-at-work ,

Thanks for raising the issue, we are looking into it, will update.

Regards,
Sujan

[sfc-gh-sghosh]

Hello @bauer-at-work ,

Could you please share the code snippet.
We checked using 3.18.0 and its working expected. Also, the IS_CURRENT output is reflecting as per current schema which is set.

`stmt.execute("USE SCHEMA UNDER_SCORE");

created_on               name                     is_default               is_current               database_name            owner                    comment                  options                  retention_time           owner_role_type          budget                   
--------------------------------------------------------------------------------
2024-08-19 04:59:50.523 ZINFORMATION_SCHEMA       N                        N                        SAMPLEDATABASE                                    Views describing the contents of schemas in this database                         30                                                null                     
2020-09-21 10:07:04.449 ZPUBLIC                   N                        N                        SAMPLEDATABASE           ACCOUNTADMIN                                                               30                       ROLE                     null                     
2021-11-03 08:09:29.784 ZS1                       N                        N                        SAMPLEDATABASE           ACCOUNTADMIN                                      MANAGED ACCESS           30                       ROLE                     null                     
2020-09-23 04:23:10.596 ZTEST                     N                        N                        SAMPLEDATABASE           ACCOUNTADMIN                                                               30                       ROLE                     null                     
2024-08-19 04:29:24.186 ZUNDER\_SCORE             N                        N                        SAMPLEDATABASE           ACCOUNTADMIN                                                               30                       ROLE                     null                     
2024-08-19 04:28:24.840 ZUNDER_SCORE              N                        Y                        SAMPLEDATABASE           ACCOUNTADMIN                                                               30                       ROLE        `

String schemaName = "\"UNDER\\_SCORE\""; 
			System.out.println(schemaName);
            stmt.execute("USE SCHEMA " + schemaName);


created_on               name                     is_default               is_current               database_name            owner                    comment                  options                  retention_time           owner_role_type          budget                   
--------------------------------------------------------------------------------
2024-08-19 05:05:29.043 ZINFORMATION_SCHEMA       N                        N                        SAMPLEDATABASE                                    Views describing the contents of schemas in this database                         30                                                null                     
2020-09-21 10:07:04.449 ZPUBLIC                   N                        N                        SAMPLEDATABASE           ACCOUNTADMIN                                                               30                       ROLE                     null                     
2021-11-03 08:09:29.784 ZS1                       N                        N                        SAMPLEDATABASE           ACCOUNTADMIN                                      MANAGED ACCESS           30                       ROLE                     null                     
2020-09-23 04:23:10.596 ZTEST                     N                        N                        SAMPLEDATABASE           ACCOUNTADMIN                                                               30                       ROLE                     null                     
2024-08-19 04:29:24.186 ZUNDER\_SCORE             N                        Y                        SAMPLEDATABASE           ACCOUNTADMIN                                                               30                       ROLE                     null                     
2024-08-19 04:28:24.840 ZUNDER_SCORE              N                        N                        SAMPLEDATABASE           ACCOUNTADMIN  
```                                                             30                       ROLE                     null 

Regards,
Sujan

[bauer-at-work]

Hi @sfc-gh-sghosh,
let me please re-iterate on my original report.

In case (i), in the output of GET SCHEMAS, "UNDER_SCORE" is shown to be the current schema.
As the underscore in the schema name is unescaped and therefore treated as a wildcard as per the JDBC DatabaseMetaData interface, there is no way for a JDBC compliant driver to know whether the user is f.e. referring to schema "UNDERXSCORE" or "UNDERYSCORE".
That's why I suppose that in case (i), schema "UNDER_SCORE" should not be shown to be the current schema.

In case (ii), however, the underscore is escaped and should therefore not be treated as a wildcard. Instead, the schema string should be stripped of the backslashes and be treated as a literal schema identifier.
In that case, now looking at your provided example, "ZUNDER\_SCORE" should not be shown to be the current schema.

Actually, you need to provide four backslashes instead of only two in your Java code examples.
In my example, I'm using only two backslashes each because I provided the schema name as JDBC URL parameters.

Please re-check your triage. Thank you!

P.S.: GitHub's markdown renderer treats a backslash as an escape character as well, so I hope nothing got stripped from your message.

[sfc-gh-sghosh]

Hello @bauer-at-work,

Sorry for the delay, I didnt notice your further question.
The database.getSchems will display all the schemas in the database, it will not get mention current schema but also all other schemas. This is as per JDBC specification only.

Now IS_CURRENT only be true for the current schema which is set for the current connection. As per above reply, the IS_CURRENT is reflecting the schema which is set.

You can cross validate by using con.getSchema(), it will return the only current schema and not all schemas.

I hope all clear now.

Regards,
Sujan