tarantool / tarantool/tarantool
sql: on-insert trigger fails on table with autoincrement during replace op
Open
Nobody has claimed this yet.
bug
sql
- Dominant language
- Lua
- Stars
- 3.7k
- Forks
- 419
- Avg merge
- 1d 23h
- Merged PRs (30d)
- 88
Description
box.execute([[CREATE TABLE t (s1 INTEGER PRIMARY KEY, s2 INTEGER);]])
box.execute([[CREATE TABLE t2 (s1 INTEGER PRIMARY KEY AUTOINCREMENT, s2 STRING);]])
box.execute([[CREATE TRIGGER t_before_insert BEFORE INSERT ON t FOR EACH ROW BEGIN INSERT INTO t2 VALUES (NULL, 'before insert'); END;]])
box.execute([[REPLACE INTO t VALUES (1,1);]])
2020-03-10 17:31:55.122 [10350] main/102/interactive key_def.h:533 E> ER_KEY_PART_TYPE: Supplied key type of part 0 does not match index part type: expected integer
---
- null
- 'Supplied key type of part 0 does not match index part type: expected integer'
...
Note that in case t2 doesn't have autoincrement field, insertion successfully finishes:
box.execute([[CREATE TABLE t (s1 INTEGER PRIMARY KEY, s2 INTEGER);]])
box.execute([[CREATE TABLE t2 (s1 INTEGER PRIMARY KEY, s2 STRING);]])
box.execute([[CREATE TRIGGER t_before_insert BEFORE INSERT ON t FOR EACH ROW BEGIN INSERT INTO t2 VALUES (1, 'before insert'); END;]])
box.execute([[REPLACE INTO t VALUES (1,1);]])
box.space.T:select()
---
- - [1, 1]
...
box.space.T2:select()
---
- - [1, 'before insert']
...
It also works fine if instead of replace insert is used:
box.execute([[CREATE TABLE t (s1 INTEGER PRIMARY KEY, s2 INTEGER);]])
box.execute([[CREATE TABLE t2 (s1 INTEGER PRIMARY KEY AUTOINCREMENT, s2 STRING);]])
box.execute([[CREATE TRIGGER t_before_insert BEFORE INSERT ON t FOR EACH ROW BEGIN INSERT INTO t2 VALUES (NULL, 'before insert'); END;]])
box.execute([[INSERT INTO t VALUES (1,1);]])
tarantool> box.space.T:select()
---
- - [1, 1]
...
tarantool> box.space.T2:select()
---
- - [1, 'before insert']
...
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by reproducing the SQL statements using a BEFORE INSERT trigger on an AUTOINCREMENT table, comparing REPLACE with INSERT as shown in the issue. Trace the database handling of REPLACE, trigger execution, and automatic key assignment. Done means the REPLACE operation succeeds and inserts the triggered row with an automatically assigned integer key.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- lua, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100