Table aliases don't work with ClickHouse

[feldlime]

Description

If I use table alias in query it is excluded from query and migration fails.

• Version: 2.8.0
• Database: ClickHouse 21.8.13
• Operating System: Debian GNU/Linux 12

Steps To Reproduce

I've created a table some_table in database mydb with column some_column, then added migration

-- migrate:up

CREATE VIEW some_view AS (
    SELECT t.some_column
    FROM some_table AS t
)

-- migrate:down

DROP VIEW some_view

and executed dbmate up.

I've got an error: Missing columns: 't.some_column' while processing query: 'SELECT t.some_column FROM mydb.some_table', required columns: 't.some_column'.

You can see that AS t part was excluded from query.

If I replace SELECT t.some_column to SELECT some_column, it works.

Expected Behavior

It has to work with given query.

[dossy]

I tried to reproduce this with the latest dbmate v2.10 and ClickHouse 22.8.21.38 on Ubuntu 20.04.6 LTS, and could not reproduce with this:

$ mkdir issue_503
$ printf -- '-- migrate:up\ncreate table some_table (some_column int) engine=MergeTree order by some_column;\n-- migrate:down\n' > issue_503/20240105002500_create_table.sql
$ printf -- '-- migrate:up\ncreate view some_view as (select t.some_column from some_table as t);\n-- migrate:down\n' > issue_503/20240105002501_create_view.sql
$ dist/dbmate -u $CLICKHOUSE_TEST_URL -d issue_503 drop
$ dist/dbmate -u $CLICKHOUSE_TEST_URL -d issue_503 -s issue_503/schema.sql up
$ cat issue_503/schema.sql

Output:

root@ac78f31eadc5:/src# mkdir issue_503
root@ac78f31eadc5:/src# printf -- '-- migrate:up\ncreate table some_table (some_column int) engine=MergeTree order by some_column;\n-- migrate:down\n' > issue_503/20240105002500_create_table.sql
root@ac78f31eadc5:/src# printf -- '-- migrate:up\ncreate view some_view as (select t.some_column from some_table as t);\n-- migrate:down\n' > issue_503/20240105002501_create_view.sql
root@ac78f31eadc5:/src# dist/dbmate -u $CLICKHOUSE_TEST_URL -d issue_503 drop
Dropping: dbmate_test
root@ac78f31eadc5:/src# dist/dbmate -u $CLICKHOUSE_TEST_URL -d issue_503 -s issue_503/schema.sql up
Creating: dbmate_test
Applying: 20240105002500_create_table.sql
Applying: 20240105002501_create_view.sql
Writing: issue_503/schema.sql
root@ac78f31eadc5:/src# cat issue_503/schema.sql 
 
--
-- Database schema
--

CREATE DATABASE IF NOT EXISTS dbmate_test;

CREATE TABLE dbmate_test.schema_migrations
(
    `version` String,
    `ts` DateTime DEFAULT now(),
    `applied` UInt8 DEFAULT 1
)
ENGINE = ReplacingMergeTree(ts)
PRIMARY KEY version
ORDER BY version
SETTINGS index_granularity = 8192;

CREATE TABLE dbmate_test.some_table
(
    `some_column` Int32
)
ENGINE = MergeTree
ORDER BY some_column
SETTINGS index_granularity = 8192;

CREATE VIEW dbmate_test.some_view
(
    `some_column` Int32
) AS
SELECT t.some_column
FROM dbmate_test.some_table AS t;


--
-- Dbmate schema migrations
--

INSERT INTO schema_migrations (version) VALUES
    ('20240105002500'),
    ('20240105002501');

If you can provide a minimal reproducible example of the issue, I'll try to see if I can reproduce it.

[feldlime]

I tested it with your setup and it worked.

I did few more tests. Seems that it's not an issue of dbmate, but a ClickHouse issue.

I can reproduce it with ClickHouse 21.8.13, but with the latest versions it works fine.

Closing the issue