Mailing-List: contact dev-help@phoenix.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@phoenix.apache.org
Date: Thu, 28 Dec 2017 10:17:00 +0000 (UTC)
From: "Sokolov Yura (JIRA)" <jira@apache.org>
To: dev@phoenix.apache.org
Message-ID: <JIRA.13127498.1514456186000.540775.1514456220477@Atlassian.JIRA>
In-Reply-To: <JIRA.13127498.1514456186000@Atlassian.JIRA>
References: <JIRA.13127498.1514456186000@Atlassian.JIRA> <JIRA.13127498.1514456186916@jira-lw-us.apache.org>
Subject: [jira] [Created] (PHOENIX-4506) Subquery IN demands column from
 order statement
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
archived-at: Thu, 28 Dec 2017 10:17:05 -0000

Sokolov Yura created PHOENIX-4506:
-------------------------------------

             Summary: Subquery IN demands column from order statement
                 Key: PHOENIX-4506
                 URL: https://issues.apache.org/jira/browse/PHOENIX-4506
             Project: Phoenix
          Issue Type: Bug
         Environment: awm emr phoenix 4.11.0 hbase 1.3
            Reporter: Sokolov Yura


I have a table:
{code}
create immutable table product_history_v3 (
        ts bigint not null,
        id varchar not null,
        product varchar,
        merchantid varchar,
        storeid varchar,
        constraint pk primary key (ts, id)
) compression=LZ4,max_filesize=150000000,memstore_flushsize=70000000,
        versions=1,update_cache_frequency=1000,append_only_schema=true,
        guid_posts_width=10000000,
SALT_BUCKETS=20;
create local index product_history_v3_id_ts on product_history_v3 (id, ts) compression=LZ4;
create local index product_history_v3_merchantid_ts on product_history_v3 (merchantid, ts) include (id) compression=LZ4;
create local index product_history_v3_storeid_ts on product_history_v3 (storeid, ts) include (id) compression=LZ4;
{code}
Phoneix forbids following query:
{code}
0: jdbc:phoenix:localhost:2181:/hbase> explain select id, ts, substr(product, 1, 20) from product_history_v3 where (id, ts) in (select id, ts from product_history_v3 where merchantid = '1479114284851799852-2-11-118-1577502676' and ts > 1514425550000 order by storeid, id, ts limit 500) order by storeid, id, ts;
Error: ERROR 511 (42890): All ORDER BY expressions must appear in SELECT DISTINCT: STOREID (state=42890,code=511)
java.sql.SQLException: ERROR 511 (42890): All ORDER BY expressions must appear in SELECT DISTINCT: STOREID
        at org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:483)
        at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:150)
        at org.apache.phoenix.compile.OrderByCompiler.compile(OrderByCompiler.java:130)
        at org.apache.phoenix.compile.QueryCompiler.compileSingleFlatQuery(QueryCompiler.java:562)
        at org.apache.phoenix.compile.QueryCompiler.compileSingleQuery(QueryCompiler.java:507)
        at org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:202)
{code}
But if I wrap it into dumb sub-middle-query, then phoenix allows to run it:
{code}
0: jdbc:phoenix:localhost:2181:/hbase> explain select id, ts, substr(product, 1, 20) from product_history_v3 where (id, ts) in (select id, ts from (select id, ts from product_history_v3 where merchantid = '1479114284851799852-2-11-118-1577502676' and ts > 1514425550000 order by storeid, id, ts limit 500)) order by storeid, id, ts;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                           PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CLIENT 1460-CHUNK 62355620 ROWS 452990608697 BYTES PARALLEL 1460-WAY FULL SCAN OVER PRODUCT_HISTORY_V3 
|     SERVER SORTED BY [PRODUCT_HISTORY_V3.STOREID, PRODUCT_HISTORY_V3.ID, PRODUCT_HISTORY_V3.TS]
| CLIENT MERGE SORT
|     SKIP-SCAN-JOIN TABLE 0
|         CLIENT 20-CHUNK 56910809 ROWS 6291457528 BYTES PARALLEL 20-WAY RANGE SCAN OVER PRODUCT_HISTORY_V3 [2,'1479114284851799852-2-11-118-1577502676',1514425550001] - [2,'1479114284851799852-2-11-118-1577502676',*]
|             SERVER FILTER BY FIRST KEY ONLY
|             SERVER TOP 500 ROWS SORTED BY ["STOREID", "ID", "TS"]
|         CLIENT MERGE SORT
|         CLIENT LIMIT 500
|         CLIENT SORTED BY [ID, TS]
|         CLIENT AGGREGATE INTO DISTINCT ROWS BY [ID, TS]
|     DYNAMIC SERVER FILTER BY (PRODUCT_HISTORY_V3.TS, PRODUCT_HISTORY_V3.ID) IN (($5.$8, $5.$7))
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12 rows selected (0,306 seconds)
{code}


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