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.

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

Parser cannot parse backslashed value #695

Closed
kaifeur opened this issue Jul 23, 2021 · 5 comments · Fixed by #747
Closed

Parser cannot parse backslashed value #695

kaifeur opened this issue Jul 23, 2021 · 5 comments · Fixed by #747
Labels
Milestone

Comments

@kaifeur
Copy link

kaifeur commented Jul 23, 2021

Trying to execute query like this:

SELECT * FROM t WHERE col IN (?);

and using preparedStatement.setObject(1, [some byte array]).
So the resulting sql looks like

SELECT * FROM t WHERE col IN (\x12\x34\x56);

and then parser is failing with an exception: Failed to parse the given SQL. If you believe the SQL is valid, please feel free to open an issue on Github with the following SQL and exception attached.

@zhicwu
Copy link
Contributor

zhicwu commented Jul 25, 2021

Hi @rubtsovnyu, was this working before? The SQL is invalid - perhaps you meant something like select 0x12? Moreover, are you trying to issue a query like select * from t where col in (1,2,3)? If that's the case and you still want to use prepared statement, you may try something like below:

// values is a byte array
StringBuilder sql = new StringBuilder("select * from t where col in (");
for (byte b : values) {
  sql.append(",?");
}
if (sql.length() > 0) {
  sql.deleteCharAt(0);
} else {
  throw new IllegalArgumentException("at least one input is required");
}
sql.insert(0, "select * from t where col in (").append(')');
try (PreparedStatement ps = connection.preparedStatement(sql.toString())) {
  for(int i = 0; i< values.length; i++) {
    ps.setInt(i + 1, values[i]);
  }
  ps.execute...
}

@kaifeur
Copy link
Author

kaifeur commented Jul 26, 2021

  1. It is working on 0.1.54 (don't know the format it uses). And I don't know the version until it works. Now I'm using 0.3.0 (0.3.1 and 0.3.1-patch are giving the same result).
  2. ClickHouse JDBC builds this SQL for me. Just check ru.yandex.clickhouse.util.ClickHouseValueFormatter#formatBytes.

@kaifeur
Copy link
Author

kaifeur commented Jul 26, 2021

Reproducing snippet:

CREATE TABLE t
(
    col_1 Array(Int8)
) ENGINE = Memory;

INSERT INTO t
VALUES ([1,2,3,4]);
String url = "jdbc:clickhouse://127.0.0.1:8123";
ClickHouseDataSource dataSource = new ClickHouseDataSource(url);
ClickHouseConnection connection = dataSource.getConnection();
PreparedStatement prepareStatement = connection.prepareStatement("SELECT * FROM t WHERE col_1 IN (?)");
prepareStatement.setObject(1, new byte[]{1,2,3,4});
ResultSet resultSet = prepareStatement.executeQuery();

@zhicwu zhicwu added bug and removed question labels Jul 26, 2021
@zhicwu zhicwu added this to the 0.3.2 Release milestone Jul 26, 2021
@zhicwu
Copy link
Contributor

zhicwu commented Jul 26, 2021

Thanks for the clarification. I added a unit test which can reproduce the issue even use ClickHouseArray.

This was introduced in commit 3be7a3c, but before I roll it back, I need some time to digest what that was for.

@zhicwu zhicwu added the module-jdbc JDBC driver label Oct 6, 2021
@zhicwu zhicwu linked a pull request Nov 29, 2021 that will close this issue
18 tasks
@zhicwu
Copy link
Contributor

zhicwu commented Dec 29, 2021

I didn't change code as it will be removed in 0.4.0. The issue should not exist in the new driver(com.clickhouse.jdbc.ClickHouseDriver).

@zhicwu zhicwu closed this as completed Dec 29, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants