[
https://issues.apache.org/jira/browse/PHOENIX-4508?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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 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)
> ----
> 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
> | 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)
