View with column swapping

CrateDB SQL
1

Hello,

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.

2

Hi @arturohu,

there have been a few bug reports with similar observations, describing flaky behavior and mixed-up fields in connection with UNION, such as:

The most recent fix in that direction was in CrateDB 5.2.4. Do you have the option to upgrade to a more recent CrateDB version?

3

Hello @hammerhead,

Thank you for your early response. :slight_smile:

Ok, we will consider upgrading to that version or newer of CrateDB as soon as possible.