phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Sokolov Yura (JIRA)" <j...@apache.org>
Subject [jira] [Created] (PHOENIX-4506) Subquery IN demands column from order statement
Date Thu, 28 Dec 2017 10:17:00 GMT
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)

Mime
View raw message