ClickHouse / clickhouse-java

ClickHouse Java Clients & JDBC Driver
https://clickhouse.com
Apache License 2.0
1.45k stars 537 forks source link

Table with transaction column name not parsed #1477

Open subkanthi opened 1 year ago

subkanthi commented 1 year ago

Describe the bug

Table with transaction column name not parsed

 create table test_execute_batch(a Int32, transaction String)engine=Memory

Expected behaviour

It should be parsed and the table should be created.

Code example

            Assert.assertFalse(stmt.execute("drop table if exists test_execute_batch; "
                    + "create table test_execute_batch(a Int32, transaction String)engine=Memory"), "Should not have result set");

Error log

2023-10-23 10:44:09:447 -0400 [main] WARN ClickHouseSqlParser - Parse error at line 1, column 83. Encountered: transaction. If you believe the SQL is valid, please feel free to open an issue on Github with this warning and the following SQL attached. drop table if exists test_execute_batch; create table test_execute_batch(a Int32, transaction String)engine=Memory

java.sql.SQLException: Code: 62. DB::Exception: Syntax error (Multi-statements are not allowed): failed at position 40 (end of query): ; create table test_execute_batch(a Int32, transaction String)engine=Memory. . (SYNTAX_ERROR) (version 23.2.4.12 (official build)) , server ClickHouseNode [uri=http://localhost:49238/test_statement]@1568086

Configuration

Environment

ClickHouse server

mzitnik commented 1 year ago

@subkanthi Can you provide a simple snippet that reproduces the issue?

subkanthi commented 1 year ago

Hi @mzitnik , here is the modified Integration test

    @Test(groups = "integration")
    public void testExecuteBatch() throws SQLException {
        Properties props = new Properties();
        try (Connection conn = newConnection(props); Statement stmt = conn.createStatement()) {
            Assert.assertEquals(stmt.executeBatch(), new int[0]);
            Assert.assertEquals(stmt.executeLargeBatch(), new long[0]);
            stmt.addBatch("select 1");
            stmt.clearBatch();
            Assert.assertEquals(stmt.executeBatch(), new int[0]);
            Assert.assertEquals(stmt.executeLargeBatch(), new long[0]);
            stmt.addBatch("select 1");
            // mixed usage
            Assert.assertThrows(SQLException.class, () -> stmt.execute("select 2"));
            Assert.assertThrows(SQLException.class, () -> stmt.executeQuery("select 2"));
            Assert.assertThrows(SQLException.class,
                    () -> stmt.executeLargeUpdate("drop table if exists non_existing_table"));
            Assert.assertThrows(SQLException.class,
                    () -> stmt.executeUpdate("drop table if exists non_existing_table"));
            // query in batch
            Assert.assertThrows(BatchUpdateException.class, () -> stmt.executeBatch());
            stmt.addBatch("select 1");
            Assert.assertThrows(BatchUpdateException.class, () -> stmt.executeLargeBatch());

            Assert.assertFalse(stmt.execute("drop table if exists test_execute_batch; "
                    + "create table test_execute_batch(a Int32, transaction String)engine=Memory"), "Should not have result set");
            stmt.addBatch("insert into test_execute_batch values(1,'1')");
            stmt.addBatch("insert into test_execute_batch values(2,'2')");
            stmt.addBatch("insert into test_execute_batch values(3,'3')");
            Assert.assertEquals(stmt.executeBatch(), new int[] { 1, 1, 1 });

Note: with backticks it works transaction

mva-coinify commented 3 weeks ago

Any chance this can be fixed? Seems like the issue is replicable and has been a year ago.

chernser commented 3 weeks ago

Good day, @mva-coinify! Will look into it. Would it help if column is wrapped with "?