Column parsing error on a coalesced integer value in a view where the coalesce values are from different tables.

[addled1]

With the following 2 tables and view in an SQLite database:

CREATE TABLE table01 ( id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, code TEXT NULL, ranking INTEGER NULL, table02id INTEGER NULL REFERENCES table02(id) );

CREATE TABLE table02 ( id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, code TEXT NULL, ranking INTEGER NULL );

CREATE VIEW view01 AS SELECT t1.id , t1.name , COALESCE(t2.code, t1.code) AS code , COALESCE(t2.ranking, t1.ranking) AS ranking FROM table01 t1 LEFT OUTER JOIN table02 t2 ON t1.table02id = t2.id;

After populating table01 with records that all have null code/ranking values and then creating 1 or 2 records in table02 with values in all columns and linking them to a couple of records in table01 I then try to fetch the data using the view into the following class:

public class TestModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Code { get; set; }
    public int? Ranking { get; set; }
}

in batches using the query: "SELECT id, name, code, ranking FROM view01 ORDER BY id LIMIT @offset,@take"

The error "Error parsing column 3 (ranking=4 - Int64)" is thrown when a table02 ranking value is present. I tried replacing coalesce with a CASE statement, I have cast the COALESCE expression to an integer, i have replaced the column ranking with a cast to integer expression in the data select query, I also tried using the joined tables rather than the view - all give the same cast error.

[addled1]

Sorry, submitted before I had finished - apologies:

I also tried a view using a coalesce statement with only one table and that worked fine: CREATE VIEW view04 AS SELECT t1.id , t1.name , COALESCE(t1.code, 'T1') AS code , COALESCE(t1.ranking, 4) AS ranking FROM table01 as t1;

A small console app (.Net 4.7.2) demonstrates the issue, it creates the SQLite database, adds test data and retrieves the data showing the error thrown ViewMapTest.zip

[addled1]

One further observation, if the number of records fetched is set to 1 or 2 instead of 50, then no error is thrown, setting it to 3 or more always causes the error