Description:
After update from 8.0.23 to 8.0.24 got inconsistent behavior on my queries to a production DB. The same query have returned different results between calls and sometimes only between different db connections.
Below is the short reproducible test case. On 8.0.23 everything is Ok. On 8.0.24 can be race conditions, but in my test case it's always the same.
The insert() version of generated column fails on 8.0.24. The concat() version does as it should. Of course on my production DB I used the insert() version.
I could not reproduce this case until have inserted NULL in MACint column.
How to repeat:
CREATE SCHEMA `new_schema` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
use `new_schema`;
CREATE TABLE `test` (
`testID` int NOT NULL AUTO_INCREMENT,
`MACint` bigint unsigned DEFAULT NULL,
`MACstr` char(12) GENERATED ALWAYS AS (lpad(convert(hex(`MACint`) using utf8mb4),12,_utf8mb4'0')) VIRTUAL,
`MACcolon` char(17) GENERATED ALWAYS AS (insert(insert(insert(insert(insert(`MACstr`,11,0,_utf8mb4':'),9,0,_utf8mb4':'),7,0,_utf8mb4':'),5,0,_utf8mb4':'),3,0,_utf8mb4':')) VIRTUAL,
`MACcolon1` char(17) GENERATED ALWAYS AS (concat(substr(`MACstr`,1,2),_utf8mb4':',substr(`MACstr`,3,2),_utf8mb4':',substr(`MACstr`,5,2),_utf8mb4':',substr(`MACstr`,7,2),_utf8mb4':',substr(`MACstr`,9,2),_utf8mb4':',substr(`MACstr`,11,2))) VIRTUAL,
PRIMARY KEY (`testID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO test (`MACint`) VALUES (10), (15), (564685488), (684756486), (688686985), (NULL), (34);
select * from test where MACcolon = '00:00:29:0C:87:89'; #NULLS
select * from test where MACcolon1 = '00:00:29:0C:87:89'; #ROW with MACcolon NULL
select * from test; #MACcolon nulls, sometimes not in all rows
