impala-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Brown (JIRA)" <j...@apache.org>
Subject [jira] [Created] (IMPALA-5504) wrong results with LEFT JOIN, inline view, and COALESCE()
Date Tue, 13 Jun 2017 21:30:00 GMT
Michael Brown created IMPALA-5504:
-------------------------------------

             Summary: wrong results with LEFT JOIN, inline view, and COALESCE()
                 Key: IMPALA-5504
                 URL: https://issues.apache.org/jira/browse/IMPALA-5504
             Project: IMPALA
          Issue Type: Bug
          Components: Frontend
    Affects Versions: Impala 2.8.0, Impala 2.9.0
            Reporter: Michael Brown
            Priority: Blocker


*Summary*

This query

{noformat}
USE tpch;
SELECT
COUNT(t1.ps_suppkey)
FROM partsupp t1
LEFT JOIN  (
SELECT
COALESCE(t2.o_custkey, -1) AS coalesce_col
FROM orders t2
LEFT JOIN partsupp t3 ON (t2.o_shippriority) = (t3.ps_partkey)
) sq ON ((t1.ps_suppkey) = (sq.coalesce_col))
{noformat}

produces a different result compared to PostgreSQL. This occurs in both 2.8- and 2.9-based
builds, so it doesn't seem to be a recent regression.

The problem is the {{COALESCE()}} function in the inline view and its relationship to the
{{LEFT JOIN}} in the outer query.

*Analysis*

Because {{t2.o_custkey}} is a primary key column and does not contain {{NULL}} values:

{noformat}
[localhost:21000] > select count(o_custkey) from orders where o_custkey is null;
+------------------+
| count(o_custkey) |
+------------------+
| 0                |
+------------------+
Fetched 1 row(s) in 0.64s
[localhost:21000] >
{noformat}

This means I can remove the use of {{COALESCE(t2.o_custkey, -1)}} and just replace it with
{{t2.o_custkey}}:

{noformat}
SELECT
COUNT(t1.ps_suppkey) c
FROM partsupp t1
LEFT JOIN  (
SELECT
t2.o_custkey AS coalesce_col
FROM orders t2
LEFT JOIN partsupp t3 ON (t2.o_shippriority) = (t3.ps_partkey)
) sq ON ((t1.ps_suppkey) = (sq.coalesce_col))
ORDER BY c
{noformat}

When I do that, the results end up matching Postgres. This is my grounds for justifying this
as an Impala bug, not a Postgres bug: either with or without the COALESCE(), the results should
have been the same.

Consider the query profiles for the query without the COALESCE, that produces the correct
results:

{noformat}
Operator          #Hosts   Avg Time   Max Time    #Rows  Est. #Rows   Peak Mem  Est. Peak
Mem  Detail                        
-----------------------------------------------------------------------------------------------------------------------------
10:AGGREGATE           1  196.528us  196.528us        1           1   24.00 KB       10.00
MB  FINALIZE                      
09:EXCHANGE            1   38.678us   38.678us        2           1          0           
  0  UNPARTITIONED                 
05:AGGREGATE           2   68.671ms   75.556ms        2           1   50.89 KB       10.00
MB                                
04:HASH JOIN           2  460.980ms  472.610ms    8.23M     800.00K  114.07 MB        6.71
MB  RIGHT OUTER JOIN, PARTITIONED 
|--08:EXCHANGE         2   53.569ms   60.459ms  800.00K     800.00K          0           
  0  HASH((t1.ps_suppkey))         
|  00:SCAN HDFS        1   19.808ms   19.808ms  800.00K     800.00K   33.02 MB      176.00
MB  tpch.partsupp t1              
07:EXCHANGE            2   91.832ms  101.949ms    1.50M       1.50M          0           
  0  HASH(t2.o_custkey)            
03:HASH JOIN           2  206.506ms  228.767ms    1.50M       1.50M  178.05 MB        6.71
MB  LEFT OUTER JOIN, BROADCAST    
|--06:EXCHANGE         2   95.342ms   97.771ms  800.00K     800.00K          0           
  0  BROADCAST                     
|  02:SCAN HDFS        1   34.058ms   34.058ms  800.00K     800.00K   33.02 MB      176.00
MB  tpch.partsupp t3              
01:SCAN HDFS           2   93.905ms  101.139ms    1.50M       1.50M   33.25 MB      176.00
MB  tpch.orders t2   
{noformat}

Now compare this to the profile with the incorrect results:

{noformat}
Operator          #Hosts   Avg Time   Max Time    #Rows  Est. #Rows   Peak Mem  Est. Peak
Mem  Detail                         
------------------------------------------------------------------------------------------------------------------------------
10:AGGREGATE           1  198.204us  198.204us        1           1   24.00 KB       10.00
MB  FINALIZE                       
09:EXCHANGE            1   93.555us   93.555us        2           1          0           
  0  UNPARTITIONED                  
05:AGGREGATE           2    3.066ms    3.717ms        2           1    8.04 MB       10.00
MB                                 
04:HASH JOIN           2  140.679ms  236.316ms  800.00K     800.00K  114.06 MB        6.71
MB  RIGHT OUTER JOIN, PARTITIONED  
|--08:EXCHANGE         2   51.184ms   53.408ms  800.00K     800.00K          0           
  0  HASH((t1.ps_suppkey))          
|  00:SCAN HDFS        1    9.601ms    9.601ms  800.00K     800.00K   33.02 MB      176.00
MB  tpch.partsupp t1               
07:EXCHANGE            2   88.551ms  177.099ms    1.50M       1.50M          0           
  0  HASH(if(TupleIsNull(), NULL... 
03:HASH JOIN           2  216.025ms  241.128ms    1.50M       1.50M  178.05 MB        6.71
MB  LEFT OUTER JOIN, BROADCAST     
|--06:EXCHANGE         2   93.506ms  105.640ms  800.00K     800.00K          0           
  0  BROADCAST                      
|  02:SCAN HDFS        1  112.021ms  112.021ms  800.00K     800.00K   32.63 MB      176.00
MB  tpch.partsupp t3               
01:SCAN HDFS           2   50.246ms   86.962ms    1.50M       1.50M   33.25 MB      176.00
MB  tpch.orders t2   
{noformat}

In the second, incorrect results with COALESCE() case, we see that the {{04 HASH JOIN}} operator
processed 800K rows, whereas in the first, without COALESCE, it processed 8.23M. It turns
out the {{partsupp}} table has exactly 800k rows. So in the buggy case, somehow the rows coming
in from the {{orders}} side are not being processed by the JOIN.

Note this was originally found by the random query generator; this was the original query:

{noformat}
USE tpch;
SELECT
COALESCE(AVG(a4.o_custkey), 30.0083477187, (COALESCE(a4.o_custkey, a1.ps_partkey, a1.ps_suppkey))
* (-65.4342458142)) AS float_col,
COUNT(a1.ps_suppkey) AS int_col,
COALESCE(a4.o_custkey, a1.ps_partkey, a1.ps_suppkey) AS int_col_1
FROM partsupp a1
LEFT JOIN (
SELECT
a2.o_custkey,
COALESCE(COALESCE(a3.ps_availqty, a3.ps_partkey, a3.ps_suppkey), a3.ps_suppkey, COALESCE(a2.o_custkey,
a3.ps_availqty)) AS int_col,
a2.o_orderstatus,
COALESCE(a2.o_custkey, LEAST(COALESCE(a2.o_custkey, 825), COALESCE(COALESCE(a2.o_orderkey,
a3.ps_availqty, a2.o_orderkey), 538)), a3.ps_availqty) AS int_col_1,
COALESCE(a2.o_orderdate, a2.o_clerk) AS char_col
FROM orders a2
LEFT JOIN partsupp a3 ON (a2.o_shippriority) = (a3.ps_partkey)
WHERE
((a2.o_custkey) IS DISTINCT FROM (a3.ps_partkey)) OR ((a2.o_totalprice) IS NULL)
) a4 ON (False) OR ((a1.ps_suppkey) = (a4.int_col_1))
WHERE
((a4.int_col_1) IS NOT DISTINCT FROM (a1.ps_suppkey)) AND ((a4.int_col) NOT IN (a1.ps_partkey,
a1.ps_availqty))
GROUP BY
COALESCE(a4.o_custkey, a1.ps_partkey, a1.ps_suppkey)
{noformat}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message