[MDEV-22756] SQL Error (1364): Field 'DB_ROW_HASH_1' doesn't have a default value Created: 2020-05-30  Updated: 2023-04-28  Resolved: 2023-04-28

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Insert
Affects Version/s: 10.4.12, 10.4.13, 10.4, 10.5, 10.6
Fix Version/s: 10.11.3, 10.4.29, 10.5.20, 10.6.13, 10.8.8, 10.9.6, 10.10.4

Type: Bug Priority: Major
Reporter: Serge Rogatch Assignee: Sergei Golubchik
Resolution: Fixed Votes: 1
Labels: None
Environment:

Windows 10 64-bit

Issue Links:
Duplicate
is duplicated by MDEV-26846 SQL Error: Field 'DB_ROW_HASH_1' does... Closed
Relates
relates to MDEV-371 Unique indexes for blobs Closed
relates to MDEV-31072 InnoDB is USING HASH and Optimizer is... Open

 Description   

For the following table:

CREATE TABLE `demo_upsert` (
 
    `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
 
    `Url` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
 
    `LastListID` BIGINT(20) NULL DEFAULT NULL,
 
    `Statistics` BIGINT(20) NOT NULL DEFAULT '0',
 
    PRIMARY KEY (`ID`) USING BTREE,
 
    UNIQUE INDEX `Url` (`Url`) USING HASH
 
)
 
COLLATE='utf8mb4_unicode_ci'
 
ENGINE=InnoDB
 
;

When I run the following query:

INSERT INTO demo_upsert (Url, LastListID) SELECT Url, 10 FROM demo_upsert ON DUPLICATE KEY UPDATE LastListID=10

I get the following error:
SQL Error (1364): Field 'DB_ROW_HASH_1' doesn't have a default value

 Comments   
Comment by Alice Sherepa [ 2020-06-03 ]

Thanks for the report! I repeated on 10.4-10.5

CREATE TABLE t1 (url TEXT NOT NULL, UNIQUE INDEX url (url) USING hash);
 
INSERT INTO t1 (url) SELECT url FROM t1;
 
 
 
mysqltest: At line 2: query 'INSERT INTO t1 (url) SELECT url FROM t1' failed: 1364: Field 'DB_ROW_HASH_1' doesn't have a default value

Comment by Bastian Waidelich [ 2023-04-05 ]

I'm puzzled that this bug still exists after such a long time, but for anyone that comes across the same issue:
A potential work around is to add the nullable column(s) to the unique index:

// ...
 
UNIQUE INDEX `Url` (`Url`, `LastListID`) USING HASH

Comment by James Kevin O'Halloran [ 2023-04-28 ]

I just encountered this bug on 4/27/23 on @@version 10.4.27-MariaDB-1:10.4.27+maria~ubu2004.

I really don't want to add nullable columns to the unique index! ugh.

Comment by James Kevin O'Halloran [ 2023-04-28 ]

Sergei, I see you closed this bug after adding an update entitled exclude generated columns from the "has default value" check.

Just in case it is useful, I encountered this bug on a table with no generated columns.

Comment by Sergei Golubchik [ 2023-04-28 ]

DB_ROW_HASH_1 is the name of the hidden generated column which is internally created to implement the unique constraint. You can see that the test case in the commit does not use any generated columns either.

Comment by James Kevin O'Halloran [ 2023-04-28 ]

ah thank you for explaining that! cheers.

Generated at Thu Feb 08 09:17:13 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.