[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:
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

  • This is not a problem in LINQPad and not a problem in the IQDriver but rather a problem in the IQToolkit. Please report your problem here:

    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()
    })
    
  • Unfortunately, it looks like the IQToolkit project is dead. There hasn't been a release since 2010. We found this bug within a few days of installing and trying out the IQ Driver which does not give us much confidence in it. I'll be advising my team to uninstall it despite the convenience it offers.

    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...
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!