PBXT TEST RUN CHANGES:
======================

The following is a list of changes to the mysql-test-run test scripts and results. It indicates the major differences between PBXT and MyISAM.

[01] Increment of partial index works, but the ID generated is incremented like a non-partial index. For example:

create table t1 (c1 char(10) not null, c2 int not null auto_increment, primary key(c1, c2));
select * from t1;
c1 c2
A  8
B  1

insert into t1 (c1) values ('B');
select * from t1;
c1 c2
A  8
B  1
B  9

The standard result would be:
c1 c2
A  8
B  1
B  2

[02] CHECK TABLE is not implemented, for example:

check table t1;
Table	Op	Msg_type	Msg_text
test.t1	check	note	The storage engine for the table doesn't support check

[03] DISABLE/ENABLE KEYs is not implemented:

alter table t1 disable keys;
Warnings:
Note	1031	Table storage engine for '...' doesn't have this option

[04] The Cardinality for non-unique indexes is not calculated, for example:

create table t1 (n1 int not null, n2 int, n3 int, n4 float,
unique(n1),
key (n1, n2, n3, n4),
show keys from t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	n1	1	n1	A	10	NULL	NULL		BTREE	
t1	1	n1_2	1	n1	A	NULL	NULL	NULL		BTREE	
t1	1	n1_2	2	n2	A	NULL	NULL	NULL	YES	BTREE	
t1	1	n1_2	3	n3	A	NULL	NULL	NULL	YES	BTREE	
t1	1	n1_2	4	n4	A	NULL	NULL	NULL	YES	BTREE	

[05] ANALYZE TABLE is not fully implemented, but returns OK, for example:

analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK

The current implementation of ANALYZE TABLE pauses to wait for the sweeper (cleanup) thread to complete. This has the affect that if you want an accurate COUNT(*) value you should call ANALYZE TABLE first (see [11]).

[06] Many changes in mysql-test-run results are due to "show create table" which displays the engine as PBXT instead of MyISAM:

SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL default '0',
  `b` int(11) default NULL,
  UNIQUE KEY `b` (`b`)
) ENGINE=PBXT DEFAULT CHARSET=latin1

[07] There are differences in the "natural" underlying table order of rows between PBXT and MyISAM. This means if no ORDER BY is specified XT may return rows in a different order to MyISAM. For example:

drop table if exists t1;
CREATE TABLE t1 (a int, b int) engine=pbxt;
insert t1 values (1, 1), (2, 2);
update t1 set b = 3 where a = 1;
select * from t1;
a	b
2	2
1	3

Instead of (in the case of MyISAM):
a	b
1	3
2	2

In general, PBXT order can vary even if the update order is the same. An example of this is 'insert_update.test'. This is due to the fact that records are freed by a background thread (the sweeper).

In addition, if you use a statement of the form:

delete from/update t1 ... limit 1;

Then the rows deleted or updated is not deterministic and will probably be different to those deleted or updated by MyISAM. As a result, you should always add an ORDER BY for as many columns as is required to create a unique order. For example in the update.test:

delete from t1 order by a limit 1;

was changed to:

delete from t1 where order by a, b desc limit 1;

In addition, I have added ORDER BY clauses to a number of SELECTs in various tests in order to obtain a repeatable result row order that can be varified (e.g. multi_update.test).

[08] The function ha_pbxt::records_in_range() is not implemented correctly yet. It just returns the value 1. For example:

create table t1 (a int auto_increment not null primary key, B CHAR(20), key (b));
insert into t1 (b) values ("hello"),("my"),("world");
explain select * from t1 where b="world";
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	B	B	21	const	1	Using where

[09] PBXT maximum key length is 1020 bytes, not 1000:

create table t1 (a text, primary key(a(1021)));
ERROR 42000: Specified key was too long; max key length is 1020 bytes

[10] Correct value for COUNT(*) is only available when activity stops (multi_update.test).

The COUNT(*) is determined by the number of allocated rows. This value is maintained by a background thread. As a result, the value is only accurate after activity stops on a table.

For example if you execute the following sequence:

create table t1 ( a int not null, b int not null) ;
insert into t1 values (1,1),(2,2),(3,3),(4,4);
delete from t1 where a < 2;
select count(*) from t1;

The result of COUNT(*) will be 4. Now execute the SELECT again, the result will be 3.

Another affect of this is shown be the following sequence:

SET SQL_MAX_JOIN_SIZE=9;
create table t1 (a int auto_increment primary key, b char(20));
insert into t1(b) values("test"),("test"),("test2"),("test2"),("test2");
delete from t1 where a>2;
select 1 from t1,t1 as t2,t1 as t3;

The last select will fail with the error "The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay" if the table is PBXT, but not on a MyISAM table. The reason is the number of rows in the table is given as 5 instead of 3.

As a result I have made the followning change to select_safe.test
SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, SQL_MAX_JOIN_SIZE=9;
--->
SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=4, SQL_MAX_JOIN_SIZE=12;

To get an accurate COUNT(*) I have added ANALYZE TABLE to some tests (see [05]). This includes the tests (multi_update.test and select.test).

[11] Transaction commit is sometimes delayed by MySQL. The example below comes from 'derived.test'. 

(This is a Mac OS X, MySQL 4.1 problem only).

The second SELECT returns 1 row with MyISAM, but no rows with PBXT. This is due to the fact that the transaction that performs the insert after the table is created is committed after the transaction that does the SELECT on the second connection!

create table t1 select 1 as a;
connect (con1,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK);
connection con1;
use test;
select * from t1;

[12] FULLTEXT indexes are not implemented by XT. So I have changed the mysql-test-run tests that create full-text indexes so that they are skipped when MyISAM is not the default engine (e.g. myisam.test).

I added this line to those tests:

-- source include/engine_myisam.inc

Contents of 'engine_myisam.inc':

--require r/true.require
disable_query_log;
select convert(@@table_type using latin1) == "MyISAM" as "TRUE";
enable_query_log;

Other tests were modified by adding ENGINE=MyISAM when the table included a full-text index.

This includes the MySQL client test, mysql_client_test.c:
rc= mysql_query(mysql, "CREATE TABLE test_bg1500 (s VARCHAR(25), FULLTEXT(s))");
--->
rc= mysql_query(mysql, "CREATE TABLE test_bg1500 (s VARCHAR(25), FULLTEXT(s)) engine=myisam");


[13] PBXT does not implement spatial data types (GEOMETRY, SPATIAL KEY, POINT, LINESTRING, etc).

This tests have been disabled for PBXT by adding:

-- source include/engine_not_pbxt.inc

Contents of 'engine_not_pbxt.inc':

--require r/true.require
disable_query_log;
select convert(@@table_type using latin1) != "PBXT" as "TRUE";
enable_query_log;

[14] PBXT does not permit access to tables of more than one database in a single transaction.

This is the case with many statements in the grant test, for example:

update mysqltest_1.t1, mysqltest_2.t2 set a=10,d=10;

or:

begin;
update mysqltest_1.t1 set a=10;
update mysqltest_2.t2 set d=10;
commit;

In this case the following error is returned: 1015: Can't lock file (errno: -1)

This can also cause problems with PBXT temporary tables. As in distinct.test:

create temporary table t4 select * from t3;

This causes an error because the temporary table t4 is in a different database to the non-temporary table t3.

The current solution to this problem is to use a different engine for the temporary table.

create temporary table t4 engine=myisam select * from t3;

I have made this change to several tests (e.g. rpl000001.test, flush.test). For example:

flush.test:
create temporary table t1(n int not null primary key)
--->
create temporary table t1(n int not null primary key) engine=myisam;

A better solution would be not to place temporary tables in a different database, although they have a different location. This would require that the name of the database be passed down to the engine.

[15] Unlike MyISAM a partial INSERT ... SELECT (due to a duplicate key error), need not be placed in the binlog. This is because ROLLBACK occurs removing any rows that may have been inserted before the error.

create table t1(a int, unique(a));
insert into t1 values(2);
create table t2(a int);
insert into t2 values(1),(2);
reset master;

insert into t1 select * from t2;
--ERROR 1062 (23000): Duplicate entry '2' for key 1

select * from t1;
a
2

Instead of (in the case of MyISAM):
a
1
2

The result of rpl_error_ignored_table.test was changed, removing 2 entries that only appeared in the binlog when using MyISAM:

This statement generated a duplicate key error:
master-bin.000001	149	Query	1	149	use `test`; insert into t1 values (1),(1)

This statement was executed just before the connection was killed:
master-bin.000001	522	Query	1	522	use `test`; update t2 set a = a + 1 + get_lock('crash_lock%20C', 10)


[16] PBXT does not support "assign to keycache":

cache index t1 key (`primary`) in keycache1;
Table	Op	Msg_type	Msg_text
test.t1	assign_to_keycache	note	The storage engine for the table doesn't support assign_to_keycache

[17] Key_read_requests and Key_reads status variable are not incremented when using PBXT. These variables are modified only when the MyISAM key cache is accessed. You can check the value of the "Handler_%" variables instead (for example Handler_read_key and Handler_read_next).

In certain tests (preload.test), the following is done:

show status like "key_read%";
Variable_name	Value
Key_read_requests	0
Key_reads	0

[18] "load index into cache t1" is not supported.

[19] lock_multi.test has the following test:

connect (locker,localhost,root,,);
connect (reader,localhost,root,,);
connect (writer,localhost,root,,);

connection locker;
create table t1(n int);
insert into t1 values (1);
lock tables t1 write;
connection writer;
send update low_priority t1 set n = 4;
connection reader;
--sleep 2
send select n from t1;
connection locker;
--sleep 2
unlock tables;

The select is varified to return a row with the value 4. However, if the update is low priority, then why does it execute before the select? The result should be the value 1!

PBXT returns a row with the value 1. However, this is not because "low priority" is implemented. It is not implemented, and is basically ingored.

However, due to the thread scheduling the reader transaction begins before the update transaction is committed. As a result, the reader does not see the update!

[20] LOAD TABLE tx FROM MASTER is not supported (only supported by the MyISAM engine).

This is used in rpl000004.test and rpl000006.test.

Added

-- source include/engine_not_pbxt.inc

to disable these tests.


[21] engine=MERGE only supports MyISAM tables.

Merged tables must be MyISAM tables.

I have disabled certain tests because the use the MERGE engine (merge.test, repl_flush_tables.test).


[22] PBXT retains the original AUTO_INCREMENT start value in SHOW CREATE TABLE. MyISAM displays the current AUTO_INCREMENT value.

For example using MyISAM:

CREATE TABLE t1 (
	t1_id INT NOT NULL AUTO_INCREMENT KEY,
	t1_name CHAR(10)
) AUTO_INCREMENT = 1000;

INSERT INTO t1 (t1_name) VALUES('MySQL');
INSERT INTO t1 (t1_name) VALUES('MySQL');
INSERT INTO t1 (t1_name) VALUES('MySQL');

SHOW CREATE TABLE t1;

Table	Create Table
t1	CREATE TABLE `t1` (
  `t1_id` int(11) NOT NULL AUTO_INCREMENT,
  `t1_name` char(10) DEFAULT NULL,
  PRIMARY KEY (`t1_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1003 DEFAULT CHARSET=latin1

The same sequence using PBXT, has the following result:

Table	Create Table
t1	CREATE TABLE `t1` (
  `t1_id` int(11) NOT NULL AUTO_INCREMENT,
  `t1_name` char(10) DEFAULT NULL,
  PRIMARY KEY (`t1_id`)
) ENGINE=PBXT AUTO_INCREMENT=1000 DEFAULT CHARSET=latin1

[23] Case insensitive can sort differently because whether 'B' or 'b' comes first on an ORDER BY is not determinate. This is because they are evaluated as equal by the comparison routine (see test ctype_collate.test).

drop table if exists t1;
CREATE TABLE t1 (
  latin1_f CHAR(32) CHARACTER SET latin1 NOT NULL
) engine=pbxt;

INSERT INTO t1 (latin1_f) VALUES (_latin1'AE');
INSERT INTO t1 (latin1_f) VALUES (_latin1'ae');

INSERT INTO t1 (latin1_f) VALUES (_latin1'AF');
INSERT INTO t1 (latin1_f) VALUES (_latin1'af');


INSERT INTO t1 (latin1_f) VALUES (_latin1'B');
INSERT INTO t1 (latin1_f) VALUES (_latin1'b');

INSERT INTO t1 (latin1_f) VALUES (_latin1'U');
INSERT INTO t1 (latin1_f) VALUES (_latin1'u');

INSERT INTO t1 (latin1_f) VALUES (_latin1'UE');
INSERT INTO t1 (latin1_f) VALUES (_latin1'ue');

select * from t1 order by latin1_f;

alter table t1 engine=myisam;

select * from t1 order by latin1_f;

[24] Even if an insert fails, an auto_increment column is not "rolled-back".

create table t1 (id int primary key auto_increment, data int, unique(data));
insert ignore into t1 values(NULL,100),(NULL,110),(NULL,120);
insert ignore into t1 values(NULL,10),(NULL,20),(NULL,110),(NULL,120),(NULL,100),(NULL,90);
insert ignore into t1 values(NULL,130),(NULL,140),(500,110),(550,120),(450,100),(NULL,150);
select * from t1 order by id;
id	data
1	100
2	110
3	120
4	10
5	20
6	90
7	130
8	140
551	150

MyISAM returns:

9	150