mysql - 带有 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 年多的时间里，这就像一个魅力。

当前行为:查询似乎只是通过提升其他(ID 1、2、3，...)返回 10 个参赛者。

---

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

我的生产服务器正在运行 MariaDB 10.3.34，这似乎是 02/2022 的安全更新。但是，我尝试在运行 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

query query 中没有任何内容表明应该随机选择电子邮件，您只能为每封电子邮件随机选择一条记录。由于内部执行路径会在正确的时间进行随机排序，这可能是幸运的，但从未保证查询会按照您的预期进行。

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

<表类="s-表"><头>身份证电子邮件获胜者<正文>1a@b.c02a@b.c03d@e.f04d@e.f15x@y.z0

如果您要将行号分配给新列并应用过滤器，根据 rand 函数的结果，您将获得行号函数的两个可能结果:

<表类="s-表"><头>身份证电子邮件获胜者RN1RN2<正文>1a@b.c0122a@b.c0213d@e.f0115x@y.z011

你在这里遇到的问题是，你没有进一步的排序，所以虽然你可以确保第 1 行出现在第 2 行之前(反之亦然)，其中电子邮件是相同的，但没有任何排序逻辑可以指示任何其他行的顺序。您可能需要对结果进行进一步排序，例如

ORDER BY 
  ROW_NUMBER() OVER (
    PARTITION BY email 
    ORDER BY 
      RAND()
  ),
  RAND() 

上面的另一个问题是，如果你选择前 10 名，不管你做什么，你都会得到 a@b.c两次，因为只有 4 条记录可供选择。

我也不认为您的查询反射(reflect)了以下标准(强调我的):

only choose from contestants that don't have any winning code (winner = 1).

在我给出的示例数据中，我认为 d@e.f就是这样的例子 - 一排有一个获胜代码，但他们有另一排还不是获胜者，因此仍然有资格根据当前规则在随后的一轮奖品中被选中。

如果以上是一个问题，那么我认为您需要以下内容:

SELECT  c.ID, c.Email
FROM    (   SELECT  c.ID, 
                    c.Email,
                    MAX(c.winner) OVER(PARTITION BY c.email) AS HasWinner,
                    ROW_NUMBER() OVER(PARTINTION BY c.email ORDER BY RAND()) AS RowNumber
            FROM    contestants AS c
            WHERE   c.date >= '2022-08-01'
            AND     c.date < '2022-09-01'
        ) AS c
WHERE   c.HasWinner = 0 -- No Winners
AND     c.RowNumber = 1 -- First Row per Email
ORDER BY RAND()
LIMIT 10;
                
                

Example on DB-Fiddle

如果这不是问题，您可以简单地将 winner 的 where 子句添加回内部查询，并删除外部查询的过滤器(以及它从内部查询中过滤的列)