ClickHouse DB::Exception: Incorrect query

The "DB::Exception: Incorrect query" error in ClickHouse signals a general query validation failure. The query may be syntactically valid (it parses correctly), but it violates semantic rules -- such as referencing incompatible types in a comparison, using clauses in invalid combinations, or structuring a query in a way that ClickHouse cannot execute. The error code is INCORRECT_QUERY.

Impact

The query is rejected during the analysis or optimization phase. No data is read, and no partial results are returned. Since this is a validation-time error, fixing it requires modifying the query text or structure.

Common Causes

1. Invalid clause combinations -- for example, using GROUP BY with ARRAY JOIN in unsupported ways, or mixing incompatible JOIN types.
2. SELECT DISTINCT with ORDER BY on non-selected columns -- ordering by a column not in the DISTINCT select list.
3. Invalid use of aggregate and non-aggregate expressions -- mixing aggregated and non-aggregated columns without proper GROUP BY.
4. Subquery structural issues -- using a multi-column subquery where a scalar is expected.
5. Invalid ALTER TABLE operations -- trying to modify a column in a way that is not supported by the table engine.
6. Conflicting query settings -- using mutually exclusive clauses or settings within the same query.

Troubleshooting and Resolution Steps

1. Read the full error message. The text following "Incorrect query" usually describes the specific validation failure:

   DB::Exception: Incorrect query: SELECT DISTINCT requires that ORDER BY columns are in the SELECT list
   

2. Simplify the query. Remove clauses one at a time to isolate which part triggers the validation error:

   -- Start with the basic query structure
   SELECT column FROM table;
   
   -- Add clauses back one by one
   SELECT column FROM table WHERE ...;
   SELECT column FROM table WHERE ... GROUP BY ...;
   

3. Fix SELECT DISTINCT with ORDER BY. Include ORDER BY columns in the SELECT list:

   -- Wrong
   SELECT DISTINCT name FROM users ORDER BY created_at;
   
   -- Correct: include the ORDER BY column
   SELECT DISTINCT name, created_at FROM users ORDER BY created_at;
   
   -- Or use a subquery
   SELECT name FROM (
       SELECT name, min(created_at) AS min_created
       FROM users GROUP BY name
   ) ORDER BY min_created;
   

4. Check aggregate/non-aggregate mixing. Ensure all non-aggregated columns appear in GROUP BY:

   -- Wrong: name is not aggregated or in GROUP BY
   SELECT name, count() FROM users;
   
   -- Correct
   SELECT name, count() FROM users GROUP BY name;
   

5. Validate ALTER TABLE operations. Some engines have restrictions on what can be altered:

   -- Check the engine type first
   SELECT engine FROM system.tables WHERE database = 'db' AND name = 'tbl';
   

6. Review subquery usage. Scalar subqueries must return exactly one row and one column:

   -- Wrong: subquery returns multiple columns
   SELECT * FROM t1 WHERE id = (SELECT id, name FROM t2 LIMIT 1);
   
   -- Correct
   SELECT * FROM t1 WHERE id = (SELECT id FROM t2 LIMIT 1);
   

Best Practices

• Build complex queries incrementally, testing each clause addition to catch validation errors early.
• Use EXPLAIN to check query plans before executing expensive queries -- this will also surface validation errors.
• Understand ClickHouse's specific SQL semantics, which may differ from standard SQL or other databases you are familiar with.
• When in doubt about clause compatibility, consult the ClickHouse SQL reference for the specific statement type.
• Use CTEs (WITH clause) to break complex queries into more readable, testable components.

Frequently Asked Questions

Q: How is INCORRECT_QUERY different from a syntax error?
A: A syntax error means the query cannot be parsed at all -- the SQL grammar is violated. INCORRECT_QUERY means the query parses successfully but violates semantic rules during validation. Think of it as the difference between a grammatically correct sentence that makes no sense versus one that cannot be read at all.

Q: Can INCORRECT_QUERY errors be version-specific?
A: Yes. ClickHouse may relax or tighten validation rules between versions. A query that was accepted in an older version might be rejected in a newer one if stricter validation was added, and vice versa.

Q: I get this error in a materialized view. How do I fix it?
A: You need to drop the materialized view and recreate it with a corrected SELECT query. Use SHOW CREATE TABLE mv_name to see the current definition, fix the issue, then DROP TABLE mv_name and create it again.

Q: Does EXPLAIN help diagnose INCORRECT_QUERY errors?
A: EXPLAIN runs the same analysis phase as a regular query, so it will surface the same validation error with the same message. However, using EXPLAIN is a good practice for testing queries without executing them.