System.Data.SQLite

Login
This project makes use of Eagle, provided by Mistachkin Systems.
Eagle: Secure Software Automation
Ticket Hash: 47c6fa04d34db6c55478e8e42cdcf38129ce1341
Title: Loading a DataTable with a JOIN/GROUP BY merges result rows
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Data_Reader Resolution: Fixed
Last Modified: 2015-03-03 17:23:47
Version Found In: 1.0.94.0
User Comments:
anonymous added on 2014-11-24 12:01:19:
A query that is executed under SQLiteStudio or SQLite Manager gives two records back. As soon as I execute it with SQLiteDataReader I get only one record. If I remove t1.T1_ID from the SELECT and GROUP BY clause it behaves in all environments in the same way. Here is the SQL script:
-- Table: Test1
CREATE TABLE Test1 ( 
    T1_ID   INTEGER PRIMARY KEY AUTOINCREMENT
                    NOT NULL,
    T1_TEXT TEXT    NOT NULL 
);

INSERT INTO [Test1] ([T1_ID], [T1_TEXT]) VALUES (1, 'T1_1');
INSERT INTO [Test1] ([T1_ID], [T1_TEXT]) VALUES (2, 'T1_2');
INSERT INTO [Test1] ([T1_ID], [T1_TEXT]) VALUES (3, 'T1_3');

-- Table: Test2
CREATE TABLE Test2 ( 
    T2_ID    INTEGER PRIMARY KEY
                     NOT NULL,
    T2_T1ID  INTEGER NOT NULL,
    T2_TEXT  TEXT,
    T2_VALUE INTEGER NOT NULL
                     DEFAULT ( 0 ) 
);

INSERT INTO [Test2] ([T2_ID], [T2_T1ID], [T2_TEXT], [T2_VALUE]) VALUES (1, 2, 'T2_1', 1);
INSERT INTO [Test2] ([T2_ID], [T2_T1ID], [T2_TEXT], [T2_VALUE]) VALUES (2, 2, 'T2_2', 2);
INSERT INTO [Test2] ([T2_ID], [T2_T1ID], [T2_TEXT], [T2_VALUE]) VALUES (3, 2, 'T2_3', 3);

-- Table: Test3
CREATE TABLE Test3 ( 
    T3_ID  INTEGER PRIMARY KEY AUTOINCREMENT
                   NOT NULL,
    T3_MIN INTEGER NOT NULL,
    T3_MAX INTEGER NOT NULL 
);

INSERT INTO [Test3] ([T3_ID], [T3_MIN], [T3_MAX]) VALUES (1, 2, 3);
INSERT INTO [Test3] ([T3_ID], [T3_MIN], [T3_MAX]) VALUES (2, 1, 2);

SELECT t1.T1_ID AS id,
       t3.T3_MIN AS min,
       t3.T3_MAX AS max,
       SUM( t2.T2_VALUE ) AS sum
  FROM Test1 t1
       INNER JOIN Test3 t3
       INNER JOIN Test2 t2
               ON t2.T2_T1ID = t1.T1_ID 
AND
t2.T2_VALUE BETWEEN t3.T3_MIN AND t3.T3_MAX
 GROUP BY t1.T1_ID,
          t3.T3_ID,
          t3.T3_MIN,
          t3.T3_MAX;

mistachkin added on 2014-11-24 17:08:09:
I'm unable to reproduce this issue here.  Could you show some C# sample code that
demonstrates the issue?

anonymous added on 2014-11-26 17:05:06:
This is the source code. I assign the return value of GetDataTable to the DataSource of a DataGridView. This works absolutly fine for simple queries. I can send you the complete Visual Studio 2013 project by email if you like.  
        public SQLiteDatabase()
        {         
            dbConnection = @"Data Source=..\..\Datenbank\KC1Daten.sqlite";
        }
        public DataTable GetDataTable ( string sql )
        {
            DataTable dt = new DataTable();
            try
            {
                SQLiteConnection cnn    = new SQLiteConnection(dbConnection);
                cnn.Open();
                SQLiteCommand mycommand = new SQLiteCommand(cnn);
                mycommand.CommandText   = sql;
                SQLiteDataReader reader = mycommand.ExecuteReader();
                dt.Load(reader);
                reader.Close();
                cnn.Close();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            return dt;
        }

mistachkin added on 2014-12-03 20:06:07:
I'm now able to reproduce the reported behavior.

The query includes the column "id", which is based on the primary key column
"t1.T1_ID", which is also included in the GROUP BY clause.  It appears that
the .NET Framework itself cannot cope with a temporary table schema that does
not also include the other primary key column (i.e. "t3.T3_ID"), which also
appears in the GROUP BY clause, in the list of result columns.

Adding "t3.T3_ID AS id2," to the SELECT query results in two rows being returned
in the final DataTable.  It should be noted that directly using SQLiteDataReader
objects always results in two rows being returned.  This issue has to do with the
integration between the .NET Framework and System.Data.SQLite.

mistachkin added on 2014-12-03 21:47:21:
Candidate fix for this is now on the "tkt-47c6fa04d3" branch,
here: [/info/tkt-47c6fa04d3]

mistachkin added on 2014-12-05 22:13:07:
It appears that other ADO.NET providers handle this situation by interpreting
the MSDN documentation for the SchemaTableColumn.IsKey to actually mean "is
primary key".  In that case, any primary key columns that may be present in
the result set cannot be considered unless only one table is referenced in the
result set (i.e. the current fix is the correct fix).

mistachkin added on 2014-12-05 22:13:44:
Fixed on trunk via check-in [1e8465a822].

mistachkin added on 2015-03-03 17:23:47:
See also, ticket [92dbf1229a].