Uploaded image for project: 'Pentaho Data Integration - Kettle'
  1. Pentaho Data Integration - Kettle
  2. PDI-12123

Field quoting of reserved database words can cause SQL failures

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Severity: High
    • Resolution: Fixed
    • Affects Version/s: 5.0.0 GA (5.0.0 GA Suite Release)
    • Fix Version/s: 6.1.0 GA
    • Component/s: Database
    • Story Points:
      1
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.
    • Sprint Team:
      Maintenance

      Description

      The easiest way to reproduce this is by creating a table in a database that contains reserved words as column names.

      PostgreSQL Example:

      CREATE TABLE reserverdwords
      (
      id integer,
      "select" character varying(20),
      "From" character varying(20)
      )

      • First thing to note is that you must quote reserved words to use it as a column name, so case is important.

      Next, to demonstrate one problem that this can introduce, create a new Transformation.
      Add the table input step to the canvas. Connect it to the database that you created the reservedwords table in.
      Press the "Get SQL select statement" button.
      Choose the reservedwords table.
      When prompted, answer YES to include field names.

      Preview the query, notice it fails to execute.

      The problem is that the reserved words are quoted, but the case of the From field is not correct.

      Other scenarios exist that relate to this problem as well. One is an inconsistency when generating SQL for a table output step from source fields that have reserved words.
      Create a Transformation with a Data Grid and a TableOutputStep (connect to postgres).
      Add fields in the data grid for FROM and FROM2.
      in the Table output Step, generate the SQL for the fields coming from the data grid.
      Notice that the quoted field "from" is used and FROM2 is left unquoted and all caps.

      It is strange that when quoting a field, the case is modified. However, the field that wasn't quoted, the case was left intact. There isn't an error at this point, but the behavior is odd.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              aliaksandr Aliaksandr Bialkevich (Inactive)
              Reporter:
              rfellows Rob Fellows (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - 0h
                  0h
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 4h
                  4h