System.Data.SQLite

Hi all,

I'm using SQLiteDataAdapter for a new project and maybe considered a bug or not well designed feature.

If using SQLiteDataAdapter in conjunction with LEFT JOINS and it may be possible that missing child rows cause a ConstraintExcpetion on Fill method, if the child table contains not null columns.

SQLiteDataAdapter seems to fill the AllowDbNull properties of the child table columns and set them to false which causes this exception. But left join expressions always can have Null values also in not null columns!

I postet my code below to show the behavior.

Regards, Stefan

static void Main()
{

    var rootPath = Path.GetDirectoryName(Assembly.GetEntryAssembly().Location);
    var dataSource = Path.Combine(rootPath, "fill_schema_test.db");
    var connectionString = $"data source={dataSource};pooling=True;max pool size=100;journal mode=Memory;Version=3";

    if (File.Exists(dataSource))
        File.Delete(dataSource);

    using (var connection = new SQLiteConnection(connectionString))
    {
        connection.Open();

        using (var command = new SQLiteCommand("CREATE TABLE HEAD ( HEAD_ID TEXT PRIMARY KEY, SOME_DATA TEXT )", connection))
            command.ExecuteNonQuery();
        using (var command = new SQLiteCommand("CREATE TABLE CHILD ( CHILD_ID TEXT PRIMARY KEY, HEAD_ID TEXT NOT NULL, SOME_DATA TEXT )", connection))
            command.ExecuteNonQuery();

        using (var command = new SQLiteCommand("INSERT INTO HEAD VALUES ( '1', 'Head-Data 1' )", connection))
            command.ExecuteNonQuery();
        using (var command = new SQLiteCommand("INSERT INTO HEAD VALUES ( '2', 'Head-Data 2' )", connection))
            command.ExecuteNonQuery();
        using (var command = new SQLiteCommand("INSERT INTO HEAD VALUES ( '3', 'Head-Data 3' )", connection))
            command.ExecuteNonQuery();

        using (var command = new SQLiteCommand("INSERT INTO CHILD VALUES ( '1', '1', 'Child-Data 1' )", connection))
            command.ExecuteNonQuery();
        using (var command = new SQLiteCommand("INSERT INTO CHILD VALUES ( '2', '1', 'Child-Data 2' )", connection))
            command.ExecuteNonQuery();

        // No Childs for HEAD_ID '2'

        using (var command = new SQLiteCommand("INSERT INTO CHILD VALUES ( '5', '3', 'Child-Data 5' )", connection))
            command.ExecuteNonQuery();
        using (var command = new SQLiteCommand("INSERT INTO CHILD VALUES ( '6', '3', 'Child-Data 6' )", connection))
            command.ExecuteNonQuery();

        // Select Joined Data via DataAdapter

        using (var command = new SQLiteCommand("SELECT HEAD.*, CHILD.* FROM HEAD LEFT JOIN CHILD ON HEAD.HEAD_ID = CHILD.HEAD_ID", connection))
        using (var adapter = new SQLiteDataAdapter(command))
        {
            var dt = new DataTable();

            try
            {
                adapter.FillSchema(dt, SchemaType.Source);
                adapter.Fill(dt); // <== Throws ConstraintException because FillSchema sets AllowDbNull Property of CHILD.HEAD_ID column to false!
                                  //     Child Table can never be a part of an left join expression, if not all head data rows have child rows!
                                  //     Considered to be a Bug!
                                  //     Only Schema information of the first table should be loaded completely.
                                  //     Left join expression always can have Null values also in not null columns
            }
            catch (ConstraintException cx)
            {
                var errors = dt.GetErrors();

                var rowErrors = errors.Length > 0
                    ? errors
                        .Select(e => $"- {e.RowError}")
                        .Aggregate((c, n) => c += Environment.NewLine + n)
                    : "- n.a -";

                Console.WriteLine(cx);
                Console.WriteLine();
                Console.WriteLine(rowErrors);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
        }
    }

    Console.ReadLine();
}

According to the code, a column marked "PRIMARY KEY" cannot be NULL.

To quote from section 3.5 of "https://sqlite.org/lang_createtable.html":

"According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in
SQLite."

For the functionality specific to System.Data.SQLite, has disallowed NULL
for "PRIMARY KEY" columns for quite a while.

Hi,

But the CHILD Table is correctly filled with all NOT NULL Columns. The null values came from the LEFT JOIN due to the fact that HEAD_ID=2 has no child rows.

The returning result set looks like this:

HEAD_ID	SOME_DATA	CHILD_ID	HEAD_ID1	SOME_DATA1
1	Head-Data 1	1	1	Child-Data 1
1	Head-Data 1	2	1	Child-Data 2
2	Head-Data 2	NULL	NULL	NULL
3	Head-Data 3	5	3	Child-Data 5
3	Head-Data 3	6	3	Child-Data 6

which is correct! The Schema of the DataTable created with FillSchema is not!

The CHILD_ID and the HEAD_ID1 (CHILD.HEAD_ID) columns are set to AllowDbNull=false because they are NOT NULL in the database. BUT they are nullable for the schema returned by the LEFT JOIN Statement! And The FillSchema method needs to return the Schema for the applied SQL-Statement not the underlying tables.