InsertTest.java

/*-
 * #%L
 * JSQLParser library
 * %%
 * Copyright (C) 2004 - 2019 JSQLParser
 * %%
 * Dual licensed under GNU LGPL 2.1 or Apache License 2.0
 * #L%
 */
package net.sf.jsqlparser.statement.insert;

import static net.sf.jsqlparser.test.TestUtils.assertDeparse;
import static net.sf.jsqlparser.test.TestUtils.assertOracleHintExists;
import static net.sf.jsqlparser.test.TestUtils.assertSqlCanBeParsedAndDeparsed;
import static net.sf.jsqlparser.test.TestUtils.assertStatementCanBeDeparsedAs;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertNull;
import static org.junit.jupiter.api.Assertions.assertThrows;
import static org.junit.jupiter.api.Assertions.assertThrowsExactly;
import static org.junit.jupiter.api.Assertions.assertTrue;

import java.io.StringReader;
import java.util.List;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.DoubleValue;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.JdbcParameter;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.relational.ExistsExpression;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.ParenthesedExpressionList;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.update.UpdateSet;
import net.sf.jsqlparser.test.TestUtils;
import org.junit.jupiter.api.Disabled;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.function.Executable;
import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.ValueSource;

public class InsertTest {

    private final CCJSqlParserManager parserManager = new CCJSqlParserManager();

    @Test
    public void testRegularInsert() throws JSQLParserException {
        String statement = "INSERT INTO mytable (col1, col2, col3) VALUES (?, 'sadfsd', 234)";
        Insert insert = (Insert) assertSqlCanBeParsedAndDeparsed(statement, true);

        assertEquals("mytable", insert.getTable().getName());
        assertEquals(3, insert.getColumns().size());
        assertEquals("col1", insert.getColumns().get(0).getColumnName());
        assertEquals("col2", insert.getColumns().get(1).getColumnName());
        assertEquals("col3", insert.getColumns().get(2).getColumnName());

        Values values = insert.getValues();
        assertEquals(3, values.getExpressions().size());
        assertTrue(values.getExpressions().get(0) instanceof JdbcParameter);
        assertEquals("sadfsd", ((StringValue) values.getExpressions().get(1)).getValue());
        assertEquals(234, ((LongValue) values.getExpressions().get(2)).getValue());
        assertEquals(statement, insert.toString());

        ExpressionList expressionList = new ParenthesedExpressionList(new JdbcParameter(),
                new StringValue("sadfsd"), new LongValue().withValue(234));

        Select select = new Values().withExpressions(expressionList);

        Insert insert2 = new Insert().withTable(new Table("mytable"))
                .withColumns(
                        new ExpressionList<>(new Column("col1"), new Column("col2"),
                                new Column("col3")))
                .withSelect(select);

        assertDeparse(insert2, statement);

        statement = "INSERT INTO myschema.mytable VALUES (?, ?, 2.3)";
        insert = (Insert) parserManager.parse(new StringReader(statement));
        assertEquals("myschema.mytable", insert.getTable().getFullyQualifiedName());
        assertEquals(3, insert.getValues().getExpressions().size());
        assertTrue(insert.getValues().getExpressions().get(0) instanceof JdbcParameter);
        assertEquals(2.3,
                ((DoubleValue) insert.getValues().getExpressions().get(2))
                        .getValue(),
                0.0);
        assertEquals(statement, "" + insert);

    }

    @Test
    public void testInsertWithKeywordValue() throws JSQLParserException {
        String statement = "INSERT INTO mytable (col1) VALUE ('val1')";
        Insert insert = (Insert) parserManager.parse(new StringReader(statement));
        assertEquals("mytable", insert.getTable().getName());
        assertEquals(1, insert.getColumns().size());
        assertEquals("col1", insert.getColumns().get(0).getColumnName());
        assertEquals("'val1'",
                (insert.getValues().getExpressions().get(0)).toString());
        assertEquals("INSERT INTO mytable (col1) VALUES ('val1')", insert.toString());

    }

    @Test
    public void testInsertFromSelect() throws JSQLParserException {
        String statement = "INSERT INTO mytable (col1, col2, col3) SELECT * FROM mytable2";
        Insert insert = (Insert) parserManager.parse(new StringReader(statement));
        assertEquals("mytable", insert.getTable().getName());
        assertEquals(3, insert.getColumns().size());
        assertEquals("col1", insert.getColumns().get(0).getColumnName());
        assertEquals("col2", insert.getColumns().get(1).getColumnName());
        assertEquals("col3", insert.getColumns().get(2).getColumnName());

        // throw a NPE since its a PlainSelect statement
        assertThrows(Exception.class, new Executable() {
            @Override
            public void execute() throws Throwable {
                insert.getValues();
            }
        });

        assertNotNull(insert.getSelect());
        assertEquals("mytable2",
                ((Table) insert.getPlainSelect().getFromItem()).getName());

        // toString uses brackets
        String statementToString = "INSERT INTO mytable (col1, col2, col3) SELECT * FROM mytable2";
        assertEquals(statementToString, "" + insert);

        assertDeparse(new Insert().withTable(new Table("mytable"))
                .addColumns(new Column("col1"), new Column("col2"), new Column("col3"))
                .withSelect(new PlainSelect()
                        .addSelectItems(new AllColumns()).withFromItem(new Table("mytable2"))),
                statement);
    }

    @Test
    public void testInsertFromSet() throws JSQLParserException {
        String statement = "INSERT INTO mytable SET col1 = 12, col2 = name1 * name2";
        Insert insert = (Insert) parserManager.parse(new StringReader(statement));
        assertEquals("mytable", insert.getTable().getName());
        assertEquals(2, insert.getSetUpdateSets().size());
        assertEquals("col1", insert.getSetUpdateSets().get(0).getColumns().get(0).getColumnName());
        assertEquals("col2", insert.getSetUpdateSets().get(1).getColumns().get(0).getColumnName());
        assertEquals("12", insert.getSetUpdateSets().get(0).getValues().get(0).toString());
        assertEquals("name1 * name2",
                insert.getSetUpdateSets().get(1).getValues().get(0).toString());
        assertEquals(statement, "" + insert);
    }

    @Test
    public void testInsertValuesWithDuplicateElimination() throws JSQLParserException {
        String statement = "INSERT INTO TEST (ID, COUNTER) VALUES (123, 0) "
                + "ON DUPLICATE KEY UPDATE COUNTER = COUNTER + 1";
        Insert insert = (Insert) parserManager.parse(new StringReader(statement));
        assertEquals("TEST", insert.getTable().getName());
        assertEquals(2, insert.getColumns().size());
        assertEquals("ID", insert.getColumns().get(0).getColumnName());
        assertEquals("COUNTER", insert.getColumns().get(1).getColumnName());
        assertEquals(2, insert.getValues().getExpressions().size());
        assertEquals(123,
                ((LongValue) insert.getValues().getExpressions().get(0))
                        .getValue());
        assertEquals(0,
                ((LongValue) insert.getValues().getExpressions().get(1))
                        .getValue());
        assertEquals(1, insert.getDuplicateUpdateSets().size());
        assertEquals("COUNTER",
                insert.getDuplicateUpdateSets().get(0).getColumns().get(0).getColumnName());
        assertEquals("COUNTER + 1",
                insert.getDuplicateUpdateSets().get(0).getValues().get(0).toString());
        assertFalse(insert.isUseSelectBrackets());
        assertTrue(insert.isUseDuplicate());
        assertEquals(statement, "" + insert);
    }

    @Test
    public void testInsertFromSetWithDuplicateElimination() throws JSQLParserException {
        String statement = "INSERT INTO mytable SET col1 = 122 "
                + "ON DUPLICATE KEY UPDATE col2 = col2 + 1, col3 = 'saint'";
        Insert insert = (Insert) parserManager.parse(new StringReader(statement));
        assertEquals("mytable", insert.getTable().getName());
        assertEquals(1, insert.getSetUpdateSets().size());
        assertEquals("col1", insert.getSetUpdateSets().get(0).getColumns().get(0).getColumnName());
        assertEquals("122", insert.getSetUpdateSets().get(0).getValues().get(0).toString());
        assertEquals(2, insert.getDuplicateUpdateSets().size());
        assertEquals("col2",
                insert.getDuplicateUpdateSets().get(0).getColumns().get(0).getColumnName());
        assertEquals("col3",
                insert.getDuplicateUpdateSets().get(1).getColumns().get(0).getColumnName());
        assertEquals("col2 + 1",
                insert.getDuplicateUpdateSets().get(0).getValues().get(0).toString());
        assertEquals("'saint'",
                insert.getDuplicateUpdateSets().get(1).getValues().get(0).toString());
        assertEquals(statement, "" + insert);
    }

    @Test
    public void testInsertMultiRowValue() throws JSQLParserException {
        String statement = "INSERT INTO mytable (col1, col2) VALUES (a, b), (d, e)";
        assertSqlCanBeParsedAndDeparsed(statement);

        ExpressionList<Expression> multiExpressionList = new ExpressionList<>()
                .addExpression(
                        new ParenthesedExpressionList<Expression>(new Column("a"), new Column("b")))
                .addExpression(
                        new ParenthesedExpressionList<Expression>(new Column("d"),
                                new Column("e")));

        Select select = new Values().withExpressions(multiExpressionList);

        Insert insert = new Insert().withTable(new Table("mytable"))
                .withColumns(new ExpressionList<>(new Column("col1"), new Column("col2")))
                .withSelect(select);

        assertDeparse(insert, statement);
    }

    @Test
    @Disabled
    // @todo: Clarify, if and why this test is supposed to fail and if it is the Parser's job to
    // decide
    // What if col1 and col2 are Array Columns?
    public void testInsertMultiRowValueDifferent() throws JSQLParserException {
        assertThrowsExactly(JSQLParserException.class, new Executable() {
            @Override
            public void execute() throws Throwable {
                CCJSqlParserUtil.parse("INSERT INTO mytable (col1, col2) VALUES (a, b), (d, e, c)");
            }
        });
    }

    @Test
    @Disabled
    public void testOracleInsertMultiRowValue() throws JSQLParserException {
        String sqlStr = "INSERT ALL\n"
                + "  INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')\n"
                + "  INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')\n"
                + "  INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')\n"
                + "SELECT * FROM dual;";
        assertSqlCanBeParsedAndDeparsed(sqlStr, true);
    }

    @Test
    public void testSimpleInsert() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "INSERT INTO example (num, name, address, tel) VALUES (1, 'name', 'test ', '1234-1234')");
    }

    @Test
    public void testInsertWithReturning() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("INSERT INTO mytable (mycolumn) VALUES ('1') RETURNING id");
    }

    @Test
    public void testInsertWithReturning2() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("INSERT INTO mytable (mycolumn) VALUES ('1') RETURNING *");
    }

    @Test
    public void testInsertWithReturning3() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "INSERT INTO mytable (mycolumn) VALUES ('1') RETURNING id AS a1, id2 AS a2");
    }

    @Test
    public void testInsertSelect() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "INSERT INTO mytable (mycolumn) SELECT mycolumn FROM mytable");
        assertSqlCanBeParsedAndDeparsed(
                "INSERT INTO mytable (mycolumn) (SELECT mycolumn FROM mytable)");
    }

    @Test
    public void testInsertWithSelect() throws JSQLParserException {
        String sqlStr1 =
                "INSERT INTO mytable (mycolumn) WITH a AS (SELECT mycolumn FROM mytable) SELECT mycolumn FROM a";
        Insert insert1 = (Insert) assertSqlCanBeParsedAndDeparsed(sqlStr1, true);
        List<WithItem<?>> insertWithItems1 = insert1.getWithItemsList();
        List<WithItem<?>> selectWithItems1 = insert1.getSelect().getWithItemsList();
        assertEquals("mytable", insert1.getTable().getFullyQualifiedName());
        assertNull(insertWithItems1);
        assertEquals(1, selectWithItems1.size());
        assertEquals("SELECT mycolumn FROM mytable",
                selectWithItems1.get(0).getSelect().getPlainSelect().toString());
        assertEquals(" a", selectWithItems1.get(0).getAlias().toString());

        String sqlStr2 =
                "INSERT INTO mytable (mycolumn) (WITH a AS (SELECT mycolumn FROM mytable) SELECT mycolumn FROM a)";
        Insert insert2 = (Insert) assertSqlCanBeParsedAndDeparsed(sqlStr2, true);
        List<WithItem<?>> insertWithItems2 = insert2.getWithItemsList();
        assertEquals("mytable", insert2.getTable().getFullyQualifiedName());
        assertNull(insertWithItems2);
        ParenthesedSelect select = (ParenthesedSelect) insert2.getSelect();
        List<WithItem<?>> selectWithItems2 = select.getSelect().getWithItemsList();
        assertEquals(1, selectWithItems2.size());
        assertEquals("SELECT mycolumn FROM mytable",
                selectWithItems2.get(0).getSelect().getPlainSelect().toString());
        assertEquals(" a", selectWithItems2.get(0).getAlias().toString());
    }

    @Test
    public void testInsertWithKeywords() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("INSERT INTO kvPair (value, key) VALUES (?, ?)");
    }

    @Test
    public void testHexValues() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("INSERT INTO TABLE2 VALUES ('1', \"DSDD\", x'EFBFBDC7AB')");
    }

    @Test
    public void testHexValues2() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("INSERT INTO TABLE2 VALUES ('1', \"DSDD\", 0xEFBFBDC7AB)");
    }

    @Test
    public void testHexValues3() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("INSERT INTO TABLE2 VALUES ('1', \"DSDD\", 0xabcde)");
    }

    @Test
    public void testDuplicateKey() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "INSERT INTO Users0 (UserId, Key, Value) VALUES (51311, 'T_211', 18) ON DUPLICATE KEY UPDATE Value = 18");
    }

    @Test
    public void testModifierIgnore() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "INSERT IGNORE INTO `AoQiSurvey_FlashVersion_Single` VALUES (302215163, 'WIN 16,0,0,235')");
    }

    @Test
    public void testModifierPriority1() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("INSERT DELAYED INTO kvPair (value, key) VALUES (?, ?)");
    }

    @Test
    public void testModifierPriority2() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "INSERT LOW_PRIORITY INTO kvPair (value, key) VALUES (?, ?)");
    }

    @Test
    public void testModifierPriority3() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "INSERT HIGH_PRIORITY INTO kvPair (value, key) VALUES (?, ?)");
    }

    @Test
    public void testIssue223() throws JSQLParserException {
        String sqlStr = "INSERT INTO user VALUES (2001, '\\'Clark\\'', 'Kent')";
        assertSqlCanBeParsedAndDeparsed(sqlStr, true,
                parser -> parser.withBackslashEscapeCharacter(true));
    }

    @Test
    public void testKeywordPrecisionIssue363() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("INSERT INTO test (user_id, precision) VALUES (1, '111')");
    }

    @Test
    public void testWithDeparsingIssue406() throws JSQLParserException {
        String sqlStr =
                "insert into mytab3 (a,b,c) select a,b,c from mytab where exists(with t as (select * from mytab2) select * from t)";
        Insert insert = (Insert) assertSqlCanBeParsedAndDeparsed(sqlStr, true);
        List<WithItem<?>> insertWithItems = insert.getWithItemsList();
        List<WithItem<?>> selectWithItems = insert.getSelect().getWithItemsList();
        assertEquals("mytab3", insert.getTable().getFullyQualifiedName());
        assertNull(insertWithItems);
        assertNull(selectWithItems);
        ExistsExpression exists = (ExistsExpression) insert.getPlainSelect().getWhere();
        assertEquals("(WITH t AS (SELECT * FROM mytab2) SELECT * FROM t)",
                exists.getRightExpression().toString());
    }

    @Test
    public void testInsertSetInDeparsing() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("INSERT INTO mytable SET col1 = 12, col2 = name1 * name2");
    }

    @Test
    public void testInsertValuesWithDuplicateEliminationInDeparsing() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("INSERT INTO TEST (ID, COUNTER) VALUES (123, 0) "
                + "ON DUPLICATE KEY UPDATE COUNTER = COUNTER + 1");
    }

    @Test
    public void testInsertValuesAliasWithDuplicateEliminationIssue() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new"
                + "  ON DUPLICATE KEY UPDATE c = new.a+new.b;");

        assertSqlCanBeParsedAndDeparsed(
                "INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new(m,n,p) "
                        + "  ON DUPLICATE KEY UPDATE c = m+n;");
    }

    @Test
    public void testInsertSetWithDuplicateEliminationInDeparsing() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("INSERT INTO mytable SET col1 = 122 "
                + "ON DUPLICATE KEY UPDATE col2 = col2 + 1, col3 = 'saint'");

        assertSqlCanBeParsedAndDeparsed("INSERT INTO t1 SET a=1,b=2,c=3 AS new"
                + "  ON DUPLICATE KEY UPDATE c = new.a+new.b;");
    }

    @Test
    public void testInsertTableWithAliasIssue526() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "INSERT INTO account AS t (name, addr, phone) SELECT * FROM user");
    }

    @Test
    public void testInsertKeyWordEnableIssue592() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "INSERT INTO T_USER (ID, EMAIL_VALIDATE, ENABLE, PASSWORD) VALUES (?, ?, ?, ?)");
    }

    @Test
    public void testInsertKeyWordIntervalIssue682() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "INSERT INTO BILLING_TASKS (TIMEOUT, INTERVAL, RETRY_UPON_FAILURE, END_DATE, MAX_RETRY_COUNT, CONTINUOUS, NAME, LAST_RUN, START_TIME, NEXT_RUN, ID, UNIQUE_NAME, INTERVAL_TYPE) VALUES (?, ?, ?, ?, ?, ?, ?, NULL, ?, ?, ?, ?, ?)");
    }

    @Test
    public void testWithAtFront() throws JSQLParserException {
        String sqlStr =
                "WITH foo AS ( SELECT attr FROM bar ) INSERT INTO lalelu (attr) SELECT attr FROM foo";
        Insert insert = (Insert) assertSqlCanBeParsedAndDeparsed(sqlStr, true);
        List<WithItem<?>> insertWithItems = insert.getWithItemsList();
        assertEquals("lalelu", insert.getTable().getFullyQualifiedName());
        assertEquals(1, insertWithItems.size());
        assertEquals("SELECT attr FROM bar",
                insertWithItems.get(0).getSelect().getPlainSelect().toString());
        assertEquals(" foo", insertWithItems.get(0).getAlias().toString());
        assertEquals("SELECT attr FROM foo", insert.getSelect().toString());
        assertEquals("foo", insert.getSelect().getPlainSelect().getFromItem().toString());
        assertEquals("[attr]", insert.getSelect().getPlainSelect().getSelectItems().toString());
    }

    @Test
    public void testNextVal() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "INSERT INTO tracker (monitor_id, user_id, module_name, item_id, item_summary, team_id, date_modified, action, visible, id) VALUES (?, ?, ?, ?, ?, ?, to_date(?, 'YYYY-MM-DD HH24:MI:SS'), ?, ?, NEXTVAL FOR TRACKER_ID_SEQ)");
    }

    @Test
    public void testNextValueFor() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "INSERT INTO tracker (monitor_id, user_id, module_name, item_id, item_summary, team_id, date_modified, action, visible, id) VALUES (?, ?, ?, ?, ?, ?, to_date(?, 'YYYY-MM-DD HH24:MI:SS'), ?, ?, NEXT VALUE FOR TRACKER_ID_SEQ)");
    }

    @Test
    public void testNextValIssue773() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "INSERT INTO tableA (ID, c1, c2) SELECT hibernate_sequence.nextval, c1, c2 FROM tableB");
    }

    @Test
    public void testBackslashEscapingIssue827() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "INSERT INTO my_table (my_column_1, my_column_2) VALUES ('my_value_1\\\\', 'my_value_2')");
    }

    @Test
    public void testDisableKeywordIssue945() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "INSERT INTO SOMESCHEMA.TEST (DISABLE, TESTCOLUMN) VALUES (1, 1)");
    }

    @Test
    public void testWithListIssue282() throws JSQLParserException {
        String sqlStr =
                "WITH myctl AS (SELECT a, b FROM mytable) INSERT INTO mytable SELECT a, b FROM myctl";
        Insert insert = (Insert) assertSqlCanBeParsedAndDeparsed(sqlStr, true);
        List<WithItem<?>> insertWithItems = insert.getWithItemsList();
        assertEquals("mytable", insert.getTable().getFullyQualifiedName());
        assertEquals(1, insertWithItems.size());
        assertEquals("SELECT a, b FROM mytable",
                insertWithItems.get(0).getSelect().getPlainSelect().toString());
        assertEquals(" myctl", insertWithItems.get(0).getAlias().toString());
        assertEquals("SELECT a, b FROM myctl", insert.getSelect().toString());
        assertEquals("myctl", insert.getSelect().getPlainSelect().getFromItem().toString());
        assertEquals("[a, b]", insert.getSelect().getPlainSelect().getSelectItems().toString());
    }

    @Test
    public void testOracleHint() throws JSQLParserException {
        assertOracleHintExists("INSERT /*+ SOMEHINT */ INTO mytable VALUES (1, 2, 3)", true,
                "SOMEHINT");

        // @todo: add a testcase supposed to not finding a misplaced hint
    }

    @Test
    public void testInsertTableArrays4() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("INSERT INTO sal_emp\n" + "    VALUES ('Carol',\n"
                + "    ARRAY[20000, 25000, 25000, 25000],\n"
                + "    ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']])", true);
    }

    @Test
    public void testKeywordDefaultIssue1470() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "INSERT INTO mytable (col1, col2, col3) VALUES (?, 'sadfsd', default)");
    }

    @Test
    public void testInsertUnionSelectIssue1491() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("insert into table1 (tf1,tf2,tf2)\n"
                + "select sf1,sf2,sf3 from s1\n" + "union\n" + "select rf1,rf2,rf2 from r1\n",
                true);

        assertSqlCanBeParsedAndDeparsed("insert into table1 (tf1,tf2,tf2)\n"
                + "( select sf1,sf2,sf3 from s1\n" + "union\n" + "select rf1,rf2,rf2 from r1\n)",
                true);

        assertSqlCanBeParsedAndDeparsed("insert into table1 (tf1,tf2,tf2)\n"
                + "(select sf1,sf2,sf3 from s1)" + "union " + "(select rf1,rf2,rf2 from r1)", true);

        assertSqlCanBeParsedAndDeparsed("insert into table1 (tf1,tf2,tf2)\n"
                + "((select sf1,sf2,sf3 from s1)" + "union " + "(select rf1,rf2,rf2 from r1))",
                true);
    }

    @Test
    public void testWithSelectFromDual() throws JSQLParserException {
        String sqlStr = "(with a as (select * from dual) select * from a)";
        ParenthesedSelect parenthesedSelect =
                (ParenthesedSelect) assertSqlCanBeParsedAndDeparsed(sqlStr, true);
        List<WithItem<?>> withItems = parenthesedSelect.getSelect().getWithItemsList();
        assertEquals(1, withItems.size());
        assertEquals("SELECT * FROM dual",
                withItems.get(0).getSelect().getPlainSelect().toString());
        assertEquals(" a", withItems.get(0).getAlias().toString());
        assertEquals("a", parenthesedSelect.getPlainSelect().getFromItem().toString());
        assertEquals("[*]", parenthesedSelect.getPlainSelect().getSelectItems().toString());
    }

    @Test
    public void testInsertOutputClause() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)  \n"
                        + "  OUTPUT INSERTED.EmployeeID,\n" + "         INSERTED.LastName,   \n"
                        + "         INSERTED.FirstName,   \n" + "         INSERTED.CurrentSales,\n"
                        + "         INSERTED.ProjectedSales\n" + "  INTO @MyTableVar  \n"
                        + "    SELECT c.LastName, c.FirstName, sp.SalesYTD  \n"
                        + "    FROM Sales.SalesPerson AS sp  \n"
                        + "    INNER JOIN Person.Person AS c  \n"
                        + "        ON sp.BusinessEntityID = c.BusinessEntityID  \n"
                        + "    WHERE sp.BusinessEntityID LIKE '2%'  \n"
                        + "    ORDER BY c.LastName, c.FirstName",
                true);
    }

    // Samples taken from: https://www.postgresql.org/docs/current/sql-insert.html
    @Test
    public void testInsertOnConflictIssue1551() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed("INSERT INTO distributors (did, dname)\n"
                + "    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')\n"
                + "    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname\n", true);
        assertSqlCanBeParsedAndDeparsed(
                "INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH')\n"
                        + "    ON CONFLICT (did) DO NOTHING",
                true);

        assertSqlCanBeParsedAndDeparsed(
                "-- Don't update existing distributors based in a certain ZIP code\n"
                        + "INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution')\n"
                        + "    ON CONFLICT (did) DO UPDATE\n"
                        + "    SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')'\n"
                        + "    WHERE d.zipcode <> '21201'",
                true);

        assertSqlCanBeParsedAndDeparsed(
                "-- Name a constraint directly in the statement (uses associated\n"
                        + "-- index to arbitrate taking the DO NOTHING action)\n"
                        + "INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')\n"
                        + "    ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING",
                true);

        assertSqlCanBeParsedAndDeparsed(
                "-- This statement could infer a partial unique index on \"did\"\n"
                        + "-- with a predicate of \"WHERE is_active\", but it could also\n"
                        + "-- just use a regular unique constraint on \"did\"\n"
                        + "INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')\n"
                        + "    ON CONFLICT (did) WHERE is_active DO NOTHING",
                true);
    }

    @Test
    public void insertOnConflictObjectsTest() throws JSQLParserException {
        String sqlStr = "WITH a ( a, b , c ) \n" + "AS (SELECT  1 , 2 , 3 )\n"
                + "insert into test\n" + "select * from a";
        Insert insert = (Insert) CCJSqlParserUtil.parse(sqlStr);
        List<WithItem<?>> withItems = insert.getWithItemsList();
        assertEquals("test", insert.getTable().getFullyQualifiedName());
        assertEquals(1, withItems.size());
        assertEquals("[1, 2, 3]",
                withItems.get(0).getSelect().getPlainSelect().getSelectItems().toString());
        assertEquals(" a", withItems.get(0).getAlias().toString());

        Expression whereExpression = CCJSqlParserUtil.parseExpression("a=1", false);
        Expression valueExpression = CCJSqlParserUtil.parseExpression("b/2", false);

        InsertConflictTarget conflictTarget = new InsertConflictTarget("a", null, null, null);
        insert.setConflictTarget(conflictTarget);

        InsertConflictAction conflictAction =
                new InsertConflictAction(ConflictActionType.DO_NOTHING);
        insert.setConflictAction(conflictAction);

        assertStatementCanBeDeparsedAs(insert,
                sqlStr + " ON CONFLICT " + conflictTarget + conflictAction, true);

        conflictTarget = new InsertConflictTarget((String) null, null, null, "testConstraint");
        conflictTarget = conflictTarget.withWhereExpression(whereExpression);
        assertNotNull(conflictTarget.withConstraintName("a").getConstraintName());
        conflictTarget.setIndexExpression(valueExpression);
        assertNotNull(conflictTarget.getIndexExpression());
        assertNotNull(conflictTarget.withIndexColumnName("b").getIndexColumnName());

        assertTrue(conflictTarget.withIndexExpression(valueExpression).getIndexColumnNames()
                .isEmpty());
        assertNotNull(conflictTarget.withWhereExpression(whereExpression).getWhereExpression());

        conflictAction = new InsertConflictAction(ConflictActionType.DO_UPDATE);
        conflictAction.addUpdateSet(new Column().withColumnName("a"), valueExpression);

        UpdateSet updateSet = new UpdateSet();
        updateSet.add(new Column().withColumnName("b"));
        updateSet.add(valueExpression);
        conflictAction = conflictAction.addUpdateSet(updateSet);

        assertNotNull(conflictAction.withWhereExpression(whereExpression).getWhereExpression());
        assertEquals(ConflictActionType.DO_UPDATE, conflictAction.getConflictActionType());

        insert = insert.withConflictTarget(conflictTarget).withConflictAction(conflictAction);

        assertStatementCanBeDeparsedAs(insert,
                sqlStr + " ON CONFLICT " + conflictTarget + conflictAction, true);

    }

    @Test
    void testMultiColumnConflictTargetIssue1749() throws JSQLParserException {
        String sqlStr =
                "INSERT INTO re_rule_mapping ( id, created_time, last_modified_time, rule_item_id, department_id, scene, operation )\n"
                        + "            VALUES\n"
                        + "                ( '1', now( ), now( ), '1', '11', 'test', 'stop7' ),\n"
                        + "                ( '2', now( ), now( ), '2', '22', 'test2', 'stop8' ) ON CONFLICT ( rule_item_id, department_id, scene ) \n"
                        + "            DO UPDATE\n"
                        + "            SET operation = excluded.operation";
        assertSqlCanBeParsedAndDeparsed(sqlStr, true);
    }

    @Test
    void testMultiColumnConflictTargetIssue955() throws JSQLParserException {
        String sqlStr =
                "INSERT INTO tableName (id,xxx0,xxx1,xxx2,is_deleted,create_time,update_time) "
                        + "VALUES (?, ?, ?, ?, ?, ?, ?) "
                        + "on conflict(xxx0, xxx1) do update set xxx1=?, update_time=?";
        assertSqlCanBeParsedAndDeparsed(sqlStr, true);
    }

    @Test
    public void testDefaultValues() throws JSQLParserException {
        String statement = "INSERT INTO mytable DEFAULT VALUES";
        // assertSqlCanBeParsedAndDeparsed(statement);
        Insert insert = (Insert) parserManager.parse(new StringReader(statement));
        assertEquals("mytable", insert.getTable().getFullyQualifiedName());
        assertEquals("INSERT INTO MYTABLE DEFAULT VALUES", insert.toString().toUpperCase());
        assertTrue(insert.isOnlyDefaultValues());
        assertDeparse(new Insert()
                .withTable(new Table("mytable"))
                .withOnlyDefaultValues(true), statement);
    }

    @Test
    public void testDefaultValuesWithAlias() throws JSQLParserException {
        String statement = "INSERT INTO mytable x DEFAULT VALUES";
        Insert insert = (Insert) assertSqlCanBeParsedAndDeparsed(statement);
        assertEquals("mytable", insert.getTable().getFullyQualifiedName());
        assertEquals("INSERT INTO MYTABLE X DEFAULT VALUES", insert.toString().toUpperCase());
        assertEquals("x", insert.getTable().getAlias().getName());
        assertTrue(insert.isOnlyDefaultValues());
        assertDeparse(new Insert()
                .withTable(new Table("mytable")
                        .withAlias(new Alias("x").withUseAs(false)))
                .withOnlyDefaultValues(true), statement);
    }

    @Test
    public void testDefaultValuesWithAliasAndAs() throws JSQLParserException {
        String statement = "INSERT INTO mytable AS x DEFAULT VALUES";
        Insert insert = (Insert) assertSqlCanBeParsedAndDeparsed(statement);
        assertEquals("mytable", insert.getTable().getFullyQualifiedName());
        assertEquals("INSERT INTO MYTABLE AS X DEFAULT VALUES", insert.toString().toUpperCase());
        assertEquals("x", insert.getTable().getAlias().getName());
        assertTrue(insert.isOnlyDefaultValues());
        assertDeparse(new Insert()
                .withTable(new Table("mytable")
                        .withAlias(new Alias("x").withUseAs(true)))
                .withOnlyDefaultValues(true), statement);
    }

    @Test
    public void throwsParseWhenDefaultKeywordUsedAsAlias() {
        String statement = "INSERT INTO mytable default DEFAULT VALUES";
        assertThrows(JSQLParserException.class,
                () -> parserManager.parse(new StringReader(statement)));
    }

    @Test
    void testInsertWithinCte() throws JSQLParserException {
        String sqlStr = "WITH inserted AS ( " +
                "   INSERT INTO x (foo) " +
                "   SELECT bar FROM b " +
                "   RETURNING y " +
                ") " +
                "INSERT INTO z (blah) " +
                "SELECT y FROM inserted";
        Insert insert = (Insert) assertSqlCanBeParsedAndDeparsed(sqlStr);
        assertEquals("z", insert.getTable().toString());
        List<WithItem<?>> withItems = insert.getWithItemsList();
        assertEquals(1, withItems.size());
        Insert innerInsert = withItems.get(0).getInsert().getInsert();
        assertEquals("x", innerInsert.getTable().toString());
        assertEquals("SELECT bar FROM b", innerInsert.getSelect().toString());
        assertEquals(" RETURNING y", innerInsert.getReturningClause().toString());
        assertEquals("INSERT INTO x (foo) SELECT bar FROM b RETURNING y", innerInsert.toString());
        assertEquals(" inserted", withItems.get(0).getAlias().toString());
    }

    @Test
    void testUpdateWithinCte() throws JSQLParserException {
        String sqlStr = "WITH updated AS ( " +
                "   UPDATE x " +
                "      SET foo = 1 " +
                "    WHERE bar = 2 " +
                "   RETURNING y " +
                ") " +
                "INSERT INTO z (blah) " +
                "SELECT y FROM updated";
        Insert insert = (Insert) assertSqlCanBeParsedAndDeparsed(sqlStr);
        assertEquals("z", insert.getTable().toString());
        List<WithItem<?>> withItems = insert.getWithItemsList();
        assertEquals(1, withItems.size());
        Update update = withItems.get(0).getUpdate().getUpdate();
        assertEquals("x", update.getTable().toString());
        assertEquals("foo", update.getUpdateSets().get(0).getColumn(0).toString());
        assertEquals("1", update.getUpdateSets().get(0).getValue(0).toString());
        assertEquals("bar = 2", update.getWhere().toString());
        assertEquals(" RETURNING y", update.getReturningClause().toString());
        assertEquals(" updated", withItems.get(0).getAlias().toString());
    }

    @Test
    void testDeleteWithinCte() throws JSQLParserException {
        String sqlStr = "WITH deleted AS ( " +
                "   DELETE FROM x " +
                "    WHERE bar = 2 " +
                "   RETURNING y " +
                ") " +
                "INSERT INTO z (blah) " +
                "SELECT y FROM deleted";
        Insert insert = (Insert) assertSqlCanBeParsedAndDeparsed(sqlStr);
        assertEquals("z", insert.getTable().toString());
        List<WithItem<?>> withItems = insert.getWithItemsList();
        assertEquals(1, withItems.size());
        Delete delete = withItems.get(0).getDelete().getDelete();
        assertEquals("x", delete.getTable().toString());
        assertEquals("bar = 2", delete.getWhere().toString());
        assertEquals(" RETURNING y", delete.getReturningClause().toString());
        assertEquals(" deleted", withItems.get(0).getAlias().toString());
    }

    @Test
    void testDeleteAndInsertWithin2Ctes() throws JSQLParserException {
        String sqlStr = "WITH deleted AS ( " +
                "   DELETE FROM x " +
                "    WHERE bar = 2 " +
                "   RETURNING y " +
                ") " +
                ", inserted AS ( " +
                "   INSERT INTO x (foo) " +
                "   SELECT bar FROM b " +
                "    WHERE y IN (SELECT y FROM deleted) " +
                "   RETURNING w " +
                ") " +
                "INSERT INTO z (blah) " +
                "SELECT w FROM inserted";
        Insert insert = (Insert) assertSqlCanBeParsedAndDeparsed(sqlStr);
        assertEquals("z", insert.getTable().toString());
        List<WithItem<?>> withItems = insert.getWithItemsList();
        assertEquals(2, withItems.size());
        Delete delete = withItems.get(0).getDelete().getDelete();
        assertEquals("x", delete.getTable().toString());
        assertEquals("bar = 2", delete.getWhere().toString());
        assertEquals(" RETURNING y", delete.getReturningClause().toString());
        assertEquals(" deleted", withItems.get(0).getAlias().toString());
        Insert innerInsert = withItems.get(1).getInsert().getInsert();
        assertEquals("x", innerInsert.getTable().toString());
        assertEquals("SELECT bar FROM b WHERE y IN (SELECT y FROM deleted)",
                innerInsert.getSelect().toString());
        assertEquals(" RETURNING w", innerInsert.getReturningClause().toString());
        assertEquals(
                "INSERT INTO x (foo) SELECT bar FROM b WHERE y IN (SELECT y FROM deleted) RETURNING w",
                innerInsert.toString());
        assertEquals(" inserted", withItems.get(1).getAlias().toString());
    }

    @Test
    void testSelectAndInsertWithin2Ctes() throws JSQLParserException {
        String sqlStr = "WITH selection AS ( " +
                "   SELECT y " +
                "     FROM z " +
                "    WHERE foo = 'bar' " +
                ") " +
                ", inserted AS ( " +
                "   INSERT INTO x (foo) " +
                "   SELECT bar FROM b " +
                "    WHERE y IN (SELECT y FROM selection) " +
                "   RETURNING w " +
                ") " +
                "INSERT INTO z (blah) " +
                "SELECT w FROM inserted";
        Insert insert = (Insert) assertSqlCanBeParsedAndDeparsed(sqlStr);
        assertEquals("z", insert.getTable().toString());
        List<WithItem<?>> withItems = insert.getWithItemsList();
        assertEquals(2, withItems.size());
        PlainSelect select = withItems.get(0).getSelect().getPlainSelect();
        assertEquals("SELECT y FROM z WHERE foo = 'bar'", select.toString());
        assertEquals(" selection", withItems.get(0).getAlias().toString());
        Insert innerInsert = withItems.get(1).getInsert().getInsert();
        assertEquals("x", innerInsert.getTable().toString());
        assertEquals("SELECT bar FROM b WHERE y IN (SELECT y FROM selection)",
                innerInsert.getSelect().toString());
        assertEquals(" RETURNING w", innerInsert.getReturningClause().toString());
        assertEquals(
                "INSERT INTO x (foo) SELECT bar FROM b WHERE y IN (SELECT y FROM selection) RETURNING w",
                innerInsert.toString());
        assertEquals(" inserted", withItems.get(1).getAlias().toString());
    }

    @Test
    void testInsertOverwrite() throws JSQLParserException {
        String sqlStr = "INSERT OVERWRITE TABLE t SELECT * FROM a";
        Insert insert = (Insert) assertSqlCanBeParsedAndDeparsed(sqlStr);
        assertEquals("t", insert.getTable().getName());
        assertTrue(insert.isOverwrite());

        sqlStr = "INSERT OVERWRITE TABLE t PARTITION (pt1, pt2) SELECT * FROM a";
        insert = (Insert) assertSqlCanBeParsedAndDeparsed(sqlStr);
        assertEquals("t", insert.getTable().getName());
        assertEquals(2, insert.getPartitions().size());
        assertEquals("pt1", insert.getPartitions().get(0).getColumn().getColumnName());
        assertNull(insert.getPartitions().get(0).getValue());
        assertTrue(insert.isOverwrite());

        sqlStr = "INSERT OVERWRITE\nTABLE t PARTITION (pt1 = 'pt1', pt2 = 'pt2') SELECT * FROM a";
        insert = (Insert) assertSqlCanBeParsedAndDeparsed(sqlStr);
        assertEquals("t", insert.getTable().getName());
        assertEquals(2, insert.getPartitions().size());
        assertEquals("pt2", insert.getPartitions().get(1).getColumn().getColumnName());
        assertEquals("'pt2'", insert.getPartitions().get(1).getValue().toString());
        assertTrue(insert.isOverwrite());

        sqlStr = "INSERT INTO\tTABLE t PARTITION (pt1 = 'pt1', pt2 = 'pt2') SELECT * FROM a";
        insert = (Insert) assertSqlCanBeParsedAndDeparsed(sqlStr);
        assertEquals("t", insert.getTable().getName());
        assertEquals(2, insert.getPartitions().size());
        assertEquals("pt1", insert.getPartitions().get(0).getColumn().getColumnName());
        assertEquals("'pt1'", insert.getPartitions().get(0).getValue().toString());
        assertFalse(insert.isOverwrite());
    }

    @ParameterizedTest
    @ValueSource(strings = {
            "INSERT INTO mytable (foo) OVERRIDING SYSTEM VALUE VALUES (1)",
            "INSERT INTO mytable (foo) OVERRIDING SYSTEM VALUE SELECT bar FROM b WHERE y = 1",
            "INSERT INTO mytable (foo) OVERRIDING SYSTEM VALUE VALUES (1) ON CONFLICT (foo) DO UPDATE SET foo = 2",
            "INSERT INTO mytable (foo) OVERRIDING SYSTEM VALUE SELECT bar FROM b WHERE y = 1 ON CONFLICT (foo) DO UPDATE SET foo = 2",
            "INSERT INTO mytable (foo) OVERRIDING SYSTEM VALUE VALUES (1) ON CONFLICT (foo) DO NOTHING",
            "INSERT INTO mytable (foo) OVERRIDING SYSTEM VALUE SELECT bar FROM b WHERE y = 1 ON CONFLICT (foo) DO NOTHING"
    })
    public void testOverridingSystemValueInsertsParse(String sqlStr) throws JSQLParserException {
        Insert insert = (Insert) assertSqlCanBeParsedAndDeparsed(sqlStr);
        assertEquals("mytable", insert.getTable().getName());
        assertEquals(true, insert.isOverriding());
    }

    @ParameterizedTest
    @ValueSource(strings = {
            "INSERT INTO overriding (foo) OVERRIDING SYSTEM VALUE VALUES (1)",
            "INSERT INTO overriding (foo) OVERRIDING SYSTEM VALUE SELECT bar FROM b WHERE y = 1",
            "INSERT INTO overriding (foo) OVERRIDING SYSTEM VALUE VALUES (1) ON CONFLICT (foo) DO UPDATE SET foo = 2",
            "INSERT INTO overriding (foo) OVERRIDING SYSTEM VALUE SELECT bar FROM b WHERE y = 1 ON CONFLICT (foo) DO UPDATE SET foo = 2",
            "INSERT INTO overriding (foo) OVERRIDING SYSTEM VALUE VALUES (1) ON CONFLICT (foo) DO NOTHING",
            "INSERT INTO overriding (foo) OVERRIDING SYSTEM VALUE SELECT bar FROM b WHERE y = 1 ON CONFLICT (foo) DO NOTHING"
    })
    public void testOverridingSystemValueInsertsParseWithTableNamedOverriding(String sqlStr)
            throws JSQLParserException {
        Insert insert = (Insert) assertSqlCanBeParsedAndDeparsed(sqlStr);
        assertEquals("overriding", insert.getTable().getName());
        assertEquals(true, insert.isOverriding());
    }

    @Test
    void insertDemo() {
        Insert insert =
                new Insert()
                        .withTable(new Table("test"))
                        .withSelect(
                                new Values()
                                        .addExpressions(
                                                new StringValue("A"), new StringValue("B")));

        TestUtils.assertStatementCanBeDeparsedAs(
                insert, "INSERT INTO test VALUES ('A', 'B')");
    }

    @Test
    public void testSimpleDuplicateInsert() throws JSQLParserException {
        assertSqlCanBeParsedAndDeparsed(
                "INSERT INTO example (num, name, address, tel) VALUES (1, 'name', 'test ', '1234-1234') ON DUPLICATE KEY update NOTHING");
    }
}