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)
