INSERT multiple rows is not supported

[cdmh]

A SQL statement to insert multiple rows into a table fails parsing

INSERT INTO links (url, name) VALUES ('https://www.google.com','Google'), ('https://www.yahoo.com','Yahoo'),('https://www.bing.com','Bing');

Produces an error at the end of the first pair of parentheses in the VALUES clause
hsql::SQLParserResult::errorColumn_ == 73
hsql::SQLParserResult::errorMsg_ == "syntax error, unexpected ',', expecting end of file"

[mrks]

I can only confirm this. The insert_statement does not support multiple values:

sql-parser/src/parser/bison_parser.y

Lines 632 to 638 in 33c0013

	insert_statement:
	INSERT INTO table_name opt_column_list VALUES '(' literal_list ')' {
	$$ = new InsertStatement(kInsertValues);
	$$->schema = $3.schema;
	$$->tableName = $3.name;
	$$->columns = $4;
	$$->values = $7;

To our defense, this is not supported by SQL-92, which we use as orientation, either:

         <insert columns and source> ::=
                [ <left paren> <insert column list> <right paren> ]
              <query expression>
              | DEFAULT VALUES

         2) An <insert columns and source> that specifies DEFAULT VALUES is
            equivalent to an <insert columns and source> that specifies a
            <query expression> of the form

              VALUES (DEFAULT, . . . )

            where the number of "DEFAULT" entries is equal to the number of
            columns of T.

That being said, this is a valuable suggestion and I would support adding it. Due to a lack of resources, adding this might take us a while. External contributions are of course welcomed.

[cdmh]

Thanks for the quick response! I’m not familiar with the standards, so didn’t appreciate this is an extension, although widely supported.

I don’t have experience with parsers, but maybe I’ll give it a go and see if I can work out how to support this.

[mrks]

It might have come in with a newer SQL standard. SQL-92 is just the one that is sufficient for our purposes. I do wish we could upgrade to a more comprehensive support of a newer version.

[fowuyu]

I also hope hyrise has this feature, it is usefull.