System.Data.SQLite

Login
This project makes use of Eagle, provided by Mistachkin Systems.
Eagle: Secure Software Automation
2015-03-03
17:23 Ticket [47c6fa04d3] Loading a DataTable with a JOIN/GROUP BY merges result rows status still Closed with 3 other changes artifact: 5fb172d905 user: mistachkin
2014-12-05
22:13 Closed ticket [47c6fa04d3]. artifact: 5191f89041 user: mistachkin
22:13 Ticket [47c6fa04d3]: 3 changes artifact: 1daaedeb51 user: mistachkin
22:09
Modify GetSchemaTable method to avoid setting SchemaTableColumn.IsKey column to true when more than one table is referenced. Fix for [47c6fa04d3]. check-in: 1e8465a822 user: mistachkin tags: trunk
2014-12-03
22:01 Ticket [47c6fa04d3] Loading a DataTable with a JOIN/GROUP BY merges result rows status still Pending with 3 other changes artifact: 8c16fd72cd user: mistachkin
21:47 Ticket [47c6fa04d3]: 4 changes artifact: 2700808ce1 user: mistachkin
21:14
Add test and candidate fix for [47c6fa04d3]. check-in: 1fdf3de322 user: mistachkin tags: tkt-47c6fa04d3
20:06 Ticket [47c6fa04d3] missing data after sql execution status still Pending with 3 other changes artifact: 48a3d2da62 user: mistachkin
2014-12-01
06:20 Ticket [47c6fa04d3]: 3 changes artifact: b7f16f1d06 user: mistachkin
2014-11-26
17:05 Ticket [47c6fa04d3]: 3 changes artifact: 34ab5e7195 user: anonymous
2014-11-25
06:46 Ticket [47c6fa04d3]: 3 changes artifact: c7ad195924 user: mistachkin
01:28 Pending ticket [47c6fa04d3]. artifact: 20f421b9ee user: mistachkin
2014-11-24
17:08 Ticket [47c6fa04d3]: 6 changes artifact: 1ae9ac5ced user: mistachkin
12:01 New ticket [47c6fa04d3]. artifact: 6eff364b36 user: anonymous

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].