Clickhouse: 19.17 Method createColumn() is not implemented for data type Set.

[SaltTan]

The test case:

ClickHouse client version 19.17.2.4 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 19.17.2 revision 54428.

:) select count()
FROM   system.query_log
ANY LEFT JOIN (select 1 as port) USING port
PREWHERE type in (1)
WHERE event_date = today()

SELECT count()
FROM system.query_log
ANY LEFT JOIN
(
    SELECT 1 AS port
) USING (port)
PREWHERE type IN (1)
WHERE event_date = today()

Received exception from server (version 19.17.2):
Code: 48. DB::Exception: Received from localhost:9000. DB::Exception: Method createColumn() is not implemented for data type Set.

0 rows in set. Elapsed: 0.002 sec.

[sunadm]

Data type mismatch. U can use toUInt16 to convert.

SELECT
    count()
FROM
    system.query_log ANY
LEFT JOIN (
    SELECT toUInt16(1) AS port)
        USING port PREWHERE TYPE IN (1)
WHERE
    event_date = today();

BUT, In version 19.11.x, system will report the exception message:

DB::Exception: Type mismatch of columns to JOIN by: port UInt16 at left, port UInt8 at right

this very clearly!

In version 19.13.x, system accept that, seems convert the type default, and will not through any exception.

In version 19.17, system through the confused exception message:

DB::Exception: Method createColumn() is not implemented for data type Set.

[SaltTan]

I think the issue is more complicated.
This query fails as well:

select count()
FROM   system.query_log
ANY LEFT JOIN system.query_log USING port
PREWHERE type in (1)
WHERE event_date = today()

And this query:

select count()
FROM   system.query_log
ANY LEFT JOIN system.query_thread_log USING query_id
PREWHERE type in (1)
WHERE event_date = today()

[DeanThompson]

This issue also exists in version 19.17.4.11 (official build). Any workaround?

[DeanThompson]

It seems that the WHERE clause should move to subqueries.

This query works:

SELECT count()
FROM
(
    SELECT query_id
    FROM system.query_log
    PREWHERE type IN (1)
    WHERE event_date = today()
)
ANY LEFT JOIN system.query_thread_log USING (query_id)

[orloffv]

in PREWHERE section
Method createColumn() is not implemented for data type Function((String, String) -> String)

SELECT
    arrayJoin AS kv_key
FROM system.query_log
ARRAY JOIN ProfileEvents.Names AS arrayJoin
PREWHERE has(arrayMap(key -> key, ProfileEvents.Names), 'Query')
WHERE arrayJoin = 'Query'
LIMIT 1

Connected to ClickHouse server version 19.17.4 revision 54428

[jarhed]

I too am getting this error with:

DB::Exception: Method createColumn() is not implemented for data type Set (version 19.17.4.11 (official build))

SELECT
    placement,
    sum(impressions) as impressionSum,
    sum(validPOI) as insidePOI,
    sum(validDeviceId) as validDeviceId,
    sum(cellularCentroid) as cellularCentroid,
    sum(sumMinDistToPOI) as sumMinDistToPOI,
    sum(geoSupplied) as geoSupplied
FROM
    impressions
INNER JOIN mysql('127.0.0.1:3306',
    'clientdashboard',
    'campaigns',
    'root',
    'fancypassword') as campaigns ON
    impressions.client_id = campaigns.id
WHERE
    placement IN ('Camp')
    AND campaigns.ref IN ('russell')
    AND date >= toDate('2019-11-20')
    AND date <= toDate('2019-12-04')
GROUP BY
    placement

[sklyar]

I have the same problem:

DB::Exception: The createColumn() method is not implemented for the Set data type (version 19.17.5.18 (official build)).

SELECT links.ID
     , links.Type
     , count()
     , toDateTime(toStartOfInterval(StartTime, INTERVAL 1 week)) AS StartTime
FROM shows
         ALL
         INNER JOIN
     (
         SELECT * FROM VALUES('ID UInt64, CampaignContentID UInt64, Type String', (23, 44, 'campaign'))
         ) AS links ON shows.CampaignContentID = links.CampaignContentID
WHERE PlatformID = 9
  AND DeviceID IN (5)
  AND shows.StartTime >= '2019-11-25 00:00:00'
  AND shows.EndTime <= '2020-01-05 23:59:59'
GROUP BY links.ID
       , links.Type
       , shows.CampaignID
       , StartTime;

The error disappears if (one of):

• remove the JOIN from the sql
• remove the IN operator from the DeviceID IN (5)

But I'm pretty sure the problem is with using JOIN.

I had to rollback to version 19.16.2.2.

[Slach]

Same problem on 19.17.4.11
following query works if remove INNER JOIN
of if remove i.session2_stat_crm_tracker IN (...)

I didn't understand error message and how corresponding JOIN and WHERE in different columns (i.pays in JOIN and i.session2_stat_crm_tracker in WHERE)

SELECT toDate(i.date)                  AS day,
       i.offer_type                    AS offer_type,
       sum(i.castats)                  AS revenue,
       uniqExact(i.session2_sessionid) AS visits
FROM wister.raw_data AS i
INNER JOIN prod.mysql_pays p ON (i.pays = p.nom)
WHERE 1
  AND i.date >= '2019-01-01 00:00:00'
  AND i.date < '2019-12-19 00:00:00'
  AND i.date < '2019-12-18 00:00:00'
  AND i.code_affilie LIKE 'aff%'
  AND (i.session2_stat_crm_tracker IN (''
    , 'CRM_REDIRECT'
    , 'CRM_NATIVE_REDIRECT'
    , 'CRM_NO'
    , 'CRM_NO_NATIVE'))

  AND i.date >= '2019-01-01 00:00:00'
  AND i.date < '2019-12-19 00:00:00'
  AND i.date < '2019-12-18 00:00:00'
  AND i.code_affilie LIKE 'aff%'
 AND p.code IN ('ITA') -- work 

GROUP BY day, offer_type

Stack trace:

0. 0x3512b60 StackTrace::StackTrace() /usr/bin/clickhouse
1. 0x351cdaf DB::Exception::Exception(std::string const&, int) /usr/bin/clickhouse
2. 0x60cbaeb DB::IDataTypeDummy::createColumn() const /usr/bin/clickhouse
3. 0x61bf648 ? /usr/bin/clickhouse
4. 0x61ca114 DB::InterpreterSelectQuery::analyzeExpressions(DB::ASTSelectQuery const&, DB::SelectQueryExpressionAnalyzer&, DB::QueryProcessingStage::Enum, DB::QueryProcessingStage::Enum, DB::Context const&, std::shared_ptr<DB::IStorage> const&, bool, std::shared_ptr<DB::FilterInfo> const&, DB::Block const&) /usr/bin/clickhouse
5. 0x61caa36 DB::InterpreterSelectQuery::getSampleBlockImpl() /usr/bin/clickhouse
6. 0x61cc439 ? /usr/bin/clickhouse
7. 0x61cd387 DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptr<DB::IAST> const&, DB::Context const&, std::shared_ptr<DB::IBlockInputStream> const&, std::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::vector<std::string, std::allocator<std::string> > const&) /usr/bin/clickhouse
8. 0x61cde4b DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptr<DB::IAST> const&, DB::Context const&, DB::SelectQueryOptions const&, std::vector<std::string, std::allocator<std::string> > const&) /usr/bin/clickhouse
9. 0x61f7e63 DB::InterpreterSelectWithUnionQuery::InterpreterSelectWithUnionQuery(std::shared_ptr<DB::IAST> const&, DB::Context const&, DB::SelectQueryOptions const&, std::vector<std::string, std::allocator<std::string> > const&) /usr/bin/clickhouse
10. 0x61af8bf DB::InterpreterFactory::get(std::shared_ptr<DB::IAST>&, DB::Context&, DB::QueryProcessingStage::Enum) /usr/bin/clickhouse
11. 0x62d476a ? /usr/bin/clickhouse
12. 0x62d72ba DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, DB::Context&, std::function<void (std::string const&)>, std::function<void (std::string const&)>) /usr/bin/clickhouse
13. 0x359e471 DB::HTTPHandler::processQuery(Poco::Net::HTTPServerRequest&, HTMLForm&, Poco::Net::HTTPServerResponse&, DB::HTTPHandler::Output&) /usr/bin/clickhouse
14. 0x35a14b1 DB::HTTPHandler::handleRequest(Poco::Net::HTTPServerRequest&, Poco::Net::HTTPServerResponse&) /usr/bin/clickhouse
15. 0x6dbcc59 Poco::Net::HTTPServerConnection::run() /usr/bin/clickhouse
16. 0x6db98bf Poco::Net::TCPServerConnection::start() /usr/bin/clickhouse
17. 0x6db9fb5 Poco::Net::TCPServerDispatcher::run() /usr/bin/clickhouse
18. 0x723f481 Poco::PooledThread::run() /usr/bin/clickhouse
19. 0x723b208 Poco::ThreadImpl::runnableEntry(void*) /usr/bin/clickhouse
20. 0x791d69f ? /usr/bin/clickhouse
21. 0x7fdf89261dc5 start_thread /usr/lib64/libpthread-2.17.so
22. 0x7fdf88d8b1cd clone /usr/lib64/libc-2.17.so

[Slach]

the workaround which ok for me is SELECT ... SETTINGS optimize_move_to_prewhere=0;

[Slach]

doesn't reproduce on 19.19.1.1956 testing release installed from official Yandex RPM repo

[den-crane]

doesn't reproduce on 19.19.1.1956 testing release installed from official Yandex RPM repo

ClickHouse client version 19.19.1.1957 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 19.19.1 revision 54430.

SELECT count()
FROM system.query_log
ANY LEFT JOIN
(
    SELECT 1 AS port
) USING (port)
PREWHERE type IN (1)
WHERE event_date = today()

Received exception from server (version 19.19.1):
Code: 48. DB::Exception: Received from localhost:9000. DB::Exception: Method createColumn() is not implemented for data type Set.

[genzgd]

We are also rolling back as a result of this bug. Fixing our various queries has proven very cumbersome.

[zydm521]

I also have this problem，But I did。
Add one level of sub query to solve the problem
select *
from A
ANY LEFT JOIN B
ON A.a=B.a
WHERE A.a IN (XXX)
AND A.b IN (XXX);

ok，change

select *
from （select * from A ）AS A
ANY LEFT JOIN B
ON A.a=B.a
WHERE A.a IN (XXX)
AND A.b IN (XXX);

Then right！
come from china!

[v-isitor]

Unfortunately we had the same issue, when we upgraded our cluster to the stable version 19.17.4.11.
I analyzed the exception's stack trace and the source code and it seems to be cause by a performance improvement, which was introduced with version 19.17.4.11:
"Run another pass of syntax/expression analysis to get potential optimizations after constant predicates are folded. #7497 (Amos Bird)"

It happens, when I'm using the IN operator within the prewhere clause and the statement contains a JOIN. The exception can also be raised, when the IN operator is used in the where clause and the optimizer moves it to prewhere internally.

I analyzed the ClickHouse source code. It seems to generate sample blocks for further optimization. If a block contains an IN operator, which uses the "Set" data type as operand, it cannot sanitize the column.

We're using ReplicatedReplacingMergeTree and ReplacingMergeTree tables. Maybe some other engines are not affected.

My stack trace:

<Error> HTTPHandler: Code: 48, e.displayText() = DB::Exception: Method createColumn() is not implemented for data type Set, Stack trace:

0.  StackTrace::StackTrace() /usr/bin/clickhouse
1.  DB::Exception::Exception(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, int) /usr/bin/clickhouse
2.  DB::IDataTypeDummy::createColumn() const /usr/bin/clickhouse
3.  ? /usr/bin/clickhouse
4.  DB::InterpreterSelectQuery::analyzeExpressions(DB::ASTSelectQuery const&, DB::SelectQueryExpressionAnalyzer&, DB::QueryProcessingStage::Enum, DB::QueryProcessingStage::Enum, DB::Context const&, std::shared_ptr<DB::IStorage> const&, bool, std::shared_ptr<DB::FilterInfo> const&, DB::Block const&) /usr/bin/clickhouse
5.  DB::InterpreterSelectQuery::getSampleBlockImpl() /usr/bin/clickhouse
6.  ? /usr/bin/clickhouse
7.  DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptr<DB::IAST> const&, DB::Context const&, std::shared_ptr<DB::IBlockInputStream> const&, std::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::vector<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> >, std::allocator<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > > > const&) /usr/bin/clickhouse
8.  DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptr<DB::IAST> const&, DB::Context const&, DB::SelectQueryOptions const&, std::vector<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> >, std::allocator<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > > > const&) /usr/bin/clickhouse
9.  DB::InterpreterSelectWithUnionQuery::InterpreterSelectWithUnionQuery(std::shared_ptr<DB::IAST> const&, DB::Context const&, DB::SelectQueryOptions const&, std::vector<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> >, std::allocator<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > > > const&) /usr/bin/clickhouse
10. DB::InterpreterSelectWithUnionQuery::getSampleBlock(std::shared_ptr<DB::IAST> const&, DB::Context const&) /usr/bin/clickhouse
11. DB::getNamesAndTypeListFromTableExpression(DB::ASTTableExpression const&, DB::Context const&) /usr/bin/clickhouse
12. DB::getDatabaseAndTablesWithColumnNames[abi:cxx11](DB::ASTSelectQuery const&, DB::Context const&) /usr/bin/clickhouse
13. DB::SyntaxAnalyzer::analyze(std::shared_ptr<DB::IAST>&, DB::NamesAndTypesList const&, std::vector<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> >, std::allocator<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > > > const&, std::shared_ptr<DB::IStorage>, DB::NamesAndTypesList const&) const /usr/bin/clickhouse
14. ? /usr/bin/clickhouse
15. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptr<DB::IAST> const&, DB::Context const&, std::shared_ptr<DB::IBlockInputStream> const&, std::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::vector<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> >, std::allocator<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > > > const&) /usr/bin/clickhouse
16. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptr<DB::IAST> const&, DB::Context const&, DB::SelectQueryOptions const&, std::vector<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> >, std::allocator<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > > > const&) /usr/bin/clickhouse
17. DB::InterpreterSelectWithUnionQuery::InterpreterSelectWithUnionQuery(std::shared_ptr<DB::IAST> const&, DB::Context const&, DB::SelectQueryOptions const&, std::vector<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> >, std::allocator<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > > > const&) /usr/bin/clickhouse
18. DB::InterpreterInsertQuery::execute() /usr/bin/clickhouse
19. ? /usr/bin/clickhouse
20. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, DB::Context&, std::function<void (std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&)>, std::function<void (std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&)>) /usr/bin/clickhouse
21. DB::HTTPHandler::processQuery(Poco::Net::HTTPServerRequest&, HTMLForm&, Poco::Net::HTTPServerResponse&, DB::HTTPHandler::Output&) /usr/bin/clickhouse
22. DB::HTTPHandler::handleRequest(Poco::Net::HTTPServerRequest&, Poco::Net::HTTPServerResponse&) /usr/bin/clickhouse
23. Poco::Net::HTTPServerConnection::run() /usr/bin/clickhouse
24. Poco::Net::TCPServerConnection::start() /usr/bin/clickhouse
25. Poco::Net::TCPServerDispatcher::run() /usr/bin/clickhouse
26. Poco::PooledThread::run() /usr/bin/clickhouse
27. Poco::ThreadImpl::runnableEntry(void*) /usr/bin/clickhouse
28. ? /usr/bin/clickhouse
29. start_thread /lib/x86_64-linux-gnu/libpthread-2.27.so
30. clone /lib/x86_64-linux-gnu/libc-2.27.so
 (version 19.17.6.36 (official build))