How to do tuple comparison?

Question

Trying the same as this question but in SQLite. In my application I need to do this type of query:

SELECT First, Last, Score
FROM mytable
WHERE
    ('John',  'Jordan',  5) <= (First, Last, Score )
    AND (First, Last, Score) <= ('Mike',  'Taylor',  50) 
ORDER BY First, Last, Score
LIMIT 1

And get the answer ('Liz', 'Jordan', 2), given this data:

First	Last	Score
Liz	Jordan	2
John	Jordan	2
Liz	Lemon	10
Mike	Taylor	100
John	Jackson	1000
Mike	Wayne	1
Liz	Lemon	20
Liz	Meyers	5
Bruce	Jackson	1

How to accomplish this in SQLite? This is a toy example, my application more columns and data types and hundreds of million of rows. If the solution is easily extensible to more/less columns, that's even better.

Tuples are ordered lexicographically, meaning that the sequences are ordered the same as their first differing elements. For example, (1,2,x) < (1,2,y) returns the same as x < y.

It's worth noting that SQL-92 (and mysql, oracle, postresql) implements this correctly. The standard uses "row value constructor" to denote what I'm calling a tuple. The behavior is defined in excruciating detail in part 8.2.7, page 209.

SQL to create the example:

create table mytable ( First char(20), Last char(20), Score int );
insert into mytable values ('Liz', 'Jordan', 2);
insert into mytable values ('John', 'Jordan', 2);
insert into mytable values ('Liz', 'Lemon', 10);
insert into mytable values ('Mike', 'Taylor', 100);
insert into mytable values ('John', 'Jackson', 1000);
insert into mytable values ('Mike', 'Wayne', 1);
insert into mytable values ('Liz', 'Lemon', 20);
insert into mytable values ('Liz', 'Meyers', 5);
insert into mytable values ('Bruce', 'Jackson', 1);
create unique index 'UNIQ' on mytable (First, Last, Score);

Cranach

sqlite now has tuples, but be aware that if any value is null then it may behave differently than expected. – Ratoon

Answer 1

SQLite doesn't support tuple comparisons. But a row constructor is a kind of shorthand. You can get the same result with a more complicated WHERE clause. I've omitted the LIMIT 1 clause to make it easier to see that both queries return the same set. (On platforms that support row constructors, that is.)

This comparison

ROW(a,b) <= ROW(c,d) 

is equivalent to

a < c OR (a = c AND b <= d)

And you can extend that to as many columns as you need.

SELECT First, Last, Score
FROM mytable
WHERE
      (('John' < First) OR 
       ('John' = First AND 'Jordan' < Last) OR 
       ('John' = First AND 'Jordan' = Last AND 5 <= Score))
  AND ((First < 'Mike') OR 
       (First = 'Mike' AND Last < 'Taylor') OR 
       (First = 'Mike' AND Last = 'Taylor' AND Score <= 50))
ORDER BY First, Last, Score

Liz  Jordan  2
Liz  Lemon  10
Liz  Lemon  20
Liz  Meyers  5

I did not test this with NULLs in the data.

---

As of 2018, SQLite does support tuple comparison. The OP's query produces the expected output using the SQL statements provided. This way of writing the query also works. (I find between ... and ... more readable.)

SELECT First, Last, Score
FROM mytable
WHERE (First, Last, Score ) between ('John',  'Jordan',  5) and ('Mike',  'Taylor',  50) 
ORDER BY First, Last, Score
Limit 1

I don't know how long ago this was introduced.

Gastight

I wonder if the mysql optimizer is smart enough to know this is the same as the tuple syntax. – Cranach

Outdated? according to this site, SQLite supports tuple comparisons: sql-workbench.net/dbms_comparison.html – Secular

row values support added on 2016-10-14 for release 3.15.0, with several fixes later on, last one on 2018-04-02 (3.23.0). See sqlite.org/changes.html for details. – Forwardlooking

Answer 2

I've been circumventing the lack of tuple comparison by using string concatenation (||) and a character sequence to ensure the fields don't "merge" and cause incorrect matches (-).

(First, Last, Score) <= ('Mike',  'Taylor',  50)

becomes

First||' - '||Last||' - '||Score <= 'Mike'||' - '||'Taylor'||' - '||'50'

or

First||' - '||Last||' - '||Score <= 'Mike - Taylor - 50'

so your SELECT would be

SELECT First, Last, Score
FROM mytable
WHERE
    'John - Jordan - 5' <= First||' - '||Last||' - '||Score
    AND First||' - '||Last||' - '||Score <= 'Mike - Taylor - 50'
ORDER BY First, Last, Score
LIMIT 1

String concatenation is pretty costly and less terse but it works the same way and looks very similar.

Duello

This is general-purpose only if you escape the concatenated strings. Do you think this has better performance than @catcall's answer? – Cranach

Answer 3

Sqlite's Raw value added in 3.15.0: https://sqlite.org/rowvalue.html

Alcock

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Chiton