Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1692

JDBC UPDATE does not use correct schema name

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 1.12.0, 1.11.0
    • Fix Version/s: 1.12.0
    • Component/s: jdbc-adapter
    • Labels:
      None

      Description

      When using a model which has a different Calcite schema name than the DB schema name, the queries generated for `UPDATE` statements are incorrect (use the calcite schema name rather than the DB schema name). This also affects `INSERT` and probably `DELETE` too (haven't tested delete specifically). `SELECT` behaves correctly.

      Example:

      {
        "version": "1.0",
        "defaultSchema": "doesntmatter",
        "schemas": [
          {
            "name": "virtschema",
            "type": "custom",
            "factory": "org.apache.calcite.adapter.jdbc.JdbcSchema$Factory",
            "operand": {
              "jdbcDriver": "org.postgresql.Driver",
              "jdbcUrl": "jdbc:postgresql://localhost:5432/my-database",
              "jdbcUser": "my-username",
              "jdbcPassword": "my-password",
              "jdbcSchema": "hr"
            }
          }
        ]
      }
      
      import org.apache.calcite.jdbc.CalciteConnection;
      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.ResultSet;
      import java.sql.Statement;
      import java.util.Properties;
      
      public class Main {
      	public static void main(String[] argv) throws Exception {
      		System.out.println("Setup");
      
      		Class.forName("org.apache.calcite.jdbc.Driver");
      
      		Class.forName(org.apache.calcite.jdbc.Driver.class.getName());
      		Properties info = new Properties();
      		info.setProperty("lex", "JAVA");
      		info.setProperty("model", "src/main/resources/model.json");
      		Connection calConnection = DriverManager.getConnection("jdbc:calcite:", info);
      		CalciteConnection calciteConnection = calConnection.unwrap(CalciteConnection.class);
      
      		Statement statement = calciteConnection.createStatement();
      		ResultSet results = statement.executeQuery("SELECT deptno FROM virtschema.emps"); // Correctly converts to hr.emps
      		results.next();
      		System.out.println("Data: " + results.getInt(1));
      		results.close();
      
      		boolean success = statement.execute("UPDATE virtschema.emps SET deptno=7"); // Incorrectly leaves as virtschema.emps
      		System.out.println("Success: " + success);
      
      		statement.close();
      		calConnection.close();
      		System.out.println("Closed");
      	}
      }
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                devans_pivotal David Evans
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: