带有PARTITION BY子句的ROW_NUMBER()已停止在MariaDB上工作

Question

我在生产服务器上使用一个带有此SQL查询的应用程序已经超过3年了，没有对查询或使用的MariaDB服务器的主要版本进行任何修改。它突然停止工作，我不知道为什么。

查询：

SELECT 
c.* 
FROM 
contestants c 
WHERE 
(
date BETWEEN '2022-08-01 00:00:00' 
AND '2022-08-31 23:59:59'
) 
AND (winner = 0) 
ORDER BY 
ROW_NUMBER() OVER (
PARTITION BY email 
ORDER BY 
RAND()
) 
LIMIT 
10;

预期行为：从contestants表中随机返回10名获胜者(该表包含提交的比赛代码，每个参赛者在该表中可以有多行(，每个email只能出现一次，只能从没有任何获胜代码的参赛者中选择(winner=1(。同一查询被执行多次(多轮奖金(，此查询确保每个电子邮件每月只能赢得一次奖金。在过去的3年多里，这就像一种魅力。

当前行为：查询似乎只是通过升序返回10名参赛者(ID 1,2,3，…(。

---

如果我在不同的web服务器(本地服务器或我的托管提供商之一(上运行查询，它运行良好。我试过MariaDB 10.3、10.5和MySQL 8.0。所有这些都有效。

我的生产服务器正在运行MariaDB 10.3.34，这似乎是2022年2月的安全更新。然而，我尝试在运行MariaDB 10.9.2的同一服务器上创建另一个数据库，结果是一样的。

这个查询突然对我停止工作，并且在不同的环境中都能很好地工作，这可能是什么问题？

---

编辑：以下是一些可以尝试的示例数据：

CREATE TABLE `contestants` (
`EntryID` int(11) DEFAULT NULL,
`firstName` varchar(255) DEFAULT NULL,
`lastName` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`phone` varchar(255) DEFAULT NULL,
`code` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`psc` varchar(255) DEFAULT NULL,
`date` datetime DEFAULT NULL,
`winner` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `contestants` (`EntryID`, `firstName`, `lastName`, `email`, `address`, `phone`, `code`, `city`, `psc`, `date`, `winner`) VALUES
(1, 'TEZvbGgxVW9oQkphNmdWaGpOdXJGZz09', 'OTRmVzZ3NjVkUUJIVzlZRUw4a0RzQT09', 'QTJlSHNwcWg2TEkrSFNpc05BcFBFcTh5YWFqcHZPekkrWCtzOWw1TWVtRT0=', 'T2J4cFl2eEwxS1R3YTJkcHpjbkx3Zz09', 'ZUI0U3o5djFXbHkrZk5KRGVXK25FUT09', 'WlJBSTl1VnFyRGk0UlBsSFRCT0pLQT09', 'Q094aGRURm1VODRsS0FrK2RYdGxNZz09', 'ZTJoaituNkJtZ0Yvc2EzamRqZmhJUT09', '2022-08-18 00:00:00', 0),
(2, 'aGZZK0dvbSs1UTIxN3VGcGxKdEZuZz09', 'VFE2VHRGaktnV0l1b21uemlUbytUUT09', 'dEIvbTI5OEhHZG5IdSsxMjVKUXpKZVlkYkVkcy9PQU00RnhsRUx0RnZIdz0=', 'KzgxQkVOb0xSNDkzRGFXcnhpM3Rtdz09', 'ZmJDMzE0V3UzaWxEcE9veXNtUFpZZz09', 'RlY3a01MSktZRlptYnhwdVJqamI1UT09', 'dXlFRmRSY25CMDBnb3REdVhFN3dPZz09', 'ZWozRXJ5VUk2bUppU2NuMmZoVm9CZz09', '2022-08-19 00:00:00', 0),
(3, 'OERRNUVCank2aHBRRVdkVjJPZmNsUT09', 'OC80TStVdlFwTi9sbGFGQVNHcEZTZz09', 'SW9nVVZ2SCtnQUg2TzRYMDBsNTBFeStReHdDZTZKUHNocStTU3FPMG1mZz0=', 'Y0ZNdXNQdGRIVEJKNzlLTDJPV3l2dz09', 'N1cyUHR0S3FPbUdUbGFmenh4ZHhFdz09', 'SHN5MjRFWTR2Zmx5R1QzVEsyMVRBUT09', 'ZjVVN3ZSYnZNdmY2cHlqR080c1JzUT09', 'OFpPcEM5R1NJTHh2eFh1N0tMQzNGdz09', '2022-08-19 00:00:00', 0),
(4, 'WFFKYTVQR3lRZFA3eDdvR21KbGZaUT09', 'ZjVhTi95RW5kang1Q0JqU3dENkhjQT09', 'ZEhJZFlwNlJrSERneW5vMytkSU9qUmdtc1p2YlAxQWhjc2hxNzFIT0JBWT0=', 'UUtlVy9VMzlHVytpWnYvQTNLU1d0QT09', 'M0h5Mm5GNUw2cHUwbFV2K1Z4MWw3Zz09', 'WURMTjZNOWxpcm9JOFZkMWJQQUR4UT09', 'M0xxRkhpeEthTnZPWitzN3BxbTdrZz09', 'Q0dVMGE1VEkzRmxCSnN4ak5LYit6Zz09', '2022-08-22 00:00:00', 0),
(5, 'VVhBZGNXaUQzdkFpd1F2WVBSR3RQQT09', 'TnFJdHRURkRoUkYzMzlWeStva1hqZz09', 'WE84ZDRkNkNvNkRaZ1M3VGQ0MzVxV3FQRlFiNWNCWkZPZFA3WjJ6cWRHOD0=', 'NjRuMlhiVDBWejJjOTlRT1A1dmlTZz09', 'ZHRibW9VK09sZVpDTXlKTDVrRkhGZz09', 'akpwaWlLaVZIOXlvRFNJTHIxVzFTZz09', 'dXlFRmRSY25CMDBnb3REdVhFN3dPZz09', 'bzJxbGJ5cU5mbXlVekNYdDdQTVc4UT09', '2022-08-22 00:00:00', 0),
(6, 'MDRlV0RZKzd2MkdEcnI5QWJjWXdHZz09', 'YWpKYTBqYmxieHdMaXEzd0VvYzJ2QT09', 'TGFLZ0lLMFBkKzZSSmxXd0FGOHFJR2xiYWZxdEJjMlBLZlJUUnNFbzVtRT0=', 'RFlkUkhMT0k5REJsang3V3Fyem9kZz09', 'ZytScDc5V2VyVnJsT0FmaWhkWFphZz09', 'UFJXVzBISXc1QVFHSm1WaC9aMjRZdz09', 'RFlkUkhMT0k5REJsang3V3Fyem9kZz09', 'a3hqcFV4NWVFNXZ3TU1rNWcvblNnUT09', '2022-08-22 00:00:00', 0),
(7, 'QzY4dzA5RDNERmNrTUk1R0pad1RCdz09', 'eWQ3aVBYUi9wcndNbDBQdk42a1l5UT09', 'LzhvcHFQMzh4NVV6a3FqQmZqbXk3OGpJa0FzS1N4a29rYmQrUER0Ny9Ybz0=', 'U2J2RWl4cGVOU3lkMkl0dnFrZjZUUT09', 'OWt1RHJ1RFZIUlltRUZPb2ZTUEhWdz09', 'bU9lUjd2SXJ5ZitKY2JmUFVsVjE5QT09', 'WHg4dmtyclF1OGtBL2NhRW9SalpIRDY2UmY3c0ptVUJvQm1SSWZHNWlTOD0=', 'RVR0SjVqNXhwam42UkZXajhITFNaZz09', '2022-08-23 00:00:00', 0),
(8, 'eEw4cWlhc09NVDMrRUp0WmpmdjBTQT09', 'YkYxRENEU3N2NEh6aDJoRjJwcUxFZz09', 'b3g2alU3ZDhPczFrdjRrMnRpSXZYZ1dRWXZLRFdEdXYvQ2VRZ2RDREsrST0=', 'RVZVQXNTd3ZsL2V3ajVQamdUaXFaZz09', 'ZGdIdGtYUjh3cmIvRnQ3V29mdy9lZz09', 'dHEzU3BualdSUTFySTFXVzloSFZiUT09', 'RnJreUhTekQ0YzZtWjVDUEc3R0Z6dz09', 'TUxOaUZvOFFQcGF2RTUvTmtQRjFDZz09', '2022-08-23 00:00:00', 0),
(9, 'REZXWXQ0UWxTV3k0N2NkdXJYZFI5Zz09', 'dUZKTGFiVG5Kb2FMTFBuTzlJUzRFZz09', 'QjJvbzhqVm9EZFQ5ZnlWVStDVjRKejh0T2FDRWpYdXdBZzUyQkQ0OUF1ND0=', 'UWQ3eDNsL2VwVFNRUTQvL05qQ3RyUT09', 'NU1Zb21QeVR1bmdrRXQ5aHBLRGtLZz09', 'SXZzQmRrRDFjbzYvaUMwWnRtTkgvQT09', 'M0xxRkhpeEthTnZPWitzN3BxbTdrZz09', 'bzJxbGJ5cU5mbXlVekNYdDdQTVc4UT09', '2022-08-23 00:00:00', 0),
(10, 'RFoxSXpUUEswRFZFUHdkaVZtSGtPZz09', 'VUxqd1o2ZnB5LzFqSUtiUUoyanFtZz09', 'b1VtQVgyZ0VOUnI0MWlEc1JqTmZZNjA0ZnlZcWJWU24wL0V6dWxyYTg5cz0=', 'WnErRS9MNGs3bkEwcnJxOThmVllCQT09', 'NU14VXRMelpYU3J2QThvUzNzLzBRdz09', 'cWMzM0dkY3NzR2sxNlVqVGFhRmQydz09', 'b1dkRStMcmg5RlVINWt6ZXprbmF3K0FyODlpVnFhSUZtaXJldEVaUnpjTT0=', 'ZWozRXJ5VUk2bUppU2NuMmZoVm9CZz09', '2022-08-23 00:00:00', 0),
(11, 'WG5LRVV6eTlmSU1Ha0JRRGllYWpkZz09', 'cC83K3B4ejVQSjB6bmVaWnppL1F0UT09', 'WmVhZHM4NnQvZjdNRnM5TElEcHhZRUF3MmxPalhVM1VIZFU3UldrMzdndz0=', 'Ymg5eHZ0dlhMYzlLVXdJV3N1YndFZz09', 'WTBIdnVTQ25JRlFRT2Rid1BCeVMxQT09', 'OUROelVUbHZZU0MxdGdsZ1NyUldaUT09', 'dXlFRmRSY25CMDBnb3REdVhFN3dPZz09', 'bmVHNlc0bGFkREphVmxhNHpNMlBvdz09', '2022-08-24 00:00:00', 0),
(12, 'MnNNdjdKbm5qZDhuQzlEQWR3Y3FGQT09', 'RHl2ZFZYOUlsYWs1ZGVtU0JpSEtUQT09', 'T0hWdktudmw4Q2Z1dkFzaFZQOXhPeUJRdm0yMHNpMUJKakVIUmg0VmJpTT0=', 'dWZ0QnVwTm5ieFc2dVBFSEdzUmZ2WkxkTmZWdUZWSFkxWHV3bVBuNUdlQT0=', 'U2gxYk1CZVh6RlQ1QXFtandkblU5dz09', 'MGloTXdVQ2RPNVE4b00weWhIRU5RZz09', 'dXlFRmRSY25CMDBnb3REdVhFN3dPZz09', 'cnJyRmk2dTlWZzRVYVg0NzVNUXVDQT09', '2022-08-24 00:00:00', 0),
(13, 'MnNNdjdKbm5qZDhuQzlEQWR3Y3FGQT09', 'RHl2ZFZYOUlsYWs1ZGVtU0JpSEtUQT09', 'T0hWdktudmw4Q2Z1dkFzaFZQOXhPeUJRdm0yMHNpMUJKakVIUmg0VmJpTT0=', 'dWZ0QnVwTm5ieFc2dVBFSEdzUmZ2WkxkTmZWdUZWSFkxWHV3bVBuNUdlQT0=', 'U2gxYk1CZVh6RlQ1QXFtandkblU5dz09', 'TmhXdDgyYnl1VVA1YTdSNUV3MVJNQT09', 'dXlFRmRSY25CMDBnb3REdVhFN3dPZz09', 'cnJyRmk2dTlWZzRVYVg0NzVNUXVDQT09', '2022-08-24 00:00:00', 0),
(14, 'SXUwa0xDcUJZWnFhUmkreVpwRU9WZz09', 'U0RCS3B0ajJzYVVpczFHRWxhS2I3dz09', 'cVkxRlhYeE04NDgyRFJXWHlPTVFmVndxZW1sNmg5bWNleEhTSzE2bUk2TT0=', 'T3ZiM2JZaURzdnEwby8rWHhCT1Nldz09', 'eStPaWtERWJmTlV4WGNqTzNRMDlPQT09', 'a2QrazdHL2NQU1d6YWcwMTJ6c0JRZz09', 'Z1VXS05LdEdSY0d0bk1HQWg1NHBjdz09', 'TGhOdHowYmhhWEdYSE5ua1RuZUg0QT09', '2022-08-25 00:00:00', 0),
(15, 'L0xyVjZBNVY1OHZ6bFhyZXY3dkFBdz09', 'Z3JSUlpYRzM5VTN4L2ZZUG9pOHhlUT09', 'bUR2L2NlYWNGS1V6cWFuSjVyOEFtd2tGb1pRWmRmaG1TbGllaU1DbnEzRT0=', 'YUcvbjk1NXNLREJwV0YyMWFLQTN5ZFhlcHJqd3FQMXpzYmd4T0xGZmd0WT0=', 'VGdva2NUclQrdHMvOUdEcDRXeEVtUT09', 'MlFVaDRQc0hZV3ZkZUJHY0FpRkpTZz09', 'RzV5MEpHUmtKNlFtZWErQ3U4cFNpdz09', 'YlRiR1h3b01ma3BVRHVURXdwSUd0dz09', '2022-08-25 00:00:00', 0),
(16, 'WmJTSVk0WUlsTCtpeWV3bjFhZUVYdz09', 'Y1lxc0gzS1NDZjQxNkEwK0RmUTJqUT09', 'MlZpWVlvbXk5bDk3UHQxZGNJaWd4NGk4d2NLS21XdXhHYzFNWGJJblFQWT0=', 'TVFOWko1R2xMaGQvNXA0K3lWNGtFUT09', 'cXJSK0dVeUtzSW80dkdIM0xDWUxZQT09', 'aGdnNkdDSDRtdm9mK0VBcjR0YUtUZz09', 'M29zUndtMDRoRUZoQ2tSRHNTZ3grN010VzNBMmV6WGsrQytTY2E5Z2JTST0=', 'M1AzQ2cySEJtbUFyQWFUVkQyLzJsUT09', '2022-08-25 00:00:00', 0),
(17, 'SDd6OE1YUFBBeHUwQWJuM1NVT1VsQT09', 'enhSdDZycC9VTGlvc2IwT0Z2SjRXQT09', 'UTJmWHBkeDhNVWQwa1h5L1R0Nisza1ZPakRxMHVTa2VnNHhCVWh2dDJMVT0=', 'b1ppUzhIMUJyVVlKVEVDYi9pWElnK1FLaVRjaTVibll4T1hsY0ZxWjFTdz0=', 'c045YlkyREtuZXA0K3UwOWRzQnBTdz09', 'S0NkWWYvTUphSVNIb2hjMjdIbHpFdz09', 'MmlXWUovanpYSUhOQVl3dkRPWE5IQT09', 'VWFvcXQ1VXEvaWRQd0pPUW1WRjdRUT09', '2022-08-25 00:00:00', 0),
(18, 'SDd6OE1YUFBBeHUwQWJuM1NVT1VsQT09', 'enhSdDZycC9VTGlvc2IwT0Z2SjRXQT09', 'UTJmWHBkeDhNVWQwa1h5L1R0Nisza1ZPakRxMHVTa2VnNHhCVWh2dDJMVT0=', 'b1ppUzhIMUJyVVlKVEVDYi9pWElnK1FLaVRjaTVibll4T1hsY0ZxWjFTdz0=', 'c045YlkyREtuZXA0K3UwOWRzQnBTdz09', 'bmZBUktiNEtLNHgvQVlMWHV6K1lVUT09', 'MmlXWUovanpYSUhOQVl3dkRPWE5IQT09', 'VWFvcXQ1VXEvaWRQd0pPUW1WRjdRUT09', '2022-08-25 00:00:00', 0),
(19, 'ZFhkQ09FVmtoR3lIMWpaSW9SSkE4Uk5iSXZ1Q2J3VHhYNFZubkE4cThSVT0=', 'K2ljc09hTzZoVkJYY3Y3NHdzeDZFQT09', 'ZFhkQ09FVmtoR3lIMWpaSW9SSkE4Uk5iSXZ1Q2J3VHhYNFZubkE4cThSVT0=', 'YlNETTlqSmVPSkluSVJpZXc0eXRNQ2FKVWRkMUs3aXY3UlBtWTUvRVZJbz0=', 'UDIrdEJ1cDlXZDVyd2hQVFNMYis3Zz09', 'SXdVQ2xKd2tXNmE1QXUyaHkzS1pwdz09', 'dXlFRmRSY25CMDBnb3REdVhFN3dPZz09', 'ZWozRXJ5VUk2bUppU2NuMmZoVm9CZz09', '2022-08-25 00:00:00', 0),
(20, 'SDd6OE1YUFBBeHUwQWJuM1NVT1VsQT09', 'enhSdDZycC9VTGlvc2IwT0Z2SjRXQT09', 'UTJmWHBkeDhNVWQwa1h5L1R0Nisza1ZPakRxMHVTa2VnNHhCVWh2dDJMVT0=', 'b1ppUzhIMUJyVVlKVEVDYi9pWElnK1FLaVRjaTVibll4T1hsY0ZxWjFTdz0=', 'c045YlkyREtuZXA0K3UwOWRzQnBTdz09', 'YS9jKzJTcldUMlFoT0xmTjVJVzVmQT09', 'MmlXWUovanpYSUhOQVl3dkRPWE5IQT09', 'VWFvcXQ1VXEvaWRQd0pPUW1WRjdRUT09', '2022-08-25 00:00:00', 0);
ALTER TABLE `contestants`
ADD KEY `idx_email` (`email`);
COMMIT;

Answer 1

查询查询中没有任何内容表明应随机选择电子邮件，您只能为每个电子邮件随机选择一条记录。这可能是幸运的，因为内部执行路径会在正确的时间导致随机排序，但查询从来没有保证能达到预期效果。

考虑以下示例数据(忽略日期(

获胜者<1>

	ID	电子邮件
1	a@b.c	0
2	a@b.c	0
3	d@e.f	0
4	d@e.f
5	x@y.z	0

考虑

DROP TABLE IF EXISTS T;
CREATE TABLE T
(EMAIL VARCHAR(3),WINNER INT);
INSERT INTO T VALUES
('AAA',0),('BBB',0),('CCC',0),('AAA',0);

限制为4

SELECT 
c.* ,ROW_NUMBER() OVER (PARTITION BY email ORDER BY RAND()) rn,
RAND(ROW_NUMBER() OVER (PARTITION BY email ORDER BY RAND())) rnrand
FROM 
T c 
WHERE 
#(
#  date BETWEEN '2022-08-01 00:00:00' 
#  AND '2022-08-31 23:59:59'
#) 
#AND 
winner = 0
ORDER BY ROW_NUMBER() OVER (PARTITION BY email ORDER BY RAND()) 
LIMIT 4;
+-------+--------+----+---------------------+
| EMAIL | WINNER | rn | rndand              |
+-------+--------+----+---------------------+
| BBB   |      0 |  1 | 0.40540353712197724 |
| CCC   |      0 |  1 | 0.40540353712197724 |
| AAA   |      0 |  1 | 0.40540353712197724 |
| AAA   |      0 |  2 |  0.6555866465490187 |
+-------+--------+----+---------------------+
4 rows in set (0.001 sec)

限制2

+-------+--------+----+---------------------+
| EMAIL | WINNER | rn | rnrand              |
+-------+--------+----+---------------------+
| BBB   |      0 |  1 | 0.40540353712197724 |
| CCC   |      0 |  1 | 0.40540353712197724 |
+-------+--------+----+---------------------+
2 rows in set (0.001 sec)