MIN() and MAX() of set of row values

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

MIN() and MAX() of set of row values

Mark Brand
Hi,

Row values make life easier in so many ways, but I was just wondering if
there is (or should be or could be) a way to use aggregate MIN() and
MAX() on a set of row values.

Mark

CREATE TABLE T (a, b);
INSERT INTO T (a, b) VALUES (1, 2), (1, 3), (2, 3);

SELECT MIN((a,b)) = (1, 2) ok  FROM T;
--Error: near line 4: row value misused

SELECT MIN((SELECT a, b)) = (1, 2) ok  FROM T;
--Error: near line 7: row value misused

SELECT (1, 2) = MIN(SELECT a, b FROM T);
--Error: near line 10: near "SELECT": syntax error

--ok
SELECT T.*
FROM T
LEFT JOIN T S
   ON (S.a, S.b) < (T.a, T.b)
WHERE S.a IS NULL;

--ok
SELECT (T.a, T.b) = (1, 2) ok
FROM T
LEFT JOIN T S
   ON (S.a, S.b) < (T.a, T.b)
WHERE S.a IS NULL;

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: MIN() and MAX() of set of row values

Simon Slavin-3
On 30 Mar 2018, at 3:48pm, Mark Brand <[hidden email]> wrote:

> SELECT MIN((a,b)) = (1, 2) ok  FROM T;
> --Error: near line 4: row value misused
>
> SELECT MIN((SELECT a, b)) = (1, 2) ok  FROM T;
> --Error: near line 7: row value misused
>
> SELECT (1, 2) = MIN(SELECT a, b FROM T);
> --Error: near line 10: near "SELECT": syntax error

You got very close to the right syntax.

SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE T (a, b);
sqlite> INSERT INTO T (a, b) VALUES (1, 2), (1, 3), (2, 3);
sqlite> SELECT min(a,b) FROM T;
1
1
2

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: MIN() and MAX() of set of row values

Igor Tandetnik-2
On 3/30/2018 12:10 PM, Simon Slavin wrote:

> On 30 Mar 2018, at 3:48pm, Mark Brand <[hidden email]> wrote:
>
>> SELECT MIN((a,b)) = (1, 2) ok  FROM T;
>> --Error: near line 4: row value misused
>>
>> SELECT MIN((SELECT a, b)) = (1, 2) ok  FROM T;
>> --Error: near line 7: row value misused
>>
>> SELECT (1, 2) = MIN(SELECT a, b FROM T);
>> --Error: near line 10: near "SELECT": syntax error
>
> You got very close to the right syntax.
>
> SQLite version 3.19.3 2017-06-27 16:48:08
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> CREATE TABLE T (a, b);
> sqlite> INSERT INTO T (a, b) VALUES (1, 2), (1, 3), (2, 3);
> sqlite> SELECT min(a,b) FROM T;
> 1
> 1
> 2

I don't think that's what the OP had in mind. They didn't want the smaller of a and b for each row, but rather the lexicographically smallest (a, b) pair among all rows.

Row values support less-than comparison, so it kind of makes sense to expect MIN to work on them, too.
--
Igor Tandetnik


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: MIN() and MAX() of set of row values

Simon Slavin-3
On 30 Mar 2018, at 5:55pm, Igor Tandetnik <[hidden email]> wrote:

> I don't think that's what the OP had in mind. They didn't want the smaller of a and b for each row, but rather the lexicographically smallest (a, b) pair among all rows.
>
> Row values support less-than comparison, so it kind of makes sense to expect MIN to work on them, too.

Ah.  Okay, I get it.

The only way I can think of to do it is to devise a metric to turn a pair (a, b) into one number.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: MIN() and MAX() of set of row values

Peter da Silva
On 3/30/18, 11:58 AM, "sqlite-users on behalf of Simon Slavin" <[hidden email] on behalf of [hidden email]> wrote:
> can think of to do it is to devise a metric to turn a pair (a, b) into one number.

Problem is you can't uniquely order pairs of points. Is (1, 2) greater or lesser than (2, 1)?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: MIN() and MAX() of set of row values

Simon Slavin-3
On 30 Mar 2018, at 6:04pm, Peter Da Silva <[hidden email]> wrote:

> On 3/30/18, 11:58 AM, "sqlite-users on behalf of Simon Slavin" <[hidden email] on behalf of [hidden email]> wrote:
>> can think of to do it is to devise a metric to turn a pair (a, b) into one number.
>
> Problem is you can't uniquely order pairs of points. Is (1, 2) greater or lesser than (2, 1)?

That's up to you.  That's why you are devising the metric.  Maybe the metric is just score == a + b .

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: MIN() and MAX() of set of row values

David Raymond
In reply to this post by Mark Brand
Why not something simpler, like

select * from T order by a, b limit 1; --for the min,

select * from T order by a desc, b desc limit 1; --for the max?

select (select a, b from T order by a, b limit 1) = (1, 2) as ok;


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Mark Brand
Sent: Friday, March 30, 2018 10:48 AM
To: SQLite mailing list
Subject: [sqlite] MIN() and MAX() of set of row values

Hi,

Row values make life easier in so many ways, but I was just wondering if
there is (or should be or could be) a way to use aggregate MIN() and
MAX() on a set of row values.

Mark

CREATE TABLE T (a, b);
INSERT INTO T (a, b) VALUES (1, 2), (1, 3), (2, 3);

SELECT MIN((a,b)) = (1, 2) ok  FROM T;
--Error: near line 4: row value misused

SELECT MIN((SELECT a, b)) = (1, 2) ok  FROM T;
--Error: near line 7: row value misused

SELECT (1, 2) = MIN(SELECT a, b FROM T);
--Error: near line 10: near "SELECT": syntax error

--ok
SELECT T.*
FROM T
LEFT JOIN T S
   ON (S.a, S.b) < (T.a, T.b)
WHERE S.a IS NULL;

--ok
SELECT (T.a, T.b) = (1, 2) ok
FROM T
LEFT JOIN T S
   ON (S.a, S.b) < (T.a, T.b)
WHERE S.a IS NULL;

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: MIN() and MAX() of set of row values

Igor Tandetnik-2
In reply to this post by Peter da Silva
On 3/30/2018 1:04 PM, Peter Da Silva wrote:
> On 3/30/18, 11:58 AM, "sqlite-users on behalf of Simon Slavin" <[hidden email] on behalf of [hidden email]> wrote:
>> can think of to do it is to devise a metric to turn a pair (a, b) into one number.
>
> Problem is you can't uniquely order pairs of points.

Yes you can.

> Is (1, 2) greater or lesser than (2, 1)?

"select (1, 2) < (2, 1)" says lesser.

For further discussion, see https://www.sqlite.org/rowvalue.html#row_value_comparisons
--
Igor Tandetnik


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: MIN() and MAX() of set of row values

David Raymond
In reply to this post by Simon Slavin-3
https://www.sqlite.org/rowvalue.html section 2.1 for ordering.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Simon Slavin
Sent: Friday, March 30, 2018 1:06 PM
To: SQLite mailing list
Subject: Re: [sqlite] MIN() and MAX() of set of row values

On 30 Mar 2018, at 6:04pm, Peter Da Silva <[hidden email]> wrote:

> On 3/30/18, 11:58 AM, "sqlite-users on behalf of Simon Slavin" <[hidden email] on behalf of [hidden email]> wrote:
>> can think of to do it is to devise a metric to turn a pair (a, b) into one number.
>
> Problem is you can't uniquely order pairs of points. Is (1, 2) greater or lesser than (2, 1)?

That's up to you.  That's why you are devising the metric.  Maybe the metric is just score == a + b .

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: MIN() and MAX() of set of row values

wmertens
As a sidenote, I believe row values were added because of keyset pagination
https://use-the-index-luke.com/no-offset. I found them to not be actually
useful, so I thought I'd explain here. (copied from my comments on that
page (now no longer visible), slightly edited)


I ended up implementing this approach for sqlite since it has the
(a,b)<(x,y) operation now, but while doing so I realized that that
comparison is not a magical operation that uses the given fields to compare
them to the sort order of the query, but instead logically equivalent to
`(a < x OR (a = x AND b < y)`.

So if you are querying with `ORDER BY a ASC, b DESC`, you have to use `(a >
x OR (a = x AND b < y))`, since there is no way to express that with the
combined operator.

Just wanted to point that out since it surprised me, and since I (wrongly)
gleaned from your explanation that the combined operator is necessary to
implement keyset pagination.

I did some EXPLAINing of "cursor where clauses" and the shortest plans were
with clauses of the form:

a >= x AND (a != x OR (b <= y AND (b != y OR c > z))

(with ORDER BY a ASC, b DESC, c ASC and x, y, z the last-seen values)

A bit annoying to write but the idea is that the DB can scan a in index
order, then b if needed, then c if needed.

In fact, writing it that way gets me less instructions than when I use the
row values. Not sure if that translates in faster queries though.


On Fri, Mar 30, 2018 at 7:09 PM David Raymond <[hidden email]>
wrote:

> https://www.sqlite.org/rowvalue.html section 2.1 for ordering.
>
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Simon Slavin
> Sent: Friday, March 30, 2018 1:06 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] MIN() and MAX() of set of row values
>
> On 30 Mar 2018, at 6:04pm, Peter Da Silva <[hidden email]>
> wrote:
>
> > On 3/30/18, 11:58 AM, "sqlite-users on behalf of Simon Slavin" <
> [hidden email] on behalf of
> [hidden email]> wrote:
> >> can think of to do it is to devise a metric to turn a pair (a, b) into
> one number.
> >
> > Problem is you can't uniquely order pairs of points. Is (1, 2) greater
> or lesser than (2, 1)?
>
> That's up to you.  That's why you are devising the metric.  Maybe the
> metric is just score == a + b .
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: feature request: MIN() and MAX() of set of row values

Mark Brand
In reply to this post by Igor Tandetnik-2
On 30/03/18 18:55, Igor Tandetnik wrote:
> <snip>
>
> Row values support less-than comparison, so it kind of makes sense to
> expect MIN to work on them, too.

That's what I was thinking too. One would expect aggregate MIN() and
MAX() to work over row values.

While we're on the subject of row values, the error on the first query
below seems unexpected:

    sqlite> select (2, 3) = (select (2, 3));
     Error: row value misused

     sqlite> select (2, 3) = (2, 3);
     1

     sqlite> select (2, 3) = (values(2, 3));
     1


Mark

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: feature request: MIN() and MAX() of set of row values

Mark Brand

On 13/04/18 09:32, Mark Brand wrote:

> On 30/03/18 18:55, Igor Tandetnik wrote:
>> <snip>
>>
>> Row values support less-than comparison, so it kind of makes sense to
>> expect MIN to work on them, too.
>
> That's what I was thinking too. One would expect aggregate MIN() and
> MAX() to work over row values.
>
> While we're on the subject of row values, the error on the first query
> below seems unexpected:
>
>    sqlite> select (2, 3) = (select (2, 3));
>     Error: row value misused
>
>     sqlite> select (2, 3) = (2, 3);
>     1
>
>     sqlite> select (2, 3) = (values(2, 3));
>     1
>

It also occurs to me that COUNT() should work (but doesn't) over sets of
row values:

     sqlite> select count((1,2));
     Error: row value misused

I would expect it to return the number of non-NULL row values in the set.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: feature request: MIN() and MAX() of set of row values

Simon Slavin-3
On 13 Apr 2018, at 8:40am, Mark Brand <[hidden email]> wrote:

> It also occurs to me that COUNT() should work (but doesn't) over sets of row values:
>
>     sqlite> select count((1,2));
>     Error: row value misused
>
> I would expect it to return the number of non-NULL row values in the set.

What should this do, and why ?

CREATE TABLE MyTable (a INTEGER, b, INTEGER, c INTEGER);
INSERT INTO MyTable VALUES (1, 1, 1);
INSERT INTO MyTable VALUES (2, 2, 2);
...
INSERT INTO MyTable VALUES (10, 10, 10);

SELECT COUNT(a, b, c) FROM MyTable;

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: feature request: MIN() and MAX() of set of row values

Mark Brand


On 13/04/18 14:12, Simon Slavin wrote:

> On 13 Apr 2018, at 8:40am, Mark Brand <[hidden email]> wrote:
>
>> It also occurs to me that COUNT() should work (but doesn't) over sets of row values:
>>
>>      sqlite> select count((1,2));
>>      Error: row value misused
>>
>> I would expect it to return the number of non-NULL row values in the set.
> What should this do, and why ?
>
> CREATE TABLE MyTable (a INTEGER, b, INTEGER, c INTEGER);
> INSERT INTO MyTable VALUES (1, 1, 1);
> INSERT INTO MyTable VALUES (2, 2, 2);
> ...
> INSERT INTO MyTable VALUES (10, 10, 10);
>
> SELECT COUNT(a, b, c) FROM MyTable;

Hi Simon,

I would expect COUNT() to count row values just as it counts normal
values. In your example, it should return 10 because there are 10 rows
in the MyTable.

When counting normal values, COUNT() excludes NULLs. If there is a such
as thing as a NULL row value, COUNT() should exclude it too.

Sqlite doesn't seem to distinguish between a row value made up of only
NULLs and a NULL row value, at least in this context:

     sqlite> select (NULL, NULL) IS (SELECT 1, 2 WHERE 0);  -- The right
side would probably satisfy anybody's idea of what "NULL row value" means.
     1

     sqlite> select (NULL, NULL) IS (SELECT NULL, NULL WHERE 1); -- The
right side is a row containing all NULLs.
     1

Therefore, probably COUNT() should exclude row values made up of only
NULLs on the grounds that these qualify as NULL row values.

You wrote

     COUNT(a, b, c)

but I would have expected

     COUNT((a, b, c))

to make it clear that COUNT() has one argument which is a row value. For
aggregate MIN() and MAX(), the "extra" parenthesis would have the
additional motivation of distinguishing the aggregate functions from the
non-aggregate MIN() and MAX() which have 2 arguments.


Mark
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users