SQLite Forum

SQLiteDataAdapter.FillSchema problem with left joined tables
Login

SQLiteDataAdapter.FillSchema problem with left joined tables

(1) By stefan.laut on 2022-01-25 14:47:19 [link] [source]

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 can provide a demo console application showing this beavior, if demanded.

Regards, Stefan

(2) By MBL (UserMBL) on 2022-01-25 16:04:33 in reply to 1 [link] [source]

  1. SQLiteDataAdapter is nothing native from SQLite.org ... the question is about your programming language, versions, environment

  2. You are talking about LEFT JOINS but not giving any example of schema or test data

  3. What kind of help do you expect?

(4) By stefan.laut on 2022-01-26 05:31:59 in reply to 2 [link] [source]

Hi,

  1. I'm using .Net 4.7.2 (C#) with the newest nuget packet System.Data.SQLite.Core (Version 1.0.115.5).

  2. I mentioned that I can provide a sample console application showing this behavior. See source-code below.

  3. On the System.Data.SQLite website is mentioned that for support issues the SQLite Forum should be used so I hope I can geht help for this issue.

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();

}

Regards, Stefan

(6) By John Dennis (jdennis) on 2022-01-26 06:32:02 in reply to 4 [link] [source]

Looks perfectly OK to me when run in the CLI:

sqlite> CREATE TABLE HEAD ( HEAD_ID TEXT PRIMARY KEY, SOME_DATA TEXT );
sqlite> CREATE TABLE CHILD ( CHILD_ID TEXT PRIMARY KEY, HEAD_ID TEXT NOT NULL, SOME_DATA TEXT );
sqlite> INSERT INTO HEAD VALUES ( '1', 'Head-Data 1' );
sqlite> INSERT INTO HEAD VALUES ( '2', 'Head-Data 2' );
sqlite> INSERT INTO HEAD VALUES ( '3', 'Head-Data 3' );
sqlite> INSERT INTO CHILD VALUES ( '1', '1', 'Child-Data 1' );
sqlite> INSERT INTO CHILD VALUES ( '2', '1', 'Child-Data 2' );
sqlite> INSERT INTO CHILD VALUES ( '5', '3', 'Child-Data 5' );
sqlite> INSERT INTO CHILD VALUES ( '6', '3', 'Child-Data 6' );
sqlite> SELECT HEAD.*, CHILD.* FROM HEAD LEFT JOIN CHILD ON HEAD.HEAD_ID = CHILD.HEAD_ID;
HEAD_ID  SOME_DATA    CHILD_ID  HEAD_ID  SOME_DATA
-------  -----------  --------  -------  ------------
1        Head-Data 1  1         1        Child-Data 1
1        Head-Data 1  2         1        Child-Data 2
2        Head-Data 2
3        Head-Data 3  5         3        Child-Data 5
3        Head-Data 3  6         3        Child-Data 6

(7) By stefan.laut on 2022-01-26 08:04:49 in reply to 6 [source]

Yes the result set is okay and like expected!

This is an issue with the resolved schema from SQLiteDataAdapter and the wrong configured .Net DataTable object. So this problem is specific to the System.Data.SQLite, not SQLite directly.

As posted below, I addressed this issue in the corresponding ticket system, too.

(5) By stefan.laut on 2022-01-26 06:04:05 in reply to 2 [link] [source]

Found a ticket system on the System.Data.SQLite website and cross postet the issue in there

System.Data.SQLite-Ticket

(3) By AlexJ (CompuRoot) on 2022-01-25 17:16:22 in reply to 1 [link] [source]