Hi Dmytro, This is not a known bug, so a JIRA would be appreciated (ideally including the above in the form of a new unit test). Thanks, James
On Thu, Jun 11, 2015 at 10:05 AM, Dmitry Sen <[email protected]> wrote: > 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 > > >
