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)