[ 
https://issues.apache.org/jira/browse/PHOENIX-4508?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Flavio Pompermaier updated PHOENIX-4508:
----------------------------------------
    Description: 
In my Phoenix tables I found that one query ens successfully while another one, 
logically equal, does not (unless that I don't apply some tuning to timeouts).

The 2 queries extract the same data but, while the first query terminates the 
second does not.
PS:  without the USE_SORT_MERGE_JOIN both queries weren't working

{code:sql}
SELECT /*+ USE_SORT_MERGE_JOIN */ COUNT(*) 
FROM PEOPLE ds JOIN MYTABLE l ON ds.PERSON_ID = l.LOCALID
WHERE l.EID IS NULL AND l.DSID = 'PEOPLE' AND l.HAS_CANDIDATES = FALSE;
{code}

+---------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
|                                                     PLAN                      
                                | EST_BYTES_READ  | EST_ROWS_READ  |  
EST_INFO_TS   |
+---------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
| SORT-MERGE-JOIN (INNER) TABLES                                                
                                | 14155777900     | 12077867       | 
1513754378759  |
|     CLIENT 42-CHUNK 6168903 ROWS 11324622221 BYTES PARALLEL 3-WAY FULL SCAN 
OVER PEOPLE                 | 14155777900     | 12077867       | 1513754378759  
|
|         SERVER FILTER BY FIRST KEY ONLY                                       
                                | 14155777900     | 12077867       | 
1513754378759  |
|     CLIENT MERGE SORT                                                         
                                | 14155777900     | 12077867       | 
1513754378759  |
| AND (SKIP MERGE)                                                              
                                | 14155777900     | 12077867       | 
1513754378759  |
|     CLIENT 15-CHUNK 5908964 ROWS 2831155679 BYTES PARALLEL 15-WAY RANGE SCAN 
OVER MYTABLE [0] - [2]  | 14155777900     | 12077867       | 1513754378759  |
|         SERVER FILTER BY (EID IS NULL AND DSID = 'PEOPLE' AND HAS_CANDIDATES 
= false)                   | 14155777900     | 12077867       | 1513754378759  |
|         SERVER SORTED BY [L.LOCALID]                                          
                                    | 14155777900     | 12077867       | 
1513754378759  |
|     CLIENT MERGE SORT                                                         
                                | 14155777900     | 12077867       | 
1513754378759  |
| CLIENT AGGREGATE INTO SINGLE ROW                                              
                                | 14155777900     | 12077867       | 
1513754378759  |
+---------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
10 rows selected (0.041 seconds)


{code:sql}
SELECT /*+ USE_SORT_MERGE_JOIN */ COUNT(*) 
FROM (SELECT LOCALID FROM MYTABLE
WHERE EID IS NULL AND DSID = 'PEOPLE' AND HAS_CANDIDATES = FALSE) l JOIN PEOPLE 
 ds ON ds.PERSON_ID = l.LOCALID;
{code}


+--------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
|                                                     PLAN                      
                               | EST_BYTES_READ  | EST_ROWS_READ  |  
EST_INFO_TS   |
+--------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
| SORT-MERGE-JOIN (INNER) TABLES                                                
                               | 14155777900     | 12077867       | 
1513754378759  |
|     CLIENT 15-CHUNK 5908964 ROWS 2831155679 BYTES PARALLEL 3-WAY RANGE SCAN 
OVER MYTABLE [0] - [2]  | 14155777900     | 12077867       | 1513754378759  |
|         SERVER FILTER BY (EID IS NULL AND DSID = 'PEOPLE' AND HAS_CANDIDATES 
= false)                  | 14155777900     | 12077867       | 1513754378759  |
|     CLIENT MERGE SORT                                                         
                               | 14155777900     | 12077867       | 
1513754378759  |
| AND (SKIP MERGE)                                                              
                               | 14155777900     | 12077867       | 
1513754378759  |
|     CLIENT 42-CHUNK 6168903 ROWS 11324622221 BYTES PARALLEL 42-WAY FULL SCAN 
OVER PEOPLE               | 14155777900     | 12077867       | 1513754378759  |
|         SERVER FILTER BY FIRST KEY ONLY                                       
                               | 14155777900     | 12077867       | 
1513754378759  |
|         SERVER SORTED BY [DS.PERSON_ID]                                       
                             | 14155777900     | 12077867       | 1513754378759 
 |
|     CLIENT MERGE SORT                                                         
                               | 14155777900     | 12077867       | 
1513754378759  |
| CLIENT AGGREGATE INTO SINGLE ROW                                              
                               | 14155777900     | 12077867       | 
1513754378759  |
+--------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+


  was:
In my Phoenix tables I found that one query ens successfully while another one, 
logically equal, does not (unless that I don't apply some tuning to timeouts).

The 2 queries extract the same data but, while the first query terminates the 
second does not.
PS:  without the USE_SORT_MERGE_JOIN both queries weren't working

```
SELECT /*+ USE_SORT_MERGE_JOIN */ COUNT(*) 
FROM PEOPLE ds JOIN MYTABLE l ON ds.PERSON_ID = l.LOCALID
WHERE l.EID IS NULL AND l.DSID = 'PEOPLE' AND l.HAS_CANDIDATES = FALSE;
```

```
+---------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
|                                                     PLAN                      
                                | EST_BYTES_READ  | EST_ROWS_READ  |  
EST_INFO_TS   |
+---------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
| SORT-MERGE-JOIN (INNER) TABLES                                                
                                | 14155777900     | 12077867       | 
1513754378759  |
|     CLIENT 42-CHUNK 6168903 ROWS 11324622221 BYTES PARALLEL 3-WAY FULL SCAN 
OVER PEOPLE                 | 14155777900     | 12077867       | 1513754378759  
|
|         SERVER FILTER BY FIRST KEY ONLY                                       
                                | 14155777900     | 12077867       | 
1513754378759  |
|     CLIENT MERGE SORT                                                         
                                | 14155777900     | 12077867       | 
1513754378759  |
| AND (SKIP MERGE)                                                              
                                | 14155777900     | 12077867       | 
1513754378759  |
|     CLIENT 15-CHUNK 5908964 ROWS 2831155679 BYTES PARALLEL 15-WAY RANGE SCAN 
OVER MYTABLE [0] - [2]  | 14155777900     | 12077867       | 1513754378759  |
|         SERVER FILTER BY (EID IS NULL AND DSID = 'PEOPLE' AND HAS_CANDIDATES 
= false)                   | 14155777900     | 12077867       | 1513754378759  |
|         SERVER SORTED BY [L.LOCALID]                                          
                                    | 14155777900     | 12077867       | 
1513754378759  |
|     CLIENT MERGE SORT                                                         
                                | 14155777900     | 12077867       | 
1513754378759  |
| CLIENT AGGREGATE INTO SINGLE ROW                                              
                                | 14155777900     | 12077867       | 
1513754378759  |
+---------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
10 rows selected (0.041 seconds)
```

```
SELECT /*+ USE_SORT_MERGE_JOIN */ COUNT(*) 
FROM (SELECT LOCALID FROM MYTABLE
WHERE EID IS NULL AND DSID = 'PEOPLE' AND HAS_CANDIDATES = FALSE) l JOIN PEOPLE 
 ds ON ds.PERSON_ID = l.LOCALID;
```
```
+--------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
|                                                     PLAN                      
                               | EST_BYTES_READ  | EST_ROWS_READ  |  
EST_INFO_TS   |
+--------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
| SORT-MERGE-JOIN (INNER) TABLES                                                
                               | 14155777900     | 12077867       | 
1513754378759  |
|     CLIENT 15-CHUNK 5908964 ROWS 2831155679 BYTES PARALLEL 3-WAY RANGE SCAN 
OVER MYTABLE [0] - [2]  | 14155777900     | 12077867       | 1513754378759  |
|         SERVER FILTER BY (EID IS NULL AND DSID = 'PEOPLE' AND HAS_CANDIDATES 
= false)                  | 14155777900     | 12077867       | 1513754378759  |
|     CLIENT MERGE SORT                                                         
                               | 14155777900     | 12077867       | 
1513754378759  |
| AND (SKIP MERGE)                                                              
                               | 14155777900     | 12077867       | 
1513754378759  |
|     CLIENT 42-CHUNK 6168903 ROWS 11324622221 BYTES PARALLEL 42-WAY FULL SCAN 
OVER PEOPLE               | 14155777900     | 12077867       | 1513754378759  |
|         SERVER FILTER BY FIRST KEY ONLY                                       
                               | 14155777900     | 12077867       | 
1513754378759  |
|         SERVER SORTED BY [DS.PERSON_ID]                                       
                             | 14155777900     | 12077867       | 1513754378759 
 |
|     CLIENT MERGE SORT                                                         
                               | 14155777900     | 12077867       | 
1513754378759  |
| CLIENT AGGREGATE INTO SINGLE ROW                                              
                               | 14155777900     | 12077867       | 
1513754378759  |
+--------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
```


> Wrong query plan generation
> ---------------------------
>
>                 Key: PHOENIX-4508
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-4508
>             Project: Phoenix
>          Issue Type: Bug
>    Affects Versions: 4.13.2-cdh5.11.2
>            Reporter: Flavio Pompermaier
>              Labels: planner, query
>
> In my Phoenix tables I found that one query ens successfully while another 
> one, logically equal, does not (unless that I don't apply some tuning to 
> timeouts).
> The 2 queries extract the same data but, while the first query terminates the 
> second does not.
> PS:  without the USE_SORT_MERGE_JOIN both queries weren't working
> {code:sql}
> SELECT /*+ USE_SORT_MERGE_JOIN */ COUNT(*) 
> FROM PEOPLE ds JOIN MYTABLE l ON ds.PERSON_ID = l.LOCALID
> WHERE l.EID IS NULL AND l.DSID = 'PEOPLE' AND l.HAS_CANDIDATES = FALSE;
> {code}
> +---------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
> |                                                     PLAN                    
>                                   | EST_BYTES_READ  | EST_ROWS_READ  |  
> EST_INFO_TS   |
> +---------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
> | SORT-MERGE-JOIN (INNER) TABLES                                              
>                                   | 14155777900     | 12077867       | 
> 1513754378759  |
> |     CLIENT 42-CHUNK 6168903 ROWS 11324622221 BYTES PARALLEL 3-WAY FULL SCAN 
> OVER PEOPLE                 | 14155777900     | 12077867       | 
> 1513754378759  |
> |         SERVER FILTER BY FIRST KEY ONLY                                     
>                                   | 14155777900     | 12077867       | 
> 1513754378759  |
> |     CLIENT MERGE SORT                                                       
>                                   | 14155777900     | 12077867       | 
> 1513754378759  |
> | AND (SKIP MERGE)                                                            
>                                   | 14155777900     | 12077867       | 
> 1513754378759  |
> |     CLIENT 15-CHUNK 5908964 ROWS 2831155679 BYTES PARALLEL 15-WAY RANGE 
> SCAN OVER MYTABLE [0] - [2]  | 14155777900     | 12077867       | 
> 1513754378759  |
> |         SERVER FILTER BY (EID IS NULL AND DSID = 'PEOPLE' AND 
> HAS_CANDIDATES = false)                   | 14155777900     | 12077867       
> | 1513754378759  |
> |         SERVER SORTED BY [L.LOCALID]                                        
>                                       | 14155777900     | 12077867       | 
> 1513754378759  |
> |     CLIENT MERGE SORT                                                       
>                                   | 14155777900     | 12077867       | 
> 1513754378759  |
> | CLIENT AGGREGATE INTO SINGLE ROW                                            
>                                   | 14155777900     | 12077867       | 
> 1513754378759  |
> +---------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
> 10 rows selected (0.041 seconds)
> {code:sql}
> SELECT /*+ USE_SORT_MERGE_JOIN */ COUNT(*) 
> FROM (SELECT LOCALID FROM MYTABLE
> WHERE EID IS NULL AND DSID = 'PEOPLE' AND HAS_CANDIDATES = FALSE) l JOIN 
> PEOPLE  ds ON ds.PERSON_ID = l.LOCALID;
> {code}
> +--------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
> |                                                     PLAN                    
>                                  | EST_BYTES_READ  | EST_ROWS_READ  |  
> EST_INFO_TS   |
> +--------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
> | SORT-MERGE-JOIN (INNER) TABLES                                              
>                                  | 14155777900     | 12077867       | 
> 1513754378759  |
> |     CLIENT 15-CHUNK 5908964 ROWS 2831155679 BYTES PARALLEL 3-WAY RANGE SCAN 
> OVER MYTABLE [0] - [2]  | 14155777900     | 12077867       | 1513754378759  |
> |         SERVER FILTER BY (EID IS NULL AND DSID = 'PEOPLE' AND 
> HAS_CANDIDATES = false)                  | 14155777900     | 12077867       | 
> 1513754378759  |
> |     CLIENT MERGE SORT                                                       
>                                  | 14155777900     | 12077867       | 
> 1513754378759  |
> | AND (SKIP MERGE)                                                            
>                                  | 14155777900     | 12077867       | 
> 1513754378759  |
> |     CLIENT 42-CHUNK 6168903 ROWS 11324622221 BYTES PARALLEL 42-WAY FULL 
> SCAN OVER PEOPLE               | 14155777900     | 12077867       | 
> 1513754378759  |
> |         SERVER FILTER BY FIRST KEY ONLY                                     
>                                  | 14155777900     | 12077867       | 
> 1513754378759  |
> |         SERVER SORTED BY [DS.PERSON_ID]                                     
>                                | 14155777900     | 12077867       | 
> 1513754378759  |
> |     CLIENT MERGE SORT                                                       
>                                  | 14155777900     | 12077867       | 
> 1513754378759  |
> | CLIENT AGGREGATE INTO SINGLE ROW                                            
>                                  | 14155777900     | 12077867       | 
> 1513754378759  |
> +--------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+



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

Reply via email to