[MDEV-9324] FR: In addition to MDEV-8605 I propose check duplication for PK and unique index before NULL checking Created: 2015-12-25  Updated: 2017-06-05  Resolved: 2017-05-29

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Insert
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: Mikhail Gavrilov Assignee: Unassigned
Resolution: Won't Fix Votes: 0
Labels: None


 Description   

In addition to MDEV-8605 I propose check duplication for PK and unique index before NULL checking.

This example should work:

CREATE TABLE `test` (
 
  `id_profile` VARBINARY(36) NOT NULL,
 
  `id_profile_type` INT(10) UNSIGNED NOT NULL,
 
  `dynamic_cols` BLOB,
 
  PRIMARY KEY (`id_profile`)
 
) ENGINE=INNODB DEFAULT CHARSET=utf8
 
 
 
INSERT INTO `test` (`id_profile`,`id_profile_type`)  VALUES
 
('f9854cb9-aacb-11e5-98e4-0050563c3a6d', 1),
 
('00a65240-aacc-11e5-98e4-0050563c3a6d', 2),
 
('0a843922-aacc-11e5-98e4-0050563c3a6d', 1);
 
 
 
INSERT INTO `test` (`id_profile`,`dynamic_cols`)  VALUES
 
('f9854cb9-aacb-11e5-98e4-0050563c3a6d', COLUMN_CREATE('sum',100)),
 
('00a65240-aacc-11e5-98e4-0050563c3a6d', COLUMN_CREATE('sum',100)),
 
('0a843922-aacc-11e5-98e4-0050563c3a6d', COLUMN_CREATE('sum',100))
 
ON DUPLICATE KEY UPDATE
 
`dynamic_cols` = VALUES(`dynamic_cols`);
 
 
 
SELECT id_profile, id_profile_type, COLUMN_JSON(dynamic_cols) FROM test

But currently having error:
Error Code: 1364
Field 'id_profile_type' doesn't have a default value

 Comments   
Comment by Elena Stepanova [ 2015-12-26 ]

I will convert it into a task and leave it to somebody else to decide whether it's possible and desirable.

However, I don't understand this use case (or rather this whole family of use cases), it looks artificial to me.
Having a non-nullable column without a default value is a wrong thing to do in the first place, why there is a need to cling onto it, what's the gain? It appears that having a default value in the table definition would save a lot of trouble and allow to avoid otherwise unnecessary and ugly workarounds or tricky logic. In this particular example, having a default value for `id_profile_type` would make the whole problem go away.

Also, FWIW, you can still do it if you switch off the strict mode.

Comment by Mikhail Gavrilov [ 2015-12-26 ]

Elena,do you have any another idea for massive update operation?
> However, I don't understand this use case (or rather this whole family of use cases), it looks artificial to me.
I think `ON DUPLICATE KEY UPDATE` mean that if record with same PK already exists we will do only `UPDATE`, checking for all column constants in this case is not needed (checking needed only for updated columns).
I am think this also increase performance for `ON DUPLICATE KEY UPDATE` when occured `UPDATE` on tables with a lot of columns.

Comment by Sergei Golubchik [ 2017-05-29 ]

I don't think it's doable without massive changes to the server and all storage engines.

First a record is filled with values, then it's sent to the engine. Engine tries to insert values into indexes, notices duplicate values, and returns an error.

One cannot store NULL in the record, if the field is declared NOT NULL. So we'd need

  • somehow pass NULL values to the engine outside of the record
  • the engine should only check uniqueness, not do a insert
  • then the server checks for NOT NULL
  • then passes the record to the engine, engine inserts the row and values into indexes.

Lots of complex changes. It's unlikely we ever get around to implementing it.

Comment by Mikhail Gavrilov [ 2017-06-05 ]

It is pity that one of the most exciting features is rejected. I understand that it is not easy to implement. But 99% of users like me use only innodb, so it is not necessary to implement this in all storage engines. And the most important thing is hepl's increase the speed massive updates records.

Generated at Thu Feb 08 07:33:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.