summaryrefslogtreecommitdiff
path: root/mysql-test/suite/pbxt/r/pbxt_bugs.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/pbxt/r/pbxt_bugs.result')
-rw-r--r--mysql-test/suite/pbxt/r/pbxt_bugs.result1276
1 files changed, 1276 insertions, 0 deletions
diff --git a/mysql-test/suite/pbxt/r/pbxt_bugs.result b/mysql-test/suite/pbxt/r/pbxt_bugs.result
new file mode 100644
index 00000000000..a9f62ff69bc
--- /dev/null
+++ b/mysql-test/suite/pbxt/r/pbxt_bugs.result
@@ -0,0 +1,1276 @@
+drop table if exists t1,t2,t3;
+create table t1 (id int auto_increment key) engine=pbxt auto_increment=200;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`id`)
+) ENGINE=PBXT AUTO_INCREMENT=200 DEFAULT CHARSET=latin1
+alter table t1 add column text char(40);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `text` char(40) DEFAULT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=PBXT AUTO_INCREMENT=200 DEFAULT CHARSET=latin1
+alter table t1 auto_increment = 100;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `id` int(11) NOT NULL AUTO_INCREMENT,
+ `text` char(40) DEFAULT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=PBXT AUTO_INCREMENT=100 DEFAULT CHARSET=latin1
+drop table if exists t1;
+create table t1 (
+id int,
+c_char char(3),
+c_varchar varchar(3),
+c_char_bin char(3) binary,
+c_varchar_bin varchar(3) binary,
+c_char_uni char(3) unicode,
+c_varchar_uni varchar(3) unicode,
+c_dec decimal(3),
+index(c_char),
+index(c_varchar),
+index(c_char_bin),
+index(c_varchar_bin),
+index(c_char_uni),
+index(c_varchar_uni),
+index(c_dec)
+) engine=pbxt;
+insert t1 values (1, "ab", "ab", "ab", "ab", "ab", "ab", 12);
+insert t1 values (2, "ab ", "ab ", "ab ", "ab ", "ab ", "ab ", 123);
+select c_char from t1 where c_char = "ab";
+c_char
+ab
+ab
+select c_varchar from t1 where c_varchar = "ab";
+c_varchar
+ab
+ab
+select c_char_bin from t1 where c_char_bin = "ab";
+c_char_bin
+ab
+ab
+select c_varchar_bin from t1 where c_varchar_bin = "ab";
+c_varchar_bin
+ab
+ab
+select c_char_uni from t1 where c_char_uni = "ab";
+c_char_uni
+ab
+ab
+select c_varchar_uni from t1 where c_varchar_uni = "ab";
+c_varchar_uni
+ab
+ab
+select c_dec from t1 where c_dec = 12;
+c_dec
+12
+select * from t1 where c_char = "ab ";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_varchar = "ab ";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_char_bin = "ab ";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_varchar_bin = "ab ";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_char_uni = "ab ";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_varchar_uni = "ab ";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_dec = 123;
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+2 ab ab ab ab ab ab 123
+drop table if exists t1;
+create table t1 (
+id int,
+c_char char(100),
+c_varchar varchar(100),
+c_char_bin char(100) binary,
+c_varchar_bin varchar(100) binary,
+c_char_uni char(100) unicode,
+c_varchar_uni varchar(100) unicode,
+c_dec decimal(65),
+index(c_char),
+index(c_varchar),
+index(c_char_bin),
+index(c_varchar_bin),
+index(c_char_uni),
+index(c_varchar_uni),
+index(c_dec)
+) engine=pbxt;
+insert t1 values (1, "ab", "ab", "ab", "ab", "ab", "ab", 12);
+insert t1 values (2, "ab ", "ab ", "ab ", "ab ", "ab ", "ab ", 123);
+select * from t1 where c_char = "ab";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_varchar = "ab";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_char_bin = "ab";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_varchar_bin = "ab";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_char_uni = "ab";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_varchar_uni = "ab";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_dec = 12;
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+select c_char from t1 where c_char = "ab ";
+c_char
+ab
+ab
+select c_varchar from t1 where c_varchar = "ab ";
+c_varchar
+ab
+ab
+select c_char_bin from t1 where c_char_bin = "ab ";
+c_char_bin
+ab
+ab
+select c_varchar_bin from t1 where c_varchar_bin = "ab ";
+c_varchar_bin
+ab
+ab
+select c_char_uni from t1 where c_char_uni = "ab ";
+c_char_uni
+ab
+ab
+select c_varchar_uni from t1 where c_varchar_uni = "ab ";
+c_varchar_uni
+ab
+ab
+select c_dec from t1 where c_dec = 123;
+c_dec
+123
+drop table if exists t1;
+create table t1 (
+id int,
+c_varchar varchar(300),
+c_varchar_bin varchar(300) binary,
+c_varchar_uni varchar(300) unicode,
+c_ttext TINYTEXT,
+c_text TEXT,
+c_mtext MEDIUMTEXT,
+c_ltext LONGTEXT,
+index(c_varchar),
+index(c_varchar_bin),
+index(c_varchar_uni),
+index(c_ttext(100)),
+index(c_text(100)),
+index(c_mtext(100)),
+index(c_ltext(100))
+) engine=pbxt;
+insert t1 values (1, "ab", "ab", "ab", "ab", "ab", "ab", "ab");
+insert t1 values (2, "ab ", "ab ", "ab ", "ab ", "ab ", "ab ", "ab ");
+select c_varchar from t1 where c_varchar = "ab";
+c_varchar
+ab
+ab
+select c_varchar_bin from t1 where c_varchar_bin = "ab";
+c_varchar_bin
+ab
+ab
+select c_varchar_uni from t1 where c_varchar_uni = "ab";
+c_varchar_uni
+ab
+ab
+select c_ttext from t1 where c_ttext = "ab";
+c_ttext
+ab
+ab
+select c_text from t1 where c_text = "ab";
+c_text
+ab
+ab
+select c_mtext from t1 where c_mtext = "ab";
+c_mtext
+ab
+ab
+select c_ltext from t1 where c_ltext = "ab";
+c_ltext
+ab
+ab
+select * from t1 where c_varchar = "ab ";
+id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext
+1 ab ab ab ab ab ab ab
+2 ab ab ab ab ab ab ab
+select * from t1 where c_varchar_bin = "ab ";
+id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext
+1 ab ab ab ab ab ab ab
+2 ab ab ab ab ab ab ab
+select * from t1 where c_varchar_uni = "ab ";
+id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext
+1 ab ab ab ab ab ab ab
+2 ab ab ab ab ab ab ab
+select * from t1 where c_ttext = "ab ";
+id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext
+1 ab ab ab ab ab ab ab
+2 ab ab ab ab ab ab ab
+select * from t1 where c_text = "ab ";
+id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext
+1 ab ab ab ab ab ab ab
+2 ab ab ab ab ab ab ab
+select * from t1 where c_mtext = "ab ";
+id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext
+1 ab ab ab ab ab ab ab
+2 ab ab ab ab ab ab ab
+select * from t1 where c_ltext = "ab ";
+id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext
+1 ab ab ab ab ab ab ab
+2 ab ab ab ab ab ab ab
+drop table if exists t1;
+create table t1 (
+id int not null,
+c_char char(3) not null,
+c_varchar varchar(3) not null,
+c_char_bin char(3) binary not null,
+c_varchar_bin varchar(3) binary not null,
+c_char_uni char(3) unicode not null,
+c_varchar_uni varchar(3) unicode not null,
+c_dec decimal(3) not null,
+index(c_char),
+index(c_varchar),
+index(c_char_bin),
+index(c_varchar_bin),
+index(c_char_uni),
+index(c_varchar_uni),
+index(c_dec)
+) engine=pbxt;
+insert t1 values (1, "ab", "ab", "ab", "ab", "ab", "ab", 12);
+insert t1 values (2, "ab ", "ab ", "ab ", "ab ", "ab ", "ab ", 123);
+select * from t1 where c_char = "ab";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_varchar = "ab";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_char_bin = "ab";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_varchar_bin = "ab";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_char_uni = "ab";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_varchar_uni = "ab";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_dec = 12;
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+select c_char from t1 where c_char = "ab ";
+c_char
+ab
+ab
+select c_varchar from t1 where c_varchar = "ab ";
+c_varchar
+ab
+ab
+select c_char_bin from t1 where c_char_bin = "ab ";
+c_char_bin
+ab
+ab
+select c_varchar_bin from t1 where c_varchar_bin = "ab ";
+c_varchar_bin
+ab
+ab
+select c_char_uni from t1 where c_char_uni = "ab ";
+c_char_uni
+ab
+ab
+select c_varchar_uni from t1 where c_varchar_uni = "ab ";
+c_varchar_uni
+ab
+ab
+select c_dec from t1 where c_dec = 123;
+c_dec
+123
+drop table if exists t1;
+create table t1 (
+id int not null,
+c_char char(100) not null,
+c_varchar varchar(100) not null,
+c_char_bin char(100) binary not null,
+c_varchar_bin varchar(100) binary not null,
+c_char_uni char(100) unicode not null,
+c_varchar_uni varchar(100) unicode not null,
+c_dec decimal(65),
+index(c_char),
+index(c_varchar),
+index(c_char_bin),
+index(c_varchar_bin),
+index(c_char_uni),
+index(c_varchar_uni),
+index(c_dec)
+) engine=pbxt;
+insert t1 values (1, "ab", "ab", "ab", "ab", "ab", "ab", 12);
+insert t1 values (2, "ab ", "ab ", "ab ", "ab ", "ab ", "ab ", 123);
+select c_char from t1 where c_char = "ab";
+c_char
+ab
+ab
+select c_varchar from t1 where c_varchar = "ab";
+c_varchar
+ab
+ab
+select c_char_bin from t1 where c_char_bin = "ab";
+c_char_bin
+ab
+ab
+select c_varchar_bin from t1 where c_varchar_bin = "ab";
+c_varchar_bin
+ab
+ab
+select c_char_uni from t1 where c_char_uni = "ab";
+c_char_uni
+ab
+ab
+select c_varchar_uni from t1 where c_varchar_uni = "ab";
+c_varchar_uni
+ab
+ab
+select c_dec from t1 where c_dec = 12;
+c_dec
+12
+select * from t1 where c_char = "ab ";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_varchar = "ab ";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_char_bin = "ab ";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_varchar_bin = "ab ";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_char_uni = "ab ";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_varchar_uni = "ab ";
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+1 ab ab ab ab ab ab 12
+2 ab ab ab ab ab ab 123
+select * from t1 where c_dec = 123;
+id c_char c_varchar c_char_bin c_varchar_bin c_char_uni c_varchar_uni c_dec
+2 ab ab ab ab ab ab 123
+drop table if exists t1;
+create table t1 (
+id int,
+c_varchar varchar(300) not null,
+c_varchar_bin varchar(300) binary not null,
+c_varchar_uni varchar(300) unicode not null,
+c_ttext TINYTEXT not null,
+c_text TEXT not null,
+c_mtext MEDIUMTEXT not null,
+c_ltext LONGTEXT not null,
+index(c_varchar),
+index(c_varchar_bin),
+index(c_varchar_uni),
+index(c_ttext(100)),
+index(c_text(100)),
+index(c_mtext(100)),
+index(c_ltext(100))
+) engine=pbxt;
+insert t1 values (1, "ab", "ab", "ab", "ab", "ab", "ab", "ab");
+insert t1 values (2, "ab ", "ab ", "ab ", "ab ", "ab ", "ab ", "ab ");
+select * from t1 where c_varchar = "ab";
+id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext
+1 ab ab ab ab ab ab ab
+2 ab ab ab ab ab ab ab
+select * from t1 where c_varchar_bin = "ab";
+id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext
+1 ab ab ab ab ab ab ab
+2 ab ab ab ab ab ab ab
+select * from t1 where c_varchar_uni = "ab";
+id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext
+1 ab ab ab ab ab ab ab
+2 ab ab ab ab ab ab ab
+select * from t1 where c_ttext = "ab";
+id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext
+1 ab ab ab ab ab ab ab
+2 ab ab ab ab ab ab ab
+select * from t1 where c_text = "ab";
+id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext
+1 ab ab ab ab ab ab ab
+2 ab ab ab ab ab ab ab
+select * from t1 where c_mtext = "ab";
+id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext
+1 ab ab ab ab ab ab ab
+2 ab ab ab ab ab ab ab
+select * from t1 where c_ltext = "ab";
+id c_varchar c_varchar_bin c_varchar_uni c_ttext c_text c_mtext c_ltext
+1 ab ab ab ab ab ab ab
+2 ab ab ab ab ab ab ab
+select c_varchar from t1 where c_varchar = "ab ";
+c_varchar
+ab
+ab
+select c_varchar_bin from t1 where c_varchar_bin = "ab ";
+c_varchar_bin
+ab
+ab
+select c_varchar_uni from t1 where c_varchar_uni = "ab ";
+c_varchar_uni
+ab
+ab
+select c_ttext from t1 where c_ttext = "ab ";
+c_ttext
+ab
+ab
+select c_text from t1 where c_text = "ab ";
+c_text
+ab
+ab
+select c_mtext from t1 where c_mtext = "ab ";
+c_mtext
+ab
+ab
+select c_ltext from t1 where c_ltext = "ab ";
+c_ltext
+ab
+ab
+drop table if exists t1;
+create table t1 (
+id int,
+c_char_suni char(4) unicode,
+c_varchar_suni varchar(4) unicode,
+c_char_uni char(255) unicode,
+c_varchar_uni varchar(300) unicode,
+index(c_char_suni),
+index(c_varchar_suni),
+index(c_char_uni),
+index(c_varchar_uni)
+) engine=pbxt;
+insert t1 values (1, "ab", "ab", "ab", "ab");
+insert t1 values (1, "abcd", "abcd", "abcd", "abcd");
+insert t1 values (1, "ab ", "ab ", "abcd123123123123123123123123123123123123", "abcd123123123123123123123123123123123123");
+select * from t1 where c_char_suni = "ab";
+id c_char_suni c_varchar_suni c_char_uni c_varchar_uni
+1 ab ab ab ab
+1 ab ab abcd123123123123123123123123123123123123 abcd123123123123123123123123123123123123
+select * from t1 where c_varchar_suni = "ab";
+id c_char_suni c_varchar_suni c_char_uni c_varchar_uni
+1 ab ab ab ab
+1 ab ab abcd123123123123123123123123123123123123 abcd123123123123123123123123123123123123
+select * from t1 where c_char_uni = "ab";
+id c_char_suni c_varchar_suni c_char_uni c_varchar_uni
+1 ab ab ab ab
+select * from t1 where c_varchar_uni = "ab";
+id c_char_suni c_varchar_suni c_char_uni c_varchar_uni
+1 ab ab ab ab
+drop table if exists t1;
+create table t1 (id int, name char(10) key) engine=pbxt;
+insert t1 values (1, "ab");
+select * from t1 where name = "ab";
+id name
+1 ab
+insert t1 values (2, "ab ");
+ERROR 23000: Duplicate entry 'ab' for key 'PRIMARY'
+drop table if exists t1;
+create table t1 (id int, name char(100) key) engine=pbxt;
+insert t1 values (1, "ab");
+select * from t1 where name = "ab";
+id name
+1 ab
+insert t1 values (2, "ab ");
+ERROR 23000: Duplicate entry 'ab' for key 'PRIMARY'
+drop table if exists t1;
+create table t1 (id int, name varchar(10) key) engine=pbxt;
+insert t1 values (1, "ab");
+select * from t1 where name = "ab";
+id name
+1 ab
+insert t1 values (2, "ab ");
+ERROR 23000: Duplicate entry 'ab ' for key 'PRIMARY'
+drop table if exists t1;
+create table t1 (id int, name varchar(100) key) engine=pbxt;
+insert t1 values (1, "ab");
+select * from t1 where name = "ab";
+id name
+1 ab
+insert t1 values (2, "ab ");
+ERROR 23000: Duplicate entry 'ab ' for key 'PRIMARY'
+drop table if exists t1;
+create table t1 (id int, name varchar(400) key) engine=pbxt;
+insert t1 values (1, "ab");
+select * from t1 where name = "ab";
+id name
+1 ab
+insert t1 values (2, "ab ");
+ERROR 23000: Duplicate entry 'ab ' for key 'PRIMARY'
+drop table if exists t1;
+create table t1 (id int, name char(10) key) engine=pbxt;
+insert t1 values (1, "ab");
+select * from t1 where name = "ab";
+id name
+1 ab
+insert t1 values (2, "ab ");
+ERROR 23000: Duplicate entry 'ab' for key 'PRIMARY'
+drop table if exists t1;
+create table t1 (id int, name char(100) key) engine=pbxt;
+insert t1 values (1, "ab");
+select * from t1 where name = "ab";
+id name
+1 ab
+insert t1 values (2, "ab ");
+ERROR 23000: Duplicate entry 'ab' for key 'PRIMARY'
+drop table if exists t1;
+create table t1 (id int, name varchar(10), index(name)) engine=pbxt;
+insert t1 values (1, "ab");
+select * from t1 where name = "ab";
+id name
+1 ab
+insert t1 values (2, "ab ");
+insert t1 values (3, "ab ");
+select * from t1 where name = "ab";
+id name
+1 ab
+2 ab
+3 ab
+insert t1 values (4, "ab ");
+drop table if exists t1;
+create table t1 (id int, name varchar(100), index(name)) engine=pbxt;
+insert t1 values (1, "ab");
+select * from t1 where name = "ab";
+id name
+1 ab
+insert t1 values (2, "ab ");
+insert t1 values (3, "ab ");
+select * from t1 where name = "ab";
+id name
+1 ab
+2 ab
+3 ab
+insert t1 values (4, "ab ");
+drop table if exists t1;
+create table t1 (id int, name varchar(400), index(name)) engine=pbxt;
+insert t1 values (1, "ab");
+select * from t1 where name = "ab";
+id name
+1 ab
+insert t1 values (2, "ab ");
+insert t1 values (3, "ab ");
+select * from t1 where name = "ab";
+id name
+1 ab
+2 ab
+3 ab
+insert t1 values (4, "ab ");
+drop table if exists t1;
+create table t1 (id int, name char(10), index(name)) engine=pbxt;
+insert t1 values (1, "ab");
+select * from t1 where name = "ab";
+id name
+1 ab
+insert t1 values (2, "ab ");
+insert t1 values (3, "ab ");
+select * from t1 where name = "ab";
+id name
+1 ab
+2 ab
+3 ab
+insert t1 values (4, "ab ");
+drop table if exists t1;
+create table t1 (id int, name char(100), index(name)) engine=pbxt;
+insert t1 values (1, "ab");
+select * from t1 where name = "ab";
+id name
+1 ab
+insert t1 values (2, "ab ");
+insert t1 values (3, "ab ");
+select * from t1 where name = "ab";
+id name
+1 ab
+2 ab
+3 ab
+insert t1 values (4, "ab ");
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 ( id int, name varchar(300)) engine=pbxt;
+begin;
+insert t1(id, name) values(1, "aaa");
+update t1 set name=REPEAT('A', 300) where id = 1;
+commit;
+flush tables;
+select * from t1;
+id name
+1 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 ( id int, name varchar(300)) engine=pbxt;
+begin;
+insert t1(id, name) values(1, REPEAT('A', 300));
+update t1 set name="aaa" where id = 1;
+commit;
+flush tables;
+select * from t1;
+id name
+1 aaa
+drop table if exists t1, t2;
+create table t1 (s1 int primary key) engine = pbxt;
+insert into t1 values (1);
+create table t2 (s1 int, foreign key (s1) references t1 (s1)) engine = pbxt;
+insert into t2 values (1);
+truncate table t1;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (Constraint: `FOREIGN_1`)
+alter table t1 engine myisam;
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `s1` int(11) NOT NULL,
+ PRIMARY KEY (`s1`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table if exists t1, t2;
+create table t1 (s1 int primary key, s2 int unique not null) engine = pbxt;
+create table t2 (s1 int, foreign key (s1) references t1 (s1)) engine = pbxt;
+select * from information_schema.table_constraints
+where constraint_type = 'FOREIGN KEY' and table_name = 't2';
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
+NULL test FOREIGN_1 test t2 FOREIGN KEY
+select * from information_schema.referential_constraints where table_name = 't2';
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME
+NULL test FOREIGN_1 NULL test PRIMARY NONE RESTRICT RESTRICT t2 t1
+drop table t2, t1;
+create table t1 (s1 int, s2 int, unique key ix1 (s1, s2)) engine = pbxt;
+create table t2 (s1 int, s2 int, foreign key (s1, s2) references t1 (s1, s2)) engine = pbxt;
+select * from information_schema.table_constraints
+where constraint_type = 'FOREIGN KEY' and table_name = 't2';
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
+NULL test FOREIGN_1 test t2 FOREIGN KEY
+select * from information_schema.referential_constraints where table_name = 't2';
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME
+NULL test FOREIGN_1 NULL test ix1 NONE RESTRICT RESTRICT t2 t1
+drop table t2, t1;
+create table t1 (s1 int, s2 int, unique key ix1 (s1, s2), unique key ix2 (s1, s2)) engine = pbxt;
+create table t2 (s1 int, s2 int, foreign key (s1, s2) references t1 (s1, s2)) engine = pbxt;
+select * from information_schema.table_constraints
+where constraint_type = 'FOREIGN KEY' and table_name = 't2';
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
+NULL test FOREIGN_1 test t2 FOREIGN KEY
+select * from information_schema.referential_constraints where table_name = 't2';
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME
+NULL test FOREIGN_1 NULL test ix1 NONE RESTRICT RESTRICT t2 t1
+drop table t2, t1;
+create table t1 (s1 int, s2 int, unique key ix1 (s1, s2)) engine = pbxt;
+create table t2 (s1 int, s2 int,
+foreign key (s1, s2) references t1 (s1, s2) on delete cascade on update set null) engine = pbxt;
+select * from information_schema.table_constraints
+where constraint_type = 'FOREIGN KEY' and table_name = 't2';
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
+NULL test FOREIGN_1 test t2 FOREIGN KEY
+select * from information_schema.referential_constraints where table_name = 't2';
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME
+NULL test FOREIGN_1 NULL test ix1 NONE SET NULL CASCADE t2 t1
+drop table t2, t1;
+create table t2 (s1 int, s2 int, s3 int, s4 int, unique key ix34 (s3, s4),
+foreign key (s1, s2) references t2 (s3, s4)) engine = pbxt;
+select * from information_schema.table_constraints
+where constraint_type = 'FOREIGN KEY' and table_name = 't2';
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
+NULL test FOREIGN_1 test t2 FOREIGN KEY
+select * from information_schema.referential_constraints where table_name = 't2';
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME
+NULL test FOREIGN_1 NULL test ix34 NONE RESTRICT RESTRICT t2 t2
+drop table t2;
+create table t1 (s1 int, s2 int, unique key ix1 (s1, s2)) engine = pbxt;
+create table t2 (s1 int, s2 int,
+foreign key (s1, s2) references t1 (s1, s2)) engine = pbxt;
+alter table t1 add constraint s2 foreign key (s1, s2) references t2 (s1, s2);
+select * from information_schema.table_constraints
+where constraint_type = 'FOREIGN KEY' and (table_name = 't1' or table_name = 't2');
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
+NULL test s2 test t1 FOREIGN KEY
+NULL test FOREIGN_1 test t2 FOREIGN KEY
+select * from information_schema.referential_constraints where (table_name = 't1' or table_name = 't2');
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME
+NULL test s2 NULL test s1 NONE RESTRICT RESTRICT t1 t2
+NULL test FOREIGN_1 NULL test ix1 NONE RESTRICT RESTRICT t2 t1
+set foreign_key_checks = 0;
+drop table t2, t1;
+set foreign_key_checks = 1;
+create table t1 (id int primary key, s1 int, foreign key (s1) references t1 (id)) engine = pbxt;
+select * from information_schema.table_constraints
+where constraint_type = 'FOREIGN KEY' and table_name = 't1';
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
+NULL test FOREIGN_1 test t1 FOREIGN KEY
+select * from information_schema.referential_constraints where table_name = 't1';
+CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME
+NULL test FOREIGN_1 NULL test PRIMARY NONE RESTRICT RESTRICT t1 t1
+drop table t1;
+drop table if exists t2,t1;
+create table t1 (s1 int primary key) engine = pbxt;
+create table t2 (s1 int primary key, foreign key (s1) references t1 (s1) on update cascade) engine = pbxt;
+set @@autocommit = 0;
+insert into t1 values (1);
+insert into t2 values (1);
+update t1 set s1 = 2;
+set @@autocommit = 1;
+select * from t1, t2;
+s1 s1
+2 2
+drop table t2, t1;
+create table t1 (s1 int primary key, s2 int, key (s1, s2)) engine = pbxt;
+create table t2 (s1 int primary key, s2 int, key (s1, s2), foreign key (s1) references t1 (s1) on update cascade) engine = pbxt;
+set @@autocommit = 0;
+insert into t1 values (1, 5);
+insert into t2 values (1, 6);
+update t1 set s1 = 2;
+set @@autocommit = 1;
+select * from t1, t2;
+s1 s2 s1 s2
+2 5 2 6
+drop table t2, t1;
+set @@autocommit = 1;
+SET foreign_key_checks = 0;
+DROP TABLE IF EXISTS t15,t14,t13,t12,t11,t10,t9,t8,t7,t6,t5,t4,t3,t2,t1;
+SET foreign_key_checks = 1;
+CREATE TABLE t1 (s1 INT PRIMARY KEY, s2 INT) engine = pbxt;
+CREATE TABLE t2 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t1 (s1) ON UPDATE CASCADE) engine = pbxt;
+CREATE TABLE t3 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t2 (s1) ON UPDATE CASCADE) engine = pbxt;
+CREATE TABLE t4 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t3 (s1) ON UPDATE CASCADE) engine = pbxt;
+CREATE TABLE t5 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t4 (s1) ON UPDATE CASCADE) engine = pbxt;
+CREATE TABLE t6 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t5 (s1) ON UPDATE CASCADE) engine = pbxt;
+CREATE TABLE t7 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t6 (s1) ON UPDATE CASCADE) engine = pbxt;
+CREATE TABLE t8 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t7 (s1) ON UPDATE CASCADE) engine = pbxt;
+CREATE TABLE t9 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t8 (s1) ON UPDATE CASCADE) engine = pbxt;
+CREATE TABLE t10(s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t9 (s1) ON UPDATE CASCADE) engine = pbxt;
+CREATE TABLE t11(s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t10(s1) ON UPDATE CASCADE) engine = pbxt;
+CREATE TABLE t12(s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t11(s1) ON UPDATE CASCADE) engine = pbxt;
+CREATE TABLE t13(s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t12(s1) ON UPDATE CASCADE) engine = pbxt;
+CREATE TABLE t14(s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t13(s1) ON UPDATE CASCADE) engine = pbxt;
+CREATE TABLE t15(s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t14(s1) ON UPDATE CASCADE) engine = pbxt;
+ALTER TABLE t1 ADD FOREIGN KEY (s2) REFERENCES t15(s1) ON UPDATE CASCADE;
+SET foreign_key_checks = 0;
+INSERT INTO t1 VALUES (1,NULL);
+INSERT INTO t2 VALUES (1);
+INSERT INTO t3 VALUES (1);
+INSERT INTO t4 VALUES (1);
+INSERT INTO t5 VALUES (1);
+INSERT INTO t6 VALUES (1);
+INSERT INTO t7 VALUES (1);
+INSERT INTO t8 VALUES (1);
+INSERT INTO t9 VALUES (1);
+INSERT INTO t10 VALUES (1);
+INSERT INTO t11 VALUES (1);
+INSERT INTO t12 VALUES (1);
+INSERT INTO t13 VALUES (1);
+INSERT INTO t14 VALUES (1);
+INSERT INTO t15 VALUES (1);
+SET foreign_key_checks = 1;
+UPDATE t1 SET s1 = 2;
+select * from t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, t15;
+s1 s2 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1
+2 NULL 2 2 2 2 2 2 2 2 2 2 2 2 2 2
+UPDATE t1 SET s2 = 2;
+select * from t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, t15;
+s1 s2 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1
+2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
+UPDATE t1 SET s1 = 3;
+select * from t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, t15;
+s1 s2 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1 s1
+3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3
+SET foreign_key_checks = 0;
+DROP TABLE IF EXISTS t15,t14,t13,t12,t11,t10,t9,t8,t7,t6,t5,t4,t3,t2,t1;
+SET foreign_key_checks = 1;
+DROP TABLE IF EXISTS t2,t1;
+CREATE TABLE t1 (s1 ENUM('a','b') PRIMARY KEY) engine = pbxt;
+CREATE TABLE t2 (s1 ENUM('A','B'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt;
+DROP TABLE t2,t1;
+CREATE TABLE t1 (s1 ENUM('a','b') PRIMARY KEY) engine = pbxt;
+CREATE TABLE t2 (s1 ENUM('A','B', 'C'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt;
+ERROR HY000: Can't create table 'test.t2' (errno: 151)
+DROP TABLE t1;
+CREATE TABLE t1 (s1 ENUM('a','b','c') PRIMARY KEY) engine = pbxt;
+CREATE TABLE t2 (s1 ENUM('d','e','f'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt;
+DROP TABLE t2,t1;
+CREATE TABLE t1 (s1 SET('a','b') PRIMARY KEY) engine = pbxt;
+CREATE TABLE t2 (s1 SET('A','B'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt;
+DROP TABLE t2,t1;
+CREATE TABLE t1 (s1 SET('a','b') PRIMARY KEY) engine = pbxt;
+CREATE TABLE t2 (s1 SET('A','B', 'C'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt;
+ERROR HY000: Can't create table 'test.t2' (errno: 151)
+DROP TABLE t1;
+CREATE TABLE t1 (s1 SET('a','b','c') PRIMARY KEY) engine = pbxt;
+CREATE TABLE t2 (s1 SET('d','e','f'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt;
+DROP TABLE t2,t1;
+CREATE TABLE t1 (s1 SET('a','b') PRIMARY KEY) engine = pbxt;
+CREATE TABLE t2 (s1 ENUM('a','b'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt;
+ERROR HY000: Can't create table 'test.t2' (errno: 151)
+DROP TABLE t1;
+CREATE TABLE t1 (s1 ENUM('a','b') PRIMARY KEY) engine = pbxt;
+CREATE TABLE t2 (s1 SET('a','b'), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt;
+ERROR HY000: Can't create table 'test.t2' (errno: 151)
+DROP TABLE t1;
+DROP TABLE IF EXISTS t2, t1;
+Warnings:
+Note 1051 Unknown table 't2'
+Note 1051 Unknown table 't1'
+CREATE TABLE t1 (s1 VARCHAR(50) PRIMARY KEY) engine = pbxt;
+CREATE TABLE t2 (s1 VARCHAR(50), FOREIGN KEY (s1) REFERENCES t1 (s1)) engine = pbxt;
+INSERT INTO t1 VALUES ('A');
+INSERT INTO t2 VALUES ('A ');
+UPDATE t1 SET s1 = 'A ';
+DELETE FROM t2;
+DELETE FROM t1;
+INSERT INTO t1 VALUES ('A');
+INSERT INTO t2 VALUES ('A');
+UPDATE t1 SET s1 = 'A ';
+UPDATE t2 SET s1 = 'A ';
+UPDATE t1 SET s1 = 'a';
+UPDATE t2 SET s1 = 'a';
+DROP TABLE t2, t1;
+DROP TABLE IF EXISTS t2,t1;
+Warnings:
+Note 1051 Unknown table 't2'
+Note 1051 Unknown table 't1'
+CREATE TABLE t1 (s1 INT PRIMARY KEY);
+CREATE TABLE t2 (s1 INT DEFAULT 2, FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET DEFAULT);
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (1);
+DELETE FROM t1 WHERE s1 = 1;
+SELECT * FROM t2;
+s1
+2
+DROP TABLE IF EXISTS t2,t1;
+CREATE TABLE t1 (s1 DATE NOT NULL UNIQUE);
+CREATE TABLE t2 (s1 DATE DEFAULT '2000-01-01', FOREIGN KEY (s1) REFERENCES t1 (s1) ON UPDATE SET DEFAULT);
+INSERT INTO t1 VALUES ('2001-01-01');
+INSERT INTO t2 VALUES ('2001-01-01');
+UPDATE t1 SET s1 = '2001-01-02';
+SELECT * FROM t2;
+s1
+2000-01-01
+DROP TABLE IF EXISTS t2,t1;
+CREATE TABLE t1 (s1 INT PRIMARY KEY);
+CREATE TABLE t2 (s1 INT DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET DEFAULT);
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (1);
+DELETE FROM t1 WHERE s1 = 1;
+SELECT * FROM t2;
+s1
+NULL
+DROP TABLE IF EXISTS t2,t1;
+CREATE TABLE t1 (s1 VARCHAR(45) primary key);
+CREATE TABLE t2 (s1 VARCHAR(45) DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET DEFAULT);
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (1);
+DELETE FROM t1 WHERE s1 = 1;
+SELECT * FROM t2;
+s1
+NULL
+DROP TABLE IF EXISTS t2,t1;
+CREATE TABLE t1 (s1 VARCHAR(45), INDEX (s1(10)));
+CREATE TABLE t2 (s1 VARCHAR(45) DEFAULT "12345678901", FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET DEFAULT);
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (1);
+DELETE FROM t1 WHERE s1 = 1;
+SELECT * FROM t2;
+s1
+12345678901
+DROP TABLE IF EXISTS t2,t1;
+CREATE TABLE t1 (s1 INT);
+CREATE TABLE t2 (s1 INT DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET DEFAULT);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1);
+ERROR HY000: Got temporary error -1 'Matching index required for '`t1` (s1)'' from PBXT
+DROP TABLE IF EXISTS t3,t2,t1;
+CREATE TABLE t1 (s1 INT primary key);
+CREATE TABLE t2 (s1 INT DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET NULL);
+CREATE TABLE t3 (s1 INT DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t2 (s1) ON DELETE SET NULL);
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (1);
+INSERT INTO t3 VALUES (1);
+DELETE FROM t1 WHERE s1 = 1;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (Constraint: `FOREIGN_1`)
+SELECT * FROM t1;
+s1
+1
+2
+SELECT * FROM t2;
+s1
+1
+SELECT * FROM t3;
+s1
+1
+DROP TABLE IF EXISTS t3,t2,t1;
+CREATE TABLE t1 (s1 INT primary key);
+CREATE TABLE t2 (s1 INT DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE SET DEFAULT);
+CREATE TABLE t3 (s1 INT DEFAULT NULL, FOREIGN KEY (s1) REFERENCES t2 (s1) ON DELETE SET DEFAULT);
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (1);
+INSERT INTO t3 VALUES (1);
+DELETE FROM t1 WHERE s1 = 1;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (Constraint: `FOREIGN_1`)
+SELECT * FROM t1;
+s1
+1
+2
+SELECT * FROM t2;
+s1
+1
+SELECT * FROM t3;
+s1
+1
+DROP TABLE IF EXISTS t3,t2,t1;
+DROP TABLE IF EXISTS t2,t1;
+CREATE TABLE t1 (s1 INT PRIMARY KEY, s2 char(1));
+CREATE TABLE t2 (s1 INT, s2 INT PRIMARY KEY,
+FOREIGN KEY (s1) REFERENCES t1 (s1) ON DELETE CASCADE);
+INSERT INTO t1 VALUES (1,'a');
+INSERT INTO t2 VALUES (1,1);
+REPLACE INTO t1 VALUES (1,'b');
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+0
+set foreign_key_checks = 0;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (s1 INT PRIMARY KEY, s2 char(1));
+set foreign_key_checks = 1;
+INSERT INTO t1 VALUES (1,'a');
+REPLACE INTO t1 VALUES (1,'b');
+DROP TABLE IF EXISTS t3,t2,t1;
+CREATE TABLE t1 (s1 INT PRIMARY KEY, s2 INT);
+CREATE TABLE t2 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t1 (s1) ON UPDATE CASCADE);
+CREATE TABLE t3 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t2 (s1) ON UPDATE CASCADE);
+DROP TABLE IF EXISTS t3,t2,t1;
+CREATE TABLE t1 (s1 ENUM('a','b') PRIMARY KEY);
+CREATE TABLE t2 (s1 ENUM('A','B','C'), FOREIGN KEY (s1) REFERENCES t1 (s1));
+ERROR HY000: Can't create table 'test.t2' (errno: 151)
+DROP TABLE IF EXISTS t2,t1;
+Warnings:
+Note 1051 Unknown table 't2'
+CREATE TABLE t1 (s1 INT PRIMARY KEY, s2 INT);
+CREATE TABLE t2 (s1 INT PRIMARY KEY, FOREIGN KEY (s1) REFERENCES t1 (s1) ON UPDATE CASCADE);
+DROP TABLE IF EXISTS t2,t1;
+create table t1 (s1 varchar(40) primary key);
+create table t2 (s1 VARCHAR(30), foreign key (s1) references t1 (s1));
+insert into t1 values ("1");
+insert into t2 values ("1");
+DROP TABLE IF EXISTS t2,t1;
+DROP TABLE IF EXISTS t5;
+CREATE TABLE t5 (
+c1 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
+c2 BIGINT SIGNED NULL,
+c3 BIGINT SIGNED NOT NULL,
+c4 TINYINT, c5 SMALLINT,
+c6 MEDIUMINT,
+c7 INT,
+c8 INTEGER,
+PRIMARY KEY(c1,c2), UNIQUE INDEX(c3));
+INSERT INTO t5 VALUES
+(0,-9223372036854775808,1,2,3,4,5,5),
+(255,-2147483648,6,7,8,9,10,10),
+(65535,-8388608,11,12,13,14,15,15),
+(16777215,-32768,16,17,18,19,20,20),
+(4294967295,-128,21,22,23,24,25,25),
+(18446744073709551615,9223372036854775807,26,27,28,29,30,30);
+INSERT INTO t5(c2,c3) VALUES(33,34) /* tries to increment out of range */;
+ERROR HY000: Failed to read auto-increment value from storage engine
+INSERT INTO t5(c2,c3) VALUES(33,34);
+ERROR HY000: Failed to read auto-increment value from storage engine
+SELECT * FROM t5;
+c1 c2 c3 c4 c5 c6 c7 c8
+1 -9223372036854775808 1 2 3 4 5 5
+255 -2147483648 6 7 8 9 10 10
+65535 -8388608 11 12 13 14 15 15
+16777215 -32768 16 17 18 19 20 20
+4294967295 -128 21 22 23 24 25 25
+18446744073709551615 9223372036854775807 26 27 28 29 30 30
+DROP TABLE t5;
+/* same test as above with signed bigint */
+CREATE TABLE t5 (
+c1 BIGINT SIGNED NOT NULL AUTO_INCREMENT,
+c2 BIGINT SIGNED NULL,
+c3 BIGINT SIGNED NOT NULL,
+c4 TINYINT, c5 SMALLINT,
+c6 MEDIUMINT,
+c7 INT,
+c8 INTEGER,
+PRIMARY KEY(c1,c2), UNIQUE INDEX(c3));
+INSERT INTO t5 VALUES
+(0,-9223372036854775808,1,2,3,4,5,5),
+(255,-2147483648,6,7,8,9,10,10),
+(65535,-8388608,11,12,13,14,15,15),
+(16777215,-32768,16,17,18,19,20,20),
+(4294967295,-128,21,22,23,24,25,25),
+(9223372036854775807,9223372036854775807,26,27,28,29,30,30);
+INSERT INTO t5(c2,c3) VALUES(33,34) /* tries to increment out of range */;
+ERROR HY000: Failed to read auto-increment value from storage engine
+INSERT INTO t5(c2,c3) VALUES(33,34);
+ERROR HY000: Failed to read auto-increment value from storage engine
+SELECT * FROM t5;
+c1 c2 c3 c4 c5 c6 c7 c8
+1 -9223372036854775808 1 2 3 4 5 5
+255 -2147483648 6 7 8 9 10 10
+65535 -8388608 11 12 13 14 15 15
+16777215 -32768 16 17 18 19 20 20
+4294967295 -128 21 22 23 24 25 25
+9223372036854775807 9223372036854775807 26 27 28 29 30 30
+CREATE TABLE t2(c1 INT SIGNED NOT NULL, c2 INT UNSIGNED NULL, c3 INT, KEY(c1), KEY(c2));
+INSERT INTO t2 VALUES(-1,1,1),(-2,2,2),(-3,3,3),(-4,4,4),(-5,5,5),(-6,6,6),(-7,7,7),(-8,8,8),(-9,9,9),(10,10,10),(-11,NULL,11),(-12,12,12);
+INSERT INTO t2 VALUES(-2147483648,0,13),(2147483647,4294967295,14),(0,2147483648,15),(2147483647,2147483647,16);
+SELECT * FROM t2;
+c1 c2 c3
+-1 1 1
+-11 NULL 11
+-12 12 12
+-2 2 2
+-2147483648 0 13
+-3 3 3
+-4 4 4
+-5 5 5
+-6 6 6
+-7 7 7
+-8 8 8
+-9 9 9
+0 2147483648 15
+10 10 10
+2147483647 2147483647 16
+2147483647 4294967295 14
+EXPLAIN SELECT c1 FROM t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 index NULL c1 4 NULL 16 Using index
+SELECT c1 FROM t2;
+c1
+-1
+-11
+-12
+-2
+-2147483648
+-3
+-4
+-5
+-6
+-7
+-8
+-9
+0
+10
+2147483647
+2147483647
+UPDATE t2 SET c1=-2147483648 WHERE c2 <> 0 ORDER BY c2 LIMIT 2;
+SELECT * FROM t2 WHERE c2 <> 0 ORDER BY c2;
+c1 c2 c3
+-12 12 12
+-2147483648 1 1
+-2147483648 2 2
+-3 3 3
+-4 4 4
+-5 5 5
+-6 6 6
+-7 7 7
+-8 8 8
+-9 9 9
+0 2147483648 15
+10 10 10
+2147483647 2147483647 16
+2147483647 4294967295 14
+UPDATE t2 SET c1=-2147483648 WHERE c2 >= 0 ORDER BY c2 DESC LIMIT 2;
+SELECT * FROM t2 WHERE c2 >= 0 ORDER BY c2 DESC;
+c1 c2 c3
+-12 12 12
+-2147483648 0 13
+-2147483648 1 1
+-2147483648 2 2
+-2147483648 2147483648 15
+-2147483648 4294967295 14
+-3 3 3
+-4 4 4
+-5 5 5
+-6 6 6
+-7 7 7
+-8 8 8
+-9 9 9
+10 10 10
+2147483647 2147483647 16
+UPDATE t2 SET c1=-2147483648 WHERE c2 <= 3 ORDER BY c2 LIMIT 2;
+SELECT * FROM t2 WHERE c2 <= 3 ORDER BY c2;
+c1 c2 c3
+-2147483648 0 13
+-2147483648 1 1
+-2147483648 2 2
+-3 3 3
+UPDATE t2 SET c1=-2147483648 WHERE c2 <=> 4 ORDER BY c2 DESC LIMIT 2;
+SELECT * FROM t2 WHERE c2 <=> 4 ORDER BY c2;
+c1 c2 c3
+-2147483648 4 4
+UPDATE t2 SET c1=-2147483648 WHERE c2 BETWEEN 4 AND 7 ORDER BY c2 LIMIT 2;
+SELECT * FROM t2 WHERE c2 BETWEEN 4 AND 7 ORDER BY c2;
+c1 c2 c3
+-2147483648 4 4
+-2147483648 5 5
+-6 6 6
+-7 7 7
+UPDATE t2 SET c1=-2147483648 WHERE c2 IN(8,9) ORDER BY c2 DESC LIMIT 2;
+SELECT * FROM t2 WHERE c2 IN(8,9) ORDER BY c2 DESC;
+c1 c2 c3
+-2147483648 8 8
+-2147483648 9 9
+UPDATE t2 SET c1=-2147483648 WHERE c2 IS NULL ORDER BY c2 LIMIT 2;
+SELECT * FROM t2 WHERE c2 IS NULL ORDER BY c2;
+c1 c2 c3
+-2147483648 NULL 11
+UPDATE t2 SET c1=-2147483648 WHERE c2>= 6 AND c2 < 9 ORDER BY c2 LIMIT 2;
+SELECT * FROM t2 WHERE c2>= 6 AND c2 < 9 ORDER BY c2;
+c1 c2 c3
+-2147483648 6 6
+-2147483648 7 7
+-2147483648 8 8
+UPDATE t2 SET c1=-2147483648 WHERE c1=-12 OR c2=1;
+SELECT * FROM t2 WHERE c1=-2147483648;
+c1 c2 c3
+-2147483648 0 13
+-2147483648 1 1
+-2147483648 12 12
+-2147483648 2 2
+-2147483648 2147483648 15
+-2147483648 4 4
+-2147483648 4294967295 14
+-2147483648 5 5
+-2147483648 6 6
+-2147483648 7 7
+-2147483648 8 8
+-2147483648 9 9
+-2147483648 NULL 11
+SELECT * FROM t2;
+c1 c2 c3
+-2147483648 0 13
+-2147483648 1 1
+-2147483648 12 12
+-2147483648 2 2
+-2147483648 2147483648 15
+-2147483648 4 4
+-2147483648 4294967295 14
+-2147483648 5 5
+-2147483648 6 6
+-2147483648 7 7
+-2147483648 8 8
+-2147483648 9 9
+-2147483648 NULL 11
+-3 3 3
+10 10 10
+2147483647 2147483647 16
+EXPLAIN SELECT c1 FROM t2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 index NULL c1 4 NULL 16 Using index
+SELECT c1 FROM t2;
+c1
+-2147483648
+-2147483648
+-2147483648
+-2147483648
+-2147483648
+-2147483648
+-2147483648
+-2147483648
+-2147483648
+-2147483648
+-2147483648
+-2147483648
+-2147483648
+-3
+10
+2147483647
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (c1 INTEGER NOT NULL PRIMARY KEY, c2 VARCHAR(255));
+LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/std_data/pbxt_load_unique_error1.inc' REPLACE INTO TABLE t1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (@c1,c2) SET c1 = @c1 % 2;
+SELECT * FROM t1 ORDER BY c1;
+c1 c2
+0 opq
+1 jkl
+DROP TABLE t1;
+create table parent (id int primary key);
+create table child (id int PRIMARY KEY, FOREIGN KEY (id) REFERENCES parent(id));
+insert into parent values (2), (3), (4);
+insert into child values (3), (4);
+delete ignore from parent;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (Constraint: `FOREIGN_1`)
+select * from parent;
+id
+2
+3
+4
+drop table child, parent;
+create schema test378222;
+use test378222;
+create table t1 (id int primary key);
+create table t2 (id int primary key);
+alter table t1 add constraint foreign key (id) references t2 (id);
+alter table t2 add constraint foreign key (id) references t1 (id);
+drop schema test378222;
+create schema test378222a;
+create schema test378222b;
+create table test378222a.t1 (id int primary key);
+create table test378222b.t2 (id int primary key);
+alter table test378222a.t1 add constraint foreign key (id) references test378222b.t2 (id);
+alter table test378222b.t2 add constraint foreign key (id) references test378222a.t1 (id);
+set foreign_key_checks = 1;
+drop schema test378222a;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
+drop schema test378222b;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
+set foreign_key_checks = 0;
+drop schema test378222a;
+drop schema test378222b;
+set foreign_key_checks = 1;
+use test;
+CREATE TABLE t1(c1 TINYINT AUTO_INCREMENT NULL KEY ) AUTO_INCREMENT=10;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` tinyint(4) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=PBXT AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES(null);
+INSERT INTO t1 VALUES(null);
+INSERT INTO t1 VALUES(null);
+SELECT * FROM t1;
+c1
+10
+11
+12
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES(null);
+SELECT * FROM t1;
+c1
+1
+DROP TABLE t1;
View Lorry Controller Status