subject:"\[jira\] \[Commented\] \(PHOENIX\-4508\) Wrong query plan generation"

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

2018-01-07 Thread James Taylor (JIRA)


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

James Taylor commented on PHOENIX-4508:
---

+1. Thanks [~maryannxue]! Please commit to master, 5.x, 4.x (including cdh), 
and 4.13 CDH branch.

> 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
>Reporter: Flavio Pompermaier
>Assignee: Maryann Xue
>  Labels: planner, query
> Attachments: PHOENIX-4508.patch
>
>
> 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
> 
> h2. First query
> {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 1132461 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)
> 
> h2. Second query
> {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   | 
>

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

2018-01-07 Thread Hadoop QA (JIRA)


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

Hadoop QA commented on PHOENIX-4508:


{color:red}-1 overall{color}.  Here are the results of testing the latest 
attachment 
  http://issues.apache.org/jira/secure/attachment/12904968/PHOENIX-4508.patch
  against master branch at commit 2136b002c37db478ffea11233f9ebb80276d2594.
  ATTACHMENT ID: 12904968

{color:green}+1 @author{color}.  The patch does not contain any @author 
tags.

{color:red}-1 tests included{color}.  The patch doesn't appear to include 
any new or modified tests.
Please justify why no new tests are needed for this 
patch.
Also please list what manual steps were performed to 
verify this patch.

{color:green}+1 javac{color}.  The applied patch does not increase the 
total number of javac compiler warnings.

{color:red}-1 release audit{color}.  The applied patch generated 1 release 
audit warnings (more than the master's current 0 warnings).

{color:green}+1 lineLengths{color}.  The patch does not introduce lines 
longer than 100

{color:green}+1 core tests{color}.  The patch passed unit tests in .

Test results: 
https://builds.apache.org/job/PreCommit-PHOENIX-Build/1704//testReport/
Release audit warnings: 
https://builds.apache.org/job/PreCommit-PHOENIX-Build/1704//artifact/patchprocess/patchReleaseAuditWarnings.txt
Console output: 
https://builds.apache.org/job/PreCommit-PHOENIX-Build/1704//console

This message is automatically generated.

> 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
>Reporter: Flavio Pompermaier
>Assignee: Maryann Xue
>  Labels: planner, query
> Attachments: PHOENIX-4508.patch
>
>
> 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
> 
> h2. First query
> {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 1132461 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  |
>

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

2018-01-06 Thread Maryann Xue (JIRA)


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

Maryann Xue commented on PHOENIX-4508:
--

Thanks to the DDLs you have provided, [~f.pompermaier], I am able to reproduce 
the problem now. I have identified that the issue is related to salted tables 
(SALT_BUCKETS=3), and without SALT_BUCKETS here it would be fine.

> 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
>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
> 
> h2. First query
> {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 1132461 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)
> 
> h2. Second query
> {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)

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

2018-01-06 Thread Flavio Pompermaier (JIRA)


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

Flavio Pompermaier commented on PHOENIX-4508:
-

Sorry, EID is not part of the PK because it can be null...the two statements 
are:

{code:sql}
CREATE TABLE IF NOT EXISTS MYTABLE (
LOCALID VARCHAR NOT NULL,
DSID VARCHAR(255) NOT NULL, 
EID CHAR(40),
ENTITY_TYPE CHAR(40),
HAS_CANDIDATES BOOLEAN,
MATCHING_REASON VARBINARY,
TO_FIX BOOLEAN
CONSTRAINT PK_MYTABLE PRIMARY KEY (LOCALID,DSID)) SALT_BUCKETS = 3
{code}

{code:sql}
CREATE TABLE IF NOT EXISTS PEOPLE (
PERSON_ID VARCHAR NOT NULL, CF_PIVA_VALID BOOLEAN, VALID BOOLEAN, FORMALITA 
BIGINT, FOTO BIGINT, VEICOLO BIGINT, TARGA VARCHAR, SERIETARGA INTEGER, 
ID_NASCITA_NAZIONE VARCHAR, ID_NASCITA_IT_PROVINCIA VARCHAR, 
ID_NASCITA_IT_COMUNE VARCHAR, ID_RES_NAZIONE VARCHAR, ID_RES_IT_PROVINCIA 
VARCHAR, ID_RES_IT_COMUNE VARCHAR, ID_RES_NAZIONE_LEGALE VARCHAR, 
ID_RES_IT_PROVINCIA_LEGALE VARCHAR, ID_RES_IT_COMUNE_LEGALE VARCHAR, PARTITAIVA 
VARCHAR, CODICEFISCALE VARCHAR, COGNOME VARCHAR, NOME VARCHAR, SESSO VARCHAR, 
NASCITA_NAZIONE VARCHAR, NASCITA_COMUNE VARCHAR, DATA_NASCITA VARCHAR, 
NASCITA_ESTERA_LUOGO VARCHAR, RES_ITALIANA_FRAZIONE VARCHAR, RES_ITALIANA_CAP 
VARCHAR, RES_ITALIANA_DUG VARCHAR, RES_ITALIANA_TOPONIMO VARCHAR, 
RES_ITALIANA_CIVICO VARCHAR, RESIDENZA_ESTERA_CITTA VARCHAR, 
RESIDENZA_ESTERA_INDIRIZZO VARCHAR, RESIDENZA_ESTERA_ZIPCODE VARCHAR
CONSTRAINT PK_TEST_PEOPLE PRIMARY KEY (PERSON_ID)) SALT_BUCKETS = 3
{code}

> 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
>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
> 
> h2. First query
> {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 1132461 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)
> 
> h2. Second query
> {code:sql}
> SELECT /*+

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

2018-01-05 Thread Maryann Xue (JIRA)


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

Maryann Xue commented on PHOENIX-4508:
--

Thank you, [~f.pompermaier]! Just to confirm, are you using the latest version 
of Phoenix? I tried to replicate this issue based on my guess of the DDLs but 
could not. The query plans turned out to be different from what you got. I'll 
just wait for your input then.

> 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
>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
> 
> h2. First query
> {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 1132461 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)
> 
> h2. Second query
> {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 =

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

2018-01-05 Thread Flavio Pompermaier (JIRA)


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

Flavio Pompermaier commented on PHOENIX-4508:
-

Yes, LID is LOCALID. Tomorrow I hope to be able to retrieve the 2 DDL.

> 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
>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
> 
> h2. First query
> {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 1132461 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)
> 
> h2. Second query
> {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

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

2018-01-05 Thread Maryann Xue (JIRA)


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

Maryann Xue commented on PHOENIX-4508:
--

It is important because a table is ordered on its primary key and should not be 
sorted over it in the query plan. I guess LID is LOCALID. If that's correct, 
there is a bug with sort merge join. I'll look into it.

> 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
>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
> 
> h2. First query
> {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 1132461 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)
> 
> h2. Second query
> {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)  |

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

2018-01-05 Thread Flavio Pompermaier (JIRA)


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

Flavio Pompermaier commented on PHOENIX-4508:
-

I can share them if it's importantin People table the PERSON_ID is the
only PK, while in MYTABLE the PK is composed by two string fields: LID
VARCHAR and EID CHAR(40)

On 5 Jan 2018 21:49, "Maryann Xue (JIRA)"  wrote:


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

Maryann Xue commented on PHOENIX-4508:
--

[~f.pompermaier], could you please share DDL of the two tables? Are
PERSON_ID and LOCALID both primary keys in their respective tables?

another one, logically equal, does not (unless that I don't apply some
tuning to timeouts).
the second does not.
+---
--+++
  | EST_BYTES_READ  | EST_ROWS_READ  |
EST_INFO_TS   |
+---
--+++
  | 14155777900 | 12077867   |
1513754378759  |
SCAN OVER PEOPLE | 14155777900 | 12077867   |
1513754378759  |
   | 14155777900 | 12077867   |
1513754378759  |
   | 14155777900 | 12077867   |
1513754378759  |
  | 14155777900 | 12077867   |
1513754378759  |
SCAN OVER MYTABLE [0] - [2]  | 14155777900 | 12077867   |
1513754378759  |
HAS_CANDIDATES = false)   | 14155777900 | 12077867
 | 1513754378759  |
  | 14155777900 | 12077867
 | 1513754378759  |
   | 14155777900 | 12077867   |
1513754378759  |
  | 14155777900 | 12077867   |
1513754378759  |
+---
--+++
PEOPLE  ds ON ds.PERSON_ID = l.LOCALID;
---+
-+++
 | EST_BYTES_READ  | EST_ROWS_READ  |
EST_INFO_TS   |
---+
-+++
 | 14155777900 | 12077867   |
1513754378759  |
SCAN OVER MYTABLE [0] - [2]  | 14155777900 | 12077867   |
1513754378759  |
HAS_CANDIDATES = false)  | 14155777900 | 12077867
 | 1513754378759  |
  | 14155777900 | 12077867   |
1513754378759  |
 | 14155777900 | 12077867   |
1513754378759  |
SCAN OVER PEOPLE   | 14155777900 | 12077867   |
1513754378759  |
  | 14155777900 | 12077867   |
1513754378759  |
| 14155777900 | 12077867   |
1513754378759  |
  | 14155777900 | 12077867   |
1513754378759  |
 | 14155777900 | 12077867   |
1513754378759  |
---+
-+++



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


> 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
>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
> 
> h2. First query
> {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   |
>

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

2018-01-05 Thread Maryann Xue (JIRA)


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

Maryann Xue commented on PHOENIX-4508:
--

[~f.pompermaier], could you please share DDL of the two tables? Are PERSON_ID 
and LOCALID both primary keys in their respective tables?

> 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
>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
> 
> h2. First query
> {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 1132461 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)
> 
> h2. Second query
> {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

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

2018-01-02 Thread Pedro Boado (JIRA)


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

Pedro Boado commented on PHOENIX-4508:
--

Feel free to provide environment details (such as client and server version , 
OS, etc) under environment tab.

> 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
>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
> 
> h2. First query
> {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 1132461 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)
> 
> h2. Second query
> {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

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

2018-01-02 Thread Flavio Pompermaier (JIRA)


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

Flavio Pompermaier commented on PHOENIX-4508:
-

I don't think so, it's just because I've faced the error using that 
version...probably it's common to all 4.13.x Phoenix releases

> 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
> 
> h2. First query
> {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 1132461 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)
> 
> h2. Second query
> {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  |
> |

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

2018-01-02 Thread Pedro Boado (JIRA)


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

Pedro Boado commented on PHOENIX-4508:
--

Is this issue exclusive to the cdh branch?

> 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
> 
> h2. First query
> {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 1132461 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)
> 
> h2. Second query
> {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   
>

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

[jira] [Commented] (PHOENIX-4508) Wrong query plan generation

12 matches

Site Navigation

Mail list logo

Footer information