[
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)
