Hi,
It looks like IN operator works incorrectly in combination with INNER JOIN and
nested queries.
If I have "HOSTNAME IN ('c6401.ambari.apache.org', 'c6402.ambari.apache.org')"
in the condition, I got correct results from Phoenix
0: jdbc:phoenix:localhost:61181> SELECT E.METRIC_NAME AS METRIC_NAME,
E.HOSTNAME AS HOSTNAME, E.APP_ID AS APP_ID, E.INSTANCE_ID AS INSTANCE_ID,
E.SERVER_TIME AS SERVER_TIME, E.START_TIME AS START_TIME, E.UNITS AS UNITS,
E.METRIC_SUM AS METRIC_SUM, E.METRIC_MAX AS METRIC_MAX, E.METRIC_MIN AS
METRIC_MIN, E.METRIC_COUNT AS METRIC_COUNT, E.METRICS AS METRICS FROM
METRIC_RECORD AS E INNER JOIN (SELECT METRIC_NAME, HOSTNAME, MAX(SERVER_TIME)
AS MAX_SERVER_TIME, APP_ID, INSTANCE_ID FROM METRIC_RECORD WHERE METRIC_NAME
= 'disk_total' AND HOSTNAME IN ('c6401.ambari.apache.org',
'c6402.ambari.apache.org') AND APP_ID = 'HOST' GROUP BY METRIC_NAME, HOSTNAME,
APP_ID, INSTANCE_ID)AS I ON E.METRIC_NAME=I.METRIC_NAME AND
E.HOSTNAME=I.HOSTNAME AND E.SERVER_TIME=I.MAX_SERVER_TIME AND E.APP_ID=I.APP_ID
AND E.INSTANCE_ID=I.INSTANCE_ID;
+-------------+------------+------------+-------------+-------------+------------+----------------------+------------+------------+------------+-----+
| METRIC_NAME | HOSTNAME | APP_ID | INSTANCE_ID | SERVER_TIME |
START_TIME | UNITS | METRIC_SUM | METRIC_MAX | METRIC_MIN | MET |
+-------------+------------+------------+-------------+-------------+------------+----------------------+------------+------------+------------+-----+
| disk_total | c6401.ambari.apache.org | HOST | null |
1434026818476 | 1434026759012 | null | 5876.160000000001 | 489.
|
| disk_total | c6402.ambari.apache.org | HOST | null |
1434026818481 | 1434026761132 | null | 5876.160000000001 | 489.
|
+-------------+------------+------------+-------------+-------------+------------+----------------------+------------+------------+------------+-----+
2 rows selected (0.311 seconds)
But if I have only 1 hostname specified in "HOSTNAME IN
('c6401.ambari.apache.org')", I got empty result
0: jdbc:phoenix:localhost:61181> SELECT E.METRIC_NAME AS METRIC_NAME,
E.HOSTNAME AS HOSTNAME, E.APP_ID AS APP_ID, E.INSTANCE_ID AS INSTANCE_ID,
E.SERVER_TIME AS SERVER_TIME, E.START_TIME AS START_TIME, E.UNITS AS UNITS,
E.METRIC_SUM AS METRIC_SUM, E.METRIC_MAX AS METRIC_MAX, E.METRIC_MIN AS
METRIC_MIN, E.METRIC_COUNT AS METRIC_COUNT, E.METRICS AS METRICS FROM
METRIC_RECORD AS E INNER JOIN (SELECT METRIC_NAME, HOSTNAME, MAX(SERVER_TIME)
AS MAX_SERVER_TIME, APP_ID, INSTANCE_ID FROM METRIC_RECORD WHERE METRIC_NAME
= 'disk_total' AND HOSTNAME IN ('c6401.ambari.apache.org') AND APP_ID = 'HOST'
GROUP BY METRIC_NAME, HOSTNAME, APP_ID, INSTANCE_ID)AS I ON
E.METRIC_NAME=I.METRIC_NAME AND E.HOSTNAME=I.HOSTNAME AND
E.SERVER_TIME=I.MAX_SERVER_TIME AND E.APP_ID=I.APP_ID AND
E.INSTANCE_ID=I.INSTANCE_ID;
+-------------+------------+------------+-------------+-------------+------------+----------------------+------------+------------+------------+-----+
| METRIC_NAME | HOSTNAME | APP_ID | INSTANCE_ID | SERVER_TIME |
START_TIME | UNITS | METRIC_SUM | METRIC_MAX | METRIC_MIN | MET |
+-------------+------------+------------+-------------+-------------+------------+----------------------+------------+------------+------------+-----+
+-------------+------------+------------+-------------+-------------+------------+----------------------+------------+------------+------------+-----+
No rows selected (0.233 seconds)
I got empty result as well, if I specify "HOSTNAME='c6401.ambari.apache.org'"
If I execute nested query as a separate query, it works correctly.
DDL:
CREATE TABLE IF NOT EXISTS METRIC_RECORD (
METRIC_NAME VARCHAR,
HOSTNAME VARCHAR,
SERVER_TIME UNSIGNED_LONG NOT NULL,
APP_ID VARCHAR,
INSTANCE_ID VARCHAR,
START_TIME UNSIGNED_LONG,
UNITS CHAR(20),
METRIC_SUM DOUBLE,
METRIC_COUNT UNSIGNED_INT,
METRIC_MAX DOUBLE,
METRIC_MIN DOUBLE,
METRICS VARCHAR CONSTRAINT pk
PRIMARY KEY (METRIC_NAME, HOSTNAME, SERVER_TIME, APP_ID,
INSTANCE_ID)) DATA_BLOCK_ENCODING='FAST_DIFF', IMMUTABLE_ROWS=true,
TTL=86400, COMPRESSION='SNAPPY'
My ENV:
sqlline version 1.1.2
phoenix-4.2.0.2.2.0.0
hbase-0.98.4.2.2.0.0
Is it a know bug?
Thank you.
BR,
Dmytro Sen
