Uploaded image for project: 'Pentaho Analysis - Mondrian'
  1. Pentaho Analysis - Mondrian
  2. MONDRIAN-1057

Match a specific string filtering in Analyzer generates the wrong regular expression in Oracle 10G

    Details

    • 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.

      Description

      Using sample data installed on an Oracle database; set a new existing datasource for Steel Wheels; once created add product code and price each to the canvas. Add a new filter using: Match a specific sttring: add the following strings: 10_19, 10_16, 12_33, 12_11 (for product codes: S10_1949, S10_1678, S12_3380, S12_1108) See Screenshot-1
      The results from the filters only returns 1 row for product code S12_1108 which was the last string added to the filter. See screenshot2.
      The attached log shows the following generated SQL:
      select "PRODUCTS"."PRODUCTCODE" as "c0" from "PRODUCTS" "PRODUCTS", "ORDERFACT" "ORDERFACT" where "ORDERFACT"."PRODUCTCODE" = "PRODUCTS"."PRODUCTCODE" group by "PRODUCTS"."PRODUCTCODE" having REGEXP_LIKE("PRODUCTS"."PRODUCTCODE", '.\Q10_19\E.|.\Q10_16\E.|.\Q12_33\E.|.12_11.', 'i') order by "PRODUCTS"."PRODUCTCODE" ASC NULLS LAST

      Running this SQL against the DB returns only 1 product code S12_1108; if I remove \Q and \E from the remaining 3 strings the SQL returns all 4 product codes.

      select "PRODUCTS"."PRODUCTCODE" as "c0" from "PRODUCTS" "PRODUCTS", "ORDERFACT" "ORDERFACT" where "ORDERFACT"."PRODUCTCODE" = "PRODUCTS"."PRODUCTCODE" group by "PRODUCTS"."PRODUCTCODE" having REGEXP_LIKE("PRODUCTS"."PRODUCTCODE", '.10_19.|.10_16.|.12_33.|.12_11.', 'i') order by "PRODUCTS"."PRODUCTCODE" ASC NULLS LAST

        Attachments

          Activity

            People

            • Assignee:
              ceverett Carter Everett (Inactive)
              Reporter:
              clopez Carlos Lopez
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: