Uploaded image for project: 'Pentaho Metadata'
  1. Pentaho Metadata
  2. PMD-936

Incorrect/Invalid SQL Generated using Metadata Editor (4.8.x - GA)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Severity: High
    • Resolution: Fixed
    • Affects Version/s: 4.8.0 GA (4.8.0 GA Suite Release)
    • Fix Version/s: 7.1.0 GA
    • Component/s: None
    • Labels:
    • 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:
      Tatooine (Maint)
    • Steps to Reproduce:
      Hide

      1. Export ORDERFACT and ORDERDETAILS table and data from steelwheels sampledata into Oracle DB
      2. Import this two tables in Metadata editor.
      3. Edit the ORDERDETAILS table and filed PRICEEACH, on this filed define the default aggregation and option aggregation, refer the image1.png
      4. Now edit the ORDERFACT table on the ORDERNUMBER filed and define the default aggregation and option aggregation, refer the image2.png
      5. Define a complex Join between ORDERFACT and ORDERDETAILS, refer the image3.png
      6. Now Using the Metadata Editor build the selection using ORDERNUMBER and STATUS from ORDERFACT and PRICEEACH from the ORDERDETAILS. Refer the screenshot image4.png
      7. Click on the SQL see the aggregation count is part of the where clause.

      Show
      1. Export ORDERFACT and ORDERDETAILS table and data from steelwheels sampledata into Oracle DB 2. Import this two tables in Metadata editor. 3. Edit the ORDERDETAILS table and filed PRICEEACH, on this filed define the default aggregation and option aggregation, refer the image1.png 4. Now edit the ORDERFACT table on the ORDERNUMBER filed and define the default aggregation and option aggregation, refer the image2.png 5. Define a complex Join between ORDERFACT and ORDERDETAILS, refer the image3.png 6. Now Using the Metadata Editor build the selection using ORDERNUMBER and STATUS from ORDERFACT and PRICEEACH from the ORDERDETAILS. Refer the screenshot image4.png 7. Click on the SQL see the aggregation count is part of the where clause.

      Description

      When we use an aggregation function in the Select Clause on a column which is also used for defining the join between two(2) tables. We have noticed that the aggregation function is also used in the "Where" Clause.

      Below is the Invalid query generated (Notice the count Distinct) :

      SELECT
      COUNT(DISTINCT BT_ORDERFACT_ORDERFACT.ORDERNUMBER) AS COL0
      ,SUM(BT_ORDERDETAILS_ORDERDETAILS.PRICEEACH) AS COL1
      ,BT_ORDERFACT_ORDERFACT.STATUS AS COL2
      FROM
      ORDERDETAILS BT_ORDERDETAILS_ORDERDETAILS
      ,ORDERFACT BT_ORDERFACT_ORDERFACT
      WHERE
      ( ( COUNT(DISTINCT BT_ORDERFACT_ORDERFACT.ORDERNUMBER) = BT_ORDERDETAILS_ORDERDETAILS.ORDERNUMBER ) AND ( BT_ORDERFACT_ORDERFACT.PRODUCTCODE = BT_ORDERDETAILS_ORDERDETAILS.PRODUCTCODE ) )
      GROUP BY
      BT_ORDERFACT_ORDERFACT.STATUS

      Steps to reproduce the issue:

      Reporting Database : Oracle

        Attachments

        1. image1.png
          image1.png
          85 kB
        2. image2.png
          image2.png
          92 kB
        3. image3.png
          image3.png
          36 kB
        4. image4.png
          image4.png
          131 kB
        5. PMD-936.7z
          63 kB

          Activity

            People

            Assignee:
            Yury_Hetman Yury Hetman (Inactive)
            Reporter:
            gboranna Gangadhara Boranna (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: