Crate won't honour NGSI type when adding new column

[c0c0n3]

Describe the bug

If QL receives an NGSI entity update with an attribute never seen before, the corresponding column will be created with an inferred type based on the attribute value. This inferred DB type may not be the same as the DB type corresponding to the attribute's NGSI type specified in the NGSI-to-SQL translation table. This issue only affects the Crate backend.

To Reproduce

1. POST an entity to the notify endpoint having a type T not already in the DB and containing a single attribute a1 with an NGSI type of Text and a value of 'hi im a1'.
2. POST an entity update for type T with a new attribute a2 of NGSI type Number and JSON string value of '25'.
3. Look at the table definition in Crate. It should have two attribute columns: a1 and a2 both of type text.
4. Select the records from the table. You should get all the attribute values inserted so far but a2 has a text value of '25' instead of being float 25.0.

Expected behavior

The Crate backend should create columns using the SQL type corresponding to the NGSI type looked up in the NGSI-to-SQL translation table. If an NGSI attribute happens to have a type (e.g. Number) which isn't compatible with its value (e.g. '25') the insert should fail but the table definition should still have a column with the right type (e.g. real).

[c0c0n3]

The problem here is that Crate dynamic columns are...a bit too dynamic :-)
In fact, w/r/t steps to reproduce above, when the entity gets notified for the first time (step 1), QL issues SQL similar to the following---omitting irrelevant details:

create table if not exists t (a1 text) with (column_policy = 'dynamic');

Which results in t getting created with an a1 column of type text and no records. Then QL issues an insert to save the entity payload

insert into t (a1) values ('hi im a1');

and the new record gets inserted. On the second update (step 2) the create table statement looks like

create table if not exists t (a1 text, a2 real) with (column_policy = 'dynamic');

But after executing this statement nothing actually happens in Crate: a2 doesn't get added and the table definition is still the same as before. As earlier, an insert statement follows the create one to actually save entity data

insert into t (a1, a2) values (null, '25');

It's only now that a2 gets created in Crate but with an inferred type of text. Notice that

select * from t where a2 = 25;

returns the lastly inserted record, and a where clause of a2 = '25' works too, but

select avg(a2) from t;
SQLActionException[SQLParseException: Cannot cast a2 to type
    [double precision, real, timestamp with time zone, integer, smallint, char]];

as expected since a2 DB type is text.

[c0c0n3]

so the bottom line is that our current insert strategy for adding new columns

create table T if not exists ( ... new_colum MAPPED_TYPE ...)
insert into T (...new_column...) values (... new_column_value ...)

is flawed since it will always result in Crate ignoring the first statement and then inferring new_column's type based on the new_column_value given in the insert. Probably we should ALTER the table the way we do in the Timescale translator...

[chicco785]

this should be fix as well by #373