Read() a Query Doesn't Return TypeName MetaData

[JamoCA]

I'm testing this using ACF2016 and v3.7.4 of this library. If a column is added dynamically using queryAddColumn() after reading a query object using spreadsheet-cfml:

qry = spreadsheet.read(src=filepath, format= "query", headerRow=1, returnVisibleValues=true);
writedump(getMetaData(var=local.qry, label="initial query"));  // TypeName key doesn't exist
queryaddcolumn(qry, "NewColumn", "varchar", []);
writedump(getMetaData(local.qry, label="query after adding column")); // TypeName only exists for newly created column
qry2 = queryexecute("SELECT * from qry WHERE ID=:id", {"id":1}, {"dbtype":"query"});

... the following error occurs when attempting to perform a query-of-queries that references any of the existing columns:

Query Of Queries runtime error.
Unsupported SQL type java.sql.Types.UNKNOWN

After reviewing the query's metdata, it appears as though the TypeName attribute is either non-existent (or occasionally defaults to UNKNOWN.)

Here's a workaround that I discovered. (It requires performing an initial QoQ before performing any query manipulations.)

qry = spreadsheet.read(src=filepath, format= "query", headerRow=1, returnVisibleValues=true);
qry = queryexecute("SELECT * from qry", {}, {"dbtype":"query"});  // perform a base QoQ to have ACF autodetect & set TypeName
queryaddcolumn(qry, "NewColumn", "varchar", []);
qry2 = queryexecute("SELECT * from qry WHERE ID=:id", {"id":1}, {"dbtype":"query"});  // this works now

[cfsimplicity]

This is down to the way ACF (all versions) handles QueryNew() where the column types (second parameter) aren't specified:

q = QueryNew( "ID", "", [ 1 ] );
WriteDump( GetMetadata( q ) );

Lucee will supply types: But ACF wont':

As explained in the docs for read(), spreadsheets have no notion of column types, only cell types. So unless you specify them when calling the function, the internal QueryNew() will be passed an empty list of column types.

So just specifying one of the queryColumnTypes options in your read() call should fix the problem.

You might think that auto-detection should be the default, but again as mentioned in the docs, it's a potentially expensive operation which in most cases probably won't be needed.

As you've found, you also have the option of doing a QoQ on the resulting query which adds the type metadata and might be more performant. But according to my tests that can interfere with/modify the returned values, so again best left to the developer to use as they see fit.