Uploaded image for project: 'Red Hat Fuse'
  1. Red Hat Fuse
  2. ENTESB-16608

SQL query with ON CONFLICT is not valid anymore

    XMLWordPrintable

Details

    • Bug
    • Status: Done
    • Critical
    • Resolution: Done
    • fuse-7.9-GA
    • fuse-7.9-GA
    • Fuse Online

    Description

      After update SQL parser, this query doesn't work

      INSERT INTO todo(id, completed, task) VALUES (:#id, :#completed, :#task) ON CONFLICT (id) DO UPDATE SET completed=:#completed, task=:#task
      

      Parser error:

      net.sf.jsqlparser.JSQLParserException: Encountered unexpected token: "CONFLICT" <S_IDENTIFIER> at line 1, column 77. Was expecting: "DUPLICATE" . Unable to fetch and process metadata
      


      The query was working before ( on Fuse Online 7.8 or upstream version 1.12.0-20210427)

      When I update the query to

      INSERT INTO todo(id, completed, task) VALUES (:#id, :#completed, :#task) ON DUPLICATE KEY UPDATE completed=:#completed, task=:#task
      

      the query is valid but the integration doesn't work.

      org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "DUPLICATE"
      

      It looks that the first (old) query is working with PostgreSql and the second (new) query is working with MySql.
      Is there any unique solution for that query which is not DB vendor-specific? (which can be used with the new SQL parser and PostgreSql demoData sampledb)

      Attachments

        1. limit-export.zip
          3 kB
        2. log
          99 kB
        3. query2.png
          query2.png
          106 kB

        Issue Links

          Activity

            People

              zregvart-redhat Zoran Regvart
              mkralik@redhat.com Matej Kralik
              Matej Kralik Matej Kralik
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: