DataTable.Load() fails when joining SQLite tables with UNIQUE constraint

[CodeSpartan]

When using the DataTable.Load() method to load data from a SQLite query with a LEFT JOIN on a column with a UNIQUE constraint, an error is thrown. The error message is "Column 'guildId' is constrained to be unique. Value '1' is already present." even though the join operation should not enforce the unique constraint on the joined columns.

To reproduce the issue, consider the following SQLite schema and data:

CREATE TABLE "characters" (
	"id"	INTEGER,
	"name"	TEXT UNIQUE,
	"guild"	INTEGER,
);

CREATE TABLE "guilds" (
	"id"	INTEGER NOT NULL UNIQUE,
	"name"	TEXT UNIQUE,
	UNIQUE("name"),
	PRIMARY KEY("id" AUTOINCREMENT)
);
CREATE UNIQUE INDEX guildname 
ON guilds(name);

INSERT INTO characters (id, name, guild) VALUES (1, 'John', 1);
INSERT INTO characters (id, name, guild) VALUES (2, 'Jeanette', 1);

INSERT INTO guilds (id, name) VALUES (1, 'Testers');

And the following pseudo C# code using Microsoft.Data.Sqlite:

var connection = new SqliteConnection(connectionParameters);
var commandTxt = @"
    SELECT guilds.name as guildName, characters.name as charName FROM guilds
    LEFT JOIN characters
    ON guilds.id = characters.guild";
var command = new SqliteCommand(commandTxt, connection);
var reader = await command.ExecuteReaderAsync();
DataTable dt = new();
dt.Load(reader);

Expected behavior is to receive two rows of data:

  /*
   * guildName	        charName 	
   * Testers 		John
   * Testers            Jeanette
   */

Actual behavior:

The line dt.Load(reader) throws an exception: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

If you wrap the last line into a try-catch and add a breakpoint in the catch statement, then going into the Command Window and typing ? dt.GetErrors()[1].RowError would produce an explanation: "Column 'guildName' is constrained to be unique. Value 'Testers' is already present."

The UNIQUE constraint is meant to enforce uniqueness on the data stored in the table, not on the results of join operations. As such, the join operation should not enforce the unique constraint on the DataTable. Furthermore, executing the same code with MySqlConnector produces desired result. And other software such as DB Browser for SQLite doesn't exhibit the problem, either.

Microsoft.Data.Sqlite version: 7.0.5
Target framework: .NET 6.0
Operating system: Windows 11