[Exception] DB::Exception: Block structure mismatch in UNION stream: different number of columns

[lamberken]

env

• clickhosue-server:version 1.1.54370
• centerlog is a distributed table

table structure

table centerlog ( 
  type String,
  date Date,
  time DateTime,
  appname String,
  module String,
  category String,
  subcategory String,
  filter1 String,
  filter2 String,
  message String,
  errormessage String
)  

select some field, throw error

SELECT time
FROM centerlog 
WHERE date = toDate('2018-04-02')
LIMIT 1

Received exception from server (version 1.1.54370):
Code: 171. DB::Exception: Received from 10.12.180.113. DB::Exception: Block structure mismatch in UNION stream: different number of columns:
date Date UInt16(size = 0), time DateTime UInt32(size = 0)
time DateTime UInt32(size = 0). 

select asterisk *, no problem

SELECT *
FROM centerlog
WHERE date = toDate('2018-04-02')
LIMIT 1

┌─type─┬───────date─┬────────────────time─┬─appname───────────────┬─module─────┬─category────┬─subcategory─┬─filter1──────────────────────┬─filter2─┬─message─────────────────────────────────────────────────────────────────┬─errormessage─┐
│ Info │ 2018-04-02 │ 2018-04-02 12:58:36 │ tcwireservice │ WxRTime │ notUseBs │             │ ohmdjVHx-rpLDY │         │ -G9NoljVHx-rpLDY, userInfo: null │              │
└──────┴────────────┴─────────────────────┴───────────────────────┴────────────┴─────────────┴─────────────┴──────────────────────────────┴─────────┴─────────────────────────────────────────────────────────────────────────┴──────────────┘

[KochetovNicolai]

Can you try to create an example how to reproduce this problem?
I've created Distributed over MergeTree with the same structure and inserted some data, but both queries on 1.1.54370 works fine for me.

[lamberken]

@KochetovNicolai, here are demo, you can try.

if the query with limit 1, it'll throw error.

use default;

CREATE TABLE centerlog_shard ON CLUSTER datacenter
( date Date,  time DateTime,  appname String) 
ENGINE = MergeTree(date, (time, appname), 8192);

CREATE TABLE centerlog ON CLUSTER datacenter
( date Date,  time DateTime,  appname String) 
ENGINE = Distributed(datacenter, default, centerlog_shard, rand());

insert into centerlog values ('2018-01-21','2018-01-21 15:12:13','test');

SELECT time
FROM centerlog 
WHERE date = '2018-01-21'
LIMIT 1;

[KochetovNicolai]

@lamber-ken, thank you for demo. I've reproduced on cluster with 2 shards.

[lamberken]

@KochetovNicolai , it seems dosn't been fixed, version 1.1.54378

SELECT time
FROM centerlog 
WHERE date = '2018-01-21'
LIMIT 1

Received exception from server (version 1.1.54378):
Code: 171. DB::Exception: DB::Exception: Block structure mismatch in UNION stream: different number of columns:
time DateTime UInt32(size = 0)
date Date UInt16(size = 0), time DateTime UInt32(size = 0). 

0 rows in set. Elapsed: 0.079 sec. 

[jpiper]

This is also happening for me on 1.1.54381

[jpiper]

This is still happening for me on 1.1.54388 :(

[Ouriel]

I have the same or a similar issue on 1.1.54388
I can't post the full table definition but it's a distributed table with userId as sharding key (with cityhash function) and date as partition for the mergeTree table under.

SELECT DISTINCT userId
FROM test.events 
WHERE date >= '2018-07-15'
LIMIT 1

Received exception from server (version 1.1.54388):
Code: 171. DB::Exception: Received from localhost:9000, 127.0.0.1. DB::Exception: Block structure mismatch in UNION stream: different number of columns:
userId String String(size = 0)
userId String String(size = 0), date Date UInt16(size = 0). 

0 rows in set. Elapsed: 0.100 sec.

Replacing the where condition with a between (or just end to the interval) make it works...

SELECT DISTINCT userId
FROM test.events 
WHERE (date >= '2018-07-15') AND (date <= today())
LIMIT 1

┌─userId───────────────────────────┐
│ 668e5c3fb780d08d62c6825a4a0607dc │
└──────────────────────────────────┘

1 rows in set. Elapsed: 0.018 sec. Processed 131.07 thousand rows, 2.87 MB (7.46 million rows/s., 163.54 MB/s.) 

Maybe that help to understand where is the issue.

[Ouriel]

This post seems related and add some info:
https://stackoverflow.com/questions/50350466/distinct-select-with-where-filter-on-clickhouse-distributed-table-failed/51786576#51786576

[wake-up-neo]

The same for LIMIT on FINAL query for ReplicatedCollapsingMergeTree

SELECT * FROM table_name FINAL LIMIT 1

Code: 171. DB::Exception: Received from localhost:9002, ::1. DB::Exception: Block structure mismatch in UNION stream: different number of columns

The column which it shows different is cityHash64(column) which is used in a tuple expression for the collapsing (primary key).

[wake-up-neo]

Sorry, the previous message is related to Distributed on ReplicatedCollapsingMergeTree.

[vmanyushin]

Same problem with latest stable version 18.12.14, 18.12.17

steps to reproduce:

CREATE DATABASE tmp ON CLUSTER cluster_r4s2;

CREATE TABLE tmp.t1_local ON CLUSTER cluster_r4s2
(
    event_date Date DEFAULT toDate(event_time),
    event_time DateTime,
    log_type UInt32,
    account_id String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/tmp.t1', '{host}', event_date, (event_time, account_id), 8192);

CREATE TABLE tmp.t1 ON CLUSTER cluster_r4s2 AS tmp.t1_local
ENGINE = Distributed('cluster_r4s2', 'tmp', 't1_local', rand());

test with nodata

SELECT
    event_date,
    event_time,
    log_type
FROM tmp.t1
WHERE (log_type = 30305) AND (account_id = '111111')

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

SELECT
    event_date,
    event_time,
    log_type
FROM tmp.t1
WHERE (log_type = 30305) AND (account_id = '111111')
LIMIT 1

Received exception from server (version 18.12.17):
Code: 171. DB::Exception: Received from localhost:9001, 127.0.0.1. DB::Exception: Block structure mismatch in UNION stream: different names of columns:
event_date Date UInt16(size = 0), event_time DateTime UInt32(size = 0), log_type UInt32 UInt32(size = 0)
log_type UInt32 UInt32(size = 0), event_date Date UInt16(size = 0), event_time DateTime UInt32(size = 0).

0 rows in set. Elapsed: 0.002 sec.

[VaBezruchko]

I reproduce same issue on 18.14.9 version with select query .

Error:
Received exception from server (version 18.14.9):
Code: 171. DB::Exception: Received from 127.0.0.1:9000. DB::Exception: Block structure mismatch in UNION stream: different names of columns:

But removing the limit section make it works.

[alexey-milovidov]

Fixed in master.

[raulgregg]

When trying to perform a UNION ALL, I'm getting this error:

DB::Exception: Block structure mismatch in function connect between Converting and Concat stream: different number of columns: id_item String String(size = 0) (version 20.5.2.7 (official build))

Both tables have the same size in columns (10), data types and column names are all the same. Tried casting all columns toString() as Concat() only concats strings and got the same error.

The error message isn't very helpful. Ideas on how to troubleshoot?

[KochetovNicolai]

@raulgregg could you please, open a separate issue?
Also please add query and table scheme.

[FrieNze]

I have the same problem. version 20.5.2.7 (official build)

DB::Exception: Block structure mismatch in function connect between Converting and Concat stream: different number of columns

My query looks like:

SELECT
    *
FROM
    (
        SELECT * FROM results_v1
        UNION ALL
        SELECT * FROM correctives_v1
    )
ORDER BY id, event_time

results_v1 and correctives_v1 have identical structure

[DouMiaoO-Oo]

@raulgregg could you please, open a separate issue?
Also please add query and table scheme.

@KochetovNicolai Hello, I just opened a issue #13936