CODEWITHSUNDEEP
sqlhadooppresto
Leo Jiang
Leo Jiang

Reputation: 26243

Force Presto to maintain order of WHERE clauses

I'm trying to run something like the following query:

SELECT * FROM foo WHERE cardinality(bar) > 0 AND bar[1] = '...';

However, I'm getting Query failed: Array subscript out of bounds. I'm assuming this is because Presto is trying to optimize the query by checking bar[1] = '...' before checking cardinality(bar) > 0. Is there a way to force Presto to maintain the order of the clauses?

Upvotes: 0

Views: 3522

Answers (1)

Dave Cameron
Dave Cameron

Reputation: 2190

I've solved this in two ways when I've needed it.

  1. Use the element_at function instead of the [] subscript notation. element_at returns a NULL when indexing past the end of an array, so you could reduce your example to one condition. element_at also works in the SELECT clause, although it isn't needed with your WHERE clause:
SELECT bar[1] FROM foo WHERE element_at(bar,1) = '...';
  1. Do the first condition in a subquery using the with clause:
WITH (SELECT * FROM foo WHERE cardinality(bar) > 0) AS populated_foo
SELECT * FROM populated_foo WHERE bar[1] = '...';

The 2nd approach doesn't make much sense for your example, but I've found it useful for more complex conditions involving row objects inside of arrays.

Upvotes: 2

Related Questions