phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Anirudha Khanna <akha...@marinsoftware.com>
Subject OR versus IN
Date Wed, 01 Jul 2015 10:40:31 GMT
Hi,

I have a simple query like,

*SELECT tb1."id" AS "id"*
*FROM "ACCOUNTS" tb1*
*WHERE tb1."id" = 87*
*  OR tb1."id" = 89 LIMIT 10000*

which when running through JAVA using jdbc driver returns me only one row.
Whereas there are two rows in the table with those ids.

The explain plan of this (from debug logs),

*CLIENT 1-CHUNK SERIAL 1-WAY RANGE SCAN OVER ACCOUNTS [87]*
*    SERVER 10000 ROW LIMIT*
*CLIENT 10000 ROW LIMIT*

When I change this query to,

*SELECT tb1."id" AS "id"*
*FROM "ACCOUNTS" tb1*
*WHERE tb1."id" IN(87, 89) LIMIT 10000*

the explain plan changes to,

*CLIENT 1-CHUNK PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER ACCOUNTS [87] -
[89]*
*    SERVER 10000 ROW LIMIT*
*CLIENT 10000 ROW LIMIT*

and both rows are returned in the ResultSet.

I would be satisfied to modify the query to use the IN clause but when I
tried the query with the OR clause through sqlline.py I got the correct
results, and this explain plan,

*+------------------------------------------------------------------------------+*
*|                                     PLAN
    |*
*+------------------------------------------------------------------------------+*
*| CLIENT 1-CHUNK PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER ACCOUNTS [87] -
[89]  |*
*|     SERVER 10000 ROW LIMIT
    |*
*| CLIENT 10000 ROW LIMIT
    |*
*+------------------------------------------------------------------------------+*


Any pointers why the OR is behaving this way in through the JDBC driver?

I am using phoenix-4.4 with CDH-5.4.2 - HBase 1.0.0.

Cheers,
Anirudha

Mime
View raw message