TABLESAMPLE REGIONS() with unsigned bigint primary key returns unordered result

[kennytm]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Populate data:

CREATE TABLE a (pk bigint unsigned primary key clustered, v text);
INSERT INTO a WITH RECURSIVE b(pk) AS (SELECT 1 UNION ALL SELECT pk+1 FROM b WHERE pk < 1000) SELECT pk, 'a' FROM b;
INSERT INTO a WITH RECURSIVE b(pk) AS (SELECT 1 UNION ALL SELECT pk+1 FROM b WHERE pk < 1000) SELECT pk + (1<<63), 'b' FROM b;
SPLIT TABLE a BY (500);

Perform the tablesample regions() query:

SELECT * FROM a TABLESAMPLE REGIONS() ORDER BY pk;

2. What did you expect to see? (Required)

+---------------------+------+
| pk                  | v    |
+---------------------+------+
|                 500 | a    |
| 9223372036854775809 | b    |
+---------------------+------+

3. What did you see instead (Required)

+---------------------+------+
| pk                  | v    |
+---------------------+------+
| 9223372036854775809 | b    |
|                 500 | a    |
+---------------------+------+

The `pk` is ordered like a signed integer.

EXPLAINing this shows the ORDER BY is eliminated. So the fix should be applied to the TableSample object itself.

mysql> explain SELECT * FROM a TABLESAMPLE REGIONS() ORDER BY pk;
+---------------+---------+------+---------------+---------------+
| id            | estRows | task | access object | operator info |
+---------------+---------+------+---------------+---------------+
| TableSample_9 | 1.00    | root |               |               |
+---------------+---------+------+---------------+---------------+

4. What is your TiDB version? (Required)

v7.6.0-alpha-nightly-20231101

Release Version: v7.6.0-alpha
Edition: Community
Git Commit Hash: c652a92df890196ad1e956da3a6afa7abf71adfb
Git Branch: heads/refs/tags/v7.6.0-alpha
UTC Build Time: 2023-11-01 14:21:14
GoVersion: go1.21.3
Race Enabled: false
Check Table Before Drop: false
Store: tikv 

(Also reproduced on v7.3.0 and v6.5.2. Should affect every TiDB version since TABLESAMPLE REGIONS() was implemented)

[kennytm]

BTW is it ok to add a hidden environmental switch or something to br/pkg/version/version.go or Dumpling CLI and other the tools (primarily sync-diff-inspector) to treat the database as if vanilla MySQL? The fragile usage of TABLESAMPLE REGIONS() (e.g. #27349) and stats-bucket decoding (e.g. pingcap/tidb-tools#623) and other TiDB-specific APIs for chunking has caused quite a significant number of bugs on the tools, and currently there is no escape hatch to force Dumpling falling back to the generic, slower but reliable algorithms.

[kennytm]

BTW this can be workedaround on the client side by forcing a Sort operation:

SELECT * FROM a TABLESAMPLE REGIONS() ORDER BY coalesce(pk)
--                                             ^~~~~~~~

+-------------------------+---------+------+---------------+----------------------------------------------------+
| id                      | estRows | task | access object | operator info                                      |
+-------------------------+---------+------+---------------+----------------------------------------------------+
| Projection_7            | 2000.00 | root |               | test.a.pk, test.a.v                                |
| └─Sort_4                | 2000.00 | root |               | Column#3                                           |
|   └─Projection_8        | 1.00    | root |               | test.a.pk, test.a.v, coalesce(test.a.pk)->Column#3 |
|     └─TableSample_6     | 1.00    | root |               |                                                    |
+-------------------------+---------+------+---------------+----------------------------------------------------+