Uploaded image for project: 'Teiid Designer'
  1. Teiid Designer
  2. TEIIDDES-3071

SQL transformation editor rewrites TRIM() function incorrectly most of the time

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 11.0.1
    • Fix Version/s: 11.1
    • Component/s: Transformations
    • Labels:
      None

      Description

      Paste the below SQL in the transformation editor to create a view table:

      SELECT
      trim(INTKEY),trim(STRINGKEY),trim(INTNUM),trim(STRINGNUM),trim(FLOATNUM),trim(LONGNUM),trim(DOUBLENUM),trim(BYTENUM),trim(DATEVALUE),trim(TIMESTAMPVALUE), trim(BOOLEANVALUE),trim(CHARVALUE),trim(SHORTVALUE),trim(BIGINTEGERVALUE),trim(BIGDECIMALVALUE),trim(OBJECTVALUE)
      FROM
      bqtora.LARGEB

      Once you save it, it gets rewritten with the defaults, but it leaves the column name out most of the time:
      SELECT
      trim(' ' FROM ), trim(' ' FROM bqtora.LARGEB.STRINGKEY), trim(' ' FROM ), trim(' ' FROM bqtora.LARGEB.STRINGNUM), trim(' ' FROM ), trim(' ' FROM ), trim(' ' FROM ), trim(' ' FROM ), trim(' ' FROM ), trim(' ' FROM ), trim(' ' FROM ), trim(' ' FROM ), trim(' ' FROM ), trim(' ' FROM ), trim(' ' FROM ), trim(' ' FROM bqtora.LARGEB.OBJECTVALUE)
      FROM
      bqtora.LARGEB

      So obviously you receive a validation parsing error:
      ERROR: Error in parsing of sql - Encountered " ")" ") "" at line 2, column 31.
      Was expecting one of:
      "char" ...
      "date" ...
      "time" ...
      "timestamp" ...
      "cast" ...
      "convert" ...
      "any" ...
      "array_agg" ...
      "case" ...
      "exists" ...
      "false" ...
      "hour" ...
      "insert" ...
      "left" ...
      "minute" ...
      "month" ...
      "not" ...
      "null" ...
      "right" ...
      "second" ...
      "some" ...
      "translate" ...
      "true" ...
      "unknown" ...
      "user" ...
      "year" ...
      "xmlagg" ...
      "xmlcast" ...
      "xmlconcat" ...
      "xmlcomment" ...
      "xmlelement" ...
      "xmlexists" ...
      "xmlforest" ...
      "xmlparse" ...
      "xmlpi" ...
      "xmlquery" ...
      "xmlserialize" ...
      "xmltext" ...
      "instead" ...
      "enabled" ...
      "disabled" ...
      "view" ...
      "key" ...
      "serial" ...
      "textagg" ...
      "count" ...
      "row_number" ...
      "rank" ...
      "dense_rank" ...
      "sum" ...
      "avg" ...
      "min" ...
      "max" ...
      "every" ...
      "stddev_pop" ...
      "stddev_samp" ...
      "var_samp" ...
      "var_pop" ...
      "document" ...
      "content" ...
      "trim" ...
      "empty" ...
      "ordinality" ...
      "path" ...
      "first" ...
      "last" ...
      "next" ...
      "substring" ...
      "extract" ...
      "to_chars" ...
      "to_bytes" ...
      "timestampadd" ...
      "timestampdiff" ...
      "querystring" ...
      "namespace" ...
      "result" ...
      "index" ...
      "accesspattern" ...
      "auto_increment" ...
      "wellformed" ...
      "sql_tsi_frac_second" ...
      "sql_tsi_second" ...
      "sql_tsi_minute" ...
      "sql_tsi_hour" ...
      "sql_tsi_day" ...
      "sql_tsi_week" ...
      "sql_tsi_month" ...
      "sql_tsi_quarter" ...
      "sql_tsi_year" ...
      "texttable" ...
      "arraytable" ...
      "selector" ...
      "skip" ...
      "width" ...
      "passing" ...
      "name" ...
      "encoding" ...
      "columns" ...
      "delimiter" ...
      "quote" ...
      "header" ...
      "nulls" ...
      "objecttable" ...
      "version" ...
      "including" ...
      "excluding" ...
      "xmldeclaration" ...
      "variadic" ...
      "exception" ...
      "raise" ...
      "chain" ...
      "jsonarray_agg" ...
      "jsonobject" ...
      "preserve" ...
      <ID> ...
      <ESCAPEDFUNCTION> ...
      <ESCAPEDTYPE> ...
      <DECIMALVAL> ...
      <FLOATVAL> ...
      <STRINGVAL> ...
      <UNSIGNEDINTEGER> ...
      <BINARYSTRINGVAL> ...
      "(" ...
      "+" ...
      "-" ...
      "?" ...
      "$" ...

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  blafond Barry LaFond
                  Reporter:
                  dsteigne Deborah Steigner
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  3 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: