CODEWITHSUNDEEP
amazon-redshift
Chris
Chris

Reputation: 21

Redshift Query Ignoring LIMIT Clause

While investigating duplicate event_id's, we noticed that Redshift seems to be ignoring the LIMIT clause on a simple query:

SELECT se_action
FROM events
WHERE event_id = 'e4a5ffbe-6a1d-47b7-ac10-6c57b2cd65ed'
ORDER BY collector_tstamp DESC
LIMIT 1;

When running this query, I receive over 800 results. Interestingly, if I remove the ORDER BY clause, I receive only one result as intended.

How do I get this to properly output a single result, or does this sound like a bug with Redshift?

Upvotes: 2

Views: 6867

Answers (3)

Cezar Vieira Rodrigues
Cezar Vieira Rodrigues

Reputation: 56

Lately reply here but i faced this issue too and noted an pattern:

Like your query, mine is using too a DistKey UUID in WHERE clause comparing string with "=" operator, in this cenario i'm facing same problem with bellow ORDER BY and LIMIT clauses being totally discard from the query (even from explain).

But when i just change the operator "=" per "LIKE" in WHERE clause my query works fine

Ex:

SELECT * FROM table WHERE id = 'UUID' ORDER BY date DESC LIMIT 1 Doesn't work, filter by "where" but discard "order by" and "limit"

SELECT * FROM table WHERE id LIKE 'UUID' ORDER BY date DESC LIMIT 1 Works perfectly with "order by" and "limit"

I didn't find anything related to this in forums but seems like a redshift bug, do you filled a bug report on AWS?

Upvotes: 0

John Rotenstein
John Rotenstein

Reputation: 269181

This does sound like a bug in Redshift. If you use LIMIT 1 (or TOP 1) then the query should return only one row.

If you are subscribed to AWS Support, please open an issue so that they can trace the underlying problem. If you are not subscribed to support, you can post to the Amazon Redshift discussion forum.

Upvotes: 0

Brent Knox
Brent Knox

Reputation: 308

There are two ways I can think of that will limit the amount of results you get from a quarry and they are LIMIT and TOP

Try this TOP Example

SELECT TOP 1 se_action
FROM events
WHERE event_id = 'e4a5ffbe-6a1d-47b7-ac10-6c57b2cd65ed'
ORDER BY collector_tstamp DESC;

Upvotes: 3

Related Questions