path: root/storage/spider/mysql-test/spider/handler/t/spider_fixes_part.test

# This test tests for Spider's bug fixes
source include/have_log_bin.inc;
--disable_warnings
--disable_query_log
--disable_result_log
--source test_init.inc
--source slave_test_init.inc
--enable_result_log
--enable_query_log
if (!$HAVE_PARTITION)
{
  --disable_query_log
  --disable_result_log
  --source slave_test_deinit.inc
  --source test_deinit.inc
  --enable_result_log
  --enable_query_log
  --enable_warnings
  skip Test requires partitioning;
}

--echo
--echo drop and create databases
--connection master_1
DROP DATABASE IF EXISTS auto_test_local;
CREATE DATABASE auto_test_local;
USE auto_test_local;
if ($USE_REPLICATION)
{
  --connection slave1_1
  DROP DATABASE IF EXISTS auto_test_local;
  CREATE DATABASE auto_test_local;
  USE auto_test_local;
}
if ($USE_CHILD_GROUP2)
{
  --connection child2_1
  DROP DATABASE IF EXISTS auto_test_remote;
  CREATE DATABASE auto_test_remote;
  USE auto_test_remote;
  --connection child2_2
  DROP DATABASE IF EXISTS auto_test_remote2;
  CREATE DATABASE auto_test_remote2;
  USE auto_test_remote2;
}
--enable_warnings

--echo
--echo test select 1
--connection master_1
SELECT 1;
if ($USE_CHILD_GROUP2)
{
  if (!$OUTPUT_CHILD_GROUP2)
  {
    --disable_query_log
    --disable_result_log
  }
  --connection child2_1
  SELECT 1;
  --connection child2_2
  SELECT 1;
  if (!$OUTPUT_CHILD_GROUP2)
  {
    --enable_query_log
    --enable_result_log
  }
}

--connection master_1
--disable_warnings
DROP TABLE IF EXISTS tb_l;
--enable_warnings
--disable_query_log
echo CREATE TABLE tb_l (
  a INT,
  b CHAR(1),
  c DATETIME,
  PRIMARY KEY(a)
) MASTER_1_ENGINE2 MASTER_1_CHARSET2;
eval CREATE TABLE tb_l (
  a INT,
  b CHAR(1),
  c DATETIME,
  PRIMARY KEY(a)
) $MASTER_1_ENGINE2 $MASTER_1_CHARSET2;
--enable_query_log
INSERT INTO tb_l (a, b, c) VALUES
  (1, 'a', '2008-08-01 10:21:39'),
  (2, 'b', '2000-01-01 00:00:00'),
  (3, 'e', '2007-06-04 20:03:11'),
  (4, 'd', '2003-11-30 05:01:03'),
  (5, 'c', '2001-12-31 23:59:59');

--echo
--echo 2.17
--echo partition with sort
if ($HAVE_PARTITION)
{
  if ($USE_CHILD_GROUP2)
  {
    if (!$OUTPUT_CHILD_GROUP2)
    {
      --disable_query_log
      --disable_result_log
    }
    --connection child2_2
    if ($OUTPUT_CHILD_GROUP2)
    {
      --disable_query_log
      echo CHILD2_2_DROP_TABLES;
      echo CHILD2_2_CREATE_TABLES;
    }
    --disable_warnings
    eval $CHILD2_2_DROP_TABLES;
    --enable_warnings
    eval $CHILD2_2_CREATE_TABLES;
    if ($OUTPUT_CHILD_GROUP2)
    {
      --enable_query_log
    }
    if ($USE_GENERAL_LOG)
    {
      TRUNCATE TABLE mysql.general_log;
    }
    --connection child2_1
    if ($OUTPUT_CHILD_GROUP2)
    {
      --disable_query_log
      echo CHILD2_1_DROP_TABLES2;
      echo CHILD2_1_CREATE_TABLES2;
    }
    --disable_warnings
    eval $CHILD2_1_DROP_TABLES2;
    --enable_warnings
    eval $CHILD2_1_CREATE_TABLES2;
    if ($OUTPUT_CHILD_GROUP2)
    {
      --enable_query_log
    }
    if ($USE_GENERAL_LOG)
    {
      TRUNCATE TABLE mysql.general_log;
    }
    if (!$OUTPUT_CHILD_GROUP2)
    {
      --enable_query_log
      --enable_result_log
    }
  }
  --connection master_1
  --disable_query_log
  echo CREATE TABLE ta_l2 (
    a INT,
    b CHAR(1),
    c DATETIME,
    PRIMARY KEY(a)
  ) MASTER_1_ENGINE MASTER_1_COMMENT2_P_2_1;
  eval CREATE TABLE ta_l2 (
    a INT,
    b CHAR(1),
    c DATETIME,
    PRIMARY KEY(a)
  ) $MASTER_1_ENGINE $MASTER_1_COMMENT2_P_2_1;
  --enable_query_log
  INSERT INTO ta_l2 SELECT a, b, c FROM tb_l;
  SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 WHERE a > 1
  ORDER BY a;
  if ($USE_CHILD_GROUP2)
  {
    if (!$OUTPUT_CHILD_GROUP2)
    {
      --disable_query_log
      --disable_result_log
    }
    --connection child2_2
    if ($USE_GENERAL_LOG)
    {
      SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %';
    }
    eval $CHILD2_2_SELECT_TABLES;
    --connection child2_1
    if ($USE_GENERAL_LOG)
    {
      SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %';
    }
    eval $CHILD2_1_SELECT_TABLES2;
    if (!$OUTPUT_CHILD_GROUP2)
    {
      --enable_query_log
      --enable_result_log
    }
  }
}

--echo
--echo 2.23
--echo partition update with moving partition
if ($HAVE_PARTITION)
{
  --connection master_1
  --disable_warnings
  DROP TABLE IF EXISTS ta_l2;
  --enable_warnings
  if ($USE_CHILD_GROUP2)
  {
    if (!$OUTPUT_CHILD_GROUP2)
    {
      --disable_query_log
      --disable_result_log
    }
    --connection child2_2
    if ($OUTPUT_CHILD_GROUP2)
    {
      --disable_query_log
      echo CHILD2_2_DROP_TABLES;
      echo CHILD2_2_CREATE_TABLES;
    }
    --disable_warnings
    eval $CHILD2_2_DROP_TABLES;
    --enable_warnings
    eval $CHILD2_2_CREATE_TABLES;
    if ($OUTPUT_CHILD_GROUP2)
    {
      --enable_query_log
    }
    if ($USE_GENERAL_LOG)
    {
      TRUNCATE TABLE mysql.general_log;
    }
    --connection child2_1
    if ($OUTPUT_CHILD_GROUP2)
    {
      --disable_query_log
      echo CHILD2_1_DROP_TABLES2;
      echo CHILD2_1_CREATE_TABLES2;
    }
    --disable_warnings
    eval $CHILD2_1_DROP_TABLES2;
    --enable_warnings
    eval $CHILD2_1_CREATE_TABLES2;
    if ($OUTPUT_CHILD_GROUP2)
    {
      --enable_query_log
    }
    if ($USE_GENERAL_LOG)
    {
      TRUNCATE TABLE mysql.general_log;
    }
    if (!$OUTPUT_CHILD_GROUP2)
    {
      --enable_query_log
      --enable_result_log
    }
  }
  --connection master_1
  --disable_query_log
  echo CREATE TABLE ta_l2 (
    a INT,
    b CHAR(1),
    c DATETIME,
    PRIMARY KEY(a)
  ) MASTER_1_ENGINE MASTER_1_COMMENT2_P_2_1;
  eval CREATE TABLE ta_l2 (
    a INT,
    b CHAR(1),
    c DATETIME,
    PRIMARY KEY(a)
  ) $MASTER_1_ENGINE $MASTER_1_COMMENT2_P_2_1;
  --enable_query_log
  INSERT INTO ta_l2 (a, b, c) VALUES (3, 'B', '2010-09-26 00:00:00');
  UPDATE ta_l2 SET a = 4 WHERE a = 3;
  SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2;
  if ($USE_CHILD_GROUP2)
  {
    if (!$OUTPUT_CHILD_GROUP2)
    {
      --disable_query_log
      --disable_result_log
    }
    --connection child2_2
    if ($USE_GENERAL_LOG)
    {
      SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %';
      SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%insert %';
      SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%update %';
      SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%delete %';
    }
    eval $CHILD2_2_SELECT_TABLES;
    --connection child2_1
    if ($USE_GENERAL_LOG)
    {
      SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %';
      SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%insert %';
      SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%update %';
      SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%delete %';
    }
    eval $CHILD2_1_SELECT_TABLES2;
    if (!$OUTPUT_CHILD_GROUP2)
    {
      --enable_query_log
      --enable_result_log
    }
  }
}

--echo index merge with partition
if ($HAVE_PARTITION)
{
  --connection master_1
  --disable_warnings
  DROP TABLE IF EXISTS ta_l_int;
  --enable_warnings
  if ($USE_CHILD_GROUP2)
  {
    if (!$OUTPUT_CHILD_GROUP2)
    {
      --disable_result_log
    }
    --disable_query_log
    --connection child2_2
    if ($OUTPUT_CHILD_GROUP2)
    {
      echo CHILD2_2_DROP_TABLES5;
      echo CHILD2_2_CREATE_TABLES5;
    }
    --disable_warnings
    eval $CHILD2_2_DROP_TABLES5;
    --enable_warnings
    eval $CHILD2_2_CREATE_TABLES5;
    --connection child2_1
    if ($OUTPUT_CHILD_GROUP2)
    {
      echo CHILD2_1_DROP_TABLES5;
      echo CHILD2_1_CREATE_TABLES5;
    }
    --disable_warnings
    eval $CHILD2_1_DROP_TABLES5;
    --enable_warnings
    eval $CHILD2_1_CREATE_TABLES5;
    --enable_query_log
    if (!$OUTPUT_CHILD_GROUP2)
    {
      --enable_result_log
    }
  }
  --connection master_1
  --disable_query_log
  echo CREATE TABLE ta_l_int (
    a INT AUTO_INCREMENT,
    b INT DEFAULT 10,
    c INT DEFAULT 11,
    PRIMARY KEY(a),
    KEY idx1(b),
    KEY idx2(c)
  ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT3_P_2_1;
  eval CREATE TABLE ta_l_int (
    a INT AUTO_INCREMENT,
    b INT DEFAULT 10,
    c INT DEFAULT 11,
    PRIMARY KEY(a),
    KEY idx1(b),
    KEY idx2(c)
  ) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT3_P_2_1;
  --enable_query_log
  INSERT INTO ta_l_int (a, b, c) VALUES (1, 2, 3);
  INSERT INTO ta_l_int (a, b, c) SELECT a + 1, b + 1, c + 1 FROM ta_l_int;
  INSERT INTO ta_l_int (a, b, c) SELECT a + 2, b + 2, c + 2 FROM ta_l_int;
  INSERT INTO ta_l_int (a, b, c) SELECT a + 4, b + 4, c + 4 FROM ta_l_int;
  INSERT INTO ta_l_int (a, b, c) SELECT a + 8, b + 8, c + 8 FROM ta_l_int;
  if ($USE_CHILD_GROUP2)
  {
    if (!$OUTPUT_CHILD_GROUP2)
    {
      --disable_query_log
      --disable_result_log
    }
    --connection child2_2
    if ($USE_GENERAL_LOG)
    {
      TRUNCATE TABLE mysql.general_log;
    }
    --connection child2_1
    if ($USE_GENERAL_LOG)
    {
      TRUNCATE TABLE mysql.general_log;
    }
    if (!$OUTPUT_CHILD_GROUP2)
    {
      --enable_query_log
      --enable_result_log
    }
  }
  --connection master_1
  SELECT a, b, c FROM ta_l_int force index(primary, idx1, idx2)
  WHERE a = 5 OR b = 5 OR c = 5 ORDER BY a;
  if ($USE_CHILD_GROUP2)
  {
    if (!$OUTPUT_CHILD_GROUP2)
    {
      --disable_query_log
      --disable_result_log
    }
    --connection child2_2
    if ($USE_GENERAL_LOG)
    {
      SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %';
    }
    --connection child2_1
    if ($USE_GENERAL_LOG)
    {
      SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%select %';
    }
    if (!$OUTPUT_CHILD_GROUP2)
    {
      --enable_query_log
      --enable_result_log
    }
  }
}


--echo
--echo 2.26
--echo auto_increment with partition
if ($HAVE_PARTITION)
{
  if ($USE_CHILD_GROUP2)
  {
    if (!$OUTPUT_CHILD_GROUP2)
    {
      --disable_query_log
      --disable_result_log
    }
    --connection child2_1
    if ($OUTPUT_CHILD_GROUP2)
    {
      --disable_query_log
      echo CHILD2_1_DROP_INCREMENT_TABLES1;
      echo CHILD2_1_CREATE_INCREMENT_TABLES1;
      echo CHILD2_1_AUTO_INCREMENT_INCREMENT2;
      echo CHILD2_1_AUTO_INCREMENT_OFFSET2;
    }
    --disable_warnings
    eval $CHILD2_1_DROP_INCREMENT_TABLES1;
    --enable_warnings
    eval $CHILD2_1_CREATE_INCREMENT_TABLES1;
    eval $CHILD2_1_AUTO_INCREMENT_INCREMENT2;
    eval $CHILD2_1_AUTO_INCREMENT_OFFSET2;
    if ($OUTPUT_CHILD_GROUP2)
    {
      --enable_query_log
    }
    if ($USE_GENERAL_LOG)
    {
      TRUNCATE TABLE mysql.general_log;
    }
    --connection child2_2
    if ($OUTPUT_CHILD_GROUP2)
    {
      --disable_query_log
      echo CHILD2_2_DROP_INCREMENT_TABLES1;
      echo CHILD2_2_CREATE_INCREMENT_TABLES1;
      echo CHILD2_2_AUTO_INCREMENT_INCREMENT2;
      echo CHILD2_2_AUTO_INCREMENT_OFFSET2;
    }
    --disable_warnings
    eval $CHILD2_2_DROP_INCREMENT_TABLES1;
    --enable_warnings
    eval $CHILD2_2_CREATE_INCREMENT_TABLES1;
    eval $CHILD2_2_AUTO_INCREMENT_INCREMENT2;
    eval $CHILD2_2_AUTO_INCREMENT_OFFSET2;
    if ($OUTPUT_CHILD_GROUP2)
    {
      --enable_query_log
    }
    if ($USE_GENERAL_LOG)
    {
      TRUNCATE TABLE mysql.general_log;
    }
    if (!$OUTPUT_CHILD_GROUP2)
    {
      --enable_query_log
      --enable_result_log
    }
  }
  --connection master_1
  SET @original_spider_auto_increment_mode = @@SESSION.spider_auto_increment_mode;
  SET SESSION spider_auto_increment_mode = -1;
  if ($USE_REPLICATION)
  {
    save_master_pos;
    --connection slave1_1
    sync_with_master;
    --connection master_1
    --disable_query_log
    SET SESSION sql_log_bin= 0;
    --enable_query_log
  }
  --disable_warnings
  DROP TABLE IF EXISTS t1;
  --enable_warnings
  --disable_query_log
  echo CREATE TABLE t1 (
    id int(11) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
  ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_INCREMENT1_P_1;
  echo MASTER_1_AUTO_INCREMENT_INCREMENT2;
  echo MASTER_1_AUTO_INCREMENT_OFFSET2;
  eval CREATE TABLE t1 (
    id int(11) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
  ) $MASTER_1_ENGINE $MASTER_1_CHARSET $MASTER_1_COMMENT_INCREMENT1_P_1;
  --disable_ps_protocol
  eval $MASTER_1_AUTO_INCREMENT_INCREMENT2;
  eval $MASTER_1_AUTO_INCREMENT_OFFSET2;
  --enable_ps_protocol
  if ($USE_REPLICATION)
  {
    SET SESSION sql_log_bin= 1;
    --connection slave1_1
    --disable_warnings
    DROP TABLE IF EXISTS t1;
    --enable_warnings
    echo CREATE TABLE t1 (
      id int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (id)
    ) SLAVE1_1_ENGINE SLAVE1_1_CHARSET SLAVE1_1_COMMENT_INCREMENT1_P_1;
    eval CREATE TABLE t1 (
      id int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (id)
    ) $SLAVE1_1_ENGINE $SLAVE1_1_CHARSET $SLAVE1_1_COMMENT_INCREMENT1_P_1;
    --connection master_1
  }
  --enable_query_log
  INSERT INTO t1 () VALUES ();
  SELECT LAST_INSERT_ID();
  SELECT MAX(id) FROM t1;
  INSERT INTO t1 () VALUES ();
  SELECT LAST_INSERT_ID();
  SELECT MAX(id) FROM t1;
  INSERT INTO t1 (id) VALUES (null);
  SELECT LAST_INSERT_ID();
  SELECT MAX(id) FROM t1;
  INSERT INTO t1 (id) VALUES (null);
  SELECT LAST_INSERT_ID();
  SELECT MAX(id) FROM t1;
  INSERT INTO t1 () VALUES (),(),(),();
  SELECT LAST_INSERT_ID();
  SELECT id FROM t1 ORDER BY id;
  SET INSERT_ID=5000;
  INSERT INTO t1 () VALUES ();
  SELECT LAST_INSERT_ID();
  SELECT MAX(id) FROM t1;
  INSERT INTO t1 (id) VALUES (10000);
  SELECT LAST_INSERT_ID();
  SELECT MAX(id) FROM t1;
  INSERT INTO t1 (id) VALUES (1000);
  SELECT LAST_INSERT_ID();
  SELECT MAX(id) FROM t1;
  SET SESSION spider_auto_increment_mode = @original_spider_auto_increment_mode;
  if ($USE_REPLICATION)
  {
    save_master_pos;
    --connection slave1_1
    sync_with_master;
    SELECT id FROM t1 ORDER BY id;
    --connection master_1
    --disable_query_log
    SET SESSION sql_log_bin= 0;
    --enable_query_log
  }
  if ($USE_CHILD_GROUP2)
  {
    if (!$OUTPUT_CHILD_GROUP2)
    {
      --disable_query_log
      --disable_result_log
    }
    --connection child2_1
    if ($USE_GENERAL_LOG)
    {
      SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%insert %';
    }
    eval $CHILD2_1_SELECT_INCREMENT_TABLES1;
    if ($OUTPUT_CHILD_GROUP2)
    {
      --disable_query_log
      echo CHILD2_1_AUTO_INCREMENT_INCREMENT1;
      echo CHILD2_1_AUTO_INCREMENT_OFFSET1;
    }
    eval $CHILD2_1_AUTO_INCREMENT_INCREMENT1;
    eval $CHILD2_1_AUTO_INCREMENT_OFFSET1;
    if ($OUTPUT_CHILD_GROUP2)
    {
      --enable_query_log
    }
    --connection child2_2
    if ($USE_GENERAL_LOG)
    {
      SELECT argument FROM mysql.general_log WHERE command_type != 'Execute' AND argument LIKE '%insert %';
    }
    eval $CHILD2_2_SELECT_INCREMENT_TABLES1;
    if ($OUTPUT_CHILD_GROUP2)
    {
      --disable_query_log
      echo CHILD2_2_AUTO_INCREMENT_INCREMENT1;
      echo CHILD2_2_AUTO_INCREMENT_OFFSET1;
    }
    eval $CHILD2_2_AUTO_INCREMENT_INCREMENT1;
    eval $CHILD2_2_AUTO_INCREMENT_OFFSET1;
    if ($OUTPUT_CHILD_GROUP2)
    {
      --enable_query_log
    }
    if (!$OUTPUT_CHILD_GROUP2)
    {
      --enable_query_log
      --enable_result_log
    }
  }
}


--echo
--echo deinit
--disable_warnings
--connection master_1
DROP DATABASE IF EXISTS auto_test_local;
if ($USE_REPLICATION)
{
  --connection slave1_1
  DROP DATABASE IF EXISTS auto_test_local;
}
if ($USE_CHILD_GROUP2)
{
  --connection child2_1
  DROP DATABASE IF EXISTS auto_test_remote;
  --connection child2_2
  DROP DATABASE IF EXISTS auto_test_remote2;
}
--disable_query_log
--disable_result_log
--source slave_test_deinit.inc
--source test_deinit.inc
--enable_result_log
--enable_query_log
--enable_warnings
--echo
--echo end of test