파티션 기준 절이 있는 ROW_NUMBER()가 Maria에서 작동을 중지했습니다.DB

파티션 기준 절이 있는 ROW_NUMBER()가 Maria에서 작동을 중지했습니다.DB

저는 3년 이상 운영 서버에서 이 SQL 쿼리가 포함된 앱을 사용했으며, 사용된 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;

예상 동작:무작위 당첨자 10명 반환contestants된 경기, 각 는 이 수 ), 각 테이블은 "(" "" "" "" "" "" "" "" ""email, 우승 중에서 해야 합니다.winner=1). 번 이 는 각 이 한 에 한 수 동일한 쿼리가 여러 번 실행되며(복수 경품 라운드), 이 쿼리는 각 전자 메일이 한 달에 한 개의 경품만 받을 수 있도록 보장합니다.이것은 지난 3년 이상 동안 매력적으로 작동했습니다.

현재 동작:쿼리는 단순히 다른 참가자(ID 1, 2, 3, ...)를 오름차순으로 10명의 참가자를 반환하는 것 같습니다.

---

다른 웹 서버(로컬 또는 호스팅 공급자 중 하나)에서 쿼리를 실행하면 제대로 작동합니다.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;

쿼리 쿼리에는 전자 메일을 임의로 선택해야 한다는 내용이 없으며 각 전자 메일에 대해 레코드를 하나만 임의로 선택합니다.내부 실행 경로로 인해 올바른 시간에 임의 정렬이 발생했기 때문에 이 작업이 제대로 수행되었을 수 있지만 쿼리가 원하는 작업을 수행하는지는 확인되지 않았습니다.

다음 예제 데이터 고려(무시 날짜)

아이디	이메일	승리자.
1	a@b.c	0
2	a@b.c	0
3	d@e.f	0
4	d@e.f	1
5	x@y.z	0

행 번호를 새 열에 할당하고 필터를 적용하면 행 번호 함수에 대해 랜드 함수의 결과에 따라 두 가지 결과를 얻을 수 있습니다.

아이디	이메일	승리자.	RN1	RN2
1	a@b.c	0	1	2
2	a@b.c	0	2	1
3	d@e.f	0	1	1
5	x@y.z	0	1	1

문제는 추가 주문이 없기 때문에 이메일이 동일한 2행 앞(또는 그 반대)에 1행이 나타나도록 할 수 있지만 다른 행 순서를 지시하는 주문 로직이 없다는 것입니다.예를 들어, 결과를 추가로 정렬해야 할 수도 있습니다.

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

위와 관련하여 당신이 가지고 있는 또 다른 문제는 당신이 무엇을 하든지 상관없이 상위 10위를 선택했다면 당신은 받게 될 것이라는 것입니다.a@b.c선택할 수 있는 레코드가 4개뿐이기 때문에 두 번입니다.

또한 당신의 질문은 다음 기준(내 질문 강조)을 반영하지 않는다고 생각합니다.

우승 코드가 없는 참가자만 선택합니다(우승자 = 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;
                
                

DB-Fiddle 예제

문제가 없는 경우 winner의 where 절을 다시 내부 쿼리에 추가하고 외부 쿼리의 필터(및 내부 쿼리에서 필터링하는 열)를 제거하면 됩니다.

고려하다

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)

언급URL : https://stackoverflow.com/questions/73632538/row-number-with-partition-by-clause-stopped-working-on-mariadb