[MDEV-27371] REPLACE INTO creates 0 value on autoincrement column Created: 2021-12-27  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Data Manipulation - Update
Affects Version/s: 10.5.10, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Karl Klepper Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None
Environment:

ubuntu docker

Issue Links:
Relates
relates to MDEV-371 Unique indexes for blobs Closed

 Description   

Is this a bug or a feature?

M:1513109 [tmp]>CREATE TABLE `counter_test` (
 
    ->   `id_counter` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 
    ->   `tmstmp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 
    ->   `id_ex` bigint(20) NOT NULL,
 
    ->   `lg` varchar(2) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
 
    ->   `title_counter` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
    ->   `content_counter` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 
    ->   PRIMARY KEY (`id_counter`),
 
    ->   UNIQUE KEY `id_ex_lg_title_counter` (`id_ex`,`lg`,`title_counter`) USING HASH,
 
    ->   KEY `title_counter` (`title_counter`(255)),
 
    ->   KEY `id_ex` (`id_ex`)
 
    -> ) ENGINE=MyISAM AUTO_INCREMENT=18110 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci PACK_KEYS=1 COMMENT='test counter';
 
Query OK, 0 rows affected (1.303 sec)
 
 
 
M:1513109 [tmp]>SELECT * FROM tmp.`counter_test`  WHERE  id_ex = '197';
 
Empty set (0.001 sec)
 
 
 
M:1513109 [tmp]>REPLACE INTO tmp.counter_test (id_counter,id_ex, lg, title_counter, content_counter) VALUES (null,'197', 'de', 'logfile', '_get_content_body');
 
Query OK, 1 row affected (0.000 sec)
 
 
 
M:1513109 [tmp]>SELECT * FROM tmp.`counter_test`  WHERE  id_ex = '197';
 
+------------+---------------------+-------+----+---------------+-------------------+
 
| id_counter | tmstmp              | id_ex | lg | title_counter | content_counter   |
 
+------------+---------------------+-------+----+---------------+-------------------+
 
|      18110 | 2021-12-27 13:51:23 |   197 | de | logfile       | _get_content_body |
 
+------------+---------------------+-------+----+---------------+-------------------+
 
1 row in set (0.000 sec)
 
 
 
M:1513109 [tmp]>REPLACE INTO tmp.counter_test (id_counter,id_ex, lg, title_counter, content_counter) VALUES (null,'197', 'de', 'logfile', '_get_content_body');
 
Query OK, 2 rows affected (0.000 sec)
 
 
 
M:1513109 [tmp]>SELECT * FROM tmp.`counter_test`  WHERE  id_ex = '197';
 
+------------+---------------------+-------+----+---------------+-------------------+
 
| id_counter | tmstmp              | id_ex | lg | title_counter | content_counter   |
 
+------------+---------------------+-------+----+---------------+-------------------+
 
|          0 | 2021-12-27 13:51:35 |   197 | de | logfile       | _get_content_body |
 
+------------+---------------------+-------+----+---------------+-------------------+
 
1 row in set (0.000 sec)
 
 
 
M:1513109 [tmp]>select version();
 
+-------------------------------------------+
 
| version()                                 |
 
+-------------------------------------------+
 
| 10.5.10-MariaDB-1:10.5.10+maria~focal-log |
 
+-------------------------------------------+
 
1 row in set (0.059 sec)

The same with using null for id_counter.

 Comments   
Comment by Alice Sherepa [ 2021-12-27 ]

Thanks!
There is some problem with index on text column `title_counter` ( using hash):

CREATE TABLE t ( id int NOT NULL auto_increment, d text NOT NULL,
 
	PRIMARY KEY (id), UNIQUE KEY (d(50)) using hash);
 
 
 
replace into t (id, d) VALUES (null,'a');
 
replace into t (id, d) VALUES (null,'a');
 
 
 
SELECT * from t;

test returns id=0 on 10.4+, id=2 - on earlier versions.

MariaDB [test]> CREATE TABLE t ( id int NOT NULL auto_increment, d text NOT NULL, PRIMARY KEY (id), UNIQUE KEY (d) );
 
Query OK, 0 rows affected (0.053 sec)
 
 
 
MariaDB [test]> replace into t (id, d) VALUES (null,'a');
 
Query OK, 1 row affected (0.008 sec)
 
 
 
MariaDB [test]> replace into t (id, d) VALUES (null,'a');
 
Query OK, 2 rows affected (0.014 sec)
 
 
 
MariaDB [test]> select * from t;
 
+----+---+
 
| id | d |
 
+----+---+
 
|  0 | a |
 
+----+---+
 
1 row in set (0.002 sec)
 
 
 
MariaDB [test]> CREATE or replace  TABLE t ( id int NOT NULL auto_increment, d text NOT NULL, PRIMARY KEY (id), UNIQUE KEY (d(100)) );
 
Query OK, 0 rows affected (0.076 sec)
 
 
 
MariaDB [test]> replace into t (id, d) VALUES (null,'a');
 
Query OK, 1 row affected (0.015 sec)
 
 
 
MariaDB [test]> replace into t (id, d) VALUES (null,'a');
 
Query OK, 2 rows affected (0.011 sec)
 
 
 
MariaDB [test]> select * from t;
 
+----+---+
 
| id | d |
 
+----+---+
 
|  2 | a |
 
+----+---+
 
1 row in set (0.002 sec)

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