Materialized view require all columns to be provided

[filimonov]

That works:

CREATE TABLE default.mv_source (`a` UInt64) ENGINE = MergeTree ORDER BY tuple();

CREATE TABLE default.mv_target (`a` UInt64) ENGINE = MergeTree ORDER BY tuple();

create materialized view mv to mv_target as select * from mv_source;

insert into mv_source values (1);

And when I add a column to mv_target it stops working (i expect to use the default value in that case)

alter table mv_target add column b UInt8;
insert into mv_source values (1);

Received exception from server (version 19.18.1):
Code: 8. DB::Exception: Received from localhost:9000. DB::Exception: Cannot find column `b` in source stream: while pushing to view default.mv. 

1 rows in set. Elapsed: 0.005 sec. 

Received exception from server (version 19.18.1):
Code: 8. DB::Exception: Received from localhost:9000. DB::Exception: Cannot find column `b` in source stream: while pushing to view default.mv. Stack trace:

0. 0x55f75c4363d0 StackTrace::StackTrace() /build/obj-x86_64-linux-gnu/../dbms/src/Common/StackTrace.cpp:228
1. 0x55f75c4361a5 DB::Exception::Exception(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, int) /build/obj-x86_64-linux-gnu/../dbms/src/Common/Exception.h:27
2. 0x55f75c23114e DB::ConvertingBlockInputStream::ConvertingBlockInputStream(DB::Context const&, std::shared_ptr<DB::IBlockInputStream> const&, DB::Block const&, DB::ConvertingBlockInputStream::MatchColumnsMode) [clone .cold] /usr/include/c++/9/bits/basic_string.h:222
3. 0x55f75febfd6f DB::PushingToViewsBlockOutputStream::process(DB::Block const&, unsigned long) /usr/include/c++/9/bits/shared_ptr_base.h:758
4. 0x55f75fec0c32 DB::PushingToViewsBlockOutputStream::write(DB::Block const&) /usr/include/c++/9/bits/stl_vector.h:916
5. 0x55f75feca093 DB::SquashingBlockOutputStream::finalize() /usr/include/c++/9/bits/stl_tree.h:978
6. 0x55f75feca2c1 DB::SquashingBlockOutputStream::writeSuffix() /usr/include/c++/9/bits/shared_ptr_base.h:1021
7. 0x55f75c4be432 DB::TCPHandler::processInsertQuery(DB::Settings const&) /build/obj-x86_64-linux-gnu/../dbms/programs/server/TCPHandler.cpp:464
8. 0x55f75c4bf905 DB::TCPHandler::runImpl() /build/obj-x86_64-linux-gnu/../dbms/programs/server/TCPHandler.cpp:257
9. 0x55f75c4bfb8b DB::TCPHandler::run() /build/obj-x86_64-linux-gnu/../dbms/programs/server/TCPHandler.cpp:1239
10. 0x55f760468960 Poco::Net::TCPServerConnection::start() /build/obj-x86_64-linux-gnu/../contrib/poco/Net/src/TCPServerConnection.cpp:57
11. 0x55f76046907d Poco::Net::TCPServerDispatcher::run() /usr/include/c++/9/bits/atomic_base.h:327
12. 0x55f761b3f2d1 Poco::PooledThread::run() /usr/include/x86_64-linux-gnu/c++/9/bits/gthr-default.h:748
13. 0x55f761b3d07c Poco::ThreadImpl::runnableEntry(void*) /build/obj-x86_64-linux-gnu/../contrib/poco/Foundation/include/Poco/AutoPtr.h:205
14. 0x55f7622b31e0 execute_native_thread_routine /usr/lib/debug/usr/bin/clickhouse
15. 0x7f26268ac6db start_thread /lib/x86_64-linux-gnu/libpthread-2.27.so
16. 0x7f26261c988f clone /build/glibc-OTsEL5/glibc-2.27/misc/../sysdeps/unix/sysv/linux/x86_64/clone.S:97

[filimonov]

Related: #7533 #1226

[vzakaznikov]

The issue is caused by

ClickHouse/dbms/src/DataStreams/ConvertingBlockInputStream.cpp

Line 65 in c4e2d71

throw Exception("Cannot find column " + backQuote(res_elem.name) + " in source stream",

.

[vzakaznikov]

Simple test case

$ cat 01069_materialized_view_alter_target_table.sql
DROP TABLE IF EXISTS mv_source;
DROP TABLE IF EXISTS mv_target;

CREATE TABLE mv_source (`a` UInt64) ENGINE = MergeTree ORDER BY tuple();
CREATE TABLE mv_target (`a` UInt64) ENGINE = MergeTree ORDER BY tuple();

CREATE MATERIALIZED VIEW mv TO mv_target AS SELECT * FROM mv_source;

INSERT INTO mv_source VALUES (1);

ALTER TABLE mv_target ADD COLUMN b UInt8;
INSERT INTO mv_source VALUES (1);

SELECT * FROM mv;
SELECT * FROM mv_target;

The expected output

$ cat 01069_materialized_view_alter_target_table.reference 
1
1
1	0
1	0

[filimonov]

Fixed in #8788 in version 19.16.13 on newer.