Generate field value for a NULL field of an autoinc/sequence column

[jd-zhang]

Issue migrated from trac ticket # 882 www.kunlunbase.com

component: computing nodes | priority: major

2022-07-07 14:59:38: zhaowei@zettadb.com created the issue

---

This is mysql behavior, implement this in regardless of connection type:

CREATE TABLE bug2247(id INT UNIQUE AUTO_INCREMENT) INSERT INTO bug2247 VALUES (NULL) --- use the sequence to produce a field value

[jd-zhang]

2022-07-08 11:46:13: smith commented

---

预期AUTO_INCREMENT具有如下行为：

1、当写入null、0或者default时，自动生成自增值
2、当插入其他值时，则需要保证后续自动生成的子增值大于当前插入的值（目前暂不保证）

原来的实现下，auto_increment仅仅是GENERATED BY DEFAULT AS IDENTITY的语法糖。为了 区别于GENERATED ...，现在重新定义auto_increment如下：

-- 创建自增列的方式1（内部自动为其产生一个序列，并绑定到自增列上）
CREATE TABLE public.t2 (
    a integer AUTO_INCREMENT
)
WITH (shard='1');

-- 创建自增列的方式2（便于pg_dump的导入）
CREATE TABLE public.t2 (
    a integer NOT NULL
)
WITH (shard='1');

ALTER TABLE public.t2 ALTER COLUMN a ADD AUTO_INCREMENT (
    SEQUENCE NAME public.t2_a_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
    SHARD 1
);

-- 创建自增列的方式3
create table t3( 
  a integer AUTO_INCREMENT (
    SEQUENCE NAME public.t2_a_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
    SHARD 1
  )
);

[jd-zhang]

2022-07-08 16:03:51: smith changed status from assigned to accepted

[jd-zhang]

2022-07-08 16:14:09: smith commented

---

目前已经实现自增列的预期行为1