Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22756

SQL Error (1364): Field 'DB_ROW_HASH_1' doesn't have a default value

Details

    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

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26846 SQL Error: Field 'DB_ROW_HASH_1' doesn't have a default value

          • Major - Major loss of function.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-371 Unique indexes for blobs

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-31072 InnoDB is USING HASH and Optimizer is confused

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            alice Alice Sherepa added a comment -

            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

            alice Alice Sherepa added a comment - 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
            bwaidelich Bastian Waidelich added a comment -

            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

            bwaidelich Bastian Waidelich added a comment - 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
            kevinoh@uw.edu James Kevin O'Halloran added a comment -

            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.

            kevinoh@uw.edu James Kevin O'Halloran added a comment - 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.
            kevinoh@uw.edu James Kevin O'Halloran added a comment -

            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.

            kevinoh@uw.edu James Kevin O'Halloran added a comment - 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.
            serg Sergei Golubchik added a comment -

            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.

            serg Sergei Golubchik added a comment - 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.
            kevinoh@uw.edu James Kevin O'Halloran added a comment -

            ah thank you for explaining that! cheers.

            kevinoh@uw.edu James Kevin O'Halloran added a comment - ah thank you for explaining that! cheers.

            People

              serg Sergei Golubchik
              rserge Serge Rogatch
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.