We are currently trying to work with two tables as one using a view.
We are experiencing weird behavior in our single-node 4.5.5 CrateDB deployment.
We have a main table and a secondary table in order to write missing data. Both tables have the same number of columns, as well as the nature of their contents.
A view is created by specifying columns and a “UNION ALL”.
CREATE VIEW mtairquality.etairqualityobserved_view AS SELECT "co" , ... FROM "mtairquality"."etairqualityobserved" UNION ALL SELECT "co", ... FROM "mtairquality"."etairqualityobserved_datarecovered"
However, when querying the view we receive a strange behaviour. For example:
SELECT "entity_id", "co2", "time_index" FROM mtairquality.etairqualityobserved_view
The result for that query on the source tables is correct, instead of the view that it seems to be swapped the value of the columns in some cases randomly, in this example the co2 is swapped with the column time_index.
co2: 163939246006000 (this is the time mark)
time_index: 129.31561 (1970-01-01T00:00:00.129Z)
If we change the query to another column, it responds correctly.
Maybe it is a problem of our CREATE VIEW which is incorrect or icompatible.
Thank you very much in advance.