window function row_number somtimes return wrong row number

[chrissata]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.
   1) PREPARE TABLE AND DATA...
   CREATE TABLE testtable (
   id bigint(20) NOT NULL AUTO_INCREMENT,
   deleted smallint(6) NOT NULL,
   m_id bigint(20) DEFAULT NULL,
   title varchar(250) NOT NULL,
   seq bigint(20) DEFAULT '0',
   val varchar(64) DEFAULT '31',
   online smallint(6) DEFAULT '1',
   PRIMARY KEY (id),
   KEY ix_testtable_deleted (deleted),
   KEY testtable_mid_seq (m_id,seq)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO testtable VALUES (1000,0,1001,'last',330,'330',1),(1001,0,1001,'',329,'329',1),(1002,0,1001,'',328,'328',1),(1003,0,1001,'',327,'328',1),(1004,0,1001,'',326,'328',1),(1005,0,1001,'',325,'328',1),(1006,0,1001,'x',324,'328',1),(1007,0,1001,'x',323,'328',1),(1008,0,1001,'x22',322,'328',1),(1009,0,1001,'x21',321,'328',1),(1010,0,1001,'x20',320,'328',1),(1011,0,1001,'x19',319,'328',1),(1012,0,1001,'x18',318,'328',1),(1013,0,1001,'x17',317,'328',1),(1014,0,1001,'x16',316,'328',1),(1015,0,1001,'x15',315,'328',1),(1016,0,1001,'x14',314,'328',1),(1017,0,1001,'x13',313,'328',1),(1018,0,1001,'x12',312,'328',1),(1019,0,1001,'x11',311,'328',1),(1020,0,1001,'x10',310,'328',1),(1021,0,1001,'x09',309,'328',1),(1022,0,1001,'x08',308,'328',1),(1023,0,1001,'x08',307,'328',1),(1024,0,1001,'x07',307,'328',1),(1025,0,1001,'x06',306,'328',1),(1026,0,1001,'x04',305,'328',1),(1027,0,1001,'x044',304,'328',1),(1028,0,1001,'x03',303,'328',1),(1029,0,1001,'x02',302,'328',1),(1030,0,1001,'x01',301,'328',1),(1031,0,1001,'x00',300,'328',1),(1032,0,1001,'x299',299,'328',1),(1033,0,1001,'x298',298,'328',1),(31034,0,1000,'abc',390,'399',1),(31035,0,1000,'abcd',391,'399',1),(31036,0,1000,'abcde',377,'309',1),(31037,0,1000,'abcde',499,'309',1),(31038,0,1000,'abcdef',499,'309',1),(31039,0,1000,'abcdefg',499,'319',1),(31040,0,1003,'aa',298,'328',1),(31041,0,1003,'aa',298,'328',1),(31042,0,1003,'aa',298,'328',1),(31043,0,1004,'a4',300,'328',1),(31044,0,1004,'a4',298,'328',1);

2) do query.
SELECT p.id, p.title, p.seq, p.m_id, p.val, row_number() over(PARTITION BY p.m_id ORDER BYp.seq DESC) AS row_num FROM testtable p WHERE p.deleted = 0 AND p.online = 1 AND p.m_id IN (1000,1001, 1002,1003,1004);

2. What did you expect to see?
   for records with m_id=1003, there row_num are correct.

3. What did you see instead?

+-------+---------+------+------+------+----------+
| id | title | seq | m_id | val | rank_num |
+-------+---------+------+------+------+----------+
| 31037 | abcde | 499 | 1000 | 309 | 1 |
| 31038 | abcdef | 499 | 1000 | 309 | 2 |
| 31039 | abcdefg | 499 | 1000 | 319 | 3 |
| 31035 | abcd | 391 | 1000 | 399 | 4 |
| 31034 | abc | 390 | 1000 | 399 | 5 |
| 31036 | abcde | 377 | 1000 | 309 | 6 |
| 1000 | last | 330 | 1001 | 330 | 1 |
| 1001 | | 329 | 1001 | 329 | 2 |
| 1002 | | 328 | 1001 | 328 | 3 |
| 1003 | | 327 | 1001 | 328 | 4 |
| 1004 | | 326 | 1001 | 328 | 5 |
| 1005 | | 325 | 1001 | 328 | 6 |
| 1006 | x | 324 | 1001 | 328 | 7 |
| 1007 | x | 323 | 1001 | 328 | 8 |
| 1008 | x22 | 322 | 1001 | 328 | 9 |
| 1009 | x21 | 321 | 1001 | 328 | 10 |
| 1010 | x20 | 320 | 1001 | 328 | 11 |
| 1011 | x19 | 319 | 1001 | 328 | 12 |
| 1012 | x18 | 318 | 1001 | 328 | 13 |
| 1013 | x17 | 317 | 1001 | 328 | 14 |
| 1014 | x16 | 316 | 1001 | 328 | 15 |
| 1015 | x15 | 315 | 1001 | 328 | 16 |
| 1016 | x14 | 314 | 1001 | 328 | 17 |
| 1017 | x13 | 313 | 1001 | 328 | 18 |
| 1018 | x12 | 312 | 1001 | 328 | 19 |
| 1019 | x11 | 311 | 1001 | 328 | 20 |
| 1020 | x10 | 310 | 1001 | 328 | 21 |
| 1021 | x09 | 309 | 1001 | 328 | 22 |
| 1022 | x08 | 308 | 1001 | 328 | 23 |
| 1024 | x07 | 307 | 1001 | 328 | 24 |
| 1023 | x08 | 307 | 1001 | 328 | 25 |
| 1025 | x06 | 306 | 1001 | 328 | 26 |
| 1032 | aa | 298 | 1001 | 328 | 27 |
| 31040 | aa | 298 | 1003 | 328 | 28 |
| 31041 | aa | 298 | 1003 | 328 | 1 |
| 31042 | aa | 298 | 1003 | 328 | 2 |
| 31043 | a4 | 300 | 1004 | 328 | 1 |
| 31044 | a4 | 298 | 1004 | 328 | 2 |
+-------+---------+------+------+------+----------+
38 rows in set (0.01 sec)

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

mysql> select version();
+--------------------+
| version() |
+--------------------+
| 5.7.25-TiDB-v3.0.1 |
+--------------------+
1 row in set (0.00 sec)

mysql> show variables like '%sql_mode%';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

[alivxxx]

Could you point out why is it wrong? And did you post the wrong result since the result column is rank_num, not row_num?

[chrissata]

Hi,
The error is that, for one of the returned records, of which the id is 31040, the final row_number column value should be 1 rather than 28.
| 31040 | aa | 298 | 1003 | 328 | 28 |

The column name "rank_num" is just an so-called alias name which has nothing to do with the result since I used "row_number() over " in my query which is pretty clear.

[zimulala]

PTAL @lamxTyler