Description:
After creating a table having the first column definition of (`id` BINARY(16) NOT NULL DEFAULT (UUID_TO_BIN(uuid(),TRUE))PRIMARY KEY), several irregularities occur. These are:
1. When inserting a new row, the DEFAULT action still occurs, but has a few caveats.
2. If there is a COMPUTED column based on 'id' that is NOT NULL, a NOT NULL error occurs for that column for an attempted insert operation where the 'id' column is not explicitly named. Explicitly naming the 'id' column with the DEFAULT keyword solves this problem.
3. Under the covers it appears as though an INSERT followed by UPDATE is occuring for the insert operation.
How to repeat:
1. Create the following table:
CREATE TABLE `t` (
`id` BINARY(16) -- UUID modified with high to low timestamp in Binary format
NOT NULL
DEFAULT ( -- If the ID is not explicitly supplied, generate a DEFAULT.....
UUID_TO_BIN(
uuid(), -- Get a current UUID
TRUE -- Swap the low and high order timestamp bits (high at highest order) for efficiency.
) -- Convert the modified UUID to binary for fast efficient indexing
) -- Finally assign the binary modified UUID to the id field
PRIMARY KEY,
`created` TIMESTAMP(6) -- DateTime created Timestamp with microsecond precision.
GENERATED ALWAYS AS ( -- Will extract the Timestamp from the modified binary UUID id field.
FROM_UNIXTIME(
cast(
round(
cast(
conv(
hex(
left(
(id << 4) >> 4 -- shift the ID left 4 bits then right 4 bits to remove any of them that are set, then....
,8) -- take the left 8 bytes which represents the number of 100 nanoseconds since Oct 15, 1582, the base date for the UUID spec, which is the date of the Gregorian reform to the Christian calendar.
), -- Change the 100 nanosecond timestamp to HEX for conversion, then....
16,
10
) -- convert from Hex (base 16) to Decimal (base 10)...
as decimal(19)
) -- and cast it to a 19 digit Decimal....
/10 -- divide by 10 to go to microseconds....
) -- and round off the 100 nano decimal to the nearest microsecond.
as decimal (24,6) -- Setup the result so we can change to whole seconds with microsecond decimal precision....
)
/1000000 -- ...then divide to change from microseconds to full seconds, retaining the microsecond portion to the right of the decimal. Now we have the number of seconds with microsecond precision sinve October 15, 1582.
- (141427 * 24 * 60 * 60) -- Now we subtract the number of seconds between that date and January 1, 1970 to get a UNIX Timestamp value, retaining microsecond precision.
) -- Lastly, we convert the UNIX Time value to the internal MySQL Timestamp Datatype.
)
NOT NULL,
`updated` TIMESTAMP(6) NOT NULL DEFAULT (created),
`v` VARCHAR(45) DEFAULT NULL
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;
2. Execute the following statement:
insert into t (v) values ('hello');
The statement fails with Error Code: 1048. Column 'created' cannot be null.
3. Execute the following statements with explicit named 'id' column using DEFAULT keyword:
insert into t (id, v) values (DEFAULT, 'hello');
select HEX(id), created, updated, v from t;
The operation succeeds and operates normally. The 'updated' column is properly populated with the DEFAULT value of the generated column 'created' which is based on the 'id' column.
4. Now, add the following trigger:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` TRIGGER `t_BEFORE_UPDATE` BEFORE UPDATE ON `t` FOR EACH ROW
SET NEW.updated = IF(
NEW.updated = OLD.updated, -- If the UPDATE statement did not explicitly supply an updated timestamp value....
now(6), -- generate an updated timestamp value....
NEW.updated -- else use the supplied timestamp value....
); -- and properly set the updated field.
$$
DELIMITER ;
5. Execute the following statements:
insert into t (v) values ('hello');
select HEX(id), created, updated, v from t;
The operation does not error out, but the 'updated' TIMESTAMP column is all zeros. This suggests that the BEFORE UPDATE trigger is firing.
6. Execute the following statements with explicit DEFAULT:
insert into t (id, v) values (DEFAULT, 'hello');
select HEX(id), created, updated, v from t;
The operation succeeds and operates normally as in step 3 above.
Suggested fix:
Problem appears to be that the DEFAULT value for the 'id' column is not being generated and sent to the 'created' column generation expression unless the 'id' column is explicitly named with the DEFAULT keyword for its value during an INSERT.
Fix the INSERT operation so that the DEFAULT value is generated and passed on to a later defined generated column even when the column is not explicitly named withe the DEFAULT keyword.
Description: After creating a table having the first column definition of (`id` BINARY(16) NOT NULL DEFAULT (UUID_TO_BIN(uuid(),TRUE))PRIMARY KEY), several irregularities occur. These are: 1. When inserting a new row, the DEFAULT action still occurs, but has a few caveats. 2. If there is a COMPUTED column based on 'id' that is NOT NULL, a NOT NULL error occurs for that column for an attempted insert operation where the 'id' column is not explicitly named. Explicitly naming the 'id' column with the DEFAULT keyword solves this problem. 3. Under the covers it appears as though an INSERT followed by UPDATE is occuring for the insert operation. How to repeat: 1. Create the following table: CREATE TABLE `t` ( `id` BINARY(16) -- UUID modified with high to low timestamp in Binary format NOT NULL DEFAULT ( -- If the ID is not explicitly supplied, generate a DEFAULT..... UUID_TO_BIN( uuid(), -- Get a current UUID TRUE -- Swap the low and high order timestamp bits (high at highest order) for efficiency. ) -- Convert the modified UUID to binary for fast efficient indexing ) -- Finally assign the binary modified UUID to the id field PRIMARY KEY, `created` TIMESTAMP(6) -- DateTime created Timestamp with microsecond precision. GENERATED ALWAYS AS ( -- Will extract the Timestamp from the modified binary UUID id field. FROM_UNIXTIME( cast( round( cast( conv( hex( left( (id << 4) >> 4 -- shift the ID left 4 bits then right 4 bits to remove any of them that are set, then.... ,8) -- take the left 8 bytes which represents the number of 100 nanoseconds since Oct 15, 1582, the base date for the UUID spec, which is the date of the Gregorian reform to the Christian calendar. ), -- Change the 100 nanosecond timestamp to HEX for conversion, then.... 16, 10 ) -- convert from Hex (base 16) to Decimal (base 10)... as decimal(19) ) -- and cast it to a 19 digit Decimal.... /10 -- divide by 10 to go to microseconds.... ) -- and round off the 100 nano decimal to the nearest microsecond. as decimal (24,6) -- Setup the result so we can change to whole seconds with microsecond decimal precision.... ) /1000000 -- ...then divide to change from microseconds to full seconds, retaining the microsecond portion to the right of the decimal. Now we have the number of seconds with microsecond precision sinve October 15, 1582. - (141427 * 24 * 60 * 60) -- Now we subtract the number of seconds between that date and January 1, 1970 to get a UNIX Timestamp value, retaining microsecond precision. ) -- Lastly, we convert the UNIX Time value to the internal MySQL Timestamp Datatype. ) NOT NULL, `updated` TIMESTAMP(6) NOT NULL DEFAULT (created), `v` VARCHAR(45) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; 2. Execute the following statement: insert into t (v) values ('hello'); The statement fails with Error Code: 1048. Column 'created' cannot be null. 3. Execute the following statements with explicit named 'id' column using DEFAULT keyword: insert into t (id, v) values (DEFAULT, 'hello'); select HEX(id), created, updated, v from t; The operation succeeds and operates normally. The 'updated' column is properly populated with the DEFAULT value of the generated column 'created' which is based on the 'id' column. 4. Now, add the following trigger: DELIMITER $$ CREATE DEFINER=`root`@`localhost` TRIGGER `t_BEFORE_UPDATE` BEFORE UPDATE ON `t` FOR EACH ROW SET NEW.updated = IF( NEW.updated = OLD.updated, -- If the UPDATE statement did not explicitly supply an updated timestamp value.... now(6), -- generate an updated timestamp value.... NEW.updated -- else use the supplied timestamp value.... ); -- and properly set the updated field. $$ DELIMITER ; 5. Execute the following statements: insert into t (v) values ('hello'); select HEX(id), created, updated, v from t; The operation does not error out, but the 'updated' TIMESTAMP column is all zeros. This suggests that the BEFORE UPDATE trigger is firing. 6. Execute the following statements with explicit DEFAULT: insert into t (id, v) values (DEFAULT, 'hello'); select HEX(id), created, updated, v from t; The operation succeeds and operates normally as in step 3 above. Suggested fix: Problem appears to be that the DEFAULT value for the 'id' column is not being generated and sent to the 'created' column generation expression unless the 'id' column is explicitly named with the DEFAULT keyword for its value during an INSERT. Fix the INSERT operation so that the DEFAULT value is generated and passed on to a later defined generated column even when the column is not explicitly named withe the DEFAULT keyword.