Description:
SET values sort by their numeric bitmask. For a SET declared with all 64
members, a value containing member 64 has a bitmask of 2^63 or more. ORDER BY
on the column sorts those values as if the bitmask were a signed 64-bit integer.
The three largest values in the example below sort BELOW the empty set, and
ORDER BY v DESC LIMIT 1 returns the wrong row.
The same column sorts correctly when the order comes from an index on it
(FORCE INDEX), and ORDER BY CAST(v AS UNSIGNED) is correct. So the server's
own two access paths disagree on the order of the same column. A SET declared
62 or 63 wide is not affected. MariaDB sorts all of these correctly.
How to repeat:
CREATE TABLE ord(id INT PRIMARY KEY,
v SET('m1','m2','m3','m4','m5','m6','m7','m8','m9','m10','m11','m12','m13',
'm14','m15','m16','m17','m18','m19','m20','m21','m22','m23','m24','m25',
'm26','m27','m28','m29','m30','m31','m32','m33','m34','m35','m36','m37',
'm38','m39','m40','m41','m42','m43','m44','m45','m46','m47','m48','m49',
'm50','m51','m52','m53','m54','m55','m56','m57','m58','m59','m60','m61',
'm62','m63','m64')) ENGINE=InnoDB;
INSERT INTO ord VALUES (1,'m1'), (2,'m63'), (3,'m64'), (4,'m1,m64'),
(5,'m63,m64'), (6,'');
SELECT id, v, CAST(v AS UNSIGNED) FROM ord ORDER BY id;
1 m1 1
2 m63 4611686018427387904
3 m64 9223372036854775808
4 m1,m64 9223372036854775809
5 m63,m64 13835058055282163712
6 0
SELECT id FROM ord ORDER BY v DESC;
-- 2, 1, 6, 5, 4, 3 expected 5, 4, 3, 2, 1, 6
SELECT id FROM ord ORDER BY v ASC;
-- 3, 4, 5, 6, 1, 2 expected 6, 1, 2, 3, 4, 5
SELECT id FROM ord ORDER BY v DESC LIMIT 1;
-- 2 expected 5
SELECT id FROM ord ORDER BY CAST(v AS UNSIGNED) DESC;
-- 5, 4, 3, 2, 1, 6 correct
ALTER TABLE ord ADD KEY kv(v);
SELECT id FROM ord FORCE INDEX(kv) ORDER BY v DESC;
-- 5, 4, 3, 2, 1, 6 correct when the order comes from the index
The wrong order is exactly the signed reading of the bitmask:
13835058055282163712 -> -4611686018427387904, 9223372036854775809 ->
-9223372036854775807, 9223372036854775808 -> -9223372036854775808.
The gate is the 64th member. For each width the rows are
{top, m1+top, (top-1)+top, m1}, where top is the highest member:
SET width 62: ORDER BY v DESC -> 3,2,1,4 by bitmask 3,2,1,4 ok
SET width 63: ORDER BY v DESC -> 3,2,1,4 by bitmask 3,2,1,4 ok
SET width 64: ORDER BY v DESC -> 4,3,2,1 by bitmask 3,2,1,4 WRONG
No warnings are raised.
Suggested fix:
The sort key that filesort builds for a SET field should be the unsigned
bitmask. In sql/filesort.cc, sortlength() forces ENUM/SET fields to
INT_RESULT ("Sort enum and set fields as their underlying ints"), and
make_sortkey_from_item() then builds the INT_RESULT key with
copy_native_longlong(to, ..., item->int_sort_key(), item->unsigned_flag);
the Item_field for a SET column has unsigned_flag = false, so bit 63 is
taken as the sign. (Field_enum::make_sort_key() itself already passes
is_unsigned = true, which is presumably why the index order is correct.)
Passing unsigned for ENUM/SET fields there would fix it.
Same root as set_col+0 returning a negative value for member 64 (reported
separately; see also Bug #93246).
Description: SET values sort by their numeric bitmask. For a SET declared with all 64 members, a value containing member 64 has a bitmask of 2^63 or more. ORDER BY on the column sorts those values as if the bitmask were a signed 64-bit integer. The three largest values in the example below sort BELOW the empty set, and ORDER BY v DESC LIMIT 1 returns the wrong row. The same column sorts correctly when the order comes from an index on it (FORCE INDEX), and ORDER BY CAST(v AS UNSIGNED) is correct. So the server's own two access paths disagree on the order of the same column. A SET declared 62 or 63 wide is not affected. MariaDB sorts all of these correctly. How to repeat: CREATE TABLE ord(id INT PRIMARY KEY, v SET('m1','m2','m3','m4','m5','m6','m7','m8','m9','m10','m11','m12','m13', 'm14','m15','m16','m17','m18','m19','m20','m21','m22','m23','m24','m25', 'm26','m27','m28','m29','m30','m31','m32','m33','m34','m35','m36','m37', 'm38','m39','m40','m41','m42','m43','m44','m45','m46','m47','m48','m49', 'm50','m51','m52','m53','m54','m55','m56','m57','m58','m59','m60','m61', 'm62','m63','m64')) ENGINE=InnoDB; INSERT INTO ord VALUES (1,'m1'), (2,'m63'), (3,'m64'), (4,'m1,m64'), (5,'m63,m64'), (6,''); SELECT id, v, CAST(v AS UNSIGNED) FROM ord ORDER BY id; 1 m1 1 2 m63 4611686018427387904 3 m64 9223372036854775808 4 m1,m64 9223372036854775809 5 m63,m64 13835058055282163712 6 0 SELECT id FROM ord ORDER BY v DESC; -- 2, 1, 6, 5, 4, 3 expected 5, 4, 3, 2, 1, 6 SELECT id FROM ord ORDER BY v ASC; -- 3, 4, 5, 6, 1, 2 expected 6, 1, 2, 3, 4, 5 SELECT id FROM ord ORDER BY v DESC LIMIT 1; -- 2 expected 5 SELECT id FROM ord ORDER BY CAST(v AS UNSIGNED) DESC; -- 5, 4, 3, 2, 1, 6 correct ALTER TABLE ord ADD KEY kv(v); SELECT id FROM ord FORCE INDEX(kv) ORDER BY v DESC; -- 5, 4, 3, 2, 1, 6 correct when the order comes from the index The wrong order is exactly the signed reading of the bitmask: 13835058055282163712 -> -4611686018427387904, 9223372036854775809 -> -9223372036854775807, 9223372036854775808 -> -9223372036854775808. The gate is the 64th member. For each width the rows are {top, m1+top, (top-1)+top, m1}, where top is the highest member: SET width 62: ORDER BY v DESC -> 3,2,1,4 by bitmask 3,2,1,4 ok SET width 63: ORDER BY v DESC -> 3,2,1,4 by bitmask 3,2,1,4 ok SET width 64: ORDER BY v DESC -> 4,3,2,1 by bitmask 3,2,1,4 WRONG No warnings are raised. Suggested fix: The sort key that filesort builds for a SET field should be the unsigned bitmask. In sql/filesort.cc, sortlength() forces ENUM/SET fields to INT_RESULT ("Sort enum and set fields as their underlying ints"), and make_sortkey_from_item() then builds the INT_RESULT key with copy_native_longlong(to, ..., item->int_sort_key(), item->unsigned_flag); the Item_field for a SET column has unsigned_flag = false, so bit 63 is taken as the sign. (Field_enum::make_sort_key() itself already passes is_unsigned = true, which is presumably why the index order is correct.) Passing unsigned for ENUM/SET fields there would fix it. Same root as set_col+0 returning a negative value for member 64 (reported separately; see also Bug #93246).