Description:
MySQL 9.7 release has important change to IFNULL() and that broke functionality in some cases.
See Release notes, page 28:
https://docs.oracle.com/cd/E17952_01/mysql-9.7-relnotes-en/mysql-9.7-relnotes-en.pdf
All functions below should return identical value:
(1) IFNULL(t.Volume, t.VolumeNorm) AS Value_IfNull
(2) COALESCE(t.Volume, t.VolumeNorm) AS Value_Coalesce
(3) IF(t.Volume IS NULL, t.VolumeNorm, t.Volume) AS Value_If
The variant (1) with IFNULL function fails.
How to repeat:
-----
1. Create sample database
-----
DROP DATABASE IF EXISTS mysqlbug;
CREATE DATABASE mysqlbug CHARACTER SET utf8mb4 COLLATE utf8mb4_lithuanian_ci;
USE mysqlbug;
DROP TABLE IF EXISTS OrderServiceComposition;
DROP TABLE IF EXISTS OrderServices;
DROP TABLE IF EXISTS ServicesComposition;
DROP TABLE IF EXISTS Services;
CREATE TABLE Services
(
Id INT UNSIGNED NOT NULL AUTO_INCREMENT,
Name VARCHAR(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_lithuanian_ci NOT NULL,
DurationInMinutes MEDIUMINT UNSIGNED NULL, -- [0; 16777215] (3 bytes)
BreakInMinutes MEDIUMINT UNSIGNED NULL, -- [0; 16777215] (3 bytes)
Unit SMALLINT UNSIGNED NULL, -- Enum value [0; 65535]
Volume DECIMAL(9, 3) NULL CONSTRAINT CH_Services_Volume_Unsigned CHECK (Volume IS NULL OR Volume >= 0), -- [0; 999,999.999]
HasComposition BOOL NOT NULL DEFAULT 0,
CONSTRAINT PK_Services_Id PRIMARY KEY (Id),
CONSTRAINT CH_Services_IsServiceOrMaterial CHECK (
(DurationInMinutes IS NULL AND (Unit IS NOT NULL AND Volume IS NOT NULL AND BreakInMinutes IS NULL)) OR
(DurationInMinutes IS NOT NULL AND (Unit IS NULL AND Volume IS NULL))
)
);
INSERT INTO Services VALUES (2,'Name1',0,0,NULL,NULL,0);
INSERT INTO Services VALUES (15,'Name2',0,0,NULL,NULL,0);
INSERT INTO Services VALUES (19,'Name3',0,0,NULL,NULL,0);
INSERT INTO Services VALUES (27,'Name4',0,0,NULL,NULL,0);
INSERT INTO Services VALUES (126,'Name5',0,0,NULL,NULL,1);
INSERT INTO Services VALUES (127,'Name6',0,0,NULL,NULL,1);
INSERT INTO Services VALUES (162,'Name7',NULL,NULL,1,100.000,0);
CREATE TABLE ServicesComposition
(
ServiceId INT UNSIGNED NOT NULL,
CompServicePosition SMALLINT UNSIGNED NOT NULL,
CompServiceId INT UNSIGNED NOT NULL,
CompServiceCount DECIMAL(9, 3) NOT NULL CONSTRAINT CH_ServicesComposition_CompServiceCount_NotZero CHECK (CompServiceCount > 0),
CONSTRAINT PK_ServicesComposition_ServiceId_CompServiceId PRIMARY KEY (ServiceId, CompServiceId),
CONSTRAINT FK_ServicesComposition_ServiceId FOREIGN KEY (ServiceId) REFERENCES Services (Id),
INDEX IX_ServicesComposition_CompServiceId (CompServiceId),
CONSTRAINT FK_ServicesComposition_CompServiceId FOREIGN KEY (CompServiceId) REFERENCES Services (Id)
);
INSERT INTO ServicesComposition VALUES (126,1,19,1.000);
INSERT INTO ServicesComposition VALUES (126,2,27,1.000);
INSERT INTO ServicesComposition VALUES (126,3,162,1.000);
INSERT INTO ServicesComposition VALUES (127,1,15,1.000);
INSERT INTO ServicesComposition VALUES (127,2,126,1.000);
CREATE TABLE OrderServices
(
Id BINARY(16) NOT NULL, -- Guid
OrderId BINARY(16) NOT NULL, -- Guid
Position SMALLINT UNSIGNED NOT NULL,
ServiceId INT UNSIGNED NOT NULL,
INDEX IX_OrderServices_ServiceId (ServiceId),
CONSTRAINT FK_OrderServices_ServiceId FOREIGN KEY (ServiceId) REFERENCES Services (Id),
CONSTRAINT PK_OrderServices_Id PRIMARY KEY (Id),
INDEX IX_OrderServices_OrderId (OrderId, Position),
DurationInMinutesNorm MEDIUMINT UNSIGNED NULL,
BreakAfterInMinutesNorm MEDIUMINT UNSIGNED NULL,
DurationInMinutes MEDIUMINT UNSIGNED NULL,
Unit SMALLINT UNSIGNED NULL,
Volume DECIMAL(9, 3) NULL CONSTRAINT CH_OrderServices_Volume_Unsigned CHECK (Volume IS NULL OR Volume >= 0), -- [0; 999,999.999]
UnitPriceWithVat DECIMAL(14, 4) NOT NULL,
CONSTRAINT CH_OrderServices_IsServiceOrMaterial CHECK (
(DurationInMinutes IS NULL AND (Unit IS NOT NULL AND Volume IS NOT NULL AND DurationInMinutesNorm IS NULL)) OR
(DurationInMinutes IS NOT NULL AND (Unit IS NULL AND Volume IS NULL AND DurationInMinutesNorm IS NOT NULL))
)
);
INSERT INTO OrderServices VALUES (0x019E0EAFFA6A79F984E62B28024912D2,0x019E0EB00A8779C1B2BB2382A6AB05F3,1,126, 0, 0, 40,NULL, NULL, 5.1971);
INSERT INTO OrderServices VALUES (0x019E0EAFFA6A7BEE9BB9998299F0ACB2,0x019E0EB00A8779C1B2BB2382A6AB05F3,0, 2, 0, 0, 0,NULL, NULL, -5.0112);
INSERT INTO OrderServices VALUES (0x019E0EAFFA6E76AB9A38509D7E797ECF,0x019E0EB00A8779C1B2BB2382A6AB05F3,3,162,NULL,NULL,NULL, 4,50.000, 0.9900);
INSERT INTO OrderServices VALUES (0x019E0EAFFA6E7820AC9967347FDDC632,0x019E0EB00A8779C1B2BB2382A6AB05F3,4,162,NULL,NULL,NULL, 4,50.000, 5.1781);
INSERT INTO OrderServices VALUES (0x019E0EAFFA6E78F1AADAC571E0001124,0x019E0EB00A8779C1B2BB2382A6AB05F3,2,126, 0, 0, 35,NULL, NULL, 9.9900);
CREATE TABLE OrderServiceComposition
(
OrderServiceId BINARY(16) NOT NULL, -- Guid
OrderId BINARY(16) NOT NULL, -- Guid
Position SMALLINT UNSIGNED NOT NULL,
INDEX IX_OrderServiceComposition_OrderId (OrderId),
-- CONSTRAINT FK_OrderServiceComposition_OrderId FOREIGN KEY (OrderId) REFERENCES Orders (Id),
ServiceId INT UNSIGNED NOT NULL,
INDEX IX_OrderServiceComposition_ServiceId (ServiceId),
CONSTRAINT FK_OrderServiceComposition_ServiceId FOREIGN KEY (ServiceId) REFERENCES Services (Id),
CONSTRAINT PK_OrderServiceComposition_OrderServiceId_ServiceId PRIMARY KEY (OrderServiceId, ServiceId),
CONSTRAINT FK_OrderServiceComposition_OrderServiceId FOREIGN KEY (OrderServiceId) REFERENCES OrderServices (Id),
DurationInMinutesNorm MEDIUMINT UNSIGNED NULL,
BreakAfterInMinutesNorm MEDIUMINT UNSIGNED NULL,
DurationInMinutes MEDIUMINT UNSIGNED NULL,
UnitNorm SMALLINT UNSIGNED NULL,
VolumeNorm DECIMAL(9, 3) NULL CONSTRAINT CH_OrderServiceComposition_VolumeNorm_Unsigned CHECK (VolumeNorm IS NULL OR VolumeNorm >= 0),
Unit SMALLINT UNSIGNED NULL,
Volume DECIMAL(9, 3) NULL CONSTRAINT CH_OrderServiceComposition_Volume_Unsigned CHECK (Volume IS NULL OR Volume >= 0),
QuantityNorm DECIMAL(9, 3) NOT NULL CONSTRAINT CH_OrderServiceComposition_QuantityNorm_Unsigned CHECK (QuantityNorm >= 0),
Quantity DECIMAL(9, 3) NOT NULL CONSTRAINT CH_OrderServiceComposition_Quantity_Unsigned CHECK (Quantity >= 0),
QuantityTotal DECIMAL(9, 3) NOT NULL CONSTRAINT CH_OrderServiceComposition_QuantityTotal_Unsigned CHECK (QuantityTotal >= 0),
CONSTRAINT CH_OrderServiceComposition_IsServiceOrMaterial CHECK (
(DurationInMinutes IS NULL AND (Unit IS NOT NULL AND Volume IS NOT NULL)) OR
(DurationInMinutes IS NOT NULL AND (Unit IS NULL AND Volume IS NULL))
)
);
INSERT INTO OrderServiceComposition VALUES (0x019E0EAFFA6A79F984E62B28024912D2,0x019E0EB00A8779C1B2BB2382A6AB05F3,0,19,5,0,5,NULL,NULL,NULL,NULL,0.000,2.000,0.000);
INSERT INTO OrderServiceComposition VALUES (0x019E0EAFFA6A79F984E62B28024912D2,0x019E0EB00A8779C1B2BB2382A6AB05F3,0,162,NULL,NULL,NULL,5,50.000,5,50.000,0.000,1.500,0.000);
INSERT INTO OrderServiceComposition VALUES (0x019E0EAFFA6E78F1AADAC571E0001124,0x019E0EB00A8779C1B2BB2382A6AB05F3,0,19,5,0,5,NULL,NULL,NULL,NULL,0.000,2.000,0.000);
INSERT INTO OrderServiceComposition VALUES (0x019E0EAFFA6E78F1AADAC571E0001124,0x019E0EB00A8779C1B2BB2382A6AB05F3,0,27,0,0,0,NULL,NULL,NULL,NULL,0.000,1.000,0.000);
-----
2. Run the query below
-----
WITH normComposition AS (
SELECT
os.Id AS OrderServiceId, sc.CompServiceId AS ServiceId,
s.DurationInMinutes AS DurationInMinutesNorm,
IFNULL(s.BreakInMinutes, IF(s.Unit IS NULL, 0, NULL)) AS BreakAfterInMinutesNorm,
s.Unit AS UnitNorm, s.Volume AS VolumeNorm, sc.CompServiceCount AS QuantityNorm,
sc.CompServicePosition, s.Name AS ServiceName, s.HasComposition
FROM OrderServices os
INNER JOIN ServicesComposition sc ON os.ServiceId = sc.ServiceId
INNER JOIN Services s ON sc.CompServiceId = s.Id
WHERE os.OrderId = uuid_to_bin('019e0eb0-0a87-79c1-b2bb-2382a6ab05f3')
AND os.UnitPriceWithVat >= 0 AND os.ServiceId >= 10
),
savedComposition AS (
SELECT
osc.OrderServiceId, osc.ServiceId, osc.DurationInMinutes, osc.Unit, osc.Volume, osc.Quantity, osc.QuantityTotal, osc.Position,
s.DurationInMinutes AS DurationInMinutesNorm,
IFNULL(s.BreakInMinutes, IF(s.Unit IS NULL, 0, NULL)) AS BreakAfterInMinutesNorm,
s.Unit AS UnitNorm, s.Volume AS VolumeNorm, s.Name AS ServiceName, s.HasComposition
FROM OrderServiceComposition osc
INNER JOIN Services s ON osc.ServiceId = s.Id
WHERE osc.OrderId = uuid_to_bin('019e0eb0-0a87-79c1-b2bb-2382a6ab05f3')
),
fullJoin AS (
SELECT
s.OrderServiceId, s.ServiceId,
s.DurationInMinutes, s.Unit, s.Volume, s.Quantity, s.QuantityTotal,
s.DurationInMinutesNorm, s.BreakAfterInMinutesNorm, s.UnitNorm, s.VolumeNorm, n.QuantityNorm,
s.Position, n.CompServicePosition,
s.ServiceName, s.HasComposition
FROM savedComposition s
LEFT JOIN normComposition n ON s.OrderServiceId = n.OrderServiceId AND s.ServiceId = n.ServiceId
UNION ALL
SELECT
n.OrderServiceId, n.ServiceId,
s.DurationInMinutes, s.Unit, s.Volume, s.Quantity, s.QuantityTotal,
n.DurationInMinutesNorm, n.BreakAfterInMinutesNorm,
n.UnitNorm, n.VolumeNorm, n.QuantityNorm,
s.Position, n.CompServicePosition,
n.ServiceName, n.HasComposition
FROM savedComposition s
RIGHT JOIN normComposition n ON s.OrderServiceId = n.OrderServiceId AND s.ServiceId = n.ServiceId
WHERE s.OrderServiceId IS NULL
)
SELECT
bin_to_uuid(t.OrderServiceId),
ROW_NUMBER() OVER (PARTITION BY t.OrderServiceId ORDER BY IFNULL(t.CompServicePosition, 999999), t.Position) AS Position,
-- All values below must be equal, but IFNULL function works incorrect
IFNULL(t.Volume, t.VolumeNorm) AS Value_IfNull,
COALESCE(t.Volume, t.VolumeNorm) AS Value_Coalesce,
IF(t.Volume IS NULL, t.VolumeNorm, t.Volume) AS Value_If
FROM fullJoin t
WHERE t.OrderServiceId = uuid_to_bin('019e0eaf-fa6e-78f1-aada-c571e0001124');
