Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-3454

ON DUPLICATE KEY construct doesn't work correctly when using lower case column names

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 4.13.0
    • Component/s: None
    • Labels:
      None

      Description

      See this test case for a repro:

      @Test
          public void testDeleteOnSingleLowerCaseVarcharColumn() throws Exception {
              Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
              Connection conn = DriverManager.getConnection(getUrl(), props);
              conn.setAutoCommit(false);
              String tableName = generateUniqueName();
              String ddl = " create table " + tableName + "(pk varchar primary key, \"counter1\" varchar, \"counter2\" smallint)";
              conn.createStatement().execute(ddl);
              String dml = "UPSERT INTO " + tableName + " VALUES('a','b') ON DUPLICATE KEY UPDATE \"counter1\" = null";
              conn.createStatement().execute(dml);
              conn.createStatement().execute(dml);
              conn.commit();
      
              ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName);
              assertTrue(rs.next());
              assertEquals("a",rs.getString(1));
              assertEquals(null,rs.getString(2));
              assertFalse(rs.next());
              
              dml = "UPSERT INTO " + tableName + " VALUES('a','b',0)";
              conn.createStatement().execute(dml);
              dml = "UPSERT INTO " + tableName + " VALUES('a','b', 0) ON DUPLICATE KEY UPDATE \"counter1\" = null, \"counter2\" = \"counter2\" + 1";
              conn.createStatement().execute(dml);
              dml = "UPSERT INTO " + tableName + " VALUES('a','b', 0) ON DUPLICATE KEY UPDATE \"counter1\" = 'c', \"counter2\" = \"counter2\" + 1";
              conn.createStatement().execute(dml);
              conn.commit();
      
              rs = conn.createStatement().executeQuery("SELECT * FROM " + tableName);
              assertTrue(rs.next());
              assertEquals("a",rs.getString(1));
              assertEquals("c",rs.getString(2));
              assertEquals(2,rs.getInt(3));
              assertFalse(rs.next());
      
              conn.close();
          }
      

      After changing the column names to upper case (or removing the quotes), the test passes.

      FYI, James R. Taylor

        Attachments

        1. PHOENIX-3454.patch
          5 kB
          James R. Taylor
        2. Screen Shot 2016-11-04 at 1.29.43 PM.png
          885 kB
          Samarth Jain

          Activity

            People

            • Assignee:
              jamestaylor James R. Taylor
              Reporter:
              samarthjain Samarth Jain
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: