Multiplying INT columns with DECIMALS results in INT results

CrateDB SQL
1

Observation:
When we multiply using values - SELECT 1 / (10*1.0 - 5*1.0) the return value is a fraction. If however we use integer fields of a CrateDB table instead of fixed values, the return value is an INT but it should be a fraction.

  1. Create table testintfieldval(val1 INTEGER, val2 INTEGER);
  2. insert some sample integer values in these fields - 1440, 1268.
  3. Run SELECT 1 / (val1 * 1.0 - val2 * 1.0) from testintfieldval . The query returns 0 instead of the expected fraction value (0.0045…)

Is this a bug on CrateDB or expected?

2

This is working as expected. In CrateDB, the columns have higher priority in our type inference logic to avoid expensive queries. For E.g. where int_column = 8 (where 8 is a long) shouldn’t result in int_column::long = 8 , but the other way around to utilize the lucene index efficiently.

As a workaround, we would need to add manual casts to the query with integer fields. For the above query, SELECT 1 / (val1 * 1.0 - val2 * 1.0 :: float) from testintfieldval after the cast (https://crate.io/docs/crate/reference/en/latest/general/ddl/data-types.html?highlight=cast#type-conversion) will return the expected output.