# View with column swapping

**URL:** <https://community.cratedb.com/t/view-with-column-swapping/1528>\
**Category:** SQL\
**Tags:** sql\
**Created:** [June 28, 2023, 4:35pm UTC](https://community.cratedb.com/t/view-with-column-swapping/1528 "2023-06-28T16:35:54Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![arturohu](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/arturohu/32/950_2.png) [@arturohu](https://community.cratedb.com/u/arturohu)\
**Post date:** [June 28, 2023, 4:35pm UTC](https://community.cratedb.com/t/view-with-column-swapping/1528/1 "2023-06-28T16:35:54Z")

</div>

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”.

```auto
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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![hammerhead](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/hammerhead/32/270_2.png) [@hammerhead](https://community.cratedb.com/u/hammerhead)\
**Post date:** [June 29, 2023, 6:34am UTC](https://community.cratedb.com/t/view-with-column-swapping/1528/2 "2023-06-29T06:34:53Z")

</div>

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:

- [UNION across multiple aggregates fails with SQL Error [XX000]: ERROR: Cannot cast value `x` to type `y` · Issue #13779 · crate/crate · GitHub](https://github.com/crate/crate/issues/13779)
- [`UNION` across multiple joined relations leads to `SQLParseException` · Issue #14330 · crate/crate · GitHub](https://github.com/crate/crate/issues/14330)

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?

---

<div class="post-metadata">

**Author:** ![arturohu](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/arturohu/32/950_2.png) [@arturohu](https://community.cratedb.com/u/arturohu)\
**Post date:** [June 29, 2023, 7:06am UTC](https://community.cratedb.com/t/view-with-column-swapping/1528/3 "2023-06-29T07:06:55Z")

</div>

Hello @hammerhead,

Thank you for your early response. 🙂

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