LIMIT does not work in a VIEW

General
#1

I have defined a VIEW that has a LIMIT set on it like so:

> select definition from pg_views where viewname = 'aview';
+---------------------------------------------------------------------------------------------------------------------------+
|                                                        definition                                                         |
+---------------------------------------------------------------------------------------------------------------------------+
| SELECT source_id, "timestamp", value FROM time_series.series_1 WHERE source_id = 9999 ORDER BY "timestamp" DESC LIMIT 100 |
+---------------------------------------------------------------------------------------------------------------------------+

but that limit seems to have no effect. Like so:

> SELECT count(*) FROM aview;
+----------+
| count(*) |
+----------+
|     1685 |
+----------+

I was just toying with the idea of creating a shorthand way of grabbing the end of the table.

Being an SQL neophyte I have no idea if LIMIT is supposed to work in a VIEW and my google foo is not helping yet.

#2

Thanks for the report. I also don’t know whether this is supposed to work.
At the very least, it would be nice to have more details in the docs.

For now, let me pull in @a-robinson who wrote the original RFC.

#3

If you’re stuck on this for the time being, the other option you have is to put the LIMIT clause on SELECT count(*) FROM aview, i.e. SELECT count(*) FROM aview LIMIT 100––that should work.

#4

Ah yes Sean, I already tried that. It works fine.

But then I start to lose the short hand convenience of the view. What I really would like is:

-- Select most recent 10 items from list in ascending order.
    SELECT * 
    FROM   (SELECT * 
            FROM   series_1 
            WHERE  source_id = 9999 
            ORDER  BY timestamp DESC 
            LIMIT  10) AS temp 
    ORDER  BY temp.timestamp ASC;

So that I get the end of the time series in the right order. All wrapped up in a view.

#5

But wait, if I wrap up want I want with a sub-select in a view it does actually respect the LIMIT in the view:

>    CREATE view aview 
    AS 
        SELECT source_id, "timestamp", value
        FROM (
            SELECT source_id, "timestamp", value
            FROM time_series.series_1
            WHERE source_id = 9999
            ORDER BY "timestamp"
            DESC LIMIT 10)
        AS temp ORDER BY temp."timestamp" ASC;


 > SELECT * FROM aview;
+-----------+---------------------------+-------+
| source_id |         timestamp         | value |
+-----------+---------------------------+-------+
|      9999 | 2017-11-23 19:47:00+00:00 |  1824 |
|      9999 | 2017-11-23 19:46:00+00:00 |  2803 |
|      9999 | 2017-11-23 19:45:00+00:00 |  1795 |
|      9999 | 2017-11-23 19:44:00+00:00 |  1204 |
|      9999 | 2017-11-23 19:43:00+00:00 |  2177 |
|      9999 | 2017-11-23 19:41:00+00:00 |  2997 |
|      9999 | 2017-11-23 19:40:00+00:00 |  1157 |
|      9999 | 2017-11-23 19:39:00+00:00 |  2330 |
|      9999 | 2017-11-23 19:38:00+00:00 |  1453 |
|      9999 | 2017-11-23 19:37:00+00:00 |  2972 |
+-----------+---------------------------+-------+
(10 rows)

My original view still does not respect the LIMIT in the view:

>    CREATE view aview 
    AS 
        SELECT source_id, 
                timestamp, 
                value 
        FROM   series_1 
        WHERE  source_id = 9999 
        ORDER  BY timestamp DESC 
        LIMIT  10; 

> SELECT * FROM aview;
+-----------+---------------------------+-------+
| source_id |         timestamp         | value |
+-----------+---------------------------+-------+
|      9999 | 2017-11-22 15:58:45+00:00 |  1257 |
|      9999 | 2017-11-22 15:58:46+00:00 |  2721 |
|      9999 | 2017-11-22 15:58:47+00:00 |   570 |
...
...
...
|      9999 | 2017-11-23 19:48:00+00:00 |  2092 |
|      9999 | 2017-11-23 19:49:00+00:00 |  2079 |
|      9999 | 2017-11-23 19:50:00+00:00 |  2047 |
+-----------+---------------------------+-------+
(2332 rows)

But now, the plot thickens…the view with the sub-select that respects the LIMIT returns results in descending order. Despite the ORDER BY temp.“timestamp” ASC.

Perhaps I am misunderstanding how all this should work. I might have to install postgresql for comparison…

#6

You are not misunderstanding! This appears to be a plain and simple bug in CockroachDB views.

#7

Ah, OK.

If I have a moment I’ll try to make a simple test case and file a bug report.

Hey, shouldn’t you guys be having a Thanks Giving break?

#8

I went to file a bug report myself and it turned out to be just as easy to submit a preliminary fix! https://github.com/cockroachdb/cockroach/pull/20246

Happy Thanksgiving to you as well!

#9

Wow, that was quick!

I look forward to checking this again.

#10

Turns out I can get the VIEW to do what I want, in ascending order, if I nest my SELECT inside another SELECT and specify ASC again:

CREATE view aview 
AS 
    SELECT source_id, "timestamp", value
    FROM (
        SELECT source_id, "timestamp", value
        FROM (
            SELECT source_id, "timestamp", value
            FROM series_1
            WHERE source_id = 9999
            ORDER BY "timestamp"
            DESC LIMIT 10)
        AS temp ORDER BY timestamp ASC)
    ORDER BY timestamp ASC;



> select * from aview;
+-----------+---------------------------+-------+
| source_id |         timestamp         | value |
+-----------+---------------------------+-------+
|      9999 | 2017-11-24 11:11:00+00:00 |  1123 |
|      9999 | 2017-11-24 11:12:00+00:00 |  2188 |
|      9999 | 2017-11-24 11:13:00+00:00 |  1083 |
|      9999 | 2017-11-24 11:15:00+00:00 |  2156 |
|      9999 | 2017-11-24 11:16:00+00:00 |  2782 |
|      9999 | 2017-11-24 11:17:00+00:00 |  2557 |
|      9999 | 2017-11-24 11:18:00+00:00 |  1942 |
|      9999 | 2017-11-24 11:19:00+00:00 |  1530 |
|      9999 | 2017-11-24 11:20:00+00:00 |  1739 |
|      9999 | 2017-11-24 11:21:00+00:00 |  1215 |
+-----------+---------------------------+-------+
(10 rows)

So that is a work around for now.