Impala query with limit 0

[#1]

I see some query submitted to Impala with "LIMIT 0' at the end which obviously do not return any rows. It seems like these queries are coming from IBM Datastage. I am from Production support team and I monitor such queries. i know we can use limit 10 or so to limit the number of rows returned but why on the earth someone wants to limit to 0? Is it just to check the syntax of query or connection or something? Before I question the team who submits such query, wanted to double check if there is any reason one would like to say limit 0 at the end.  official documentation at official impala document  does not say any thing about it. 

[#2 · Accepted solution]

Hi,

This use case seems to be similar to queries which have a filter "WHERE 1=0" - those also do not return any data.

See for example such a discussion why that is needed/used in some places:

https://stackoverflow.com/questions/9140606/why-would-you-use-where-1-0-statement-in-sql

My best guess would be that the application calling such queries needs the schema of the result set in advance - for example to create and render the layout (columns) or autofilters or to prepare another subsequent query properly (for example it has to do some decisions - which filters the next query needs to contain, maybe they are different for a string and for a date column, etc)

Best regards

 Miklos

Miklos Szurap

Customer Operations Engineer, Cloudera

[#3]

From where the query is submitted[Jdbc/ODBC, Impala-shell, Hue]. If it is from JDBC/ODBC then the query is generated to figure out column names. You can disable the feature that generates it with the PreparedMetadataLimitZero flag - see [page 90] https://www.cloudera.com/documentation/other/connectors/impala-jdbc/latest/Cloudera-JDBC-Driver-for-...

[#4]

@npr73, have any of the replies helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future.