<commit_before>package com.splicemachine.derby.impl.sql.execute.operations;

import com.google.common.collect.Lists;
import com.splicemachine.derby.test.framework.SQLClosures;
import com.splicemachine.derby.test.framework.SpliceSchemaWatcher;
import com.splicemachine.derby.test.framework.SpliceWatcher;
import org.junit.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import static java.lang.String.format;
import static org.junit.Assert.*;

public class PrimaryKeyIT {

    private static final String SCHEMA = PrimaryKeyIT.class.getSimpleName().toUpperCase();

    private static SpliceWatcher spliceClassWatcher = new SpliceWatcher(SCHEMA);

    @ClassRule
    public static SpliceSchemaWatcher spliceSchemaWatcher = new SpliceSchemaWatcher(SCHEMA);

    @BeforeClass
    public static void insertTestData() throws Exception {
        spliceClassWatcher.executeUpdate("create table A (name varchar(50),val int, CONSTRAINT FOO PRIMARY KEY(name))");
        spliceClassWatcher.executeUpdate("create table AB (name varchar(50),val int, CONSTRAINT AB_PK PRIMARY KEY(val, name))");

        spliceClassWatcher.executeUpdate("insert into A values ('sfines',1),('jleach',2),('mzweben',3),('gdavis',4),('dgf',5)");
        spliceClassWatcher.executeUpdate("insert into AB values ('dfg',1)");
    }

    @Rule
    public SpliceWatcher methodWatcher = new SpliceWatcher(SCHEMA);

    // DB-3315: Updating row with primary key does not fail when it should.
    @Test
    public void updatePrimaryKeyOnRow() throws Exception {
        methodWatcher.executeUpdate("create table X(a varchar(9) primary key)");
        methodWatcher.executeUpdate("insert into X values('AAA')");
        methodWatcher.executeUpdate("insert into X values('MMM')");
        // should be a PK violation
        try {
            methodWatcher.executeUpdate("update X set a ='AAA' where a='MMM'");
            fail("Did not throw an exception on duplicate records on primary key");
        } catch (SQLException e) {
            assertTrue(e.getMessage(),e.getMessage().contains("it would have caused a duplicate key value in a unique or primary key constraint") &&
                e.getMessage().contains("defined on 'X'"));
            assertEquals("Expected 2 rows in table X", 2L, methodWatcher.query("select count(*) from X"));
        }
    }

    // DB-3013: Updating row with primary key and unique index fails.
    @Test
    public void updatePrimaryKeyOnRowWithUniqueIndex() throws Exception {
        methodWatcher.executeUpdate("create table UU(a int primary key, b int unique)");
        methodWatcher.executeUpdate("insert into UU values(1,1)");
        methodWatcher.executeUpdate("update UU set a=-10 where a=1");
        assertEquals(1L, methodWatcher.query("select count(*) from UU where a=-10"));
        assertEquals(0L, methodWatcher.query("select count(*) from UU where a=1"));
    }

    @Test
    public void testCannotInsertDuplicatePks() throws Exception {
        try {
            methodWatcher.executeUpdate("insert into A values ('sfines',1)");
            fail("Did not throw an exception on duplicate records on primary key");
        } catch (SQLException e) {
            assertTrue(e.getMessage().contains("identified by 'FOO' defined on 'A'"));
            assertEquals(1L, methodWatcher.query("select count(*) from A where name = 'sfines'"));
        }
    }

    @Test
    public void testDeleteAndInsertInSameTransaction() throws Exception {
        Connection conn = methodWatcher.getOrCreateConnection();
        conn.setAutoCommit(false);
        try {
            assertEquals(1L, methodWatcher.query("select count(*) from A where name='sfines'"));

            methodWatcher.executeUpdate("delete from A");
            methodWatcher.executeUpdate("insert into A (name,val) values ('sfines',2)");

            assertEquals(1L, methodWatcher.query("select count(*) from A where name='sfines'"));
        } finally {
            conn.rollback();
        }
    }

    @Test
    public void testInsertAndDeleteInSameTransaction() throws Exception {
        Connection conn = methodWatcher.getOrCreateConnection();
        conn.setAutoCommit(false);
        try {
            assertEquals(0L, methodWatcher.query("select count(*) from A where name='other'"));

            methodWatcher.executeUpdate("insert into A (name, val) values ('other',2)");
            assertEquals(1L, methodWatcher.query("select count(*) from A where name='other'"));

            methodWatcher.executeUpdate("delete from A where name = 'other'");

            assertEquals(0L, methodWatcher.query("select count(*) from A where name='other'"));
        } finally {
            conn.rollback();
        }
    }

    /* Regression test for Bug 419 */
    @Test(expected = SQLException.class, timeout = 10000)
    public void testDuplicateInsertFromSameTable() throws Exception {
        try {
            methodWatcher.executeUpdate("insert into A select * from A");
        } catch (SQLException sql) {
            assertTrue("Incorrect error returned!", sql.getSQLState().contains("23505"));
            throw sql;
        }
    }
<commit_msg>DB-3315: updating PK does not fail when it should. Refactored expected error check given code review feedback.<commit_after>package com.splicemachine.derby.impl.sql.execute.operations;

import com.google.common.collect.Lists;
import com.splicemachine.derby.test.framework.SQLClosures;
import com.splicemachine.derby.test.framework.SpliceSchemaWatcher;
import com.splicemachine.derby.test.framework.SpliceWatcher;
import org.junit.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import static java.lang.String.format;
import static org.junit.Assert.*;

public class PrimaryKeyIT {

    private static final String SCHEMA = PrimaryKeyIT.class.getSimpleName().toUpperCase();

    private static SpliceWatcher spliceClassWatcher = new SpliceWatcher(SCHEMA);

    @ClassRule
    public static SpliceSchemaWatcher spliceSchemaWatcher = new SpliceSchemaWatcher(SCHEMA);

    @BeforeClass
    public static void insertTestData() throws Exception {
        spliceClassWatcher.executeUpdate("create table A (name varchar(50),val int, CONSTRAINT FOO PRIMARY KEY(name))");
        spliceClassWatcher.executeUpdate("create table AB (name varchar(50),val int, CONSTRAINT AB_PK PRIMARY KEY(val, name))");

        spliceClassWatcher.executeUpdate("insert into A values ('sfines',1),('jleach',2),('mzweben',3),('gdavis',4),('dgf',5)");
        spliceClassWatcher.executeUpdate("insert into AB values ('dfg',1)");
    }

    @Rule
    public SpliceWatcher methodWatcher = new SpliceWatcher(SCHEMA);

    // DB-3315: Updating row with primary key does not fail when it should.
    @Test
    public void updatePrimaryKeyOnRow() throws Exception {
        methodWatcher.executeUpdate("create table X(a varchar(9) primary key)");
        methodWatcher.executeUpdate("insert into X values('AAA')");
        methodWatcher.executeUpdate("insert into X values('MMM')");
        // should be a PK violation
        try {
            methodWatcher.executeUpdate("update X set a ='AAA' where a='MMM'");
            fail("Did not throw an exception on duplicate records on primary key");
        } catch (SQLException e) {
            assertEquals("Incorrect error returned.", "23505", e.getSQLState());
            assertEquals("Expected 2 rows in table X", 2L, methodWatcher.query("select count(*) from X"));
        }
    }

    // DB-3013: Updating row with primary key and unique index fails.
    @Test
    public void updatePrimaryKeyOnRowWithUniqueIndex() throws Exception {
        methodWatcher.executeUpdate("create table UU(a int primary key, b int unique)");
        methodWatcher.executeUpdate("insert into UU values(1,1)");
        methodWatcher.executeUpdate("update UU set a=-10 where a=1");
        assertEquals(1L, methodWatcher.query("select count(*) from UU where a=-10"));
        assertEquals(0L, methodWatcher.query("select count(*) from UU where a=1"));
    }

    @Test
    public void testCannotInsertDuplicatePks() throws Exception {
        try {
            methodWatcher.executeUpdate("insert into A values ('sfines',1)");
            fail("Did not throw an exception on duplicate records on primary key");
        } catch (SQLException e) {
            assertEquals("Incorrect error returned.", "23505", e.getSQLState());
            assertEquals(1L, methodWatcher.query("select count(*) from A where name = 'sfines'"));
        }
    }

    @Test
    public void testDeleteAndInsertInSameTransaction() throws Exception {
        Connection conn = methodWatcher.getOrCreateConnection();
        conn.setAutoCommit(false);
        try {
            assertEquals(1L, methodWatcher.query("select count(*) from A where name='sfines'"));

            methodWatcher.executeUpdate("delete from A");
            methodWatcher.executeUpdate("insert into A (name,val) values ('sfines',2)");

            assertEquals(1L, methodWatcher.query("select count(*) from A where name='sfines'"));
        } finally {
            conn.rollback();
        }
    }

    @Test
    public void testInsertAndDeleteInSameTransaction() throws Exception {
        Connection conn = methodWatcher.getOrCreateConnection();
        conn.setAutoCommit(false);
        try {
            assertEquals(0L, methodWatcher.query("select count(*) from A where name='other'"));

            methodWatcher.executeUpdate("insert into A (name, val) values ('other',2)");
            assertEquals(1L, methodWatcher.query("select count(*) from A where name='other'"));

            methodWatcher.executeUpdate("delete from A where name = 'other'");

            assertEquals(0L, methodWatcher.query("select count(*) from A where name='other'"));
        } finally {
            conn.rollback();
        }
    }

    /* Regression test for Bug 419 */
    @Test(expected = SQLException.class, timeout = 10000)
    public void testDuplicateInsertFromSameTable() throws Exception {
        try {
            methodWatcher.executeUpdate("insert into A select * from A");
        } catch (SQLException sql) {
            assertEquals("Incorrect error returned.", "23505", sql.getSQLState());
            throw sql;
        }
    }

{'fraction_non_alphanumeric': 0.11373679154658982, 'fraction_numerical': 0.011527377521613832, 'mean_word_length': 3.307405875051717, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2898793', 'n_tokens_mistral': 2923, 'n_tokens_neox': 2821, 'n_words': 771}