Uploaded image for project: 'Apache HAWQ'
  1. Apache HAWQ
  2. HAWQ-1339

Cache lookup failed after explain OLAP grouping query

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: backlog
    • Component/s: Catalog
    • Labels:
      None

      Description

      Some OLAP grouping query may error out with "division by zero", and when do query explain, notice of "cache lookup failed for attribute 7 of relation 75036 (lsyscache.c:437)" occurred.

      postgres=# SELECT sale.vn,sale.cn,sale.dt,GROUPING(sale.vn), TO_CHAR(COALESCE(MAX(DISTINCT floor(sale.vn+sale.qty)),0),'99999999.9999999'),TO_CHAR(COALESCE(VAR_SAMP(floor(sale.pn/sale.prc)),0),'99999999.9999999'),TO_CHAR(COALESCE(COUNT(floor(sale.qty+sale.prc)),0),'99999999.9999999')
      postgres-# FROM sale,customer,vendor
      postgres-# WHERE sale.cn=customer.cn AND sale.vn=vendor.vn
      postgres-# GROUP BY ROLLUP((sale.prc),(sale.vn,sale.vn),(sale.pn,sale.pn),(sale.dt),(sale.qty,sale.vn,sale.qty)),ROLLUP((sale.pn),(sale.vn,sale.pn),(sale.qty)),(),sale.cn HAVING COALESCE(VAR_POP(sale.cn),0) >= 45.5839785564113;
      ERROR:  division by zero  (seg0 localhost:40000 pid=25205)
      postgres=#
      postgres=# explain SELECT sale.vn,sale.cn,sale.dt,GROUPING(sale.vn), TO_CHAR(COALESCE(MAX(DISTINCT floor(sale.vn+sale.qty)),0),'99999999.9999999'),TO_CHAR(COALESCE(VAR_SAMP(floor(sale.pn/sale.prc)),0),'99999999.9999999'),TO_CHAR(COALESCE(COUNT(floor(sale.qty+sale.prc)),0),'99999999.9999999')
      FROM sale,customer,vendor
      WHERE sale.cn=customer.cn AND sale.vn=vendor.vn
      GROUP BY ROLLUP((sale.prc),(sale.vn,sale.vn),(sale.pn,sale.pn),(sale.dt),(sale.qty,sale.vn,sale.qty)),ROLLUP((sale.pn),(sale.vn,sale.pn),(sale.qty)),(),sale.cn HAVING COALESCE(VAR_POP(sale.cn),0) >= 45.5839785564113;
      NOTICE:  cache lookup failed for attribute 7 of relation 75036 (lsyscache.c:437)
      

      The reproduction steps are:

      Step 1: Prepare schema and data using attached olap_setup.sql
      
      Step 2: Run below OLAP grouping query
      -- OLAP query involving MAX() function
      SELECT sale.vn,sale.cn,sale.dt,GROUPING(sale.vn), TO_CHAR(COALESCE(MAX(DISTINCT floor(sale.vn+sale.qty)),0),'99999999.9999999'),TO_CHAR(COALESCE(VAR_SAMP(floor(sale.pn/sale.prc)),0),'99999999.9999999'),TO_CHAR(COALESCE(COUNT(floor(sale.qty+sale.prc)),0),'99999999.9999999')
      FROM sale,customer,vendor
      WHERE sale.cn=customer.cn AND sale.vn=vendor.vn
      GROUP BY ROLLUP((sale.prc),(sale.vn,sale.vn),(sale.pn,sale.pn),(sale.dt),(sale.qty,sale.vn,sale.qty)),ROLLUP((sale.pn),(sale.vn,sale.pn),(sale.qty)),(),sale.cn HAVING COALESCE(VAR_POP(sale.cn),0) >= 45.5839785564113;
      
      explain SELECT sale.vn,sale.cn,sale.dt,GROUPING(sale.vn), TO_CHAR(COALESCE(MAX(DISTINCT floor(sale.vn+sale.qty)),0),'99999999.9999999'),TO_CHAR(COALESCE(VAR_SAMP(floor(sale.pn/sale.prc)),0),'99999999.9999999'),TO_CHAR(COALESCE(COUNT(floor(sale.qty+sale.prc)),0),'99999999.9999999')
      FROM sale,customer,vendor
      WHERE sale.cn=customer.cn AND sale.vn=vendor.vn
      GROUP BY ROLLUP((sale.prc),(sale.vn,sale.vn),(sale.pn,sale.pn),(sale.dt),(sale.qty,sale.vn,sale.qty)),ROLLUP((sale.pn),(sale.vn,sale.pn),(sale.qty)),(),sale.cn HAVING COALESCE(VAR_POP(sale.cn),0) >= 45.5839785564113;
      

        Attachments

        1. olap_setup.sql
          3 kB
          Amy Bai

          Activity

            People

            • Assignee:
              rlei Radar Da Lei
              Reporter:
              abai Amy Bai
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: