System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: db4515dc343b69e04845ec757fc2ab33de77a28d
Title: Wrong constraints reported.
Status: Deferred Type: Feature_Request
Severity: Important Priority: Medium
Subsystem: Data_Reader Resolution: Works_As_Designed
Last Modified: 2015-05-22 18:55:48
Version Found In: 1.0.95.0
User Comments:
anonymous added on 2015-03-03 20:22:07:

SQLite report wrong constraints, so it throws ConstraintException when you load results of query into DataTable. Reproduce:

using(SQLiteConnection conn=new SQLiteConnection("DataSource=:memory:")) {
    conn.Open();
    using(SQLiteCommand comm=new SQLiteCommand(@"
        create table t(id primary key,parent);
        insert into t values (1,null),(2,1),(3,1);
    ",conn)) {
        comm.ExecuteNonQuery();
    }
    using(SQLiteCommand comm=new SQLiteCommand(@"
        select t2.id
        from
            t as t1 left join
            t as t2 on t2.id=t1.parent
    ",conn)) {
        using(SQLiteDataReader dr=comm.ExecuteReader()) {
            DataTable dt=new DataTable();
            try {
                dt.Load(dr);
            } catch(Exception e) {
                Console.WriteLine(e);
            }
            Constraint cons=dt.Constraints[0];
            Console.WriteLine(cons.GetType());
            Console.WriteLine(string.Join(",",(object[])((UniqueConstraint)cons).Columns));
        }
    }
}


mistachkin added on 2015-03-03 22:40:14:
Possibly related to ticket [92dbf1229a].

mistachkin added on 2015-03-04 05:34:14:
Further research reveals that this is not a new issue.  It has been this way since
at least release 1.0.89.0, possibly before.

Also, for the SELECT query in question, which contains a JOIN (from a given
table to itself), the PRIMARY KEY column values are not actually unique.

Simplified schema:

    CREATE TABLE t1(x INTEGER PRIMARY KEY, y INTEGER REFERENCES t1);
    INSERT INTO t1 (x, y) VALUES(1, NULL),(2, 1),(3, 1);

Simplified test case:

    SELECT t1_2.x FROM t1 AS t1_1 LEFT JOIN t1 AS t1_2 ON t1_2.x = t1_1.y;

However, the following SELECT query does not produce an error because the 'x'
column values are unique:

    SELECT t1_1.x FROM t1 AS t1_1 LEFT JOIN t1 AS t1_2 ON t1_2.x = t1_1.y;

Therefore, I'm heavily inclined to consider this behavior "by design".

mistachkin added on 2015-03-04 05:40:10:
Further information: There is no easy way for System.Data.SQLite to realize that
the 'x' column should not contain unique values for the query, primarily because
the sqlite3_column_table_name() API ends up returning "t1", which is later used
to build the constraints.

mistachkin added on 2015-03-04 19:16:23:
The issue appears to exist in 1.0.66.0 as well.

Attachments: