[ https://issues.apache.org/jira/browse/HAWQ-1339?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Amy updated HAWQ-1339: ---------------------- 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. {code} 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) {code} The reproduction steps are: {code} 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; {code} was: Some explain on OLAP grouping query may encounter error "cache lookup failed for attribute 7 of relation 75036 (lsyscache.c:437)". ''' 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 '9 99. 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; ''' > Cache lookup failed after explain OLAP grouping query > ----------------------------------------------------- > > Key: HAWQ-1339 > URL: https://issues.apache.org/jira/browse/HAWQ-1339 > Project: Apache HAWQ > Issue Type: Bug > Components: Catalog > Reporter: Amy > Assignee: Amy > Fix For: 2.3.0.0-incubating > > Attachments: olap_setup.sql > > > 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. > {code} > 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) > {code} > The reproduction steps are: > {code} > 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; > {code} -- This message was sent by Atlassian JIRA (v6.3.15#6346)