SNOW-387943: SFDataConverter.csharpValToSfVal throws Exception when querying a boolean column for NULL

[wilcobrouwer]

Issue description

When passing a query that queries a boolean type column, an exception is thrown.

Example query: SELECT "*" FROM "table" WHERE "table.booleanattribute" IS null

Updating from client version 2.0.0 to 2.0.3 changed the error message, but still does not work. It now reads: "No corresponding Snowflake type for type AnsiString"

Note that running this exact query on the server directly, using a worksheet, does work as expected.

Configuration

Client version: 2.0.3 Server version: 5.26.2 .NET version: 5.0 OS: Windows 10 (updated to the latest version)

[sfc-gh-kterada]

@wilcobrouwer, are you able to share a snippet that is able to reproduce the issue reported? I've attempted to the behavior independently using .NET Connector 2.0.0, 2.0.3, and 2.0.6 (target framework: .NET 5.0), querying a simple table (create or replace table snow_387943_t (b boolean)) and reading the result of the query using GetValue() but do not see an exception or error being generated during execution. Thank you.

[virgilp]

The exception is from SnowflakeDBCommand.convertToBindList(). Try using SELECT "*" FROM "table" WHERE "table.booleanattribute" IN (?) and pass null as an argument.

[virgilp]

With that said, it does make me wonder whether the query should be legal in the first place. Like, sure, the error message from the driver is not great; and the fact that Snowflake otherwise executes the query "correctly" (if we do pass the null value/ after fixing the driver as suggested in #345 ) doesn't help in debugging the root cause. That said, I do believe we should fix the query on our side too.

[sfc-gh-kterada]

Thanks for the guidance, @virgilp.

I attempted several variations to reproduce the error originally reported by @wilcobrouwer, but ultimately was only to reproduce it using the following pattern running .NET Connector GA 2.0.3: ... var sql = "SELECT * FROM <TABLE_NAME> WHERE <COLUMN_NAME> IN (?);"; cmd.CommandText = sql; var p1 = cmd.CreateParameter(); p1.ParameterName = "1"; p1.Value = "NULL"; p1.DbType = DbType.AnsiString; cmd.Parameters.Add(p1); ... var reader = cmd.ExecuteReader(); ...

The following pattern returns the expected result set: ... var sql = "SELECT * FROM <TABLE_NAME> WHERE <COLUMN_NAME> IN (?);"; cmd.CommandText = sql; var p1 = cmd.CreateParameter(); p1.ParameterName = "1"; p1.Value = DBNull.Value; p1.DbType = DbType.Boolean; cmd.Parameters.Add(p1); ... var reader = cmd.ExecuteReader(); ...

Is refactoring the application logic to use the updated pattern a feasible solution? If not, could you please expand on the use case details as well as what the intended result of the query should be based on the statement around fixing the root query?

[json0007]

I get the same error when passing a Datetime through dapper in .NET

at Snowflake.Data.Core.SFDataConverter.csharpTypeValToSfTypeVal(DbType srcType, Object srcVal) at Snowflake.Data.Client.SnowflakeDbCommand.convertToBindList(List`1 parameters) at Snowflake.Data.Client.SnowflakeDbCommand.ExecuteInternalAsync(CancellationToken cancellationToken, Boolean describeOnly) at Snowflake.Data.Client.SnowflakeDbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)

var query = $@"
                SELECT ID
                FROM {ACTIVITIES}
                WHERE FILE_DATE > :fromDate
                LIMIT 1000;";
        
return  (await db.QueryAsync<Activity>(query, new {fromDate = DateTime.Now.AddDays(-1)}))

[sfc-gh-jtang]

To clean up and re-prioritize more pressing bugs and feature requests we are closing all issues older than 6 months as of April 1, 2023. If there are any issues or feature requests that you would like us to address, please create them according to the new templates we have created. For urgent issues, opening a support case with this link Snowflake Community is the fastest way to get a response.