[jira] [Created] (PHOENIX-3325) optimize lhs = ANY(...) as lhs in (...)

["Nick Dimiduk (JIRA)" <ji...@apache.org>]

Nick Dimiduk created PHOENIX-3325:
-------------------------------------

             Summary: optimize lhs = ANY(...) as lhs in (...)
                 Key: PHOENIX-3325
                 URL: https://issues.apache.org/jira/browse/PHOENIX-3325
             Project: Phoenix
          Issue Type: Improvement
    Affects Versions: 4.7.0
            Reporter: Nick Dimiduk


Phoenix will efficiently optimize a {{WHERE x IN (...)}} clause, when x is part of the primary key, into a skip-scan or point lookup, based on the column's rowkey position. The same could be done for a {{WHERE x = ANY(...)}}. This would be especially convenient for clients using prepared statements where the number of element compared is not known ahead of time. They may prepare the query once using {{ANY}} and bind an array, rather than re-building it each invocation with a concatenation of {{?}}'s based on the member size.

{noformat}
0: jdbc:phoenix:localhost> explain select * from test1 where a = any(ARRAY[1,3]);
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                      PLAN                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER TEST1                                                                                                                                                       |
|     SERVER FILTER BY org.apache.phoenix.expression.function.ArrayAnyComparisonExpression [children=[ARRAY[1,3], A = org.apache.phoenix.expression.function.ArrayElemRefExpression [children=[ARRAY[1,3], |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows selected (0.008 seconds)
0: jdbc:phoenix:localhost> explain select * from test1 where a in (1,3);
+------------------------------------------------------------------+
|                               PLAN                               |
+------------------------------------------------------------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY POINT LOOKUP ON 2 KEYS OVER TEST1  |
+------------------------------------------------------------------+
1 row selected (0.039 seconds)
{noformat}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)