polardbxengine/mysql-test/suite/xengine/t/xengine.test

--source suite/xengine/include/have_xengine.inc

#
# XEngine Storage Engine tests
#
select ENGINE,COMMENT,TRANSACTIONS,XA,SAVEPOINTS from information_schema.engines where engine = 'xengine';

--disable_warnings
drop table if exists t0,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10;
drop table if exists t11,t12,t13,t14,t15,t16,t17,t18,t19,t20;
drop table if exists t21,t22,t23,t24,t25,t26,t27,t28,t29;
drop table if exists t30,t31,t32,t33,t34,t35,t36,t37,t38,t39;
drop table if exists t40,t41,t42,t43,t44,t45,t46,t47,t48,t49;
--enable_warnings

# Disable background compaction to prevent stats from affect explain output
SET @ORIG_PAUSE_BACKGROUND_WORK = @@XENGINE_PAUSE_BACKGROUND_WORK;
SET GLOBAL XENGINE_PAUSE_BACKGROUND_WORK = 1;

SET @ORIG_SQL_LOG_BIN = @@SQL_LOG_BIN;
SET SQL_LOG_BIN = 0;
--echo #
--echo # Issue #1: Don't update indexes if index values have not changed
--echo #
# [Jay Edgar] I moved this test first because it uses the
# xengine_number_keys_written value, but this value is affected out of band
# by drop tables.  There is a background thread that periodically processes
# through the list of dropped keys and if any are gone from the database it
# deletes information related to the key - and this delete causes this count
# to be incorrect.  I moved this test first and made the whole test require
# a fresh server to hopefully avoid tihs.
create table t1 (
  pk int primary key,
  a int,
  b int,
  key(a)
) engine=xengine;

insert into t1 values
(1,1,1), (2,2,2), (3,3,3), (4,4,4);

set @var1=(select variable_value
           from performance_schema.global_status
           where variable_name='xengine_number_keys_written');

--echo # Do an update that doesn't change the key 'a'.
update t1 set b=3334341 where a=2;

set @var2=(select variable_value
           from performance_schema.global_status
           where variable_name='xengine_number_keys_written');
--echo # The following should produce 1
select @var2 - @var1;

--echo # Do an update that sets the key to the same value
update t1 set a=pk where a=3;
set @var3=(select variable_value
           from performance_schema.global_status
           where variable_name='xengine_number_keys_written');
--echo # We have 'updated' column to the same value, so the following must return 0:
select @var3 - @var2;
drop table t1;
SET SQL_LOG_BIN = @ORIG_SQL_LOG_BIN;

create table t0 (a int primary key) engine=xengine;
show create table t0;
drop table t0;

create table t1 (a int primary key, b int) engine=xengine;
insert into t1 values (1,1);
insert into t1 values (2,2);

select * from t1;

--echo # Check that we can create another table and insert there
create table t2 (a varchar(10) primary key, b varchar(10)) engine=xengine charset latin1 collate latin1_bin;
insert into t2 value ('abc','def');
insert into t2 value ('hijkl','mnopq');
select * from t2;

--echo # Select again from t1 to see that records from different tables dont mix
select * from t1;

explain select * from t2 where a='no-such-key';
--replace_column 10 #
explain select * from t2 where a='abc';
select * from t2 where a='abc';

--echo # Try a composite PK
create table t3 (
  pk1 int,
  pk2 varchar(10),
  col1 varchar(10),
  primary key(pk1, pk2)
) engine=xengine;

insert into t3 values (2,'two', 'row#2');
insert into t3 values (3,'three', 'row#3');
insert into t3 values (1,'one', 'row#1');

select * from t3;
select * from t3 where pk1=3 and pk2='three';

drop table t1, t2, t3;

--echo #
--echo # Test blob values
--echo #

create table t4 (a int primary key, b blob) engine=xengine;
insert into t4 values (1, repeat('quux-quux', 60));
insert into t4 values (10, repeat('foo-bar', 43));
insert into t4 values (5, repeat('foo-bar', 200));

insert into t4 values (2, NULL);


select
 a,
 (case a
   when 1  then b=repeat('quux-quux', 60)
   when 10 then b=repeat('foo-bar', 43)
   when 5  then b=repeat('foo-bar', 200)
   when 2  then b is null
   else 'IMPOSSIBLE!' end) as CMP
from t4;

drop table t4;

--echo #
--echo # Test blobs of various sizes
--echo #

--echo # TINYBLOB
create table t5 (a int primary key, b tinyblob) engine=xengine;
insert into t5 values (1, repeat('quux-quux', 6));
insert into t5 values (10, repeat('foo-bar', 4));
insert into t5 values (5, repeat('foo-bar', 2));
select
 a,
 (case a
   when 1  then b=repeat('quux-quux', 6)
   when 10 then b=repeat('foo-bar', 4)
   when 5  then b=repeat('foo-bar', 2)
   else 'IMPOSSIBLE!' end) as CMP
from t5;
drop table t5;

--echo # MEDIUMBLOB
create table t6 (a int primary key, b mediumblob) engine=xengine;
insert into t6 values (1, repeat('AB', 65000));
insert into t6 values (10, repeat('bbb', 40000));
insert into t6 values (5, repeat('foo-bar', 2));
select
 a,
 (case a
   when 1  then b=repeat('AB', 65000)
   when 10 then b=repeat('bbb', 40000)
   when 5  then b=repeat('foo-bar', 2)
   else 'IMPOSSIBLE!' end) as CMP
from t6;
drop table t6;

--echo # LONGBLOB
create table t7 (a int primary key, b longblob) engine=xengine;
insert into t7 values (1, repeat('AB', 65000));
insert into t7 values (10, repeat('bbb', 40000));
insert into t7 values (5, repeat('foo-bar', 2));
select
 a,
 (case a
   when 1  then b=repeat('AB', 65000)
   when 10 then b=repeat('bbb', 40000)
   when 5  then b=repeat('foo-bar', 2)
   else 'IMPOSSIBLE!' end) as CMP
from t7;
drop table t7;


--echo #
--echo # Check if DELETEs work
--echo #
create table t8 (a varchar(10) primary key, col1 varchar(12)) engine=xengine charset latin1 collate latin1_bin;

insert into t8 values
 ('one', 'eins'),
 ('two', 'zwei'),
 ('three', 'drei'),
 ('four', 'vier'),
 ('five', 'funf');

--echo # Delete by PK
--replace_column 10 #
explain delete from t8 where a='three';
delete from t8 where a='three';

select * from t8;

--echo # Delete while doing a full table scan
delete from t8 where col1='eins' or col1='vier';
select * from t8;

--echo # delete w/o WHERE:
delete from t8;
select * from t8;

--echo #
--echo # Test UPDATEs
--echo #
insert into t8 values
 ('one', 'eins'),
 ('two', 'zwei'),
 ('three', 'drei'),
 ('four', 'vier'),
 ('five', 'funf');

update t8 set col1='dva' where a='two';

update t8 set a='fourAAA' where col1='vier';

select * from t8;
delete from t8;

--echo #
--echo # Basic transactions tests
--echo #
begin;
insert into t8 values ('trx1-val1', 'data');
insert into t8 values ('trx1-val2', 'data');
rollback;
select * from t8;

begin;
insert into t8 values ('trx1-val1', 'data');
insert into t8 values ('trx1-val2', 'data');
commit;
select * from t8;

drop table t8;

--echo #
--echo # Check if DROP TABLE works
--echo #
create table t8 (a varchar(10) primary key, col1 varchar(12)) engine=xengine;
select * from t8;
insert into t8 values ('foo','foo');
drop table t8;
create table t8 (a varchar(10) primary key, col1 varchar(12)) engine=xengine;
select * from t8;
drop table t8;

--echo #
--echo # MDEV-3961: Assertion ... on creating a TEMPORARY XEngine table
--echo #
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TEMPORARY TABLE t10 (pk INT PRIMARY KEY) ENGINE=XEngine;

--echo #
--echo # MDEV-3963: JOIN or WHERE conditions involving keys on XEngine tables don't work
--echo #
CREATE TABLE t10 (i INT PRIMARY KEY) ENGINE=XEngine;
INSERT INTO t10 VALUES (1),(3);
CREATE TABLE t11 (j INT PRIMARY KEY) ENGINE=XEngine;
INSERT INTO t11 VALUES (1),(4);

analyze table t10, t11;
select * from t10;
select * from t11;
--replace_column 10 # 11 #
EXPLAIN
SELECT * FROM t10, t11 WHERE i=j;
SELECT * FROM t10, t11 WHERE i=j;

DROP TABLE t10,t11;

--echo #
--echo # MDEV-3962: SELECT with ORDER BY causes "ERROR 1030 (HY000): Got error 122
--echo #
CREATE TABLE t12 (pk INT PRIMARY KEY) ENGINE=XEngine;
INSERT INTO t12 VALUES (2),(1);
SELECT * FROM t12 ORDER BY pk;
DROP TABLE t12;

--echo #
--echo # MDEV-3964: Assertion `!pk_descr' fails in ha_xengine::open on adding partitions ...
--echo #
--error ER_NOT_SUPPORTED_YET
create table t14 (pk int primary key) engine=XEngine partition by hash(pk) partitions 2;
#--error ER_GET_ERRNO
#alter table t14 add partition partitions 2;
# ^^ works, but causes weird warnings in error log.
#drop table t14;

--echo #
--echo # MDEV-3960: Server crashes on running DISCARD TABLESPACE on a XEngine table
--echo #
create table t9 (i int primary key) engine=xengine;
--error ER_ILLEGAL_HA
alter table t9 discard tablespace;
drop table t9;

--echo #
--echo # MDEV-3959: Assertion `slice->size() == table->s->reclength' fails ...
--echo #   on accessing a table after ALTER
--echo #
CREATE TABLE t15 (a INT, xengine_pk INT PRIMARY KEY) ENGINE=XEngine;
INSERT INTO t15 VALUES (1,1),(5,2);
#--error ER_ILLEGAL_HA
ALTER TABLE t15 DROP COLUMN a;
DROP TABLE t15;

--echo #
--echo # MDEV-3968: UPDATE produces a wrong result while modifying a PK on a XEngine table
--echo #
create table t16 (pk int primary key, a char(8)) engine=XEngine;
insert into t16 values (1,'a'),(2,'b'),(3,'c'),(4,'d');

#
# Not anymore: The following query will still eat a record because of CANT-SEE-OWN-CHANGES
# property.
#
--error ER_DUP_ENTRY
update t16 set pk=100, a = 'updated' where a in ('b','c');
select * from t16;
drop table t16;

--echo #
--echo # MDEV-3970: A set of assorted crashes on inserting a row into a XEngine table
--echo #
--disable_warnings
drop table if exists t_very_long_table_name;
--enable_warnings

CREATE TABLE `t_very_long_table_name` (
   `c` char(1) NOT NULL,
   `c0` char(0) NOT NULL,
   `c1` char(1) NOT NULL,
   `c20` char(20) NOT NULL,
   `c255` char(255) NOT NULL,
   PRIMARY KEY (`c255`)
 ) ENGINE=XEngine DEFAULT CHARSET=latin1;
INSERT INTO t_very_long_table_name VALUES ('a', '', 'c', REPEAT('a',20), REPEAT('x',255));
drop table t_very_long_table_name;


--echo #
--echo # Test table locking and read-before-write checks.
--echo #
create table t17 (pk varchar(12) primary key, col1 varchar(12)) engine=xengine;
insert into t17 values ('row1', 'val1');

--error ER_DUP_ENTRY
insert into t17 values ('row1', 'val1-try2');
--error ER_DUP_ENTRY
insert into t17 values ('ROW1', 'val1-try2');

insert into t17 values ('row2', 'val2');
insert into t17 values ('row3', 'val3');

--echo # This is ok
update t17 set pk='row4' where pk='row1';

--echo # This will try to overwrite another row:
--error ER_DUP_ENTRY
update t17 set pk='row3' where pk='row2';

select * from t17;

--echo #
--echo # Locking tests
--echo #

connect (con1,localhost,root,,);

--echo # First, make sure there's no locking when transactions update different rows
connection con1;
set autocommit=0;
update t17 set col1='UPD1' where pk='row2';

connection default;
update t17 set col1='UPD2' where pk='row3';

connection con1;
commit;

connection default;
select * from t17;

--echo # Check the variable
show variables like 'xengine_lock_wait_timeout';
set xengine_lock_wait_timeout=2; # seconds
show variables like 'xengine_lock_wait_timeout';

--echo # Try updating the same row from two transactions
connection con1;
begin;
update t17 set col1='UPD2-AA' where pk='row2';

connection default;
--error ER_LOCK_WAIT_TIMEOUT
update t17 set col1='UPD2-BB' where pk='row2';

set xengine_lock_wait_timeout=1000; # seconds
--send
  update t17 set col1='UPD2-CC' where pk='row2';

connection con1;
rollback;

connection default;
reap;
select * from t17 where pk='row2';

drop table t17;

disconnect con1;
--echo #
--echo #  MDEV-4035: XEngine: SELECT produces different results inside a transaction (read is not repeatable)
--echo #
--enable_connect_log

create table t18 (pk int primary key, i int) engine=XEngine;
begin;
select * from t18;
select * from t18 where pk = 1;

--connect (con1,localhost,root,,)
insert into t18 values (1,100);

--connection default
select * from t18;
select * from t18 where pk = 1;
commit;

drop table t18;

--echo #
--echo # MDEV-4036: XEngine: INSERT .. ON DUPLICATE KEY UPDATE does not work, produces ER_DUP_KEY
--echo #
create table t19 (pk int primary key, i int) engine=XEngine;
insert into t19 values (1,1);
insert into t19 values (1,100) on duplicate key update i = 102;
select * from t19;
drop table t19;

--echo # MDEV-4037: XEngine: REPLACE doesn't work, produces ER_DUP_KEY
create table t20 (pk int primary key, i int) engine=XEngine;
insert into t20 values (1,1);
replace into t20 values (1,100);
select * from t20;
drop table t20;

--echo #
--echo # MDEV-4041: Server crashes in Primary_key_comparator::get_hashnr on INSERT
--echo #
create table t21 (v varbinary(16) primary key, i int) engine=XEngine;
insert into t21 values ('a',1);
select * from t21;
drop table t21;

--echo #
--echo # MDEV-4047: XEngine: Assertion `0' fails in Protocol::end_statement() on multi-table INSERT IGNORE
--echo #

CREATE TABLE t22 (a int primary key) ENGINE=XEngine;
INSERT INTO t22 VALUES (1),(2);
CREATE TABLE t23 (b int primary key) ENGINE=XEngine;
INSERT INTO t23 SELECT * FROM t22;
DELETE IGNORE t22.*, t23.* FROM t22, t23 WHERE b < a;
DROP TABLE t22,t23;

--echo #
--echo # MDEV-4046: XEngine: Multi-table DELETE locks itself and ends with ER_LOCK_WAIT_TIMEOUT
--echo #
CREATE TABLE t24 (pk int primary key) ENGINE=XEngine;
INSERT INTO t24 VALUES (1),(2);

CREATE TABLE t25 LIKE t24;
INSERT INTO t25 SELECT * FROM t24;

DELETE t25.* FROM t24, t25;
DROP TABLE t24,t25;

--echo #
--echo # MDEV-4044: XEngine: UPDATE or DELETE with ORDER BY locks itself
--echo #
create table t26 (pk int primary key, c char(1)) engine=XEngine;
insert into t26 values (1,'a'),(2,'b');
update t26 set c = 'x' order by pk limit 1;
delete from t26 order by pk limit 1;
select * from t26;
drop table t26;


--echo #
--echo # Test whether SELECT ... FOR UPDATE puts locks
--echo #
create table t27(pk varchar(10) primary key, col1 varchar(20)) engine=XEngine;
insert into t27 values
  ('row1', 'row1data'),
  ('row2', 'row2data'),
  ('row3', 'row3data');

connection con1;
begin;
select * from t27 where pk='row3' for update;

connection default;
set xengine_lock_wait_timeout=1;
--error ER_LOCK_WAIT_TIMEOUT
update t27 set col1='row2-modified' where pk='row3';

connection con1;
rollback;
connection default;
disconnect con1;

drop table t27;

--echo #
--echo # MDEV-4060: XEngine: Assertion `! trx->batch' fails in
--echo #
create table t28 (pk int primary key, a int) engine=XEngine;
insert into t28 values (1,10),(2,20);
begin;
update t28 set a = 100 where pk = 3;
rollback;
select * from t28;
drop table t28;


--echo #
--echo # Secondary indexes
--echo #
create table t30 (
  pk varchar(16) not null primary key,
  key1 varchar(16) not null,
  col1 varchar(16) not null,
  key(key1)
) engine=xengine charset latin1 collate latin1_bin;

insert into t30 values ('row1', 'row1-key', 'row1-data');
insert into t30 values ('row2', 'row2-key', 'row2-data');
insert into t30 values ('row3', 'row3-key', 'row3-data');

analyze table t30;
--replace_column 10 #
explain
select * from t30 where key1='row2-key';
select * from t30 where key1='row2-key';

--replace_column 10 #
explain
select * from t30 where key1='row1';
--echo # This will produce nothing:
select * from t30 where key1='row1';

--replace_column 10 # 11 #
explain
select key1 from t30;
select key1 from t30;

--echo # Create a duplicate record
insert into t30 values ('row2a', 'row2-key', 'row2a-data');

--echo # Can we see it?
select * from t30 where key1='row2-key';

delete from t30 where pk='row2';
select * from t30 where key1='row2-key';

--echo #
--echo # Range scans on secondary index
--echo #
delete from t30;
insert into t30 values
  ('row1', 'row1-key', 'row1-data'),
  ('row2', 'row2-key', 'row2-data'),
  ('row3', 'row3-key', 'row3-data'),
  ('row4', 'row4-key', 'row4-data'),
  ('row5', 'row5-key', 'row5-data');
analyze table t30;

--replace_column 10 #
explain
select * from t30 where key1 <='row3-key';
select * from t30 where key1 <='row3-key';

--replace_column 10 #
explain
select * from t30 where key1 between 'row2-key' and 'row4-key';
select * from t30 where key1 between 'row2-key' and 'row4-key';

--replace_column 10 #
explain
select * from t30 where key1 in ('row2-key','row4-key');
select * from t30 where key1 in ('row2-key','row4-key');

--replace_column 10 # 11 #
explain
select key1 from t30 where key1 in ('row2-key','row4-key');
select key1 from t30 where key1 in ('row2-key','row4-key');

--replace_column 10 #
explain
select * from t30 where key1 > 'row1-key' and key1 < 'row4-key';
select * from t30 where key1 > 'row1-key' and key1 < 'row4-key';

--replace_column 10 #
explain
select * from t30 order by key1 limit 3;
select * from t30 order by key1 limit 3;

--replace_column 10 #
explain
select * from t30 order by key1 desc limit 3;
select * from t30 order by key1 desc limit 3;

--echo #
--echo # Range scans on primary key
--echo #
--replace_column 10 #
explain
select * from t30 where pk <='row3';
select * from t30 where pk <='row3';

--replace_column 10 #
explain
select * from t30 where pk between 'row2' and 'row4';
select * from t30 where pk between 'row2' and 'row4';

--replace_column 10 #
explain
select * from t30 where pk in ('row2','row4');
select * from t30 where pk in ('row2','row4');

--replace_column 10 #
explain
select * from t30 order by pk limit 3;
select * from t30 order by pk limit 3;

drop table t30;


--echo #
--echo # MDEV-3841: XEngine: Reading by PK prefix does not work
--echo #
create table t31 (i int, j int, k int, primary key(i,j,k)) engine=XEngine;
insert into t31 values (1,10,100),(2,20,200);
select * from t31 where i = 1;
select * from t31 where j = 10;
select * from t31 where k = 100;
select * from t31 where i = 1 and j = 10;
select * from t31 where i = 1 and k = 100;
select * from t31 where j = 10 and k = 100;
select * from t31 where i = 1 and j = 10 and k = 100;
drop table t31;

--echo #
--echo # MDEV-4055: XEngine: UPDATE/DELETE by a multi-part PK does not work
--echo #
create table t32 (i int, j int, k int, primary key(i,j,k), a varchar(8)) engine=XEngine;
insert into t32 values
  (1,10,100,''),
  (2,20,200,'');
select * from t32 where i = 1 and j = 10 and k = 100;
update t32 set a = 'updated' where i = 1 and j = 10 and k = 100;
select * from t32;
drop table t32;

--echo #
--echo # MDEV-3841: XEngine: Assertion `0' fails in ha_xengine::index_read_map on range select with ORDER BY .. DESC
--echo #
CREATE TABLE t33 (pk INT PRIMARY KEY, a CHAR(1)) ENGINE=XEngine;
INSERT INTO t33 VALUES (1,'a'),(2,'b');
SELECT * FROM t33 WHERE pk <= 10 ORDER BY pk DESC;
DROP TABLE t33;

--echo #
--echo # MDEV-4081: XEngine throws error 122 on an attempt to create a table with unique index
--echo #
#--error ER_GET_ERRMSG
--echo #  Unique indexes can be created, but uniqueness won't be enforced
create table t33 (pk int primary key, u int, unique index(u)) engine=XEngine;
drop table t33;

--echo #
--echo # MDEV-4077: XEngine: Wrong result (duplicate row) on select with range
--echo #
CREATE TABLE t34 (pk INT PRIMARY KEY) ENGINE=XEngine;
INSERT INTO t34 VALUES (10),(11);
SELECT pk FROM t34 WHERE pk > 5 AND pk < 15;
SELECT pk FROM t34 WHERE pk BETWEEN 5 AND 15;
SELECT pk FROM t34 WHERE pk > 5;
SELECT pk FROM t34 WHERE pk < 15;
drop table t34;

--echo #
--echo # MDEV-4086: XEngine does not allow a query with multi-part pk and index and ORDER BY .. DEC
--echo #
create table t35 (a int, b int, c int, d int, e int, primary key (a,b,c), key (a,c,d,e)) engine=XEngine;
insert into t35 values (1,1,1,1,1),(2,2,2,2,2);
select * from t35 where a = 1 and c = 1 and d = 1 order by e desc;
drop table t35;

--echo #
--echo # MDEV-4084: XEngine: Wrong result on IN subquery with index
--echo #
CREATE TABLE t36 (pk INT PRIMARY KEY, a INT, KEY(a)) ENGINE=XEngine;
INSERT INTO t36 VALUES (1,10),(2,20);
SELECT 3 IN ( SELECT a FROM t36 );
drop table t36;

--echo #
--echo # MDEV-4084: XEngine: Wrong result on IN subquery with index
--echo #
CREATE TABLE t37 (pk INT PRIMARY KEY, a INT, b CHAR(1), KEY(a), KEY(a,b))
  ENGINE=XEngine;
INSERT INTO t37 VALUES (1,10,'x'), (2,20,'y');
SELECT MAX(a) FROM t37 WHERE a < 100;
DROP TABLE t37;

--echo #
--echo # MDEV-4090: XEngine: Wrong result (duplicate rows) on range access with secondary key and ORDER BY DESC
--echo #
CREATE TABLE t38 (pk INT PRIMARY KEY, i INT, KEY(i)) ENGINE=XEngine;
INSERT INTO t38 VALUES (1,10), (2,20);
SELECT i FROM t38 WHERE i NOT IN (8) ORDER BY i DESC;
drop table t38;

--echo #
--echo # MDEV-4092: XEngine: Assertion `in_table(pa, a_len)' fails in Rdb_key_def::cmp_full_keys
--echo #            with a multi-part key and ORDER BY .. DESC
--echo #
CREATE TABLE t40 (pk1 INT PRIMARY KEY, a INT, b VARCHAR(1), KEY(b,a)) ENGINE=XEngine;
INSERT INTO t40 VALUES (1, 7,'x'),(2,8,'y');

CREATE TABLE t41 (pk2 INT PRIMARY KEY) ENGINE=XEngine;
INSERT INTO t41 VALUES (1),(2);

SELECT * FROM t40, t41 WHERE pk1 = pk2 AND b = 'o' ORDER BY a DESC;
DROP TABLE t40,t41;

--echo #
--echo # MDEV-4093: XEngine: IN subquery by secondary key with NULL among values returns true instead of NULL
--echo #
CREATE TABLE t42 (pk INT PRIMARY KEY, a INT, KEY(a)) ENGINE=XEngine;
INSERT INTO t42 VALUES (1, NULL),(2, 8);
SELECT ( 3 ) NOT IN ( SELECT a FROM t42 );
DROP TABLE t42;

--echo #
--echo # MDEV-4094: XEngine: Wrong result on SELECT and ER_KEY_NOT_FOUND on
--echo #            DELETE with search by NULL-able secondary key ...
--echo #
CREATE TABLE t43 (pk INT PRIMARY KEY, a INT, b CHAR(1), KEY(a)) ENGINE=XEngine;
INSERT INTO t43 VALUES (1,8,'g'),(2,9,'x');
UPDATE t43 SET pk = 10 WHERE a = 8;
REPLACE INTO t43 ( a ) VALUES ( 8 );
REPLACE INTO t43 ( b ) VALUES ( 'y' );
SELECT * FROM t43 WHERE a = 8;
DELETE FROM t43 WHERE a = 8;
DROP TABLE t43;

--echo #
--echo # Basic AUTO_INCREMENT tests
--echo #
create table t44(pk int primary key auto_increment, col1 varchar(12)) engine=xengine;
insert into t44 (col1) values ('row1');
insert into t44 (col1) values ('row2');
insert into t44 (col1) values ('row3');
select * from t44;
drop table t44;

--echo #
--echo # ALTER TABLE tests
--echo #
create table t45 (pk int primary key, col1 varchar(12)) engine=xengine;
insert into t45 values (1, 'row1');
insert into t45 values (2, 'row2');
alter table t45 rename t46;
select * from t46;
drop table t46;
--error ER_BAD_TABLE_ERROR
drop table t45;


--echo #
--echo # Check Bulk loading
--echo # Bulk loading used to overwrite existing data
--echo # Now it fails if there is data overlap with what
--echo # already exists
--echo #
show variables like 'xengine%';
create table t47 (pk int primary key, col1 varchar(12)) engine=xengine;
insert into t47 values (1, 'row1');
insert into t47 values (2, 'row2');
#set xengine_bulk_load=1;
insert into t47 values (3, 'row3'),(4, 'row4');
#set xengine_bulk_load=0;
# Check concurrent bulk loading
--connect (con1,localhost,root,,)
#set xengine_bulk_load=1;
insert into t47 values (10, 'row10'),(11, 'row11');
--connection default
#set xengine_bulk_load=1;
insert into t47 values (100, 'row100'),(101, 'row101');
--disconnect con1
--connection default
#set xengine_bulk_load=0;
--disable_query_log
let $wait_condition = select count(*) = 8 as c from t47;
--source include/wait_condition.inc
--enable_query_log
select * from t47;
drop table t47;

--echo #
--echo # Fix TRUNCATE over empty table (transaction is committed when it wasn't
--echo # started)
--echo #
create table t48(pk int primary key auto_increment, col1 varchar(12)) engine=xengine;
set autocommit=0;
#--error ER_ILLEGAL_HA
truncate table t48;
set autocommit=1;
drop table t48;

--echo #
--echo # MDEV-4059: XEngine: query waiting for a lock cannot be killed until query timeout exceeded
--echo #
--enable_connect_log

create table t49 (pk int primary key, a int) engine=XEngine;
insert into t49 values (1,10),(2,20);
begin;
update t49 set a = 100 where pk = 1;

--connect (con1,localhost,root,,)
--let $con1_id = `SELECT CONNECTION_ID()`
set xengine_lock_wait_timeout=60;
set @var1= to_seconds(now());
send update t49 set a = 1000 where pk = 1;

--connect (con2,localhost,root,,)
--echo kill query \$con1_id;
--disable_query_log
# If we immeditely kill the query - internally the condition broadcast can
# occur before the lock is waiting on the condition, thus the broadcast call
# is lost.  Sleep 1 second to avoid this condition.
--sleep 1
eval kill query $con1_id;
--enable_query_log
--connection con1
--error ER_QUERY_INTERRUPTED
--reap
set @var2= to_seconds(now());

# We expect the time to kill query in con1 should be below
# xengine_lock_wait_timeout (60).
select if ((@var2 - @var1) < 60, "passed", (@var2 - @var1)) as 'result';

--connection default
--disconnect con1

commit;
drop table t49;

--echo #
--echo # Index-only tests for INT-based columns
--echo #
create table t1 (pk int primary key, key1 int, col1 int, key(key1)) engine=xengine;
insert into t1 values (1,1,1);
insert into t1 values (2,2,2);
insert into t1 values (-5,-5,-5);
--echo # INT column uses index-only:
--replace_column 10 #
explain
select key1 from t1 where key1=2;
select key1 from t1 where key1=2;
select key1 from t1 where key1=-5;
drop table t1;


create table t2 (pk int primary key, key1 int unsigned, col1 int, key(key1)) engine=xengine;
insert into t2 values (1,1,1), (2,2,2);
--echo # INT UNSIGNED column uses index-only:
--replace_column 10 #
explain
select key1 from t2 where key1=2;
select key1 from t2 where key1=2;
drop table t2;


create table t3 (pk bigint primary key, key1 bigint, col1 int, key(key1)) engine=xengine;
insert into t3 values (1,1,1), (2,2,2);
--echo # BIGINT uses index-only:
--replace_column 10 #
explain
select key1 from t3 where key1=2;
select key1 from t3 where key1=2;
drop table t3;

--echo #
--echo # Index-only reads for string columns
--echo #
create table t1 (
  pk int primary key,
  key1 char(10) character set binary,
  col1 int,
  key (key1)
) engine=xengine;
insert into t1 values(1, 'one',11), (2,'two',22);
--replace_column 10 #
explain
select key1 from t1 where key1='one';
--echo # The following will produce no rows. This looks like a bug,
--echo #  but it is actually correct behavior. Binary strings are end-padded
--echo #  with \0 character (and not space).  Comparison does not ignore
--echo #   the tail of \0.
select key1 from t1 where key1='one';
--replace_column 10 #
explain
select hex(key1) from t1 where key1='one\0\0\0\0\0\0\0';
select hex(key1) from t1 where key1='one\0\0\0\0\0\0\0';
drop table t1;


create table t2 (
  pk int primary key,
  key1 char(10) collate latin1_bin,
  col1 int,
  key (key1)
) engine=xengine;
insert into t2 values(1, 'one',11), (2,'two',22);
--replace_column 10 #
explain
select key1 from t2 where key1='one';
select key1 from t2 where key1='one';
drop table t2;


create table t3 (
  pk int primary key,
  key1 char(10) collate utf8_bin,
  col1 int,
  key (key1)
) engine=xengine;
insert into t3 values(1, 'one',11), (2,'two',22);
--replace_column 10 #
explain
select key1 from t3 where key1='one';
select key1 from t3 where key1='one';
drop table t3;


--echo # a VARCHAR column
create table t4 (
  pk int primary key,
  key1 varchar(10) collate latin1_bin,
  key(key1)
) engine=xengine;
insert into t4 values(1, 'one'), (2,'two'),(3,'threee'),(55,'fifty-five');

analyze table t4;

--replace_column 10 #
explain
select key1 from t4 where key1='two';
select key1 from t4 where key1='two';

select key1 from t4 where key1='fifty-five';

--replace_column 10 # 11 #
explain
select key1 from t4 where key1 between 's' and 'u';
select key1 from t4 where key1 between 's' and 'u';

drop table t4;

--echo #
--echo # MDEV-4305: XEngine: Assertion `((keypart_map + 1) & keypart_map) == 0' fails in calculate_key_len
--echo #
CREATE TABLE t1 (pk1 INT, pk2 CHAR(32), i INT, PRIMARY KEY(pk1,pk2), KEY(i)) ENGINE=XEngine;
INSERT INTO t1 VALUES (1,'test1',6),(2,'test2',8);
SELECT * FROM t1 WHERE i != 3 OR  pk1 > 9;
DROP TABLE t1;

--echo #
--echo # MDEV-4298: XEngine: Assertion `thd->is_error() || kill_errno' fails in ha_rows filesort
--echo #
CREATE TABLE t1 (pk INT PRIMARY KEY, i INT, KEY(i)) ENGINE=XEngine;
INSERT INTO t1 VALUES (1,1),(2,2);
BEGIN;
UPDATE t1 SET i = 100;

--connect (con1,localhost,root,,test)
--error ER_LOCK_WAIT_TIMEOUT
DELETE IGNORE FROM t1 ORDER BY i;
--disconnect con1

--connection default
COMMIT;
DROP TABLE t1;

--echo #
--echo # MDEV-4324: XEngine: Valgrind "Use of uninitialised value" warnings on inserting value into varchar field
--echo #  (testcase only)
--echo #
CREATE TABLE t1 (pk INT PRIMARY KEY, c VARCHAR(4)) ENGINE=XEngine;
INSERT INTO t1 VALUES (1,'foo'), (2,'bar');
DROP TABLE t1;

--echo #
--echo # MDEV-4304: XEngine: Index-only scan by a field with utf8_bin collation returns garbage symbols
--echo #
CREATE TABLE t1 (pk INT PRIMARY KEY, c1 CHAR(1), c2 CHAR(1), KEY(c1)) ENGINE=XEngine CHARSET utf8 COLLATE utf8_bin;
INSERT INTO t1 VALUES (1,'h','h');
SELECT * FROM t1;
SELECT c1 FROM t1;
DROP TABLE t1;

--echo #
--echo # MDEV-4300: XEngine: Server crashes in inline_mysql_mutex_lock on SELECT .. FOR UPDATE
--echo #
CREATE TABLE t2 (pk INT PRIMARY KEY, i INT, KEY (i)) ENGINE=XEngine;
INSERT INTO t2 VALUES (1,4),(2,5);
SELECT 1 FROM t2 WHERE i < 0 FOR UPDATE;
DROP TABLE t2;

--echo #
--echo # MDEV-4301: XEngine: Assertion `pack_info != __null' fails in Rdb_key_def::unpack_record
--echo #
CREATE TABLE t1 (pk INT PRIMARY KEY, i INT, c CHAR(1), KEY(c,i)) ENGINE=XEngine;
INSERT INTO t1 VALUES (1,4,'d'),(2,8,'e');
SELECT MAX( pk ) FROM t1 WHERE i = 105 AND c = 'h';
DROP TABLE t1;

--echo #
--echo # MDEV-4337: XEngine: Inconsistent results comparing a char field with an int field
--echo #
create table t1 (c char(1), i int, primary key(c), key(i)) engine=XEngine;
insert into t1 values ('2',2),('6',6);
select * from t1 where c = i;
select * from t1 ignore index (i) where c = i;
drop table t1;


--echo #
--echo # Test statement rollback inside a transaction
--echo #
create table t1 (pk varchar(12) primary key) engine=xengine;
insert into t1 values ('old-val1'),('old-val2');

create table t2 (pk varchar(12) primary key) engine=xengine;
insert into t2 values ('new-val2'),('old-val1');

begin;
insert into t1 values ('new-val1');
--error ER_DUP_ENTRY
insert into t1 select * from t2;
commit;

select * from t1;
drop table t1, t2;

--echo #
--echo # MDEV-4383: XEngine: Wrong result of DELETE .. ORDER BY .. LIMIT:
--echo #   rows that should be deleted remain in the table
--echo #
CREATE TABLE t2 (pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=XEngine;
CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=XEngine;

INSERT INTO t1 (pk) VALUES (NULL),(NULL);
BEGIN;
INSERT INTO t2 (pk) VALUES (NULL),(NULL);
INSERT INTO t1 (pk) VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL);

--enable_info
SELECT * FROM t1 ORDER BY pk LIMIT 9;
DELETE FROM t1 ORDER BY pk LIMIT 9;
SELECT * FROM t1 ORDER BY pk LIMIT 9;
--disable_info

DROP TABLE t1,t2;

--echo #
--echo # MDEV-4374: XEngine: Valgrind warnings 'Use of uninitialised value' on
--echo #   inserting into a varchar column
--echo #
CREATE TABLE t1 (pk INT PRIMARY KEY, a VARCHAR(32)) ENGINE=XEngine;
INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
DROP TABLE t1;


--echo #
--echo # MDEV-4061: XEngine: Changes from an interrupted query are still applied
--echo #

--enable_connect_log

create table t1 (pk int primary key, a int) engine=XEngine;
insert into t1 values (1,10),(2,20);

--let $con_id = `select connection_id()`

set autocommit = 1;
--send
update t1 set a = sleep(100) where pk = 1;

--connect (con1,localhost,root,,)
--echo kill query \$con_id;
--disable_query_log
eval kill query $con_id;
--enable_query_log

--connection default
--error ER_QUERY_INTERRUPTED
--reap

select * from t1;
--disconnect con1
--disable_connect_log
drop table t1;


--echo #
--echo # MDEV-4099: XEngine: Wrong results with index and range access after INSERT IGNORE or REPLACE
--echo #
CREATE TABLE t1 (pk INT PRIMARY KEY, a SMALLINT, b INT, KEY (a)) ENGINE=XEngine;
INSERT IGNORE INTO t1 VALUES (1, 157, 0), (2, 1898, -504403), (1, -14659,  0);
SELECT * FROM t1;
SELECT pk FROM t1;
SELECT * FROM t1 WHERE a != 97;
DROP TABLE t1;


--echo #
--echo # Test @@xengine_max_row_locks
--echo #
CREATE TABLE t1 (pk INT PRIMARY KEY, a int) ENGINE=XEngine;
set @a=-1;
insert into t1 select (@a:=@a+1), 1234 from performance_schema.session_variables limit 100;
set @tmp1= @@xengine_max_row_locks;
set xengine_max_row_locks= 20;
--error ER_GET_ERRMSG
update t1 set a=a+10;
DROP TABLE t1;


--echo #
--echo # Test AUTO_INCREMENT behavior problem,
--echo #  "explicit insert into an auto-inc column is not noticed by XEngine"
--echo #
create table t1 (i int primary key auto_increment) engine=XEngine;

insert into t1 values (null);
insert into t1 values (null);
select * from t1;
drop table t1;

create table t2 (i int primary key auto_increment) engine=XEngine;

insert into t2 values (1);
select * from t2;

--echo # this fails (ie. used to fail), XEngine engine did not notice use of '1' above
insert into t2 values (null);
select * from t2;

--echo # but then this succeeds, so previous statement must have incremented next number counter
insert into t2 values (null);
select * from t2;
drop table t2;

--echo #
--echo # Fix Issue#2: AUTO_INCREMENT value doesn't survive server shutdown
--echo #
create table t1 (i int primary key auto_increment) engine=XEngine;

insert into t1 values (null);
insert into t1 values (null);

SET GLOBAL XENGINE_PAUSE_BACKGROUND_WORK = @ORIG_PAUSE_BACKGROUND_WORK;

--source include/restart_mysqld.inc

SET @ORIG_PAUSE_BACKGROUND_WORK = @@XENGINE_PAUSE_BACKGROUND_WORK;
SET GLOBAL XENGINE_PAUSE_BACKGROUND_WORK = 1;

insert into t1 values (null);
select * from t1;

drop table t1;

--echo #
--echo # Fix Issue #3: SHOW TABLE STATUS shows Auto_increment=0
--echo #
create table t1 (i int primary key auto_increment) engine=XEngine;

insert into t1 values (null),(null);
analyze table t1;
--replace_column 5 # 6 # 7 # 12 # 13 # 14 #
show table status like 't1';
drop table t1;

--echo #
--echo # Fix Issue #4: Crash when using pseudo-unique keys
--echo #
CREATE TABLE t1 (
  i INT,
  t TINYINT,
  s SMALLINT,
  m MEDIUMINT,
  b BIGINT,
  pk MEDIUMINT AUTO_INCREMENT PRIMARY KEY,
  UNIQUE KEY b_t (b,t)
) ENGINE=xengine;

INSERT INTO t1 (i,t,s,m,b) VALUES (1,2,3,4,5),(1000,100,10000,1000000,1000000000000000000),(5,100,10000,1000000,100000000000000000),(2,3,4,5,6),(3,4,5,6,7),(101,102,103,104,105),(10001,103,10002,10003,10004),(10,11,12,13,14),(11,12,13,14,15),(12,13,14,15,16);

SELECT b+t FROM t1 WHERE (b,t) IN ( SELECT b, t FROM t1 WHERE i>1 ) ORDER BY b+t;
DROP TABLE t1;

--echo #
--echo # Fix issue #5: Transaction rollback doesn't undo all changes.
--echo #
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t1 (id int auto_increment primary key, value int) engine=xengine;

set autocommit=0;
begin;
set @a:=0;
insert into t1 select @a:=@a+1, @a from t0 A, t0 B, t0 C, t0 D where D.a<4;
insert into t1 select @a:=@a+1, @a from t0 A, t0 B, t0 C, t0 D where D.a<4;
insert into t1 select @a:=@a+1, @a from t0 A, t0 B, t0 C, t0 D where D.a<4;
rollback;
select count(*) from t1;

set autocommit=1;
drop table t0, t1;

--echo #
--echo # Check status variables
--echo #
--replace_column 2 #
show status like 'xengine%';

select VARIABLE_NAME from performance_schema.global_status where VARIABLE_NAME LIKE 'xengine%';
--echo # XEngine-SE's status variables are global internally
--echo #  but they are shown as both session and global, like InnoDB's status vars.
select VARIABLE_NAME from performance_schema.session_status where VARIABLE_NAME LIKE 'xengine%';


--echo #
--echo # Fix issue #9: HA_ERR_INTERNAL_ERROR when running linkbench
--echo #
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t1 (
  pk int primary key,
  col1 varchar(255),
  key(col1)
) engine=xengine;
insert into t1 select a, repeat('123456789ABCDEF-', 15) from t0;
select * from t1 where pk=3;
drop table t0, t1;

--echo #
--echo # Fix issue #10: Segfault in Rdb_key_def::get_primary_key_tuple
--echo #
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

CREATE TABLE t1 (
  id1 bigint(20) unsigned NOT NULL DEFAULT '0',
  id2 bigint(20) unsigned NOT NULL DEFAULT '0',
  link_type bigint(20) unsigned NOT NULL DEFAULT '0',
  visibility tinyint(3) NOT NULL DEFAULT '0',
  data varchar(255) NOT NULL DEFAULT '',
  time bigint(20) unsigned NOT NULL DEFAULT '0',
  version int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (link_type,id1,id2)
) engine=xengine;

insert into t1 select a,a,a,1,a,a,a from t0;

alter table t1 add index id1_type (id1,link_type,visibility,time,version,data);
select * from t1 where id1 = 3;

drop table t0,t1;

--echo #
--echo # Test column families
--echo #

create table t1 (
  pk int primary key,
  col1 int,
  col2 int,
  key(col1) comment 'cf3',
  key(col2) comment 'cf4'
) engine=xengine;

insert into t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);

--replace_column 10 #
explain
select * from t1 where col1=2;
select * from t1 where col1=2;

--replace_column 10 #
explain
select * from t1 where col2=3;
select * from t1 where col2=3;

select * from t1 where pk=4;

drop table t1;

--echo #
--echo # Try primary key in a non-default CF:
--echo #
create table t1 (
  pk int,
  col1 int,
  col2 int,
  key(col1) comment 'cf3',
  key(col2) comment 'cf4',
  primary key (pk) comment 'cf5'
) engine=xengine;
insert into t1 values (1,1,1), (2,2,2), (3,3,3), (4,4,4), (5,5,5);

--replace_column 10 #
explain
select * from t1 where col1=2;
select * from t1 where col1=2;

select * from t1 where pk=4;

drop table t1;

--echo #
--echo # Issue #15: SIGSEGV from reading in blob data
--echo #
CREATE TABLE t1 (
  id int not null,
  blob_col text,
  PRIMARY KEY (id)
) ENGINE=XENGINE CHARSET=latin1;

INSERT INTO t1 SET id=123, blob_col=repeat('z',64000) ON DUPLICATE KEY UPDATE blob_col=VALUES(blob_col);
INSERT INTO t1 SET id=123, blob_col=''                ON DUPLICATE KEY UPDATE blob_col=VALUES(blob_col);
DROP TABLE t1;


--echo #
--echo # Issue #17: Automatic per-index column families
--echo #
create table t1 (
  id int not null,
  key1 int,
  PRIMARY KEY (id),
  index (key1) comment '$per_index_cf'
) engine=xengine;

create table t1_err (
  id int not null,
  key1 int,
  PRIMARY KEY (id),
  index (key1) comment 'test.t1.key1'
) engine=xengine;

create table t2_err (
  id int not null,
  key1 int,
  PRIMARY KEY (id),
  index (key1) comment 'test.t1.key2'
) engine=xengine;
drop table t1_err;
drop table t2_err;

--echo # Unfortunately there is no way to check which column family everything goes to
insert into t1 values (1,1);
select * from t1;
--echo # Check that ALTER and RENAME are disallowed
alter table t1 add col2 int;

rename table t1 to t2;

drop table t2;

--echo # Check detection of typos in \$per_index_cf
create table t100 (
  id int not null,
  key1 int,
  PRIMARY KEY (id),
  index (key1) comment '$per_idnex_cf'
)engine=xengine;

drop table t100;


--echo #
--echo # Issue #22: SELECT ... FOR UPDATE takes a long time
--echo #
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t1 (
  id1 int,
  id2 int,
  value1 int,
  value2 int,
  primary key(id1, id2) COMMENT 'new_column_family',
  key(id2)
) engine=xengine default charset=latin1 collate=latin1_bin;

insert into t1 select A.a, B.a, 31, 1234 from t0 A, t0 B;

--replace_column 10 #
explain
select * from t1 where id1=30 and value1=30 for update;

set @var1=(select variable_value
           from performance_schema.global_status
           where variable_name='xengine_number_keys_read');

select * from t1 where id1=3 and value1=3 for update;

set @var2=(select variable_value
           from performance_schema.global_status
           where variable_name='xengine_number_keys_read');
--echo # The following must return true (before the fix, the difference was 70):
select if((@var2 - @var1) < 30, 1, @var2-@var1);

drop table t0,t1;

--echo #
--echo # Issue #33: SELECT ... FROM xengine_table ORDER BY primary_key uses sorting
--echo #
create table t1 (id int primary key, value int) engine=xengine;
insert into t1 values (1,1),(2,2),(3,3);
analyze table t1;
--echo # The following must not use 'Using filesort':
--replace_column 10 # 11 #
explain select * from t1 ORDER BY id;
drop table t1;

--echo #
--echo # Issue #26: Index-only scans for DATETIME and TIMESTAMP
--echo #
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

--echo # Try a DATETIME column:
create table t1 (
  pk int auto_increment primary key,
  kp1 datetime,
  kp2 int,
  col1 int,
  key(kp1, kp2)
) engine=xengine;
insert into t1 (kp1,kp2)
select date_add('2015-01-01 12:34:56', interval a day), a from t0;

select * from t1;

analyze table t1;
--echo # This must show 'Using index'
--replace_column 10 # 11 #
explain
select kp1,kp2 from t1 force index (kp1)
where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59';

select kp1,kp2 from t1 force index (kp1)
where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59';

--echo # Now, the same with NOT NULL column
create table t2 (
  pk int auto_increment primary key,
  kp1 datetime not null,
  kp2 int,
  col1 int,
  key(kp1, kp2)
) engine=xengine;
insert into t2 select * from t1;
analyze table t2;
--echo # This must show 'Using index'
--replace_column 10 # 11 #
explain
select kp1,kp2 from t2 force index (kp1)
where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59';

select kp1,kp2 from t2 force index (kp1)
where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59';
drop table t1,t2;

--echo # Try a DATE column:
create table t1 (
  pk int auto_increment primary key,
  kp1 date,
  kp2 int,
  col1 int,
  key(kp1, kp2)
) engine=xengine;
insert into t1 (kp1,kp2)
select date_add('2015-01-01', interval a day), a from t0;

analyze table t1;
select * from t1;

--echo # This must show 'Using index'
--replace_column 10 # 11 #
explain
select kp1,kp2 from t1 force index (kp1)
where kp1 between '2015-01-01' and '2015-01-05';

select kp1,kp2 from t1 force index (kp1)
where kp1 between '2015-01-01' and '2015-01-05';

--echo # Now, the same with NOT NULL column
create table t2 (
  pk int auto_increment primary key,
  kp1 date not null,
  kp2 int,
  col1 int,
  key(kp1, kp2)
) engine=xengine;
insert into t2 select * from t1;
analyze table t2;
--echo # This must show 'Using index'
--replace_column 10 # 11 #
explain
select kp1,kp2 from t2 force index (kp1)
where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59';

select kp1,kp2 from t2 force index (kp1)
where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59';
drop table t1,t2;

--echo #
--echo # Try a TIMESTAMP column:
--echo #
create table t1 (
  pk int auto_increment primary key,
  kp1 timestamp,
  kp2 int,
  col1 int,
  key(kp1, kp2)
) engine=xengine;
insert into t1 (kp1,kp2)
select date_add('2015-01-01 12:34:56', interval a day), a from t0;

select * from t1;

--echo # This must show 'Using index'
--replace_column 10 # 11 #
explain
select kp1,kp2 from t1 force index (kp1)
where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59';

select kp1,kp2 from t1 force index (kp1)
where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59';

--echo # Now, the same with NOT NULL column
create table t2 (
  pk int auto_increment primary key,
  kp1 timestamp not null,
  kp2 int,
  col1 int,
  key(kp1, kp2)
) engine=xengine;
insert into t2 select * from t1;
analyze table t2;
--echo # This must show 'Using index'
--replace_column 10 # 11 #
explain
select kp1,kp2 from t2 force index (kp1)
where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59';

select kp1,kp2 from t2 force index (kp1)
where kp1 between '2015-01-01 00:00:00' and '2015-01-05 23:59:59';
drop table t1,t2;

--echo #
--echo # Try a TIME column:
--echo #
create table t1 (
  pk int auto_increment primary key,
  kp1 time,
  kp2 int,
  col1 int,
  key(kp1, kp2)
) engine=xengine;
--disable_warnings
insert into t1 (kp1,kp2)
select date_add('2015-01-01 09:00:00', interval a minute), a from t0;
--enable_warnings

analyze table t1;
select * from t1;

--echo # This must show 'Using index'
--replace_column 10 # 11 #
explain
select kp1,kp2 from t1 force index (kp1)
where kp1 between '09:01:00' and '09:05:00';

select kp1,kp2 from t1 force index (kp1)
where kp1 between '09:01:00' and '09:05:00';

--echo # Now, the same with NOT NULL column
create table t2 (
  pk int auto_increment primary key,
  kp1 time not null,
  kp2 int,
  col1 int,
  key(kp1, kp2)
) engine=xengine;
insert into t2 select * from t1;
analyze table t2;
--echo # This must show 'Using index'
--replace_column 10 # 11 #
explain
select kp1,kp2 from t2 force index (kp1)
where kp1 between '09:01:00' and '09:05:00';

select kp1,kp2 from t2 force index (kp1)
where kp1 between '09:01:00' and '09:05:00';
drop table t1,t2;

--echo #
--echo # Try a YEAR column:
--echo #
create table t1 (
  pk int auto_increment primary key,
  kp1 year,
  kp2 int,
  col1 int,
  key(kp1, kp2)
) engine=xengine;
--disable_warnings
insert into t1 (kp1,kp2) select 2015+a, a from t0;
--enable_warnings

analyze table t1;
select * from t1;

--echo # This must show 'Using index'
--replace_column 10 # 11 #
explain
select kp1,kp2 from t1 force index (kp1)
where kp1 between '2016' and '2020';

select kp1,kp2 from t1 force index (kp1)
where kp1 between '2016' and '2020';

--echo # Now, the same with NOT NULL column
create table t2 (
  pk int auto_increment primary key,
  kp1 year not null,
  kp2 int,
  col1 int,
  key(kp1, kp2)
) engine=xengine;
insert into t2 select * from t1;
analyze table t2;
--echo # This must show 'Using index'
--replace_column 10 # 11 #
explain
select kp1,kp2 from t2 force index (kp1)
where kp1 between '2016' and '2020';

select kp1,kp2 from t2 force index (kp1)
where kp1 between '2016' and '2020';

drop table t1,t2;

--echo #
--echo # Issue #57: Release row locks on statement errors
--echo #
create table t1 (id int primary key) engine=xengine;
insert into t1 values (1), (2), (3);
begin;
insert into t1 values (4), (5), (6);
--error ER_DUP_ENTRY
insert into t1 values (7), (8), (2), (9);
select * from t1;

-- connect(con1,localhost,root,,)
--connection con1
begin;
--error ER_LOCK_WAIT_TIMEOUT
select * from t1 where id=4 for update;

select * from t1 where id=7 for update;

select * from t1 where id=9 for update;

--connection default
-- disconnect con1
drop table t1;

--echo #Index on blob column
SET @old_mode = @@sql_mode;
SET sql_mode = 'strict_all_tables';
create table t1 (a int, b text, c varchar(400), Primary Key(a), Key(c, b(255))) engine=xengine;
drop table t1;
create table t1 (a int, b text, c varchar(400), Primary Key(a), Key(b(1255))) engine=xengine charset latin1;
insert into t1 values (1, '1abcde', '1abcde'), (2, '2abcde', '2abcde'), (3, '3abcde', '3abcde');
select * from t1;
--replace_column 10 #
explain select * from t1 where b like '1%';
--replace_column 10 #
explain select b, a from t1 where b like '1%';
update t1 set b= '12345' where b = '2abcde';
select * from t1;
drop table t1;
--error ER_TOO_LONG_KEY
create table t1 (a int, b text, c varchar(400), Primary Key(a), Key(b(2255))) engine=xengine;
SET sql_mode = @old_mode;

drop table t0;

--echo #
--echo # Fix assertion failure (attempt to overrun the key buffer) for prefix indexes
--echo #

create table t1 (
  pk int primary key,
  col1 varchar(100),
  key (col1(10))
) engine=xengine;

insert into t1 values (1, repeat('0123456789', 9));

drop table t1;

--echo #
--echo # Issue #76: Assertion `buf == table->record[0]' fails in virtual int ha_xengine::delete_row(const uchar*)
--echo #

CREATE TABLE t1 (pk INT PRIMARY KEY, f1 INT) ENGINE=XEngine;
CREATE TABLE t2 (pk INT PRIMARY KEY, f1 INT) ENGINE=XEngine;

CREATE TRIGGER tr AFTER DELETE ON t1 FOR EACH ROW DELETE FROM t2 WHERE pk = old.pk;

INSERT INTO t1 VALUES (1,1);
REPLACE INTO t1 VALUES (1,2);

SELECT * FROM t1;
DROP TABLE t1, t2;

--echo #
--echo # Issue #99: UPDATE for table with VARCHAR pk gives "Can't find record" error
--echo #
create table t1(a int primary key);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t2 (
  a varchar(32) primary key,
  col1 int
) engine=xengine;

insert into t2
select concat('v-', 100 + A.a*100 + B.a), 12345 from t1 A, t1 B;
update t2 set a=concat('x-', a) where a between 'v-1002' and 'v-1004';

drop table t1,t2;

--echo #
--echo # Issue #131: Assertion `v->cfd_->internal_comparator().Compare(start, end) <= 0' failed
--echo #
CREATE TABLE t2(c1 INTEGER UNSIGNED NOT NULL,
                c2 INTEGER NULL,
                c3 TINYINT,
                c4 SMALLINT,
                c5 MEDIUMINT,
                c6 INT,
                c7 BIGINT,
                PRIMARY KEY(c1,c6)) ENGINE=XEngine charset latin1;
INSERT INTO t2 VALUES (1,1,1,1,1,1,1);
SELECT * FROM t2 WHERE c1 > 4294967295 ORDER BY c1,c6;
EXPLAIN SELECT * FROM t2 WHERE c1 > 4294967295 ORDER BY c1,c6;
drop table t2;

--echo #
--echo # Issue #135: register transaction was not being called for statement
--echo #
--disable_warnings
DROP DATABASE IF EXISTS test_db;
--enable_warnings
CREATE DATABASE test_db;
CREATE TABLE test_db.t1(c1 INT PRIMARY KEY);
LOCK TABLES test_db.t1 READ;
SET AUTOCOMMIT=0;
SELECT c1 FROM test_db.t1;
START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY;
DROP DATABASE test_db;

--echo #
--echo # Issue #143: Split xengine_bulk_load option into two
--echo #
CREATE TABLE t1 (id int primary key, value int) engine=XEngine;
SET unique_checks=0;
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t1 VALUES(1, 2);
INSERT INTO t1 VALUES(1, 3);
SELECT * FROM t1;
--error ER_UNKNOWN_ERROR
REPLACE INTO t1 VALUES(4, 4);
--error ER_UNKNOWN_ERROR
INSERT INTO t1 VALUES(5, 5) ON DUPLICATE KEY UPDATE value=value+1;
TRUNCATE TABLE t1;
#SET @save_xengine_bulk_load_size= @@xengine_bulk_load_size;
SET unique_checks=1;
#SET xengine_commit_in_the_middle=1;
#SET xengine_bulk_load_size=10;
BEGIN;
INSERT INTO t1 (id) VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
  (11),(12),(13),(14),(15),(16),(17),(18),(19);
ROLLBACK;
SELECT * FROM t1;
INSERT INTO t1 (id) VALUES (11),(12),(13),(14),(15);
BEGIN;
UPDATE t1 SET value=100;
ROLLBACK;
SELECT * FROM t1;
BEGIN;
DELETE FROM t1;
ROLLBACK;
SELECT * FROM t1;
#SET xengine_commit_in_the_middle=0;
#SET xengine_bulk_load_size= @save_xengine_bulk_load_size;
DROP TABLE t1;

--echo #
--echo # Issue #185 Assertion `BaseValid()' failed in void xengine::BaseDeltaIterator::Advance()
--echo #
CREATE TABLE t2(id INT NOT NULL PRIMARY KEY, data INT) Engine=MEMORY;
INSERT INTO t2 VALUES (100,NULL),(150,"long varchar"),(200,"varchar"),(250,"long long long varchar");
create TABLE t1 (a int not null, b int not null, primary key(a,b));
INSERT INTO t1  VALUES (1,1);
SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4));
DROP TABLE t1, t2;

--echo #
--echo # Issue #189 ha_xengine::load_auto_incr_value() creates implicit snapshot and doesn't release
--echo #
--connect (con1,localhost,root,,)
create table r1 (id int auto_increment primary key, value int);
insert into r1 (id) values (null), (null), (null), (null), (null);
connection con1;
create table r2 like r1;
show create table r2;
connection default;
begin;
insert into r1 values (10, 1);
commit;
connection con1;
begin;
select * from r1;
commit;
connection default;
drop table r1, r2;

# hidden primary key
create table r1 (id int auto_increment, value int, index i(id));
insert into r1 (id) values (null), (null), (null), (null), (null);
connection con1;
create table r2 like r1;
show create table r2;
connection default;
begin;
insert into r1 values (10, 1);
commit;
connection con1;
begin;
select * from r1;
commit;
connection default;
drop table r1, r2;

disconnect con1;

--echo #
--echo # Issue#211 Crash on LOCK TABLES + START TRANSACTION WITH CONSISTENT SNAPSHOT
--echo #
CREATE TABLE t1(c1 INT);
lock TABLE t1 read local;
SELECT 1 FROM t1 GROUP BY TRIM(LEADING RAND()FROM'');
set AUTOCOMMIT=0;
start transaction with consistent snapshot;
SELECT * FROM t1;
COMMIT;
UNLOCK TABLES;
DROP TABLE t1;

--echo #
--echo # Issue#213 Crash on LOCK TABLES + partitions
--echo #
--error ER_NOT_SUPPORTED_YET
CREATE TABLE t1(a BIGINT,b BIGINT,KEY (b), PRIMARY KEY(a)) engine=xengine PARTITION BY HASH(a) PARTITIONS 2;
CREATE TABLE t1(a BIGINT,b BIGINT,KEY (b), PRIMARY KEY(a)) engine=xengine;
INSERT INTO t1(a)VALUES (20010101101010.999949);
lock tables t1 write,t1 as t0 write,t1 as t2 write;
SELECT a FROM t1 ORDER BY a;
truncate t1;
INSERT INTO t1 VALUES(X'042000200020',X'042000200020'),(X'200400200020',X'200400200020');
UNLOCK TABLES;
DROP TABLE t1;

--echo #
--echo # Issue#250: MyX/Innodb different output from query with order by on table with index and decimal type
--echo #  (the test was changed to use VARCHAR, because DECIMAL now supports index-only, and this issue
--echo #   needs a datype that doesn't support index-inly)
--echo #

CREATE TABLE t1(
  c1 varchar(10) character set utf8 collate utf8_general_ci NOT NULL,
  c2 varchar(10) character set utf8 collate utf8_general_ci,
  c3 INT,
  INDEX idx(c1,c2)
);
INSERT INTO t1 VALUES ('c1-val1','c2-val1',5);
INSERT INTO t1 VALUES ('c1-val2','c2-val3',6);
INSERT INTO t1 VALUES ('c1-val3','c2-val3',7);
SELECT * FROM t1 force index(idx) WHERE c1 <> 'c1-val2' ORDER BY c1 DESC;
--replace_column 10 #
explain SELECT * FROM t1  force index(idx) WHERE c1 <> '1' ORDER BY c1 DESC;
drop table t1;

--echo #
--echo # Issue#267: MyX issue with no matching min/max row and count(*)
--echo #
CREATE TABLE t1(c1 INT UNSIGNED, c2 INT SIGNED, INDEX idx2(c2));
INSERT INTO t1 VALUES(1,null);
INSERT INTO t1 VALUES(2,null);
SELECT count(*) as total_rows, min(c2) as min_value FROM t1;
DROP TABLE t1;

--echo #
--echo # Issue#263: MyX auto_increment skips values if you insert a negative value
--echo #
# We have slightly different behavior regarding auto-increment values than
# InnoDB, so the results of the SHOW TABLE STATUS command will be slightly
# different.  InnoDB will reserve 3 values but only use 2 of them (because
# the user hard-coded a -1 as the second value).  MyX will only reserve
# the values as needed, so only 2 values will be used.  This means that the
# SHOW TABLE STATUS in InnoDB will indicate that the next auto-increment
# value is 4 while MyX will show it as 3.
CREATE TABLE t1(a INT AUTO_INCREMENT KEY) engine=xengine;
INSERT INTO t1 VALUES(0),(-1),(0);
ANALYZE TABLE t1;
--replace_column 5 # 6 # 7 # 12 # 13 # 14 #
SHOW TABLE STATUS LIKE 't1';
SELECT * FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a INT AUTO_INCREMENT KEY) engine=xengine;
INSERT INTO t1 VALUES(0),(10),(0);
ANALYZE TABLE t1;
--replace_column 5 # 6 # 7 # 12 # 13 # 14 #
SHOW TABLE STATUS LIKE 't1';
SELECT * FROM t1;
DROP TABLE t1;

--echo #
--echo # Issue #411: Setting xengine_commit_in_the_middle commits transaction
--echo # without releasing iterator
--echo #

CREATE TABLE t1 (id1 bigint(20),
                 id2 bigint(20),
                 id3 bigint(20),
                 PRIMARY KEY (id1, id2, id3))
                 DEFAULT CHARSET=latin1;

CREATE TABLE t2 (id1 bigint(20),
                 id2 bigint(20),
                 PRIMARY KEY (id1, id2))
                 DEFAULT CHARSET=latin1;


#set xengine_commit_in_the_middle=1;
#SET @save_xengine_bulk_load_size= @@xengine_bulk_load_size;
#set xengine_bulk_load_size = 100;

--disable_query_log
let $j = 10000;
while ($j)
{
  --eval insert into t1 (id1, id2, id3) values (0, $j, 0);
  --eval insert into t2 (id1, id2) values (0, $j);
  dec $j;
}
--enable_query_log

DELETE t2, t1 FROM t2 LEFT JOIN t1 ON t2.id2 = t1.id2 AND t2.id1 = t1.id1 WHERE t2.id1 = 0;

#SET xengine_bulk_load_size= @save_xengine_bulk_load_size;
#SET xengine_commit_in_the_middle=0;
DROP TABLE t1, t2;


SET GLOBAL XENGINE_PAUSE_BACKGROUND_WORK = @ORIG_PAUSE_BACKGROUND_WORK;
--source suite/xengine/include/check_xengine_log_error.inc