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 |               |                                                    |
+-------------------------+---------+------+---------------+----------------------------------------------------+

[JasonWu0506]

/type regression

[JasonWu0506]

/type remove regression

[ti-chi-bot]

@JasonWu0506: The label(s) type/remove cannot be applied, because the repository doesn't have them.

In response to this:

/type remove regression

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the ti-community-infra/tichi repository.

[JasonWu0506]

/remove-type regression