ClickHouse/clickhouse-java

ClickHouseSqlParser Parse error on insert into tab_xx select from ( select subQuery )

zzmark opened this issue · 0 comments

zzmark commented

Describe the bug

execute multiQuery on insert into select from ( subQuery ) ; after has other sql , stmt parse fail

Expected behaviour

Code example

String task = """
       select count(), 'res' from bi_3.test_left_1;
       
       insert into bi_3.test_left_2
       select user.phone, toYYYYMM(user.login_date), last_value(user.login_date), sum(log.cnt)
       from (
           select phone, login_date
           from event
           group by phone, login_date
        ) user
       join (
           select phone, login_date, 1 as cnt
           from event
           where DAY(login_date) between 1 and 5
           group by phone, login_date
       ) log
       on user.phone = log.phone and toYYYYMM(user.login_date) = toYYYYMM(log.login_date) + 1
       group by user.phone, toYYYYMM(user.login_date);
       
       select count() from event;
       """;

try (
                Connection conn = createConnection();
 ) {
     Connection wrap = conn;
     if (wrap instanceof ClickHouseConnection ckConn) {
        var pstmt = ckConn.parse(task, ckConn.getConfig(), Map.of());
        System.out.println();
    }
} catch (SQLException e) {
    throw new RuntimeException(e);
}

Error log

Parse error at line 19, column 1.  Encountered: select. If you believe the SQL is valid, please feel free to open an issue on Github with this warning and the following SQL attached.
insert into db_t.test_left_2
select user.phone, toYYYYMM(user.login_date), last_value(user.login_date), sum(log.cnt)
from (
    select phone, login_date
    from event
    group by phone, login_date
 ) user
join (
    select phone, login_date, 1 as cnt
    from event
    where DAY(login_date) between 1 and 5
    group by phone, login_date
) log
on user.phone = log.phone and toYYYYMM(user.login_date) = toYYYYMM(log.login_date) + 1
group by user.phone, toYYYYMM(user.login_date);

select count() from event;

Configuration

Environment

  • Client version: 0.6.4
  • Language version: java openjdk 17
  • OS: Windows 10 / Centos 7

ClickHouse server

  • ClickHouse Server version: 24.3
  • ClickHouse Server non-default settings, if any:
  • CREATE TABLE statements for tables involved:
create table test_left_2
(
  phone String,
  login_date Date,
  last_login Date,
  cnt Int32
)
engine = MergeTree
order by phone;

create table event
(
    phone String,
    login_date Date
) engine = MergeTree
order by phone;