There is a change in behavior with nuget package System.Data.SQLite.Core between version 1.0.117 and 1.0.118.

It seemed as an issue with data type and UNION ALL (see previous thread) but it is more likely a problem with SQLiteDataAdapter and DataTable. So I am creating a new thread.

See the sample. With version 1.0.117 both DataTables have column type Double but with version 1.0.118 the ResultTableCopy has type Int64.

It only happens when the first row contains integer.

Here is the sample code (C#/.NET 8):

using System.Data;
using System.Data.SQLite;

var db = new SQLiteConnection("Data Source=:memory:");
db.Open();
var cmd = db.CreateCommand();
cmd.CommandText = """
    CREATE TABLE SourceTable1 (ColumnName REAL); 
    INSERT INTO SourceTable1 VALUES(123); 
    CREATE TABLE SourceTable2 (ColumnName REAL);     
    INSERT INTO SourceTable2 VALUES(456); 
    INSERT INTO SourceTable2 VALUES(58); 
    
    CREATE TABLE ResultTable AS
        SELECT CAST(ColumnName AS REAL) as ResultColumn FROM SourceTable1
        UNION ALL 
        SELECT CAST(ColumnName AS REAL) as ResultColumn FROM SourceTable2;
    
    CREATE TABLE ResultTableCopy AS SELECT * FROM ResultTable;
    """;
cmd.ExecuteNonQuery();

PrintType("ResultTable");
PrintType("ResultTableCopy");
void PrintType(string tableName)
{
    var dataTable = new DataTable();
    using var adapter = new SQLiteDataAdapter();
    adapter.SelectCommand = new SQLiteCommand($"""
                                               SELECT ResultColumn
                                               FROM {tableName};
                                               """, db);
    adapter.Fill(dataTable);
    Console.WriteLine(dataTable.Columns[0].DataType);
}

If you are going to use SQLite successfully while imposing fixed datatypes on columns, you need to use expressions such as, CAST( <your expression here> AS <datatype name you want> ) .

You should also read up on Type Affinity in SQLite.

That is such an excellent explanation that I will not try to summarize it here.

With version 1.0.117 both DataTables have column type Double but with version 1.0.118 the ResultTableCopy has type Int64.

The thinking that permits such an assertion will not easily survive a careful study of the above-linked doc.

(Reply edited to alter and expand 1st response point.)

I just want to know whether this is a bug or an intended change.

It is neither. The results continue to be consistent with the documentation.

In the doc section, "CREATE TABLE ... AS SELECT Statements", 4th sentence in the 1st paragraph reads " The declared type of each column is determined by the expression affinity of the corresponding expression in the result set of the SELECT statement, as follows: ...", listing declared type "REAL" for REAL expression affinity. The linked expression affinity states that "An expression of the form 'CAST(expr AS type)' has an affinity that is the same as a column with a declared type of 'type'."

From post #1, the DDL reading CREATE TABLE ResultTable AS SELECT CAST(ColumnName AS REAL) as ResultColumn FROM SourceTable1 UNION ALL SELECT CAST(ColumnName AS REAL) as ResultColumn FROM SourceTable2; , might be thought to have a well-defined expression affinity for its one and only result column, but the doc on compound SELECTs is silent on this. This means that the final condition would seem to apply, "Otherwise, an expression has no affinity." However, the created "ResultTable" has type NUM for its ResultColumn. To me, this appears incorrect.

It may well be problematic to propagate column expression affinities through a UNION, but I would think they should either propagate unscathed when consistent or be dropped -- not yield something else. And how this plays out in compound selects should be documented.

There is a fairly simple way to make this more explicit: CREATE TABLE ResultTable AS SELECT CAST(c1 AS REAL) as ColumnName FROM( SELECT ColumnName AS c1 FROM SourceTable1 UNION ALL SELECT ColumnName AS c1 FROM SourceTable2 ); , with a result promised by the docs.

(Remainder of this post is unchanged.)

Given that it behaves "unpredictably" I would assume this is a bug

That would be an ill-founded assumption. The premise is insufficient to support the conclusion.

The thinking that permits such an assertion will not easily survive a careful study of the above-linked doc.

What do you mean? It's not an assertion, it's a fact.

Another try: The state of mind which I see as the likely source of statements such as "[tables] have column type Double" and "[some other table] has type Int64" will probably be altered by a careful reading of the doc that I linked.

Run the sample code.

What you wrote was clear and believable enough. The issue here is not whether what you can objectively see is really happening. The issue is why. And that question is most efficiently resolved by adapting your mental model to what the SQLite docs clearly explain rather than insisting that columns have a datatype and your observations inconsistent with that notion indicate either a bug or an intended behavioral change.