System.Data.SQLite

SQLite report wrong constraints, so it throws <code>ConstraintException</code> when you load results of query into <code>DataTable</code>. Reproduce:<pre>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));
        }
    }
}</pre>

Possibly related to ticket [92dbf1229a].

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

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.

The issue appears to exist in 1.0.66.0 as well.