ClickHouse / ClickHouse/ClickHouse
Unable to use field name in where clause of a view created by another views
- Dominant language
- C++
- Stars
- 49.9k
- Forks
- 9k
- Avg merge
- 21h 32m
- Merged PRs (30d)
- 515
Description
**Describe the unexpected behaviour**
I have a view which is created from 2 other views (which are joined together).
When I select from that view, it return normally but when I add a where clause which contains a field that is in that view, clickhouse throw exception said that: Missing column ... while processing query.
**How to reproduce**
* Which ClickHouse server version to use: yandex/clickhouse-server:21.7.6.39
* Step to reproduce:
```
-- create a sample database and insert some mock data
CREATE DATABASE view_error_example;
CREATE TABLE view_error_example.employees
(
`emp_id` UInt32,
`name` String,
`dept_id` UInt32,
`salary_id` UInt32
)
ENGINE = MergeTree()
ORDER BY emp_id;
CREATE TABLE view_error_example.departments
(
`id` UInt32,
`name` String
)
ENGINE = MergeTree
ORDER BY id;
CREATE TABLE view_error_example.salaries
(
`id` UInt32,
`amount` Float32
)
ENGINE = MergeTree
ORDER BY id;
INSERT INTO view_error_example.employees VALUES(1, 'John', 1, 1),(2, 'David', 1, 2),(3, 'Marry', 2, 3);
INSERT INTO view_error_example.departments VALUES(1, 'Dev'),(2, 'Sale');
INSERT INTO view_error_example.salaries VALUES(1, 99.0)(2, 123.0),(3, 80.5);
```
- create some views that lead to unexpected behavior:
```
-- create first view from 'employees' and 'departments' table:
CREATE VIEW view_error_example.first_view AS
SELECT *
FROM view_error_example.employees AS e
INNER JOIN view_error_example.departments AS d ON e.dept_id = d.id;
-- create second view from 'employees' and 'salaries' table:
CREATE VIEW view_error_example.second_view AS
SELECT *
FROM view_error_example.employees AS e
INNER JOIN view_error_example.salaries AS s ON e.salary_id = s.id;
-- create a view which is a joined view between the 2 above views:
CREATE VIEW view_error_example.view_from_views AS
SELECT *
FROM view_error_example.first_view AS v1
INNER JOIN view_error_example.second_view AS v2 ON v1.emp_id = v2.emp_id;
```
The above table return a table like this:
```
┌─emp_id─┬─name──┬─dept_id─┬─salary_id─┬─id─┬─d.name─┬─v2.emp_id─┬─v2.name─┬─v2.dept_id─┬─v2.salary_id─┬─v2.id─┬─amount─┐
│ 1 │ John │ 1 │ 1 │ 1 │ Dev │ 1 │ John │ 1 │ 1 │ 1 │ 99 │
│ 2 │ David │ 1 │ 2 │ 1 │ Dev │ 2 │ David │ 1 │ 2 │ 2 │ 123 │
│ 3 │ Marry │ 2 │ 3 │ 2 │ Sale │ 3 │ Marry │ 2 │ 3 │ 3 │ 80.5 │
└────────┴───────┴─────────┴───────────┴────┴────────┴───────────┴─────────┴────────────┴──────────────┴───────┴────────┘
```
**Expected behavior**
when i select:
```
SELECT v2.name
FROM view_error_example.view_from_views
```
it's works fine:
```
┌─v2.name─┐
│ John │
│ David │
│ Marry │
└─────────┘
```
but when i select:
```
SELECT v2.name
FROM view_error_example.view_from_views
WHERE v2.name = 'John'
```
=> throw exception:
Received exception from server (version 21.7.6):
`
Code: 47. DB::Exception: Received from localhost:9001. DB::Exception: Missing columns: 'v2.name' while processing query: 'SELECT emp_id FROM view_error_example.employees AS e ALL INNER JOIN view_error_example.departments AS d ON dept_id = id WHERE `v2.name` = 'John'', required columns: 'emp_id' 'dept_id' 'id' 'v2.name', maybe you meant: ['emp_id','dept_id'], joined columns: 'id' 'd.name'.
`
Expected behavior: it should return :
```
┌─v2.name─┐
│ John │
└─────────┘
```
Contributor guide
Assessment
This issue has not been assessed yet.