Clickhouse LIMIT BY is not supported

Describe the bug
A clear and concise description of what the bug is.

SQL has normal query results on the Clickhouse, but cannot be parsed using jsqlparser

SQL

select
	exp_id ,
	usercnt1 as exp_user_cnt,
	case
		when usercnt1 = 0 then 0
		else retain1 / usercnt1
	end as value
from
	(
	select
		exp_id,
		count(case when retain_days = 1 then uid end) as usercnt1,
		cast(count(case when retain_days = 1 and is_retain = 'true' then uid end) as double) as retain1
	from
		(
		select
			exp_id,
			day,
			uid,
			retain_days,
			is_retain
		from
			c_dm.dm_grw_user_sd
		where
			day >= '2021-11-29'
			and day <= '2021-12-01'
			and layer_id = 2010
		order by
			exp_id,
			uid,
			retain_days,
			day
		limit 1 by exp_id,
		uid,
		retain_days ) a
	group by
		exp_id )
order by
	exp_id

JAVA

CCJSqlParserUtil.parse(sql)

ERROR

net.sf.jsqlparser.JSQLParserException: Encountered unexpected token: "by" "BY"
    at line 33, column 11.

Was expecting one of:

    ")"
    "EXCEPT"
    "FOR"
    "INTERSECT"
    "MINUS"
    "UNION"
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:190)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:63)
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "by" "BY"
    at line 33, column 11.

System

Database you are using
clickhouse
Java Version 1.8
JSqlParser version 4.2

Greetings.

please what exactly does

limit 1 by exp_id,
		uid,
		retain_days

represent? Please point me on the SQL specification you are following here.

If you relate to Clickhouse LIMIT BY, then unfortunately it is not supported in JSQLParser. Pull Requests are certainly welcome.

Please consider changing the Caption into Clickhouse LIMIT BY is not supported yet.

feat: ClickHouse `LIMIT ... BY ...` clause

Basic implementation provided, but please give it a proper testing and show any SQL Statements not working as expected.
(I do not use ClickHouse and DuckDB does not support it.)

Assorted Fixes #8 (#1807)

Backslash Quotation of Single-Quote `'\\''` (#1813)

Navigation Menu

Repository navigation

Clickhouse LIMIT BY is not supported #1436

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Clickhouse LIMIT BY is not supported #1436

Description

Activity

manticore-projects commented on Dec 2, 2021

manticore-projects commented on Apr 30, 2023

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions