sqlite/test/insert6.test

# 2015 March 20
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# The tests in this file ensure that sorter objects are used by 
# "INSERT INTO ... SELECT ..." statements when possible.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix insert6

# Return the number of OP_SorterOpen instructions in the SQL passed as
# the only argument if it is compiled using connection [db].
#
proc sorter_count {sql} {
  set res 0
  db cache flush
  db eval "EXPLAIN $sql" x {
    if {$x(opcode) == "SorterOpen"} { incr res }
  }
  return $res
}


#-------------------------------------------------------------------------
# Warm body test. This verifies that the simplest case works for both
# regular and WITHOUT ROWID tables.
#
do_execsql_test 1.1 {
  CREATE TABLE t2(x UNIQUE ON CONFLICT IGNORE, y, z);
  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<99 )
  INSERT INTO t2 SELECT abs(random()), abs(random()), abs(random()) FROM cnt;
}

foreach {tn nSort schema} {
  1 3 { CREATE TABLE t1(a, b, c) }
  2 4 { CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID }
} {

  do_test 1.$tn.1 {
    execsql { DROP TABLE IF EXISTS t1 }
    execsql $schema 
  } {}

  do_execsql_test 1.$tn.2 {
    CREATE INDEX t1a ON t1(a);
    CREATE INDEX t1b ON t1(b);
    CREATE INDEX t1c ON t1(c);
  }

  do_execsql_test 1.$tn.3 {
    INSERT INTO t1 SELECT x, y, z FROM t2;
    PRAGMA integrity_check;
    SELECT count(*) FROM t1;
  } {ok 100}
  
  do_execsql_test 1.$tn.4 {
    INSERT INTO t1 SELECT -x, y, z FROM t2;
    PRAGMA integrity_check;
  } {ok}

  do_execsql_test 1.$tn.5 {
    SELECT count(*) FROM t1;
  } {200}

  do_test 1.$tn.6 {
    sorter_count { INSERT INTO t1 SELECT * FROM t2 }
  } $nSort
}

#-------------------------------------------------------------------------
# The following test cases check that the sorters are disabled if any
# of the following are true:
#
#   2.1: The statement specifies "ON CONFLICT FAIL", "IGNORE" or "REPLACE".
#
#   2.2: The statement does not explicitly specify a conflict mode and 
#        there are one or more PRIMARY KEY or UNIQUE constraints with 
#        "OR FAIL", "OR IGNORE" or "OR REPLACE" as the conflict handling 
#        mode.
#
#   2.3: There are one or more INSERT triggers on the target table.
#
#   2.4: The target table is the parent or child of an FK constraint.
#

do_execsql_test 2.1.1 {
  CREATE TABLE x1(a, b, c);
  CREATE INDEX x1a ON x1(a);

  CREATE TABLE x2(a, b, c);
  CREATE UNIQUE INDEX x2a ON x2(a);

  CREATE TABLE x3(a PRIMARY KEY, b, c);
  CREATE TABLE x4(a PRIMARY KEY, b, c) WITHOUT ROWID;
}

do_test 2.1.2 { sorter_count { INSERT OR REPLACE INTO x1 SELECT * FROM t2 } } 0
do_test 2.1.3 { sorter_count { INSERT OR REPLACE INTO x2 SELECT * FROM t2 } } 0
do_test 2.1.4 { sorter_count { INSERT OR REPLACE INTO x3 SELECT * FROM t2 } } 0
do_test 2.1.5 { sorter_count { INSERT OR REPLACE INTO x4 SELECT * FROM t2 } } 0

do_test 2.1.6 { sorter_count { INSERT OR IGNORE INTO x1 SELECT * FROM t2 } } 0
do_test 2.1.7 { sorter_count { INSERT OR IGNORE INTO x2 SELECT * FROM t2 } } 0
do_test 2.1.8 { sorter_count { INSERT OR IGNORE INTO x3 SELECT * FROM t2 } } 0
do_test 2.1.9 { sorter_count { INSERT OR IGNORE INTO x4 SELECT * FROM t2 } } 0

do_test 2.1.10 { sorter_count { INSERT OR FAIL INTO x1 SELECT * FROM t2 } } 0
do_test 2.1.11 { sorter_count { INSERT OR FAIL INTO x2 SELECT * FROM t2 } } 0
do_test 2.1.12 { sorter_count { INSERT OR FAIL INTO x3 SELECT * FROM t2 } } 0
do_test 2.1.13 { sorter_count { INSERT OR FAIL INTO x4 SELECT * FROM t2 } } 0

do_test 2.1.14 { sorter_count { INSERT OR ROLLBACK INTO x1 SELECT * FROM t2} } 1
do_test 2.1.15 { sorter_count { INSERT OR ROLLBACK INTO x2 SELECT * FROM t2} } 1
do_test 2.1.16 { sorter_count { INSERT OR ROLLBACK INTO x3 SELECT * FROM t2} } 1
do_test 2.1.17 { sorter_count { INSERT OR ROLLBACK INTO x4 SELECT * FROM t2} } 1

do_test 2.1.18 { sorter_count { INSERT OR ABORT INTO x1 SELECT * FROM t2 } } 1
do_test 2.1.19 { sorter_count { INSERT OR ABORT INTO x2 SELECT * FROM t2 } } 1
do_test 2.1.20 { sorter_count { INSERT OR ABORT INTO x3 SELECT * FROM t2 } } 1
do_test 2.1.21 { sorter_count { INSERT OR ABORT INTO x4 SELECT * FROM t2 } } 1


foreach {tn scount schema} {
  2.1   0 { CREATE TABLE t1(a UNIQUE ON CONFLICT FAIL, b, c) }
  2.2   0 { CREATE TABLE t1(a, b UNIQUE ON CONFLICT IGNORE, c) }
  2.3   0 { CREATE TABLE t1(a, b, c UNIQUE ON CONFLICT REPLACE) }
  2.4   0 { CREATE TABLE t1(a PRIMARY KEY ON CONFLICT FAIL, b, c) }
  2.5   0 { CREATE TABLE t1(a, b PRIMARY KEY ON CONFLICT IGNORE, c) }
  2.6   0 { CREATE TABLE t1(a, b, c PRIMARY KEY ON CONFLICT REPLACE) }
  2.7   0 { 
    CREATE TABLE t1(a PRIMARY KEY ON CONFLICT FAIL, b, c) WITHOUT ROWID
  }
  2.8   0 { 
    CREATE TABLE t1(a, b PRIMARY KEY ON CONFLICT IGNORE, c) WITHOUT ROWID
  }
  2.9   0 { 
    CREATE TABLE t1(a, b, c PRIMARY KEY ON CONFLICT REPLACE) WITHOUT ROWID
  }

  3.1   1 {
    CREATE TABLE t1(a, b, c);
    CREATE INDEX i1 ON t1(a);
  }
  3.2   0 {
    CREATE TABLE t1(a, b, c);
    CREATE INDEX i1 ON t1(a);
    CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END;
  }
  3.3   0 {
    CREATE TABLE t1(a, b, c);
    CREATE INDEX i1 ON t1(a);
    CREATE TRIGGER tr2 BEFORE INSERT ON t1 BEGIN SELECT 1; END;
  }

  4.1   2 {
    CREATE TABLE t1(a PRIMARY KEY, b, c);
    CREATE INDEX i1 ON t1(a);
    CREATE TABLE c1(x, y REFERENCES t1 DEFERRABLE INITIALLY DEFERRED);
    PRAGMA foreign_keys = 0;
  }
  4.2   0 {
    CREATE TABLE t1(a PRIMARY KEY, b, c);
    CREATE INDEX i1 ON t1(a);
    CREATE TABLE c1(x, y REFERENCES t1 DEFERRABLE INITIALLY DEFERRED);
    PRAGMA foreign_keys = 1;
  }

  4.3   1 {
    CREATE TABLE p1(x, y UNIQUE);
    CREATE TABLE t1(a, b, c REFERENCES p1(y));
    CREATE INDEX i1 ON t1(a);
    PRAGMA foreign_keys = 0;
  }
  4.4   0 {
    CREATE TABLE p1(x, y UNIQUE);
    CREATE TABLE t1(a, b, c REFERENCES p1(y));
    CREATE INDEX i1 ON t1(a);
    PRAGMA foreign_keys = 1;
  }

} {
  execsql { 
    DROP TABLE IF EXISTS t1;
    DROP TABLE IF EXISTS c1;
    DROP TABLE IF EXISTS p1;
  }

  do_test 2.2.$tn {
    execsql $schema
    sorter_count { INSERT INTO t1 SELECT * FROM t2 }
  } $scount
}

#-------------------------------------------------------------------------
# Test that if a UNIQUE constraint is violated and the on conflict mode
# is either ABORT or ROLLBACK, the conflict is handled correctly.
#
#   3.2: Check that conflicts are actually detected. 
#   3.3: Check that OR ROLLBACK really does rollback the transaction.
#   3.4: Check that OR ABORT does not.
#
do_execsql_test 3.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(b, c));
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);
  INSERT INTO t1 VALUES(7, 8, 9);
  CREATE TABLE src(a, b, c);
}

do_catchsql_test 3.2.1 {
  INSERT INTO src VALUES (10, 11, 12), (7, 14, 12);
  INSERT INTO t1 SELECT * FROM src;
} {1 {UNIQUE constraint failed: t1.a}}

do_catchsql_test 3.2.2 {
  DELETE FROM src;
  INSERT INTO src VALUES (10, 11, 12), (13, 5, 6);
  INSERT INTO t1 SELECT * FROM src;
} {1 {UNIQUE constraint failed: t1.b, t1.c}}

do_catchsql_test 3.2.3.1 {
  CREATE TABLE t3(a);
  CREATE UNIQUE INDEX t3a ON t3(a);

  CREATE TABLE t3src(a);
  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<10 )
  INSERT INTO t3src SELECT 'abc' FROM cnt;
} {0 {}}

#  execsql { PRAGMA vdbe_trace = 1 }
do_catchsql_test 3.2.3.2 {
  INSERT INTO t3 SELECT * FROM t3src;
} {1 {UNIQUE constraint failed: t3.a}}

do_catchsql_test 3.3.1 {
  DELETE FROM src;
  BEGIN;
    INSERT INTO src VALUES (10, 11, 12), (7, 13, 14);
    INSERT OR ROLLBACK INTO t1 SELECT * FROM src;
} {1 {UNIQUE constraint failed: t1.a}}
do_catchsql_test 3.3.2 {
  DELETE FROM src;
  BEGIN;
    INSERT INTO src VALUES (10, 11, 12), (13, 5, 6);
    INSERT OR ROLLBACK INTO t1 SELECT * FROM src;
} {1 {UNIQUE constraint failed: t1.b, t1.c}}
do_test 3.3.3 {
  sqlite3_get_autocommit db
} 1

do_catchsql_test 3.4.1 {
  DELETE FROM src;
  BEGIN;
    INSERT INTO src VALUES (10, 11, 12), (7, 14, 12);
    INSERT OR ABORT INTO t1 SELECT * FROM src;
} {1 {UNIQUE constraint failed: t1.a}}
do_catchsql_test 3.4.2 {
  ROLLBACK;
  DELETE FROM src;
  BEGIN;
    INSERT INTO src VALUES (10, 11, 12), (13, 5, 6);
    INSERT OR ABORT INTO t1 SELECT * FROM src;
} {1 {UNIQUE constraint failed: t1.b, t1.c}}
do_test 3.4.3 {
  sqlite3_get_autocommit db
} 0
do_execsql_test 3.4.4 { ROLLBACK }

#-------------------------------------------------------------------------
# The following tests - 4.* - check that this optimization is actually
# doing something helpful. They do this by executing a big 
# "INSERT INTO SELECT" statement in wal mode with a small pager cache.
# Once with "OR FAIL" (so that the sorters are not used) and once with
# the default "OR ABORT" (so that they are).
#
# If the sorters are doing their job, the wal file generated by the 
# "OR ABORT" case should be much smaller than the "OR FAIL" trial.
#

proc odd_collate {lhs rhs} {
  string compare [string range $lhs 6 end] [string range $rhs 6 end]
}

proc do_insert6_4_test {tn sql} {

  reset_db
  db collate odd_collate odd_collate
  execsql $sql
  db_save_and_close

  foreach {tn2 ::onerror ::var} {
    1 "OR ABORT" ::sz1
    2 "OR FAIL"  ::sz2
  } {
    do_test $tn.$tn2 {
      db_restore_and_reopen
      db collate odd_collate odd_collate
      execsql "
        PRAGMA journal_mode = wal;
        PRAGMA cache_size = 5;
        PRAGMA wal_autocheckpoint = 0;
        INSERT $onerror INTO t1 SELECT * FROM src;
      "
      set $var [file size test.db-wal]
      db close
    } {}
  }

  do_test $tn.3.($::sz1<$::sz2) {
    expr {$sz1 < ($sz2/2)}
  } 1

  sqlite3 db test.db
  db collate odd_collate odd_collate
  integrity_check $tn.4 
}

do_insert6_4_test 4.1 {
  CREATE TABLE t1(a, b, c);
  CREATE UNIQUE INDEX t1a ON t1(a);
  CREATE UNIQUE INDEX t1bc ON t1(b, c);

  CREATE TABLE src(x, y, z);
  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 )
  INSERT INTO src 
  SELECT randomblob(50), randomblob(50), randomblob(50) FROM cnt;
}

do_insert6_4_test 4.2 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, x);
  CREATE UNIQUE INDEX t1b ON t1(b);
  CREATE INDEX t1x1 ON t1(x);
  CREATE INDEX t1x2 ON t1(x);
  CREATE INDEX t1x3 ON t1(x);
  CREATE INDEX t1x4 ON t1(x);

  CREATE TABLE src(a, b, x);
  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 )
  INSERT INTO src 
  SELECT random(), x, zeroblob(50) FROM cnt;
}

do_insert6_4_test 4.3 {
  CREATE TABLE t1(a, b, c);
  CREATE UNIQUE INDEX t1ab ON t1(a, b);
  CREATE UNIQUE INDEX t1ac ON t1(a, c);

  CREATE TABLE src(a, b, c);
  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 )
  INSERT INTO src 
  SELECT zeroblob(50), randomblob(50), randomblob(50) FROM cnt;
}

db collate odd_collate odd_collate
do_insert6_4_test 4.5 {
  CREATE TABLE t1(t COLLATE odd_collate, v COLLATE odd_collate);
  CREATE UNIQUE INDEX t1t ON t1(t);
  CREATE UNIQUE INDEX t1v ON t1(v);

  CREATE TABLE src(t, v);
  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 )
  INSERT INTO src 
  SELECT hex(randomblob(50)), hex(randomblob(50)) FROM cnt;
}

db collate odd_collate odd_collate
do_insert6_4_test 4.6 {
  CREATE TABLE t1(t COLLATE odd_collate PRIMARY KEY) WITHOUT ROWID;
  CREATE TABLE src(t);
  WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 )
  INSERT INTO src 
  SELECT hex(randomblob(50)) FROM cnt;
}

#-------------------------------------------------------------------------
# At one point the sorters were used for INSERT statements that specify
# "OR FAIL", "REPLACE" or "IGNORE" if there were no PRIMARY KEY or
# UNIQUE indexes. This is incorrect, as all such tables have an implicit
# IPK column. So using the sorters can cause corruption. This test checks
# that that problem no longer exists.
#
reset_db
do_execsql_test 5.1 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  CREATE INDEX t1b ON t1(b);
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);
}

do_catchsql_test 5.2 {
  INSERT OR FAIL INTO t1 
  SELECT 2, 'x', 'x' UNION ALL SELECT 3, 'x', 'x' UNION ALL SELECT 4, 'x', 'x';
} {1 {UNIQUE constraint failed: t1.a}}

integrity_check 5.3


finish_test