[BUG] IQ Driver Generates Bad SQL and returns wrong results
Version: 5.05.02(AnyCPU)
IQ Driver: 2.1.2.0
Database: MemSQL 4.1.3
I believe I found a bug where the IQ driver is generating bad SQL and returning an incorrect result set.
Here's the C# querying a table called Creatives that has a column on ID:
The SQL generated is this:
My expression, c.Id % 2 == 1, is completely absent from the query and I get a totally incorrect result set.
Experimentation has indicated that there is some kind of optimization gone wrong here where the driver thinks it can run that expression while materializing the results instead of on the server.
Running this same query using Linq-to-sql yields the correct SQL:
This bug is severe IMO as it doesn't even result in a run-time error. I just get a garbage result set.
IQ Driver: 2.1.2.0
Database: MemSQL 4.1.3
I believe I found a bug where the IQ driver is generating bad SQL and returning an incorrect result set.
Here's the C# querying a table called Creatives that has a column on ID:
Creatives
.Take(10)
.Select(c => c.Id % 2 == 1)
.GroupBy(c => c)
.Select(c => new {
IsOdd = c.Key,
Count = c.Count()
})
The SQL generated is this:
SELECT t0.id, COUNT(*) AS agg1
FROM (
SELECT t1.id
FROM creatives AS t1
LIMIT 0, 10
) AS t0
GROUP BY t0.id
My expression, c.Id % 2 == 1, is completely absent from the query and I get a totally incorrect result set.
Experimentation has indicated that there is some kind of optimization gone wrong here where the driver thinks it can run that expression while materializing the results instead of on the server.
Running this same query using Linq-to-sql yields the correct SQL:
-- Region Parameters
DECLARE @p0 Int = 2
DECLARE @p1 Int = 1
-- EndRegion
SELECT COUNT(*) AS [Count], [t2].[value] AS [IsOdd]
FROM (
SELECT
(CASE
WHEN ([t1].[id] % @p0) = @p1 THEN 1
WHEN NOT (([t1].[id] % @p0) = @p1) THEN 0
ELSE NULL
END) AS [value]
FROM (
SELECT TOP (10) [t0].[id]
FROM [Creatives] AS [t0]
) AS [t1]
) AS [t2]
GROUP BY [t2].[value]
This bug is severe IMO as it doesn't even result in a run-time error. I just get a garbage result set.
Comments
https://iqtoolkit.codeplex.com/
As you can see, this project is still in Alpha stage therefore bugs are to be expected.
If you want to work around these issues, you can retrieve the entire data set and perform the calculation client side by adding a single AsEnumerable() operation:
Creatives .Take(10) .AsEnumerable() // <-- .Select(c => c.Id % 2 == 1) .GroupBy(c => c) .Select(c => new { IsOdd = c.Key, Count = c.Count() })For anyone else reading this, I've tested the MySQL .Net Connector (https://dev.mysql.com/downloads/connector/net/1.0.html) and it does not have this bug. We've been using that with success for a while now from outside of Linqpad...