Clickhouse: UUID comparison in WHERE returns Bad type of field when the column is part of PK

Created on 9 Jul 2018 · 5Comments · Source: ClickHouse/ClickHouse

I'm trying to compare UUIDs in WHERE statement

SELECT DISTINCT uuid_column from a_table WHERE uuid_column = toUUID('00000000-0000-03f8-9cb8-cb1b82fb3900')

Returns

[2018-07-08 01:08:00] [169] ClickHouse exception, code: 169, host: localhost, port: 53478; Code: 169, e.displayText() = DB::Exception: Bad type of Field, e.what() = DB::Exception
[2018-07-08 01:08:00] java.lang.Throwable: Code: 169, e.displayText() = DB::Exception: Bad type of Field, e.what() = DB::Exception
[2018-07-08 01:08:00]   at ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:53)
[2018-07-08 01:08:00]   at ru.yandex.clickhouse.except.ClickHouseExceptionSpecifier.specify(ClickHouseExceptionSpecifier.java:28)
[2018-07-08 01:08:00]   at ru.yandex.clickhouse.ClickHouseStatementImpl.getInputStream(ClickHouseStatementImpl.java:528)
[2018-07-08 01:08:00]   at ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:107)
[2018-07-08 01:08:00]   at ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:91)
[2018-07-08 01:08:00]   at ru.yandex.clickhouse.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:87)
[2018-07-08 01:08:00]   at com.intellij.database.remote.jdbc.impl.RemoteStatementImpl.executeQuery(RemoteStatementImpl.java:161)
[2018-07-08 01:08:00]   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[2018-07-08 01:08:00]   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
[2018-07-08 01:08:00]   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
[2018-07-08 01:08:00]   at java.lang.reflect.Method.invoke(Method.java:498)
[2018-07-08 01:08:00]   at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:346)
[2018-07-08 01:08:00]   at sun.rmi.transport.Transport$1.run(Transport.java:200)
[2018-07-08 01:08:00]   at sun.rmi.transport.Transport$1.run(Transport.java:197)
[2018-07-08 01:08:00]   at java.security.AccessController.doPrivileged(Native Method)
[2018-07-08 01:08:00]   at sun.rmi.transport.Transport.serviceCall(Transport.java:196)
[2018-07-08 01:08:00]   at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:568)
[2018-07-08 01:08:00]   at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:826)
[2018-07-08 01:08:00]   at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$0(TCPTransport.java:683)
[2018-07-08 01:08:00]   at java.security.AccessController.doPrivileged(Native Method)
[2018-07-08 01:08:00]   at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:682)
[2018-07-08 01:08:00]   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
[2018-07-08 01:08:00]   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
[2018-07-08 01:08:00]   at java.lang.Thread.run(Thread.java:745) (no stack trace)

When running

SELECT
    toTypeName(uuid_column),
    toTypeName(toUUID('00000000-0000-03f8-9cb8-cb1b82fb3900'))
FROM a_table

It gives UUID and UUID so the types should be the same. What am I doing wrong? Is the UUID implementation still raw? Thanks!

Source

simPod

Most helpful comment

Ok got it. Happens when column is in primary key

CREATE TABLE IF NOT EXISTS test
(
    created_at DateTime,
    id UUID
)
ENGINE = MergeTree
PARTITION BY toDate(created_at)
ORDER BY (created_at, id);

INSERT INTO test (created_at, id) VALUES (now(), '00000000-0000-03f8-9cb8-cb1b82fb3900');


SELECT * FROM test WHERE id = '00000000-0000-03f8-9cb8-cb1b82fb3900';

simPod on 10 Jul 2018

👍2

All 5 comments

Weird, I can't reproduce it. What's the table structure. Could you post the full reproducible steps?

amosbird on 10 Jul 2018

You're right. Just tried to create minimal example and it works. Will try to dig the cause.

simPod on 10 Jul 2018

Ok got it. Happens when column is in primary key

CREATE TABLE IF NOT EXISTS test
(
    created_at DateTime,
    id UUID
)
ENGINE = MergeTree
PARTITION BY toDate(created_at)
ORDER BY (created_at, id);

INSERT INTO test (created_at, id) VALUES (now(), '00000000-0000-03f8-9cb8-cb1b82fb3900');


SELECT * FROM test WHERE id = '00000000-0000-03f8-9cb8-cb1b82fb3900';

simPod on 10 Jul 2018

👍2

https://github.com/yandex/ClickHouse/issues/1770#issuecomment-405502405

simPod on 18 Jul 2018

👍1

In master.