go/adbc/driver/snowflake: GetObjects API inconsistent case sensitivity for patterns

[ryan-syed]

The spec doesn't seem clear for case-sensitivity.

Similarly, in the getObjectsDbSchemas driver implementation it uses LIKE whereas getObjectsTables uses ILIKE. What should be the correct handling as per the spec? Should we allow the patterns to be case-insensitive?

func (c *cnxn) getObjectsDbSchemas(ctx context.Context, depth adbc.ObjectDepth, catalog *string, dbSchema *string) (result map[string][]string, err error) {
	if depth == adbc.ObjectDepthCatalogs {
		return
	}

	conditions := make([]string, 0)
	if catalog != nil && *catalog != "" {
		conditions = append(conditions, ` CATALOG_NAME LIKE \'`+*catalog+`\'`)
	}
	if dbSchema != nil && *dbSchema != "" {
		conditions = append(conditions, ` SCHEMA_NAME LIKE \'`+*dbSchema+`\'`)
	}

	cond := strings.Join(conditions, " AND ")
	if cond != "" {
		cond = `statement := 'SELECT * FROM (' || statement || ') WHERE ` + cond + `';`
	}

func (c *cnxn) getObjectsTables(ctx context.Context, depth adbc.ObjectDepth, catalog *string, dbSchema *string, tableName *string, columnName *string, tableType []string) (result internal.SchemaToTableInfo, err error) {
	if depth == adbc.ObjectDepthCatalogs || depth == adbc.ObjectDepthDBSchemas {
		return
	}

	result = make(internal.SchemaToTableInfo)
	includeSchema := depth == adbc.ObjectDepthAll || depth == adbc.ObjectDepthColumns

	conditions := make([]string, 0)
	if catalog != nil && *catalog != "" {
		conditions = append(conditions, ` TABLE_CATALOG ILIKE \'`+*catalog+`\'`)
	}
	if dbSchema != nil && *dbSchema != "" {
		conditions = append(conditions, ` TABLE_SCHEMA ILIKE \'`+*dbSchema+`\'`)
	}
	if tableName != nil && *tableName != "" {
		conditions = append(conditions, ` TABLE_NAME ILIKE \'`+*tableName+`\'`)
	}

[ryan-syed]

#1299 Found while working on adding more metadata tests

[lidavidm]

The behavior is up to the vendor; Snowflake's case-sensitivity is odd so it may be best to be case-insensitive by default

[zeroshade]

In my experience, most SQL dialects tend to be case-insensitive by default unless quotes are used to enforce the casing, so I agree with @lidavidm here. As long as we support allowing the user to include quotes in the value so that they can choose to enforce casing if they want, I think we're okay.

[ryan-syed]

Thanks, I will work on making the GetObjects case-insensitive for the patterns. Should I create an issue for BigQuery driver too, as it uses LIKE throughout in its implementation:

arrow-adbc/csharp/src/Drivers/BigQuery/BigQueryConnection.cs

Line 364 in cc0c4e9

query = string.Concat(query, string.Format(" WHERE table_name LIKE '{0}'", Sanitize(tableNamePattern)));

In my experience, most SQL dialects tend to be case-insensitive by default unless quotes are used to enforce the casing, so I agree with @lidavidm here. As long as we support allowing the user to include quotes in the value so that they can choose to enforce casing if they want, I think we're okay.

The behavior is up to the vendor; Snowflake's case-sensitivity is odd so it may be best to be case-insensitive by default

[lidavidm]

Yes, please go ahead and file another issue - thanks!

[ryan-syed]

Yes, please go ahead and file another issue - thanks!

created issue for BigQuery here: #1321

[ryan-syed]

Created PR: #1328