Clickhouse: Problem with INSERT if having a VIEW with subqueries & AS keyword

[hatarist]

That's a really strange one!

When you create a view (let's call it test_stats) that has a subquery with multiple tables (test1 and test2) combined using UNION ALL clause, _and_ the first table in that clause has some custom-named columns (using AS); then if you try to insert some data to the first table (test1) the server will raise an exception that the renamed column wasn't found.

Should the VIEW/UNION ALL query affect the original tables? It looks really weird to me.

Here's an example:

>> CREATE TABLE test1 (id UInt64, date Date) ENGINE = MergeTree(date, (id, date), 8192);
>> CREATE TABLE test2 (id UInt64, date Date) ENGINE = MergeTree(date, (id, date), 8192);

>> CREATE VIEW test_stats AS SELECT _name, rows_total, rows_inserted, minus(rows_total, rows_inserted) AS _delta FROM 
       (SELECT * FROM (
           SELECT 'test1' AS _name, 1234 AS rows_total, count() AS rows_inserted FROM default.test1
               UNION ALL
           SELECT 'test2' AS _name, 4568 AS rows_total, count() AS rows_inserted FROM default.test2
           )
       );

>> SELECT * FROM test_stats;

Ok. 0 rows in set.

>> INSERT INTO test1 VALUES (1, '2016-09-10');

Received exception from server:
Code: 10. DB::Exception: Received from localhost:9000, ::1. DB::Exception: Not found column: 'rows_total'.

1 rows in set.

Even if I write explicitly that I want to insert just the id and date, it still raises the same exception:

>> INSERT INTO test1 (id, date) VALUES (1, '2016-09-10');

Received exception from server:
Code: 10. DB::Exception: Received from localhost:9000, ::1. DB::Exception: Not found column: 'rows_total'.

1 rows in set.

Inserting into other tables works OK with either syntax:

>> INSERT INTO test2 (id, date) VALUES (1, '2016-09-10');

Ok. 1 rows in set.

>> INSERT INTO test2 VALUES (2, '2016-10-10');

Ok. 1 rows in set.

>> SELECT * FROM test_stats;

┌─_name─┬─rows_total─┬─rows_inserted─┬─_delta─┐
│ test2 │       4568 │             2 │   4566 │
└───────┴────────────┴───────────────┴────────┘`

>> DROP TABLE test_stats;

Ok. 0 rows in set.

Let's make sure it was the view/subquery/union's fault:

>> INSERT INTO test1 (id, date) VALUES (1, '2016-09-10');

Ok. 1 rows in set.

>> CREATE VIEW test_stats AS SELECT cnt FROM 
          (SELECT cnt FROM (
              SELECT count() AS cnt FROM default.test1
              UNION ALL
              SELECT count() AS cnt FROM default.test2
          )
      );

>> INSERT INTO test1 (id, date) VALUES (2, '2016-09-10');

Received exception from server:
Code: 10. DB::Exception: Received from localhost:9000, ::1. DB::Exception: Not found column cnt in block. There are only columns: id, date.

1 rows in set.

I modified the server's output to make it less bloated; this bug appears on the v1.1.53981 (sorry, can't test it with the newer one right now).

[alexey-milovidov]

Confirmed. We have an issue with VIEWS and UNION ALL.

PS. Sorry for the delay, I was on conference trip this week.

[hatarist]

I just noticed that the tables are affected even if there are views without UNION ALL.
If there's a column rename in the subquery, it messes with the latter INSERT queries to the table.

Also, ClickHouse seems to lose track of the current database.

Proof of concept:

:) CREATE TABLE hits
    (
        date Date,
        timestamp DateTime,
        id String
    )
    ENGINE = MergeTree(date, (date, id), 8192);

Ok. 0 rows in set. Elapsed: 0.081 sec.

 :) CREATE VIEW hits_break AS SELECT date, id AS hit_id FROM hits;  -- oops, it didn't actually break the INSERT

Ok. 0 rows in set. Elapsed: 0.080 sec.

 :) INSERT INTO hits VALUES ('2016-11-11', '2016-11-11 14:00:00', '1');

Ok. 0 rows in set. Elapsed: 0.007 sec.

 :) CREATE VIEW hits_break_2 AS SELECT * FROM (SELECT date, id AS hit_id FROM hits);

Received exception from server:
Logical error while creating StorageView. Could not retrieve database name from select query.
 :) CREATE VIEW hits_break_2 AS SELECT * FROM (SELECT date, id AS hit_id FROM default.hits);

Ok. 0 rows in set. Elapsed: 0.223 sec.

 :) INSERT INTO hits VALUES ('2016-11-11', '2016-11-11 14:01:00', '2');

Received exception from server:
Not found column hit_id in block. There are only columns: date, timestamp, id

[sirkon]

Any progress on this?

[golangg]

+1

[erebus1]

same problem

[VKoptev]

+1

[mbobakov]

Any estimates ?

[dyomina-nn]

Is any updates or workarounds?

[filimonov]

Generally VIEWS do work nice. My workaround is to avoid views, and generate long and ugly selects.

[dyomina-nn]

unfortunately my MATERIALIZED VIEWs dont work if they have subqueries or JOIN, but with AS keyword they work normal. And for me using long and ugly selects isnt very good, there are too many big and long selects....
At that VIEW works fine, analogic MATERIALIZED VIEW doesnt work...

[sirkon]

I decided to hunt down this issue, reproduced steps and oops

This is old clickhouse version, it works and I did nothing. I am running Ubuntu 17.10. I remember though I installed libpoco yesterday, together with libpoco and related stuff, including libmysqlclient-dev and default-libmysqlclient-dev. Could it be related somehow?

[zhang2014]

I tried to reappear with the above statement, But I can't reproduce this problem. Maybe this problem has been fixed? Can we close this Issue? @alexey-milovidov