Clickhouse: Segmentation fault due to all join

[imbelousov]

Version 18.16.1 rev 54412
Tested on CentOS 7.4.1708

DDL:

drop table persons;
drop table children;

create table persons (
    id String,
    name String
) engine MergeTree order by id;

create table children (
    id String,
    childName String
) engine MergeTree order by id;

Test data:

insert into persons (id, name)
values
('1', 'John'),
('2', 'Jack'),
('3', 'Daniel'),
('4', 'James'),
('5', 'Amanda');

insert into children (id, childName)
values
('1', 'Robert'),
('1', 'Susan'),
('3', 'Sarah'),
('4', 'David'),
('4', 'Joseph'),
('5', 'Robert');

Failed queries:

select *
from persons
all inner join children using id;

select *
from persons
all inner join (
    select *
    from children
) as j using id;

select *
from (
    select *
    from persons
) as s
all inner join (
    select *
    from children
) as j using id

But successful queries:

select *
from persons
all inner join (
    select *
    from children
) using id;

select *
from (
    select *
    from persons
)
all inner join (
    select *
    from children
) using id;

select *
from (
    select *
    from persons
) as s
all inner join (
    select *
    from children
) using id

Log:

2019.01.21 13:22:03.790646 [ 2 ] {} <Error> BaseDaemon: ########################################
2019.01.21 13:22:03.790700 [ 2 ] {} <Error> BaseDaemon: (from thread 31) Received signal Segmentation fault (11).
2019.01.21 13:22:03.790709 [ 2 ] {} <Error> BaseDaemon: Address: 0x7f6078600000
2019.01.21 13:22:03.790714 [ 2 ] {} <Error> BaseDaemon: Access: read.
2019.01.21 13:22:03.790719 [ 2 ] {} <Error> BaseDaemon: Address not mapped to object.
2019.01.21 13:22:03.814114 [ 2 ] {} <Error> BaseDaemon: 0. clickhouse-server(memcpy+0x103) [0x63480d3]
2019.01.21 13:22:03.814164 [ 2 ] {} <Error> BaseDaemon: 1. clickhouse-server(void DB::writeAnyEscapedString<(char)39>(char const*, char const*, DB::WriteBuffer&)+0x1df) [0x2cbb1af]
2019.01.21 13:22:03.814175 [ 2 ] {} <Error> BaseDaemon: 2. clickhouse-server(DB::BlockOutputStreamFromRowOutputStream::write(DB::Block const&)+0x94) [0x53cfb64]
2019.01.21 13:22:03.814184 [ 2 ] {} <Error> BaseDaemon: 3. clickhouse-server(DB::MaterializingBlockOutputStream::write(DB::Block const&)+0x5fd) [0x5134bcd]
2019.01.21 13:22:03.814197 [ 2 ] {} <Error> BaseDaemon: 4. clickhouse-server(DB::copyData(DB::IBlockInputStream&, DB::IBlockOutputStream&, std::atomic<bool>*)+0x91) [0x4c9df91]
2019.01.21 13:22:03.814213 [ 2 ] {} <Error> BaseDaemon: 5. clickhouse-server(DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, DB::Context&, std::function<void (std::string const&)>)+0x4ee) [0x4eb040e]
2019.01.21 13:22:03.814229 [ 2 ] {} <Error> BaseDaemon: 6. clickhouse-server(DB::HTTPHandler::processQuery(Poco::Net::HTTPServerRequest&, HTMLForm&, Poco::Net::HTTPServerResponse&, DB::HTTPHandler::Output&)+0x321c) [0x2beaa3c]
2019.01.21 13:22:03.814244 [ 2 ] {} <Error> BaseDaemon: 7. clickhouse-server(DB::HTTPHandler::handleRequest(Poco::Net::HTTPServerRequest&, Poco::Net::HTTPServerResponse&)+0x42c) [0x2bed14c]
2019.01.21 13:22:03.814256 [ 2 ] {} <Error> BaseDaemon: 8. clickhouse-server(Poco::Net::HTTPServerConnection::run()+0x2af) [0x5fccf8f]
2019.01.21 13:22:03.814264 [ 2 ] {} <Error> BaseDaemon: 9. clickhouse-server(Poco::Net::TCPServerConnection::start()+0xf) [0x5fc3cef]
2019.01.21 13:22:03.814272 [ 2 ] {} <Error> BaseDaemon: 10. clickhouse-server(Poco::Net::TCPServerDispatcher::run()+0x166) [0x5fc40b6]
2019.01.21 13:22:03.814279 [ 2 ] {} <Error> BaseDaemon: 11. clickhouse-server(Poco::PooledThread::run()+0x77) [0x62755a7]
2019.01.21 13:22:03.814286 [ 2 ] {} <Error> BaseDaemon: 12. clickhouse-server(Poco::ThreadImpl::runnableEntry(void*)+0x38) [0x6271768]
2019.01.21 13:22:03.814293 [ 2 ] {} <Error> BaseDaemon: 13. clickhouse-server() [0x6a7fa8f]
2019.01.21 13:22:03.814299 [ 2 ] {} <Error> BaseDaemon: 14. /lib64/libpthread.so.0(+0x7dd5) [0x7f60910a9dd5]

[KochetovNicolai]

Have reproduced on master.

[4ertus2]

Trying reproduce on master. The queries fail but without segfault:

0. ./clickhouse-server(StackTrace::StackTrace()+0x28) [0xab42278]
1. ./clickhouse-server(DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int)+0x4b) [0xab0e0eb]
2. ./clickhouse-server(DB::Block::checkNumberOfRows() const+0x1232) [0xff08132]
3. ./clickhouse-server(DB::NativeBlockOutputStream::write(DB::Block const&)+0x5b) [0xff7221b]
4. ./clickhouse-server(DB::TCPHandler::sendData(DB::Block const&)+0xaf) [0xac0b26f]
5. ./clickhouse-server(DB::TCPHandler::processOrdinaryQuery()+0x4ca) [0xac09dea]
6. ./clickhouse-server(DB::TCPHandler::runImpl()+0x2ef0) [0xac035a0]
7. ./clickhouse-server(DB::TCPHandler::run()+0x24) [0xac11174]

If I remove one of duplicated ids - ('4', 'David'), ('4', 'Joseph'), all the queries are success. But with unexpected value in children.id for 'Susan'.

┌─id─┬─name───┬─j.id─┬─childName─┐
│ 1  │ John   │ 1    │ Robert    │
│ 1  │ John   │      │ Susan     │
│ 3  │ Daniel │ 3    │ Sarah     │
│ 4  │ James  │ 4    │ David     │
│ 5  │ Amanda │ 5    │ Robert    │
└────┴────────┴──────┴───────────┘

If I attach 'format PrettyCompact' to the queries it leads to an error

Data compressed with different methods, given method byte 69, previous method byte 82

[4ertus2]

According to MySQL behaviour we should result only one column from using statement for asterisks. It'll probably solve the main issue.

>select * from persons inner join children using(id)
id    name    childName
1    John    Robert
1    John    Susan
3    Daniel    Sarah
4    James    David
4    James    Joseph
5    Amanda    Robert

[4ertus2]

It will hide an issue #4141
The main problem is not fixed:

select children.* from persons all inner join children using id;
DB::Exception: Sizes of columns doesn't match: default.children.id: 5, childName: 6

select * from persons all inner join children as ch on id = ch.id;
DB::Exception: Sizes of columns doesn't match: id: 6, ch.id: 5

Update: broken between 18.14.2 and 18.14.3. Last unaffected release - 18.12.17.
36c3feea8 2018-10-07 | Merge pull request #3270 from yandex/left-join-right-keys-fix

[4ertus2]

fix for inner and left joins #4184
right & full are in progress

[4ertus2]

right & full appended to #4184

Complex RIGHT|FULL JOIN ON is not working yet:

select X.*, Y.* from X full join Y on (X.id + 1) = (Y.id + 1) order by id;
DB::Exception: Not found column plus(id, 1) in block. There are only columns: id, x_name.