Multiple WHERE IN statements and ODBC
Nobody has claimed this yet.
- Dominant language
- C#
- Stars
- 18.4k
- Forks
- 3.7k
- Avg merge
- 5h 8m
- Merged PRs (30d)
- 1
Description
I'm using Dapper to make Select statments against a DB2 database (with ODBC drive). When I include one "WHERE IN" clause it works correctly:
//Works!
var p = new DynamicParameters();
var list = new float[] { 0.35f, 0.36f, 0.37f };
p.Add("ids", list);
string query = $"select * from {_table} where FIELD1 in ?ids?";
return WithConnection(c => c.Query<Entity>(query, p));
However, if I include another WHERE IN clause, then I don't get the expected result:
//Unexpected result (I should get 2 elements but returns empty)
var p = new DynamicParameters();
var list = new float[] { 0.35f, 0.36f, 0.37f };
var list2 = new int[] { 2423634, 2423635, 2423636, 2423637, 2417456 };
p.Add("ids", list);
p.Add("ids2", list2);
string query = $"select * from {_table} where FIELD1 in ?ids? and FIELD2 in ?ids2?";
return WithConnection(c => c.Query<Entity>(query, p));
Moreover, if I use a WHERE IN clause and a simple condition, I get an error:
//Error Error: [SQL0302] Data conversion or data mapping error.
// Cause . . . . . : Error type 6 has occurred. Error types and
// their meanings are: ... 6 -- Numeric data that is not valid. ...
var p = new DynamicParameters();
var list = new float[] { 0.35f, 0.36f, 0.37f };
int id = 2423634;
p.Add("ids", list);
p.Add("id", id);
string query = $"select * from {_table} where FIELD1 in ?ids? and FIELD2 = ?id?";
return WithConnection(c => c.Query<Entity>(query, p));
If I use the condition without the WHERE IN it works correctly.
Am I doing anything wrong or could it be a bug in Dapper? (I think there was an similar bug when using WHERE IN clauses solved in 1.50.2 version).
Thank you in advance.
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
No source file or test is identified. Reproduce the three DB2-over-ODBC queries from the issue, comparing one expanded WHERE IN parameter with multiple IN parameters and a scalar condition; done means the expected rows are returned and the SQL0302 conversion error no longer occurs.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, sql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100