Bug 6619

Summary: INSERT INTO table with auto_increment column needs bulk execution
Product: SQL Reporter: Ying Zhang <y.zhang>
Component: allAssignee: SQL devs <bugs-sql>
Status: NEW ---    
Severity: enhancement CC: y.zhang
Priority: Normal    
Version: 11.29.3 (Mar2018)   
Hardware: All   
OS: All   

Description Ying Zhang cwiconfidential 2018-06-27 15:13:03 CEST
User-Agent:       Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/11.1.1 Safari/605.1.15
Build Identifier: 

When using INSERT INTO to insert values into a table containing an auto_increment column, the current MAL plan uses multiple sql.next_value and bat.append to generate and insert values for the auto_increment column.

This can be improved using bulk operations, such as how COPY INTO fills in the values of an auto_increment column.

Reproducible: Always

Steps to Reproduce:
See the difference in the MAL plans of the following queries:

sql>create table slow (a int, b int auto_increment);
operation successful
sql>explain insert into slow(a) values (1), (2), (3), (4);
+----------------------------------------------------------------------------------------------------------------------------+
| mal                                                                                                                        |
+============================================================================================================================+
| function user.s18_1():void;                                                                                                |
|     X_5:void := querylog.define("explain insert into slow(a) values (1), (2), (3), (4);":str, "default_pipe":str, 42:int); |
|     X_9:bat[:int] := bat.new(nil:int);                                                                                     |
|     X_8:int := sql.mvc();                                                                                                  |
|     X_13:bat[:int] := bat.append(X_9:bat[:int], 1:int, true:bit);                                                          |
|     X_17:bat[:int] := bat.append(X_13:bat[:int], 2:int, true:bit);                                                         |
|     X_20:bat[:int] := bat.append(X_17:bat[:int], 3:int, true:bit);                                                         |
|     X_23:bat[:int] := bat.append(X_20:bat[:int], 4:int, true:bit);                                                         |
|     X_24:bat[:int] := bat.new(nil:int);                                                                                    |
|     X_29:lng := sql.next_value("sys":str, "seq_8595":str);                                                                 |
|     X_30:int := calc.int(X_29:lng);                                                                                        |
|     X_31:bat[:int] := bat.append(X_24:bat[:int], X_30:int, true:bit);                                                      |
|     X_34:lng := sql.next_value("sys":str, "seq_8595":str);                                                                 |
|     X_35:int := calc.int(X_34:lng);                                                                                        |
|     X_36:bat[:int] := bat.append(X_31:bat[:int], X_35:int, true:bit);                                                      |
|     X_39:lng := sql.next_value("sys":str, "seq_8595":str);                                                                 |
|     X_40:int := calc.int(X_39:lng);                                                                                        |
|     X_41:bat[:int] := bat.append(X_36:bat[:int], X_40:int, true:bit);                                                      |
|     X_44:lng := sql.next_value("sys":str, "seq_8595":str);                                                                 |
|     X_45:int := calc.int(X_44:lng);                                                                                        |
|     X_46:bat[:int] := bat.append(X_41:bat[:int], X_45:int, true:bit);                                                      |
|     X_49:int := sql.append(X_8:int, "sys":str, "slow":str, "a":str, X_23:bat[:int]);                                       |
|     X_53:int := sql.append(X_49:int, "sys":str, "slow":str, "b":str, X_46:bat[:int]);                                      |
|     X_55:lng := aggr.count(X_46:bat[:int]);                                                                                |
|     sql.affectedRows(X_53:int, X_55:lng);                                                                                  |
| end user.s18_1;                                                                                                            |
| # optimizer.mitosis()                                                                                                      |
| # optimizer.dataflow()                                                                                                     |
| ...                                                                                                                        |
+----------------------------------------------------------------------------------------------------------------------------+
52 tuples
sql>explain copy into slow(a) from '/tmp/ints.csv'(a);
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| mal                                                                                                                                               |
+===================================================================================================================================================+
| function user.s12_1():void;                                                                                                                       |
|     X_1:void := querylog.define("copy into slow(a) from \\'/tmp/ints.csv\\'(a);":str, "default_pipe":str, 25:int);                                |
| barrier X_91:bit := language.dataflow();                                                                                                          |
|     X_4:int := sql.mvc();                                                                                                                         |
|     X_25:bat[:int] := sql.copy_from(nil:ptr, "|":str, "\\n":str, nil:str, "null":str, "/tmp/ints.csv":str, -1:lng, 0:lng, 0:int, 0:int, nil:str); |
|     X_28:bat[:str] := algebra.project(X_25:bat[:int], "sys":str);                                                                                 |
|     X_31:bat[:lng] := batsql.next_value(X_28:bat[:str], "seq_8595":str);                                                                          |
|     X_34:bat[:int] := batcalc.int(X_31:bat[:lng]);                                                                                                |
|     X_36:int := sql.append(X_4:int, "sys":str, "slow":str, "a":str, X_25:bat[:int]);                                                              |
|     X_40:int := sql.append(X_36:int, "sys":str, "slow":str, "b":str, X_34:bat[:int]);                                                             |
|     X_42:lng := aggr.count(X_34:bat[:int]);                                                                                                       |
|     language.pass(X_25:bat[:int]);                                                                                                                |
|     language.pass(X_34:bat[:int]);                                                                                                                |
| exit X_91:bit;                                                                                                                                    |
|     sql.affectedRows(X_40:int, X_42:lng);                                                                                                         |
| end user.s12_1;                                                                                                                                   |
| ...                                                                                                                                               |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
41 tuples

$ cat /tmp/ints.csv 
234
13
123
4345