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

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

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • 11.1
    • 11.0.1
    • Transformations
    • 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> ...
      "(" ...
      "+" ...
      "-" ...
      "?" ...
      "$" ...

      Attachments

        Issue Links

          Activity

            People

              blafond Barry LaFond
              rhn-support-dsteigner Deborah Steigner (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: