~drizzle-trunk/drizzle/development

set storage_engine = InnoDB;

set autocommit=1;

drop table if exists t1;

drop table if exists t2;

drop table if exists t3;

create table t1 (a int);

create table t2 (a int unique);

create table t3 (a int);

insert into t1 (a) values (1), (2);

insert into t3 (a) values (1), (2);

insert into t2 (a) values (1001);

insert into t1 (a) values (1);

select * from t2;

a

1001

rollback;

select * from t2;

a

1001

insert into t2 (a) values (1002);

select * from t2;

a

1001

1002

rollback;

select * from t2;

a

1001

1002

insert into t2 (a) values (1003);

update t1 set a= a + 3;

select * from t2;

a

1001

1002

1003

rollback;

select * from t2;

a

1001

1002

1003

insert into t2 (a) values (1004);

update t1, t3 set t1.a = 0, t3.a = 0 where (4 = 4) and (t1.a = t3.a);

select * from t2;

a

1001

1002

1003

1004

rollback;

select * from t2;

a

1001

1002

1003

1004

insert into t2 (a) values (1005);

delete from t1 where (a = 5);

select * from t2;

a

1001

1002

1003

1004

1005

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

insert into t2 (a) values (1006);

delete from t1, t3 using t1, t3 where (6 = 6) ;

select * from t2;

a

1001

1002

1003

1004

1005

1006

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

insert into t2 (a) values (1007);

replace t1 values (7);

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

insert into t2 (a) values (1008);

replace into t3 (a) select 8 from t1;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

insert into t2 (a) values (1009);

select 9 from t1 ;

9

9

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

insert into t2 (a) values (1010);

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

insert into t2 (a) values (1011);

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

insert into t2 (a) values (1013);

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

insert into t2 (a) values (1014);

show open tables;

Database	Table	In_use	Name_locked

test	t2	0	0

test	t1	0	0

test	t3	0	0

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

insert into t2 (a) values (1015);

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

insert into t2 (a) values (1016);

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

insert into t2 (a) values (1017);

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

insert into t2 (a) values (1018);

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

1018

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

1018

insert into t2 (a) values (1019);

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

1018

1019

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

1018

1019

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

1018

1019

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

1018

1019

insert into t2 (a) values (1021);

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

1018

1019

1021

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

1018

1019

1021

insert into t2 (a) values (1022);

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

1018

1019

1021

1022

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

1018

1019

1021

1022

insert into t2 (a) values (1023);

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

1018

1019

1021

1022

1023

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

1018

1019

1021

1022

1023

=======================================================================

Testing select_to_file

=======================================================================

insert into t2 (a) values (1025);

select 25 into outfile "../tmp/dml.out" from t1;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

1018

1019

1021

1022

1023

1025

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

1018

1019

1021

1022

1023

1025

insert into t2 (a) values (1026);

load data infile "../std_data_ln/words.dat" into table t1 (a) set a:=26;

ERROR HY000: Incorrect integer value: 'Aarhus' for column 'a' at row 1

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

1018

1019

1021

1022

1023

1025

1026

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

1018

1019

1021

1022

1023

1025

1026

=======================================================================

Testing select_dumpvar

=======================================================================

insert into t2 (a) values (1027);

select 27 into @foo;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

1018

1019

1021

1022

1023

1025

1026

1027

rollback;

select * from t2;

a

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1013

1014

1015

1016

1017

1018

1019

1021

1022

1023

1025

1026

1027

=======================================================================

Cleanup

=======================================================================

set autocommit=default;

drop table t1;

drop table t2;

drop table t3;

#

# Bug#12713 Error in a stored function called from a SELECT doesn't

# cause ROLLBACK of statem

#

# Verify that two-phase commit is not issued for read-only

# transactions.

#

# Verify that two-phase commit is issued for read-write transactions,

# even if the change is done inside a stored function called from

# SELECT or SHOW statement.

#

set autocommit=0;

drop table if exists t1;

drop table if exists t2;

drop table if exists t3;