Visit our new documentation site! This documentation page is no longer updated.

Known Limitations

The Presto query engine has some limitations compared to Hive at this stage. We welcome your feedback and will make improvements based on these.

Table of Contents

Column Name Escaping

When a column name matches the name of a reserved keyword, the name of the columns needs to be quoted. While in Hive, as most the SQL-based query languages, the quotation character is the back tick character `, in Presto quoting of a column name is accomplished with the double quote character instead . See the comparison below:

SELECT `join` FROM mytbl      # Hive
SELECT "join" FROM mytbl      # Presto

Please be aware that quoting a column name in single quotes will make the query parser interpret the content as a simple string producing a perhaps unexpected result:

SELECT "join" FROM mytbl LIMIT 10

result:
value1
value2
value3
value4
...
value10

SELECT 'join' FROM mytbl

result:
join
join
join
join
...
join

Table Schemas are Required

When querying via Presto, you must set a Schema for the table(s); the v syntax doesn’t work.

JOIN Order

Presto does not currently support cost-based JOIN optimizations, meaning JOINs are not automatically reordered based on table size. Please make sure that smaller tables are on the right hand size of JOIN, and they must fit in memory. Otherwise out of memory exceptions will cause the query to fail.

SELECT
  ...
FROM
  large_table
JOIN
  small_table

INSERT OVERWRITE Statements are NOT Supported

Presto does not currently support INSERT OVERWRITE Statements. Please delete table before INSERT INTO. See the detail here.

Error Message – NOT_SUPPORTED: NULL values are not allowed on the probe side of SemiJoin operator

This error is caused by using NULL IN empty subquery has an issue on result correctness.

The IN predicate determines if any values produced by the subquery are equal to the provided expression. The result of IN follows the standard rules for nulls. The subquery must produce exactly one column.

Example: “user” column contains NULL value, then the following query can be failed.

select *
from www_access
where
  user in (select 0 as user_id)   
=> Query 20170526_042346_18708_6huz3 failed: NULL values are not allowed on the probe side of SemiJoin operator. See the query plan for details.

As a workaround, adding IS NOT NULL clause works.

select *
from www_access
where
  user in (select 0 as user_id)
 and user is not null

Error Message – XXX is not of type ROW error

This error is encountered when ORDER BY a table column shadowed by a column name. Presto 0.161 was fixed column resolution rules for ORDER BY to match the behavior expected by the SQL standard.

Example: The following query uses same name between “www_access t” and “t.host as t”.

SELECT
  t.user as u,
  t.host as t
FROM www_access t
ORDER BY  t.u
=> Query 20170526_032612_15419_6huz3 failed: line 5:11: Expression "t" is not of type ROW

This error should be resoved by rewritting a column alias.

SELECT
  t.user as u,
  t.host as h
FROM www_access t
ORDER BY  t.u

Error Message – Key not present in map

Subscript operator was changed for map types to fail if the key is not present in the map, but it also breaks backward compatibility. Users are able to use element_at function.

Example:

If user agent in agent column doesn’t contain os_version info, the following query can be failed from Presto 0.173.

SELECT
TD_PARSE_AGENT(agent)['os_version'] AS os_version
FROM www_access
=> Query 20170526_033328_15818_6huz3 failed: Key not present in map: os_version

This error should be resolved by using element_at function at Presto 0.173.

SELECT
element_at(TD_PARSE_AGENT(agent), 'os_version') AS os_version
FROM www_access

If “os_version” is not present in “agent” column, this query return NULL as “os_version”


Last modified: Jun 26 2017 05:58:12 UTC

If this article is incorrect or outdated, or omits critical information, let us know. For all other issues, access our support channels.