INSERT with SetOperations

Simplify the INSERT production Use SetOperations for Select and Values Better Bracket handling for WITH ... SELECT ... Fixes #1491
JSQLParser · wumpz · May 15, 2022 · May 8, 2022 · May 8, 2022 · May 8, 2022
commit 267b22a7c6080b00dd92f51891c09c9d6e23b21d
diff --git a/src/main/java/net/sf/jsqlparser/statement/insert/Insert.java b/src/main/java/net/sf/jsqlparser/statement/insert/Insert.java
@@ -15,28 +15,31 @@
 import java.util.Iterator;
 import java.util.List;
 import java.util.Optional;
+
 import net.sf.jsqlparser.expression.Expression;
 import net.sf.jsqlparser.expression.OracleHint;
+import net.sf.jsqlparser.expression.RowConstructor;
+import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
 import net.sf.jsqlparser.expression.operators.relational.ItemsList;
 import net.sf.jsqlparser.schema.Column;
 import net.sf.jsqlparser.schema.Table;
 import net.sf.jsqlparser.statement.Statement;
 import net.sf.jsqlparser.statement.StatementVisitor;
 import net.sf.jsqlparser.statement.select.PlainSelect;
 import net.sf.jsqlparser.statement.select.Select;
+import net.sf.jsqlparser.statement.select.SelectBody;
 import net.sf.jsqlparser.statement.select.SelectExpressionItem;
+import net.sf.jsqlparser.statement.select.SetOperationList;
 import net.sf.jsqlparser.statement.select.WithItem;
+import net.sf.jsqlparser.statement.values.ValuesStatement;
 
 @SuppressWarnings({"PMD.CyclomaticComplexity"})
 public class Insert implements Statement {
 
     private Table table;
     private OracleHint oracleHint = null;
     private List<Column> columns;
-    private ItemsList itemsList;
-    private boolean useValues = true;
     private Select select;
-    private boolean useSelectBrackets = true;
     private boolean useDuplicate = false;
     private List<Column> duplicateUpdateColumns;
     private List<Expression> duplicateUpdateExpressionList;
@@ -86,20 +89,46 @@ public void setColumns(List<Column> list) {
      *
      * @return the values of the insert
      */
+    @Deprecated
     public ItemsList getItemsList() {
-        return itemsList;
+        if (select!=null) {
+            SelectBody selectBody = select.getSelectBody();
+            if (selectBody instanceof SetOperationList) {
+                SetOperationList setOperationList = (SetOperationList) selectBody;
+                List<SelectBody> selects = setOperationList.getSelects();
+
+                if (selects.size() == 1) {
+                    SelectBody selectBody1 = selects.get(0);
+                    if (selectBody1 instanceof ValuesStatement) {
+                        ValuesStatement valuesStatement = (ValuesStatement) selectBody1;
+                        if (valuesStatement.getExpressions() instanceof ExpressionList) {
+                            ExpressionList expressionList = (ExpressionList) valuesStatement.getExpressions();
+
+                            if (expressionList.getExpressions().size() == 1 && expressionList.getExpressions().get(0) instanceof RowConstructor) {
+                                RowConstructor rowConstructor = (RowConstructor) expressionList.getExpressions().get(0);
+                                return rowConstructor.getExprList();
+                            } else {
+                                return expressionList;
+                            }
+                        } else {
+                            return valuesStatement.getExpressions();
+                        }
+                    }
+                }
+            }
+        }
+        return null;
     }
 
-    public void setItemsList(ItemsList list) {
-        itemsList = list;
-    }
 
+    @Deprecated
     public boolean isUseValues() {
-        return useValues;
-    }
-
-    public void setUseValues(boolean useValues) {
-        this.useValues = useValues;
+        SelectBody selectBody = select.getSelectBody();
+        if (selectBody instanceof ValuesStatement) {
+            return true;
+        } else {
+            return false;
+        }
     }
 
     public boolean isReturningAllColumns() {
@@ -126,12 +155,9 @@ public void setSelect(Select select) {
         this.select = select;
     }
 
+    @Deprecated
     public boolean isUseSelectBrackets() {
-        return useSelectBrackets;
-    }
-
-    public void setUseSelectBrackets(boolean useSelectBrackets) {
-        this.useSelectBrackets = useSelectBrackets;
+        return false;
     }
 
     public boolean isUseDuplicate() {
@@ -234,24 +260,10 @@ public String toString() {
             sql.append(PlainSelect.getStringList(columns, true, true)).append(" ");
         }
 
-        if (useValues) {
-            sql.append("VALUES ");
+        if (select != null) {
+            sql.append(select);
         }
 
-        if (itemsList != null) {
-            sql.append(itemsList);
-        } else {
-            if (useSelectBrackets) {
-                sql.append("(");
-            }
-            if (select != null) {
-                sql.append(select);
-            }
-            if (useSelectBrackets) {
-                sql.append(")");
-            }
-        }
-
         if (useSet) {
             sql.append("SET ");
             for (int i = 0; i < getSetColumns().size(); i++) {
@@ -289,21 +301,11 @@ public Insert withWithItemsList(List<WithItem> withList) {
         return this;
     }
 
-    public Insert withUseValues(boolean useValues) {
-        this.setUseValues(useValues);
-        return this;
-    }
-
     public Insert withSelect(Select select) {
         this.setSelect(select);
         return this;
     }
 
-    public Insert withUseSelectBrackets(boolean useSelectBrackets) {
-        this.setUseSelectBrackets(useSelectBrackets);
-        return this;
-    }
-
     public Insert withUseDuplicate(boolean useDuplicate) {
         this.setUseDuplicate(useDuplicate);
         return this;
@@ -369,11 +371,6 @@ public Insert withSetColumns(List<Column> columns) {
         return this;
     }
 
-    public Insert withItemsList(ItemsList itemsList) {
-        this.setItemsList(itemsList);
-        return this;
-    }
-
     public Insert addColumns(Column... columns) {
         List<Column> collection = Optional.ofNullable(getColumns()).orElseGet(ArrayList::new);
         Collections.addAll(collection, columns);

diff --git a/src/main/java/net/sf/jsqlparser/statement/select/Select.java b/src/main/java/net/sf/jsqlparser/statement/select/Select.java
@@ -23,6 +23,8 @@ public class Select implements Statement {
     private SelectBody selectBody;
     private List<WithItem> withItemsList;
 
+    private boolean useWithBrackets = false;
+
     @Override
     public void accept(StatementVisitor statementVisitor) {
         statementVisitor.visit(this);
@@ -41,11 +43,27 @@ public void setSelectBody(SelectBody body) {
         selectBody = body;
     }
 
+    public void setUsingWithBrackets(boolean useWithBrackets) {
+        this.useWithBrackets = useWithBrackets;
+    }
+
+    public Select withUsingWithBrackets(boolean useWithBrackets) {
+        this.useWithBrackets = useWithBrackets;
+        return this;
+    }
+
+    public boolean isUsingWithBrackets() {
+        return this.useWithBrackets;
+    }
+
     @Override
     @SuppressWarnings({"PMD.CyclomaticComplexity", "PMD.NPathComplexity"})
     public String toString() {
         StringBuilder retval = new StringBuilder();
         if (withItemsList != null && !withItemsList.isEmpty()) {
+            if (useWithBrackets) {
+                retval.append("( ");
+            }
             retval.append("WITH ");
             for (Iterator<WithItem> iter = withItemsList.iterator(); iter.hasNext();) {
                 WithItem withItem = iter.next();
@@ -57,6 +75,9 @@ public String toString() {
             }
         }
         retval.append(selectBody);
+        if (withItemsList != null && !withItemsList.isEmpty() && useWithBrackets) {
+            retval.append(" )");
+        }
         return retval.toString();
     }
 

diff --git a/src/main/java/net/sf/jsqlparser/util/deparser/InsertDeParser.java b/src/main/java/net/sf/jsqlparser/util/deparser/InsertDeParser.java
@@ -77,13 +77,9 @@ public void deParse(Insert insert) {
             buffer.append(")");
         }
 
-        if (insert.getItemsList() != null) {
-            insert.getItemsList().accept(this);
-        }
-
         if (insert.getSelect() != null) {
             buffer.append(" ");
-            if (insert.isUseSelectBrackets()) {
+            if (insert.getSelect().isUsingWithBrackets()) {
                 buffer.append("(");
             }
             if (insert.getSelect().getWithItemsList() != null) {
@@ -94,7 +90,7 @@ public void deParse(Insert insert) {
                 buffer.append(" ");
             }
             insert.getSelect().getSelectBody().accept(selectVisitor);
-            if (insert.isUseSelectBrackets()) {
+            if (insert.getSelect().isUsingWithBrackets()) {
                 buffer.append(")");
             }
         }

diff --git a/src/main/java/net/sf/jsqlparser/util/deparser/StatementDeParser.java b/src/main/java/net/sf/jsqlparser/util/deparser/StatementDeParser.java
@@ -142,6 +142,9 @@ public void visit(Select select) {
         expressionDeParser.setBuffer(buffer);
         selectDeParser.setExpressionVisitor(expressionDeParser);
         if (select.getWithItemsList() != null && !select.getWithItemsList().isEmpty()) {
+            if (select.isUsingWithBrackets()) {
+                buffer.append("( ");
+            }
             buffer.append("WITH ");
             for (Iterator<WithItem> iter = select.getWithItemsList().iterator(); iter.hasNext();) {
                 WithItem withItem = iter.next();
@@ -153,6 +156,9 @@ public void visit(Select select) {
             }
         }
         select.getSelectBody().accept(selectDeParser);
+        if (select.isUsingWithBrackets()) {
+            buffer.append(" )");
+        }
     }
 
     @Override

diff --git a/src/main/java/net/sf/jsqlparser/util/validation/feature/H2Version.java b/src/main/java/net/sf/jsqlparser/util/validation/feature/H2Version.java
@@ -100,6 +100,7 @@ public enum H2Version implements Version {
                     // http://h2database.com/html/commands.html#insert
                     Feature.insert,
                     Feature.insertValues,
+                    Feature.values,
                     Feature.insertFromSelect,
                     // http://h2database.com/html/commands.html#update
                     Feature.update,
@@ -136,7 +137,8 @@ public enum H2Version implements Version {
                     // http://www.h2database.com/html/commands.html#grant_role
                     Feature.grant,
                     // http://h2database.com/html/commands.html#commit
-                    Feature.commit));
+                    Feature.commit
+            ));
 
     private Set<Feature> features;
     private String versionString;

diff --git a/src/main/java/net/sf/jsqlparser/util/validation/feature/MariaDbVersion.java b/src/main/java/net/sf/jsqlparser/util/validation/feature/MariaDbVersion.java
@@ -60,7 +60,7 @@ public enum MariaDbVersion implements Version {
                     Feature.withItem, Feature.withItemRecursive,
 
                     // https://mariadb.com/kb/en/insert/
-                    Feature.insert, Feature.insertValues,
+                    Feature.insert, Feature.insertValues, Feature.values,
                     Feature.insertFromSelect, Feature.insertModifierPriority, Feature.insertModifierIgnore,
                     Feature.insertUseSet, Feature.insertUseDuplicateKeyUpdate, Feature.insertReturningExpressionList,
 

diff --git a/src/main/java/net/sf/jsqlparser/util/validation/feature/MySqlVersion.java b/src/main/java/net/sf/jsqlparser/util/validation/feature/MySqlVersion.java
@@ -54,6 +54,7 @@ public enum MySqlVersion implements Version {
                     // https://dev.mysql.com/doc/refman/8.0/en/insert.html
                     Feature.insert,
                     Feature.insertValues,
+                    Feature.values,
                     Feature.insertFromSelect, Feature.insertUseSet, Feature.insertModifierPriority,
                     Feature.insertModifierIgnore, Feature.insertUseDuplicateKeyUpdate,
 

diff --git a/src/main/java/net/sf/jsqlparser/util/validation/feature/OracleVersion.java b/src/main/java/net/sf/jsqlparser/util/validation/feature/OracleVersion.java
@@ -88,6 +88,7 @@ public enum OracleVersion implements Version {
                     // https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/INSERT.html
                     Feature.insert,
                     Feature.insertValues,
+                    Feature.values,
                     // https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/INSERT.html
                     // see "single_table_insert"
                     Feature.insertFromSelect,

diff --git a/src/main/java/net/sf/jsqlparser/util/validation/feature/PostgresqlVersion.java b/src/main/java/net/sf/jsqlparser/util/validation/feature/PostgresqlVersion.java
@@ -109,6 +109,7 @@ public enum PostgresqlVersion implements Version {
                     // https://www.postgresql.org/docs/current/sql-insert.html
                     Feature.insert,
                     Feature.insertValues,
+                    Feature.values,
                     Feature.insertFromSelect,
                     Feature.insertReturningAll, Feature.insertReturningExpressionList,
                     // https://www.postgresql.org/docs/current/sql-update.html

diff --git a/src/main/java/net/sf/jsqlparser/util/validation/feature/SQLVersion.java b/src/main/java/net/sf/jsqlparser/util/validation/feature/SQLVersion.java
@@ -29,10 +29,13 @@ public enum SQLVersion implements Version {
             Feature.jdbcParameter,
             Feature.jdbcNamedParameter,
             // common features
+            Feature.setOperation,
             Feature.select,
             Feature.selectGroupBy, Feature.function,
             Feature.insert,
+            Feature.insertFromSelect,
             Feature.insertValues,
+            Feature.values,
             Feature.update,
             Feature.delete,
             Feature.truncate,