Inconsistent MySQL query results
<p>I have a table called "transaction" that holds transactions made on a certain payment system.
I wanted to get the daily report for July 2023 (for example) and ran the following query: </p>
<pre class="brush:sql;toolbar:false;">SELECT DAY(CREATED), count(*) FROM `transaction`
WHERE STATUS = 'approved' AND MONTH(CREATED) = 07 AND YEAR(CREATED) = 2023
GROUP BY DAY(CREATED)
</pre>
<p>The result skips some days, for example for the 7th day of the month, I get nothing, but when I change the query to: </p>
<pre class="brush:sql;toolbar:false;">SELECT DAY(CREATED), count(*) FROM `transaction`
WHERE STATUS = 'approved' AND MONTH(CREATED) = 07 AND YEAR(CREATED) = 2023 AND DAY(CREATED) = 7
GROUP BY DAY(CREATED);
</pre>
<p> (Basically adding <code>DAY(CREATED) = 7</code> to the query)</p>
<p>It returns the count for day 7.</p>
<p>Why are they inconsistent?
How can I solve this problem in the first query? </p>
<p>Thanks in advance :)</p>
<p>Edit:
Table structure and data examples: </p>
<pre class="brush:sql;toolbar:false;">CREATE TABLE `transaction` (
`ID` int NOT NULL,
`PLATFORM_ID` int NOT NULL,
`ENTITY_ID` int NOT NULL,
`RELATED_TRANSACTION_ID` int NOT NULL DEFAULT '0',
`ORIGIN_ID` varchar(120) NOT NULL,
`BANK` varchar(64) DEFAULT NULL,
`AMOUNT` double NOT NULL,
`CURRENCY` varchar(3) NOT NULL,
`TYPE` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`CREATED` datetime NOT NULL,
`CARD_NO` varchar(20) DEFAULT NULL,
`CARD_BRAND` varchar(45) DEFAULT NULL,
`CARD_EXPIRE_YEAR` int DEFAULT NULL,
`CARD_EXPIRE_MONTH` varchar(45) DEFAULT NULL,
`HOLDER_NAME` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`EMAIL` varchar(256) DEFAULT NULL,
`STATUS` enum('approved','declined','filtered','pending') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`IS_3D` tinyint(1) DEFAULT NULL,
`CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`AMOUNTEUR` double DEFAULT NULL,
`IS_FRAUD` tinyint(1) NOT NULL DEFAULT '0',
`FRAUD_DATE` date DEFAULT NULL,
`KYC_DATE` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
</pre>
<p>Example insertion: </p>
<pre class="brush:sql;toolbar:false;">INSERT INTO `transaction` (`ID`, `PLATFORM_ID`, `ENTITY_ID`, `RELATED_TRANSACTION_ID`, `ORIGIN_ID`, `BANK`, `AMOUNT` , `CURRENCY`, `TYPE`, `CREATED`, `CARD_NO`, `CARD_BRAND`, `CARD_EXPIRE_YEAR`, `CARD_EXPIRE_MONTH`, `HOLDER_NAME`, `EMAIL`, `STATUS`, `IS_3D`, `CREATED_AT`, ` AMOUNTEUR`, `IS_FRAUD`, `FRAUD_DATE`, `KYC_DATE`) VALUES
(1, 1, 87, 0, '219114359', 'SOME BANK', 150, 'USD', 'DB', '2022-02-01 00:00:17', '000000XXXXXX0000', 'MasterCard', 2025 , '06', 'NAME FAMILY', 'aaaaaaaaa@gmail.com', 'approved', 0, '2022-08-25 13:12:58', 150.7605, 0, NULL, NULL);
</pre>
<p>I've omitted the index definition because I don't think it's relevant in this case. </p>
