go/adbc/driver/snowflake: handling of wildcards in GetObjects

[frbvianna]

What happened?

Hi, I'm seeing unexpected results when trying to retrieve object information with adbc.GetObjects.

For instance, in the following call, the underscores and percent signs are always treated as wildcards:
cnxn.GetObjects(ctx, adbc.ObjectDepthTables, "DB%", "SCHEMA_WITH_UNDERSCORES", nil, nil, nil)

I expected to see that the identifiers would be treated literally. To my understanding, this is due to the usage of SQL LIKE. I have tried to escape these characters with \\ (two blackslashes) before calling the function, but this does not seem to work.

Besides, I have created the following schema to test the special characters:
CREATE SCHEMA <DB-NAME>." SCHEMA_MiXeD_`~!@#$%^&*()-_=+[]{};:\'""."",<>?\| 元数æ�®ç¨æˆ·å¤šå—节 ";

The schema is then displayed in Snowflake with the following name (note that the double-quotes are de-duplicated in the created object):
SCHEMA_MiXeD_`~!@#$%^&*()-_=+[]{};:\'".",<>?\| 元数æ�®ç¨æˆ·å¤šå—节

While I'm able to create this schema, I cannot retrieve its contained tables at ObjectDepthTables:
cnxn.GetObjects(ctx, adbc.ObjectDepthTables, "<DB-NAME>", " SCHEMA_MiXeD_`~!@#$%^&*()-_=+[]{};:\\'\".\",<>?\\| 元数æ�®ç¨æˆ·å¤šå—节 ", nil, nil, nil)

Can the wildcards be handled literally? Can you help to understand why I cannot retrieve the objects contained in the schema above if I provide its name?

Note that I'm intentionally testing edge cases since we expect to read arbitrary user-provided schema names in the application.

Stack Trace

No response

How can we reproduce the bug?

Create schemas with underscores _, percent signs %, or the following one, which cannot be inspected with GetObjects:
CREATE SCHEMA <DB-NAME>." SCHEMA_MiXeD_`~!@#$%^&*()-_=+[]{};:\'""."",<>?\| 元数æ�®ç¨æˆ·å¤šå—节 ";

Environment/Setup

github.com/apache/arrow-adbc/go/adbc v1.6.0
macOS 15.5

[lidavidm]

Hmm, we don't specify a way to escape, but I think there should be a way to escape (if perhaps database-specific), or to disable the wildcards when desired.

[CurtHagenlocher]

(This was also mentioned in #1508)

[lidavidm]

#1398 is also in principle related

[lidavidm]

I think I've stalled on those enough; @zeroshade @amoeba and Curt what do you think of adding a way to disable wildcards here (and elsewhere)? I think escaping kind of doesn't make sense; in general we expect the "real name" and not the escaped or quoted name in these kinds of parameters

[CurtHagenlocher]

The design of this aspect of the API probably dates back to ODBC 1.0 and is clearly influenced by the LIKE feature of SQL (which I assume is often used to implement it). If I want to say "show me all the tables starting with spt_ then I need both a way to match an arbitrary suffix and potentially a way to say that the underscore isn't a wild card. In some dialects of SQL, this might turn into select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME like 'spt[_]%'.

But while I think there's a use case for being able to do this, it's a nontrivial change and perhaps not of very high value. I think we could solve the more-immediate problems people have had with a more incremental change: define a standard connection property e.g. "adbc.connection.get_objects.disable_wildcards" where if set to "true" on a driver that supports it, the driver will treat the identifiers as literal.

[amoeba]

I think the an option to disable wildcards seems like the best approach. I assume this would mean drivers would use an = instead of LIKE for these types of queries when the option is true?

[CurtHagenlocher]

I think the an option to disable wildcards seems like the best approach. I assume this would mean drivers would use an = instead of LIKE for these types of queries when the option is true?

The exact details are going to depend on the driver. That looks to be true for the Snowflake driver but e.g. the C# BigQuery driver is not using SQL to query the catalogs.

[amoeba]

Ah, right. Thanks @CurtHagenlocher. Still getting all that goes into ADBC loaded into my brain.

[frbvianna]

About the schema that is not working ( SCHEMA_MiXeD_`~!@#$%^&*()-_=+[]{};:\'".",<>?\| 元数æ�®ç¨æˆ·å¤šå—节 ), I think I nailed it down to the failing character.

Looks like the problem is with the backslashes (\).

I created another schema named schema\backslash and similarly cannot retrieve its tables in the GetObjects call if I provide the following string: "schema\\backslash"

Likewise, if I execute the SHOW statement in Snowflake, I get no results:
SHOW TERSE SCHEMAS LIKE 'schema\backslash' IN ACCOUNT;
It looks like I can only retrieve it if I use 'schema\\\\backslash' in the above statement (4 backslashes).

But if I pass the string "schema\\\\\\\\backslash" to GetObjects, I even get a SQL compilation failure.
If I set it to "schema_backslash", it works as expected due to the wildcard.

One thing to note is that I can generally escape the wildcards in the SHOW statement by using \\ (two backslashes) even without the ESCAPE '\\' clause, so maybe from Snowflake side it's expected that we need 4 backslashes.
However, as mentioned before, I cannot use the \\ to escape the wildcards in the ADBC driver, so I was hoping that the single backslash would've worked.

At this point, it's not clear to me whether it's a driver issue or a Snowflake issue (or both?).

Can you please confirm? Should I perhaps create a separate issue for it?

[CurtHagenlocher]

Thanks @frbvianna for a thorough analysis.

At this point, it's not clear to me whether it's a driver issue or a Snowflake issue (or both?).

It's certainly not a Snowflake issue in that Snowflake's LIKE (or in this case ILIKE) has a certain behavior and it's the driver's responsibility to use it correctly. In this case, it looks like the driver should be escaping the backslash when constructing the ILIKE-based queries in order to account for the server's behavior.