I had been using an app with this SQL query on my production server for more than 3 years, without any modifications to the query or to the major version of MariaDB server used. It suddenly stopped working and I have no clue why.
The query:
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;
Expected behavior: Return 10 random winners from contestants table (the table contains submitted contest codes, each contestant can have multiple rows in this table), each email should be present only once, only choose from contestants that don't have any winning code (winner = 1). The same query is executed multiple times (multiple prize rounds) and this query ensures each e-mail can win only one prize per month. This worked like a charm for past 3 years.
Current behavior: The query seems to be simply returning 10 contestants by ascending other (IDs 1,2,3,...).
If I run the query on a different web server (either local, or one of my hosting providers), it works well. I've tried MariaDB 10.3, 10.5, and MySQL 8.0. All of them work.
My production server is running MariaDB 10.3.34, which seems to be a security update from 02/2022. However I've tried creating another database on the same server running MariaDB 10.9.2 and the results are the same.
What could be the issue that this query suddenly stopped working for me, and works well on different environments?
EDIT: Here's some sample data to try with:
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;
CodePudding user response：
There is nothing in query query that indicates emails should be randomly chosen, you only choose one record for each email at random. This may have worked by luck due to the internal execution path causing a random sort at the right time, but the query has never been guaranteed to do what you are expecting.
Considering the following example data (ignoring date)
| ID | winner | |
|---|---|---|
| 1 | [email protected] | 0 |
| 2 | [email protected] | 0 |
| 3 | [email protected] | 0 |
| 4 | [email protected] | 1 |
| 5 | [email protected] | 0 |
If you were to assign your row number to a new column and apply the filter, you'd get two possible results for you row number function depending on the outcome of the rand function:
| ID | winner | RN1 | RN2 | |
|---|---|---|---|---|
| 1 | [email protected] | 0 | 1 | 2 |
| 2 | [email protected] | 0 | 2 | 1 |
| 3 | [email protected] | 0 | 1 | 1 |
| 5 | [email protected] | 0 | 1 | 1 |
The issue you have here is, that you have no further ordering, so while you can ensure that Row 1 appears before row 2 (or vice versa) where the email is the same, there is no ordering logic that would dictate any of the other rows order. You may need a further sort the results, e.g.
ORDER BY
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY
RAND()
),
RAND()
Another issue that you have with the above is that if you chose the top 10 regardless of what you do you are going to get [email protected] twice because there are only 4 records to chose from.
I also don't think your query reflects the following criterion (emphasis mine):
only choose from contestants that don't have any winning code (winner = 1).
In the sample data I have given I think [email protected] is the example of this - one row has a winning code, but they have another that is not yet a winner so would still be eligible to be picked on a subsequent round of prizes with the current rules.
If the above is an issue then I think you want the following:
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;
If it is not an issue you can simply add the where clause on winner back in to the inner query, and remove the filter on the outer query (and the column it filters on from the inner query)
CodePudding user response：
Consider
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);
with limit 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)
limit 2
------- -------- ---- ---------------------
| EMAIL | WINNER | rn | rnrand |
------- -------- ---- ---------------------
| BBB | 0 | 1 | 0.40540353712197724 |
| CCC | 0 | 1 | 0.40540353712197724 |
------- -------- ---- ---------------------
2 rows in set (0.001 sec)