Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

MySQL compatibility: <=> support and JOIN ON conditions #53061

Closed
slvrtrn opened this issue Aug 4, 2023 · 6 comments
Closed

MySQL compatibility: <=> support and JOIN ON conditions #53061

slvrtrn opened this issue Aug 4, 2023 · 6 comments
Assignees
@vdimir
Labels
feature

Comments

@slvrtrn
Copy link
Contributor

slvrtrn commented Aug 4, 2023

While using QuickSight via MySQL, it generates the following query to filter the "top X results":

SELECT `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg`, COUNT(*) AS `count`
FROM (SELECT DATE(`created`)                              AS `$f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg_join_1`,
             RANK() OVER (ORDER BY COUNT(`created`) DESC) AS `$RANK_3`
      FROM `cell_towers`
      GROUP BY DATE(`created`)) AS `t`
       INNER JOIN (SELECT DATE(`created`) AS `f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg`
                   FROM `cell_towers`) AS `t0`
                  ON `t`.`$f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg_join_1` <=>
                     `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg`
WHERE `t`.`$RANK_3` <= 10
GROUP BY `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg`
ORDER BY `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg` DESC
LIMIT 2500;

This query fails with the following exception:

ERROR 62 (00000): Code: 62. DB::Exception: Syntax error: failed at position 472 ('>'): > `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg` WHERE `t`.`$RANK_3` <= 10 GROUP BY `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg` ORDER BY `t0`. 
Expected one of: CAST operator, ANY, ALL, NOT, INTERVAL, CASE, DATE, TIMESTAMP, tuple, collection of literals, array, number, literal, NULL, Bool, true, false, string literal, asterisk, qualified asterisk, compound identifier, list of elements, identifier, COLUMNS matcher, COLUMN

if I change <=> to just <=, then the error is different:

ERROR 403 (00000): Code: 403. DB::Exception: Unsupported JOIN ON conditions. 
Unexpected '`$f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg_join_1` <= `f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg`': 
While processing `$f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg_join_1` <= `f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg`. 
(INVALID_JOIN_ON_EXPRESSION) (version 23.8.1.774 (official build))

How to reproduce

  • Which ClickHouse server version to use: the latest head version.
  • Which interface to use, if matters: MySQL
  • Non-default settings, if any:
    <custom_settings_prefixes>SQL_,custom_</custom_settings_prefixes> in config.xml
  • Sample data for all these tables: cell towers sample dataset

Error message and/or stacktrace

Original stacktrace (with <=>)

2023.08.04 19:01:32.176427 [ 47 ] {mysql:27:cdd033a2-f4a1-41c9-8663-0685aaa30f01} <Debug> executeQuery: (from 172.20.0.1:45294) SELECT `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg`, COUNT(*) AS `count` FROM ( SELECT DATE(`created`) AS `$f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg_join_1`, RANK() OVER (ORDER BY COUNT(`created`) DESC) AS `$RANK_3` FROM `cell_towers` GROUP BY DATE(`created`) ) AS `t` INNER JOIN ( SELECT DATE(`created`) AS `f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg` FROM `cell_towers` ) AS `t0` ON `t`.`$f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg_join_1` <=> `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg` WHERE `t`.`$RANK_3` <= 10 GROUP BY `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg` ORDER BY `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg` DESC LIMIT 2500 (stage: Complete)
2023.08.04 19:01:32.176520 [ 47 ] {mysql:27:cdd033a2-f4a1-41c9-8663-0685aaa30f01} <Error> executeQuery: Code: 62. DB::Exception: Syntax error: failed at position 472 ('>'): > `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg` WHERE `t`.`$RANK_3` <= 10 GROUP BY `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg` ORDER BY `t0`. Expected one of: CAST operator, ANY, ALL, NOT, INTERVAL, CASE, DATE, TIMESTAMP, tuple, collection of literals, array, number, literal, NULL, Bool, true, false, string literal, asterisk, qualified asterisk, compound identifier, list of elements, identifier, COLUMNS matcher, COLUMNS, qualified COLUMNS matcher, substitution, MySQL-style global variable, end of query. (SYNTAX_ERROR) (version 23.8.1.774 (official build)) (from 172.20.0.1:45294) (in query: SELECT `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg`, COUNT(*) AS `count` FROM ( SELECT DATE(`created`) AS `$f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg_join_1`, RANK() OVER (ORDER BY COUNT(`created`) DESC) AS `$RANK_3` FROM `cell_towers` GROUP BY DATE(`created`) ) AS `t` INNER JOIN ( SELECT DATE(`created`) AS `f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg` FROM `cell_towers` ) AS `t0` ON `t`.`$f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg_join_1` <=> `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg` WHERE `t`.`$RANK_3` <= 10 GROUP BY `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg` ORDER BY `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg` DESC LIMIT 2500), Stack trace (when copying this message, always include the lines below):

0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000c6fded7 in /usr/bin/clickhouse
1. DB::Exception::createDeprecated(String const&, int, bool) @ 0x00000000070fed0d in /usr/bin/clickhouse
2. DB::parseQueryAndMovePosition(DB::IParser&, char const*&, char const*, String const&, bool, unsigned long, unsigned long) @ 0x000000001374193c in /usr/bin/clickhouse
3. DB::executeQueryImpl(char const*, char const*, std::shared_ptr<DB::Context>, bool, DB::QueryProcessingStage::Enum, DB::ReadBuffer*) @ 0x0000000012110f88 in /usr/bin/clickhouse
4. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, std::shared_ptr<DB::Context>, std::function<void (DB::QueryResultDetails const&)>, std::optional<DB::FormatSettings> const&) @ 0x0000000012117a2c in /usr/bin/clickhouse
5. DB::MySQLHandler::run() @ 0x0000000012f4c471 in /usr/bin/clickhouse
6. Poco::Net::TCPServerConnection::start() @ 0x00000000159536f4 in /usr/bin/clickhouse
7. Poco::Net::TCPServerDispatcher::run() @ 0x00000000159548f1 in /usr/bin/clickhouse
8. Poco::PooledThread::run() @ 0x0000000015a8aa27 in /usr/bin/clickhouse
9. Poco::ThreadImpl::runnableEntry(void*) @ 0x0000000015a88cfc in /usr/bin/clickhouse
10. ? @ 0x00007f8ea83cb609 in ?
11. ? @ 0x00007f8ea82f0133 in ?

2023.08.04 19:01:32.176656 [ 47 ] {mysql:27:cdd033a2-f4a1-41c9-8663-0685aaa30f01} <Debug> MemoryTracker: Peak memory usage (for query): 0.00 B.
2023.08.04 19:01:32.176706 [ 47 ] {} <Error> MySQLHandler: MySQLHandler: Cannot read packet: : Code: 62. DB::Exception: Syntax error: failed at position 472 ('>'): > `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg` WHERE `t`.`$RANK_3` <= 10 GROUP BY `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg` ORDER BY `t0`. Expected one of: CAST operator, ANY, ALL, NOT, INTERVAL, CASE, DATE, TIMESTAMP, tuple, collection of literals, array, number, literal, NULL, Bool, true, false, string literal, asterisk, qualified asterisk, compound identifier, list of elements, identifier, COLUMNS matcher, COLUMNS, qualified COLUMNS matcher, substitution, MySQL-style global variable, end of query. (SYNTAX_ERROR), Stack trace (when copying this message, always include the lines below):

0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000c6fded7 in /usr/bin/clickhouse
1. DB::Exception::createDeprecated(String const&, int, bool) @ 0x00000000070fed0d in /usr/bin/clickhouse
2. DB::parseQueryAndMovePosition(DB::IParser&, char const*&, char const*, String const&, bool, unsigned long, unsigned long) @ 0x000000001374193c in /usr/bin/clickhouse
3. DB::executeQueryImpl(char const*, char const*, std::shared_ptr<DB::Context>, bool, DB::QueryProcessingStage::Enum, DB::ReadBuffer*) @ 0x0000000012110f88 in /usr/bin/clickhouse
4. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, std::shared_ptr<DB::Context>, std::function<void (DB::QueryResultDetails const&)>, std::optional<DB::FormatSettings> const&) @ 0x0000000012117a2c in /usr/bin/clickhouse
5. DB::MySQLHandler::run() @ 0x0000000012f4c471 in /usr/bin/clickhouse
6. Poco::Net::TCPServerConnection::start() @ 0x00000000159536f4 in /usr/bin/clickhouse
7. Poco::Net::TCPServerDispatcher::run() @ 0x00000000159548f1 in /usr/bin/clickhouse
8. Poco::PooledThread::run() @ 0x0000000015a8aa27 in /usr/bin/clickhouse
9. Poco::ThreadImpl::runnableEntry(void*) @ 0x0000000015a88cfc in /usr/bin/clickhouse
10. ? @ 0x00007f8ea83cb609 in ?
11. ? @ 0x00007f8ea82f0133 in ?
 (version 23.8.1.774 (official build))

stacktrace with <=> changed to <=

2023.08.04 19:03:03.575016 [ 47 ] {mysql:28:542e55a3-efe0-4726-8ac6-6fd2477fbc9b} <Error> executeQuery: Code: 403. DB::Exception: Unsupported JOIN ON conditions. Unexpected '`$f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg_join_1` <= `f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg`': While processing `$f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg_join_1` <= `f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg`. (INVALID_JOIN_ON_EXPRESSION) (version 23.8.1.774 (official build)) (from 172.20.0.1:36688) (in query: SELECT `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg`, COUNT(*) AS `count` FROM ( SELECT DATE(`created`) AS `$f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg_join_1`, RANK() OVER (ORDER BY COUNT(`created`) DESC) AS `$RANK_3` FROM `cell_towers` GROUP BY DATE(`created`) ) AS `t` INNER JOIN ( SELECT DATE(`created`) AS `f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg` FROM `cell_towers` ) AS `t0` ON `t`.`$f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg_join_1` <= `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg` WHERE `t`.`$RANK_3` <= 10 GROUP BY `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg` ORDER BY `t0`.`f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg` DESC LIMIT 2500), Stack trace (when copying this message, always include the lines below):

0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000c6fded7 in /usr/bin/clickhouse
1. DB::Exception::Exception<String>(int, FormatStringHelperImpl<std::type_identity<String>::type>, String&&) @ 0x0000000006d6168d in /usr/bin/clickhouse
2. DB::CollectJoinOnKeysMatcher::visit(DB::ASTFunction const&, std::shared_ptr<DB::IAST> const&, DB::CollectJoinOnKeysMatcher::Data&) @ 0x00000000120c1431 in /usr/bin/clickhouse
3. DB::InDepthNodeVisitor<DB::CollectJoinOnKeysMatcher, true, false, std::shared_ptr<DB::IAST> const>::doVisit(std::shared_ptr<DB::IAST> const&) @ 0x000000001207cba8 in /usr/bin/clickhouse
4. DB::TreeRewriter::analyzeSelect(std::shared_ptr<DB::IAST>&, DB::TreeRewriterResult&&, DB::SelectQueryOptions const&, std::vector<DB::TableWithColumnNamesAndTypes, std::allocator<DB::TableWithColumnNamesAndTypes>> const&, std::vector<String, std::allocator<String>> const&, std::shared_ptr<DB::TableJoin>) const @ 0x00000000120760bf in /usr/bin/clickhouse
5. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context> const&, std::optional<DB::Pipe>, std::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&, std::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::shared_ptr<DB::PreparedSets>)::$_0::operator()(bool) const @ 0x0000000011d2e75c in /usr/bin/clickhouse
6. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context> const&, std::optional<DB::Pipe>, std::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&, std::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::shared_ptr<DB::PreparedSets>) @ 0x0000000011d22b2c in /usr/bin/clickhouse
7. DB::InterpreterSelectWithUnionQuery::InterpreterSelectWithUnionQuery(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context>, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&) @ 0x0000000011dd0932 in /usr/bin/clickhouse
8. DB::InterpreterFactory::get(std::shared_ptr<DB::IAST>&, std::shared_ptr<DB::Context>, DB::SelectQueryOptions const&) @ 0x0000000011cd97fe in /usr/bin/clickhouse
9. DB::executeQueryImpl(char const*, char const*, std::shared_ptr<DB::Context>, bool, DB::QueryProcessingStage::Enum, DB::ReadBuffer*) @ 0x00000000121143ca in /usr/bin/clickhouse
10. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, std::shared_ptr<DB::Context>, std::function<void (DB::QueryResultDetails const&)>, std::optional<DB::FormatSettings> const&) @ 0x0000000012117a2c in /usr/bin/clickhouse
11. DB::MySQLHandler::run() @ 0x0000000012f4c471 in /usr/bin/clickhouse
12. Poco::Net::TCPServerConnection::start() @ 0x00000000159536f4 in /usr/bin/clickhouse
13. Poco::Net::TCPServerDispatcher::run() @ 0x00000000159548f1 in /usr/bin/clickhouse
14. Poco::PooledThread::run() @ 0x0000000015a8aa27 in /usr/bin/clickhouse
15. Poco::ThreadImpl::runnableEntry(void*) @ 0x0000000015a88cfc in /usr/bin/clickhouse
16. ? @ 0x00007f8ea83cb609 in ?
17. ? @ 0x00007f8ea82f0133 in ?

2023.08.04 19:03:03.575174 [ 47 ] {mysql:28:542e55a3-efe0-4726-8ac6-6fd2477fbc9b} <Debug> MemoryTracker: Peak memory usage (for query): 0.00 B.
2023.08.04 19:03:03.575214 [ 47 ] {} <Error> MySQLHandler: MySQLHandler: Cannot read packet: : Code: 403. DB::Exception: Unsupported JOIN ON conditions. Unexpected '`$f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg_join_1` <= `f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg`': While processing `$f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg_join_1` <= `f4245df6-e4eb-428b-9181-8644f65ccaec.created_tg`. (INVALID_JOIN_ON_EXPRESSION), Stack trace (when copying this message, always include the lines below):

0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000c6fded7 in /usr/bin/clickhouse
1. DB::Exception::Exception<String>(int, FormatStringHelperImpl<std::type_identity<String>::type>, String&&) @ 0x0000000006d6168d in /usr/bin/clickhouse
2. DB::CollectJoinOnKeysMatcher::visit(DB::ASTFunction const&, std::shared_ptr<DB::IAST> const&, DB::CollectJoinOnKeysMatcher::Data&) @ 0x00000000120c1431 in /usr/bin/clickhouse
3. DB::InDepthNodeVisitor<DB::CollectJoinOnKeysMatcher, true, false, std::shared_ptr<DB::IAST> const>::doVisit(std::shared_ptr<DB::IAST> const&) @ 0x000000001207cba8 in /usr/bin/clickhouse
4. DB::TreeRewriter::analyzeSelect(std::shared_ptr<DB::IAST>&, DB::TreeRewriterResult&&, DB::SelectQueryOptions const&, std::vector<DB::TableWithColumnNamesAndTypes, std::allocator<DB::TableWithColumnNamesAndTypes>> const&, std::vector<String, std::allocator<String>> const&, std::shared_ptr<DB::TableJoin>) const @ 0x00000000120760bf in /usr/bin/clickhouse
5. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context> const&, std::optional<DB::Pipe>, std::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&, std::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::shared_ptr<DB::PreparedSets>)::$_0::operator()(bool) const @ 0x0000000011d2e75c in /usr/bin/clickhouse
6. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context> const&, std::optional<DB::Pipe>, std::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&, std::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::shared_ptr<DB::PreparedSets>) @ 0x0000000011d22b2c in /usr/bin/clickhouse
7. DB::InterpreterSelectWithUnionQuery::InterpreterSelectWithUnionQuery(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context>, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&) @ 0x0000000011dd0932 in /usr/bin/clickhouse
8. DB::InterpreterFactory::get(std::shared_ptr<DB::IAST>&, std::shared_ptr<DB::Context>, DB::SelectQueryOptions const&) @ 0x0000000011cd97fe in /usr/bin/clickhouse
9. DB::executeQueryImpl(char const*, char const*, std::shared_ptr<DB::Context>, bool, DB::QueryProcessingStage::Enum, DB::ReadBuffer*) @ 0x00000000121143ca in /usr/bin/clickhouse
10. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, std::shared_ptr<DB::Context>, std::function<void (DB::QueryResultDetails const&)>, std::optional<DB::FormatSettings> const&) @ 0x0000000012117a2c in /usr/bin/clickhouse
11. DB::MySQLHandler::run() @ 0x0000000012f4c471 in /usr/bin/clickhouse
12. Poco::Net::TCPServerConnection::start() @ 0x00000000159536f4 in /usr/bin/clickhouse
13. Poco::Net::TCPServerDispatcher::run() @ 0x00000000159548f1 in /usr/bin/clickhouse
14. Poco::PooledThread::run() @ 0x0000000015a8aa27 in /usr/bin/clickhouse
15. Poco::ThreadImpl::runnableEntry(void*) @ 0x0000000015a88cfc in /usr/bin/clickhouse
16. ? @ 0x00007f8ea83cb609 in ?
17. ? @ 0x00007f8ea82f0133 in ?
 (version 23.8.1.774 (official build))
@UnamedRus
Copy link
Contributor

Kinda duplicate #26928 (comment)

@vdimir vdimir self-assigned this Aug 18, 2023
@UnamedRus
Copy link
Contributor

UnamedRus commented Aug 23, 2023 •
edited

It's also called IS NOT DISTINCT FROM in ANSI SQL

https://learn.microsoft.com/en-us/sql/t-sql/queries/is-distinct-from-transact-sql?view=sql-server-ver16

@vdimir vdimir mentioned this issue Aug 23, 2023
Merged
5 tasks
@vdimir
Copy link
Member

vdimir commented Sep 5, 2023

https://fiddle.clickhouse.com/b280d66c-f027-4647-bea2-4af863ca6cc5

@vdimir vdimir closed this as completed Sep 5, 2023
@den-crane
Copy link
Contributor

:(

select null <=> null;

DB::Exception: Function isNotDistinctFrom can be used only in the JOIN ON section: While processing isNotDistinctFrom(NULL, NULL). (NOT_IMPLEMENTED)

#3889

@UnamedRus
Copy link
Contributor

It's task number 5 in #53755

@vdimir
Copy link
Member

vdimir commented Sep 11, 2023 •
edited

We are planning to implement <=> in select as well, it should be relatively easy, though not the high priority.

Created issue to track #54499

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@vdimir vdimir

Labels
feature
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
5 participants
@slvrtrn @vdimir @UnamedRus @alexey-milovidov @den-crane