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

valid SQL is throwing syntax error on jsonb datatypes #643

Closed
seanmmills opened this issue Sep 21, 2016 · 7 comments
Closed

valid SQL is throwing syntax error on jsonb datatypes #643

seanmmills opened this issue Sep 21, 2016 · 7 comments
Milestone

Comments

@seanmmills
Copy link

@seanmmills seanmmills commented Sep 21, 2016

The test case...

package org.postgresql.test.jdbc4;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertTrue;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;
import org.postgresql.test.TestUtil;
import org.postgresql.test.jdbc2.BaseTest4;

public class JsonbTest extends BaseTest4 {

  private Connection conn;

  public void setUp() throws Exception {
    super.setUp();
    conn = con;
    TestUtil.createTable(conn, "jsonbtest", "detail jsonb");
  }

  public void tearDown() throws SQLException {
    TestUtil.dropTable(conn, "jsonbtest");
    super.tearDown();
  }

  @Test
  public void testJsonb() throws SQLException {
    if (!TestUtil.haveMinimumServerVersion(conn, "9.4")) {
      return;
    }
    Statement stmt = conn.createStatement();
    stmt.executeUpdate("INSERT INTO jsonbtest (detail) VALUES ('{\"a\": 1}')");
    stmt.executeUpdate("INSERT INTO jsonbtest (detail) VALUES ('{\"b\": 1}')");
    stmt.executeUpdate("INSERT INTO jsonbtest (detail) VALUES ('{\"c\": 1}')");

    ResultSet rs = stmt.executeQuery("SELECT count(1) FROM jsonbtest");
    assertTrue(rs.next());
    assertEquals(3, rs.getInt(1));
    rs.close();

    rs = stmt.executeQuery("SELECT count(1) FROM jsonbtest WHERE detail ? 'a' = false;");
    assertTrue(rs.next());
    assertEquals(2, rs.getInt(1));
    rs.close();
    stmt.close();
  }
}

...returns...

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
  Position: 45
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2458)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2158)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:291)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:432)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:358)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:305)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:291)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:269)
    at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:236)
    at org.postgresql.test.jdbc4.JsonbTest.testJsonb(JsonbTest.java:53)
@davecramer
Copy link
Member

@davecramer davecramer commented Sep 21, 2016

Unfortunately you can't use

rs = stmt.executeQuery("SELECT count(1) FROM jsonbtest WHERE detail ?
'a' = false;");

IIRC you need to use 2 ?? change it to

rs = stmt.executeQuery("SELECT count(1) FROM jsonbtest WHERE

detail ?? 'a' = false;");

Dave Cramer

On 20 September 2016 at 20:56, seanmmills notifications@github.com wrote:

The test case...

package org.postgresql.test.jdbc4;
import static org.junit.Assert.assertEquals;import static org.junit.Assert.assertTrue;
import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;
import org.junit.Test;import org.postgresql.test.TestUtil;import org.postgresql.test.jdbc2.BaseTest4;
public class JsonbTest extends BaseTest4 {

private Connection conn;

public void setUp() throws Exception {
super.setUp();
conn = con;
TestUtil.createTable(conn, "jsonbtest", "detail jsonb");
}

public void tearDown() throws SQLException {
TestUtil.dropTable(conn, "jsonbtest");
super.tearDown();
}

@test
public void testJsonb() throws SQLException {
if (!TestUtil.haveMinimumServerVersion(conn, "9.4")) {
return;
}
Statement stmt = conn.createStatement();
stmt.executeUpdate("INSERT INTO jsonbtest (detail) VALUES ('{"a": 1}')");
stmt.executeUpdate("INSERT INTO jsonbtest (detail) VALUES ('{"b": 1}')");
stmt.executeUpdate("INSERT INTO jsonbtest (detail) VALUES ('{"c": 1}')");

ResultSet rs = stmt.executeQuery("SELECT count(1) FROM jsonbtest");
assertTrue(rs.next());
assertEquals(3, rs.getInt(1));
rs.close();

rs = stmt.executeQuery("SELECT count(1) FROM jsonbtest WHERE detail ? 'a' = false;");
assertTrue(rs.next());
assertEquals(2, rs.getInt(1));
rs.close();
stmt.close();

}
}

...returns...

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
Position: 45
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2458)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2158)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:291)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:432)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:358)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:305)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:291)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:269)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:236)
at org.postgresql.test.jdbc4.JsonbTest.testJsonb(JsonbTest.java:53)


You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
#643, or mute the thread
https://github.com/notifications/unsubscribe-auth/AAYz9jxAfHLdjc70i9MLTFYqHv03MTq_ks5qsIDYgaJpZM4KCS6A
.

@polobo
Copy link

@polobo polobo commented Sep 21, 2016

A bit more explanation. The "?" is a designated character in JDBC
(parameter placeholder) so attempting to use it as an operator fails.
Recent driver builds allow you to escape the question-mark's client-side
placeholder behavior and instead send it to the server for processing as an
operator. You do this via doubling.

Dave

On Tue, Sep 20, 2016 at 6:01 PM, Dave Cramer notifications@github.com
wrote:

Unfortunately you can't use

rs = stmt.executeQuery("SELECT count(1) FROM jsonbtest WHERE detail ?
'a' = false;");

IIRC you need to use 2 ?? change it to

@seanmmills
Copy link
Author

@seanmmills seanmmills commented Sep 21, 2016

This worked in driver versions prior to 9.4.10. According to statement, escaping is not required for Statements, however it is required for PreparedStatements.

@seanmmills
Copy link
Author

@seanmmills seanmmills commented Sep 21, 2016

Also, escaping doesn't fix the problem in 9.4.10+. We use flyway in our projects and existing SQL phases containing this syntax can't be changed without causing great pain.

@seanmmills seanmmills changed the title valid SQL is throwing syntax error on json/jsonb datatypes valid SQL is throwing syntax error on jsonb datatypes Sep 21, 2016
@scottmiller123
Copy link

@scottmiller123 scottmiller123 commented Sep 21, 2016

@seanmmills : nice work on the test case!

@vlsi
Copy link
Member

@vlsi vlsi commented Sep 21, 2016

stmt.executeQuery(String) should not require escaping of ?, so this is a bug around 232569c

vlsi added a commit to vlsi/pgjdbc that referenced this issue Sep 21, 2016
vlsi added a commit to vlsi/pgjdbc that referenced this issue Sep 21, 2016
@vlsi vlsi closed this in #644 Sep 21, 2016
vlsi added a commit that referenced this issue Sep 21, 2016
@vlsi vlsi added this to the 9.4.1212 milestone Sep 21, 2016
@seanmmills
Copy link
Author

@seanmmills seanmmills commented Sep 21, 2016

Great work! Thank you for the quick turn around!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

5 participants