tarantool / tarantool/tarantool

sql: on-insert trigger fails on table with autoincrement during replace op

Open
#4,803 0 comments 0 reactions 0 assignees View on GitHub

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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.