summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/include/ps_query.inc44
-rw-r--r--mysql-test/r/bdb.result24
-rw-r--r--mysql-test/r/ctype_utf8.result24
-rw-r--r--mysql-test/r/ndb_subquery.result8
-rw-r--r--mysql-test/r/ps_2myisam.result64
-rw-r--r--mysql-test/r/ps_3innodb.result64
-rw-r--r--mysql-test/r/ps_4heap.result64
-rw-r--r--mysql-test/r/ps_5merge.result128
-rw-r--r--mysql-test/r/ps_6bdb.result64
-rw-r--r--mysql-test/r/ps_7ndb.result214
-rw-r--r--mysql-test/t/bdb.test20
-rw-r--r--mysql-test/t/ctype_utf8.test20
-rw-r--r--mysql-test/t/ndb_subquery.test10
-rw-r--r--mysql-test/t/ps_7ndb.test4
14 files changed, 420 insertions, 332 deletions
diff --git a/mysql-test/include/ps_query.inc b/mysql-test/include/ps_query.inc
index e02d0d5bf96..27ab85410c8 100644
--- a/mysql-test/include/ps_query.inc
+++ b/mysql-test/include/ps_query.inc
@@ -104,13 +104,13 @@ prepare stmt1 from ' select substr(''MySQL'',1,?) from t1 where a=1 ' ;
execute stmt1 using @arg00 ;
# variations on 'concat'
set @arg00='MySQL' ;
-select a , concat(@arg00,b) from t1 ;
+select a , concat(@arg00,b) from t1 order by a;
# BUG#3796 Prepared statement, select concat(<parameter>,<column>),wrong result
-prepare stmt1 from ' select a , concat(?,b) from t1 ' ;
+prepare stmt1 from ' select a , concat(?,b) from t1 order by a ' ;
execute stmt1 using @arg00;
#
-select a , concat(b,@arg00) from t1 ;
-prepare stmt1 from ' select a , concat(b,?) from t1 ' ;
+select a , concat(b,@arg00) from t1 order by a ;
+prepare stmt1 from ' select a , concat(b,?) from t1 order by a ' ;
execute stmt1 using @arg00;
# variations on 'group_concat'
@@ -147,7 +147,7 @@ create table t5 (id1 int(11) not null default '0',
value2 varchar(100), value1 varchar(100)) ;
insert into t5 values (1,'hh','hh'),(2,'hh','hh'),
(1,'ii','ii'),(2,'ii','ii') ;
-prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? ' ;
+prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? order by id1,value1 ' ;
set @arg00=1 ;
set @arg01='hh' ;
execute stmt1 using @arg00, @arg01 ;
@@ -216,8 +216,8 @@ execute stmt1 using @arg00 ;
# parameter in IN
set @arg00=2 ;
set @arg01=3 ;
-select a FROM t1 where a in (@arg00,@arg01);
-prepare stmt1 from ' select a FROM t1 where a in (?,?) ';
+select a FROM t1 where a in (@arg00,@arg01) order by a;
+prepare stmt1 from ' select a FROM t1 where a in (?,?) order by a ';
execute stmt1 using @arg00, @arg01;
# case derived from client_test.c: test_bug1500()
set @arg00= 'one' ;
@@ -270,9 +270,9 @@ execute stmt1 using @arg00 ;
##### parameter used in having clause
set @arg00='two' ;
select a,b FROM t1 where a is not NULL
-AND b is not NULL having b <> @arg00 ;
+AND b is not NULL having b <> @arg00 order by a ;
prepare stmt1 from ' select a,b FROM t1 where a is not NULL
-AND b is not NULL having b <> ? ' ;
+AND b is not NULL having b <> ? order by a ' ;
execute stmt1 using @arg00 ;
##### parameter used in order clause
@@ -297,7 +297,7 @@ execute stmt1 using @arg00;
##### parameter used in limit clause
set @arg00=1;
-prepare stmt1 from ' select a,b from t1
+prepare stmt1 from ' select a,b from t1 order by a
limit 1 ';
execute stmt1 ;
# currently (May 2004, Version 4.1) it is impossible
@@ -327,10 +327,10 @@ select '------ join tests ------' as test_sequence ;
# no parameter
select first.a as a1, second.a as a2
from t1 first, t1 second
-where first.a = second.a ;
+where first.a = second.a order by a1 ;
prepare stmt1 from ' select first.a as a1, second.a as a2
from t1 first, t1 second
- where first.a = second.a ';
+ where first.a = second.a order by a1 ';
execute stmt1 ;
# some parameters
@@ -350,15 +350,15 @@ execute stmt1 using @arg00, @arg01, @arg02;
drop table if exists t2 ;
--enable_warnings
create table t2 as select * from t1 ;
-set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) ' ;
-set @query2= 'SELECT * FROM t2 natural join t1 ' ;
-set @query3= 'SELECT * FROM t2 join t1 using(a) ' ;
-set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) ' ;
-set @query5= 'SELECT * FROM t2 natural left join t1 ' ;
-set @query6= 'SELECT * FROM t2 left join t1 using(a) ' ;
-set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) ' ;
-set @query8= 'SELECT * FROM t2 natural right join t1 ' ;
-set @query9= 'SELECT * FROM t2 right join t1 using(a) ' ;
+set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
+set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
+set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
+set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
+set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
+set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
+set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
+set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
+set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
let $1= 9 ;
while ($1)
{
@@ -424,7 +424,7 @@ execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
######## correlated subquery
# no parameter
prepare stmt1 from ' select a, b FROM t1 outer_table where
- a = (select a from t1 where b = outer_table.b ) ';
+ a = (select a from t1 where b = outer_table.b ) order by a ';
# also Bug#4000 (only BDB tables)
# Bug#4106 : ndb table, query with correlated subquery, wrong result
execute stmt1 ;
diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result
index 846adaf53ef..6da3dbb929d 100644
--- a/mysql-test/r/bdb.result
+++ b/mysql-test/r/bdb.result
@@ -1260,3 +1260,27 @@ a length(a) char_length(a)
ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ 510 255
drop table t1;
SET NAMES latin1;
+CREATE TABLE t1 (
+id int unsigned NOT NULL auto_increment,
+list_id smallint unsigned NOT NULL,
+term TEXT NOT NULL,
+PRIMARY KEY(id),
+INDEX(list_id, term(4))
+) ENGINE=BDB CHARSET=utf8;
+INSERT INTO t1 SET list_id = 1, term = "letterc";
+INSERT INTO t1 SET list_id = 1, term = "letterb";
+INSERT INTO t1 SET list_id = 1, term = "lettera";
+INSERT INTO t1 SET list_id = 1, term = "letterd";
+SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterc");
+id
+1
+SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterb");
+id
+2
+SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera");
+id
+3
+SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd");
+id
+4
+DROP TABLE t1;
diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result
index 0c9ea42a0f1..0b98d9432ae 100644
--- a/mysql-test/r/ctype_utf8.result
+++ b/mysql-test/r/ctype_utf8.result
@@ -726,3 +726,27 @@ length(subject)
3
432
DROP TABLE t1;
+CREATE TABLE t1 (
+id int unsigned NOT NULL auto_increment,
+list_id smallint unsigned NOT NULL,
+term TEXT NOT NULL,
+PRIMARY KEY(id),
+INDEX(list_id, term(4))
+) ENGINE=MYISAM CHARSET=utf8;
+INSERT INTO t1 SET list_id = 1, term = "letterc";
+INSERT INTO t1 SET list_id = 1, term = "letterb";
+INSERT INTO t1 SET list_id = 1, term = "lettera";
+INSERT INTO t1 SET list_id = 1, term = "letterd";
+SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterc");
+id
+1
+SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterb");
+id
+2
+SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera");
+id
+3
+SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd");
+id
+4
+DROP TABLE t1;
diff --git a/mysql-test/r/ndb_subquery.result b/mysql-test/r/ndb_subquery.result
index 8c89805a765..f65f09b71b3 100644
--- a/mysql-test/r/ndb_subquery.result
+++ b/mysql-test/r/ndb_subquery.result
@@ -10,7 +10,7 @@ explain select * from t2 where p NOT IN (select p from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where
2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 Using index
-select * from t2 where p NOT IN (select p from t1);
+select * from t2 where p NOT IN (select p from t1) order by p;
p u o
4 4 4
5 5 5
@@ -18,7 +18,7 @@ explain select * from t2 where p NOT IN (select u from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where
2 DEPENDENT SUBQUERY t1 unique_subquery u u 4 func 1 Using index
-select * from t2 where p NOT IN (select u from t1);
+select * from t2 where p NOT IN (select u from t1) order by p;
p u o
4 4 4
5 5 5
@@ -26,7 +26,7 @@ explain select * from t2 where p NOT IN (select o from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where
2 DEPENDENT SUBQUERY t1 index_subquery o o 4 func 1 Using index
-select * from t2 where p NOT IN (select o from t1);
+select * from t2 where p NOT IN (select o from t1) order by p;
p u o
4 4 4
5 5 5
@@ -34,7 +34,7 @@ explain select * from t2 where p NOT IN (select p+0 from t1);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where
-select * from t2 where p NOT IN (select p+0 from t1);
+select * from t2 where p NOT IN (select p+0 from t1) order by p;
p u o
4 4 4
5 5 5
diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result
index 68e0a53fdba..63463baabfd 100644
--- a/mysql-test/r/ps_2myisam.result
+++ b/mysql-test/r/ps_2myisam.result
@@ -165,26 +165,26 @@ execute stmt1 using @arg00 ;
substr('MySQL',1,?)
MyS
set @arg00='MySQL' ;
-select a , concat(@arg00,b) from t1 ;
+select a , concat(@arg00,b) from t1 order by a;
a concat(@arg00,b)
1 MySQLone
2 MySQLtwo
3 MySQLthree
4 MySQLfour
-prepare stmt1 from ' select a , concat(?,b) from t1 ' ;
+prepare stmt1 from ' select a , concat(?,b) from t1 order by a ' ;
execute stmt1 using @arg00;
a concat(?,b)
1 MySQLone
2 MySQLtwo
3 MySQLthree
4 MySQLfour
-select a , concat(b,@arg00) from t1 ;
+select a , concat(b,@arg00) from t1 order by a ;
a concat(b,@arg00)
1 oneMySQL
2 twoMySQL
3 threeMySQL
4 fourMySQL
-prepare stmt1 from ' select a , concat(b,?) from t1 ' ;
+prepare stmt1 from ' select a , concat(b,?) from t1 order by a ' ;
execute stmt1 using @arg00;
a concat(b,?)
1 oneMySQL
@@ -234,14 +234,14 @@ create table t5 (id1 int(11) not null default '0',
value2 varchar(100), value1 varchar(100)) ;
insert into t5 values (1,'hh','hh'),(2,'hh','hh'),
(1,'ii','ii'),(2,'ii','ii') ;
-prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? ' ;
+prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? order by id1,value1 ' ;
set @arg00=1 ;
set @arg01='hh' ;
execute stmt1 using @arg00, @arg01 ;
id1 value1
1 hh
-2 hh
1 ii
+2 hh
drop table t5 ;
drop table if exists t5 ;
create table t5(session_id char(9) not null) ;
@@ -307,11 +307,11 @@ execute stmt1 using @arg00 ;
a
set @arg00=2 ;
set @arg01=3 ;
-select a FROM t1 where a in (@arg00,@arg01);
+select a FROM t1 where a in (@arg00,@arg01) order by a;
a
2
3
-prepare stmt1 from ' select a FROM t1 where a in (?,?) ';
+prepare stmt1 from ' select a FROM t1 where a in (?,?) order by a ';
execute stmt1 using @arg00, @arg01;
a
2
@@ -385,13 +385,13 @@ a b
4 four
set @arg00='two' ;
select a,b FROM t1 where a is not NULL
-AND b is not NULL having b <> @arg00 ;
+AND b is not NULL having b <> @arg00 order by a ;
a b
1 one
3 three
4 four
prepare stmt1 from ' select a,b FROM t1 where a is not NULL
-AND b is not NULL having b <> ? ' ;
+AND b is not NULL having b <> ? order by a ' ;
execute stmt1 using @arg00 ;
a b
1 one
@@ -439,7 +439,7 @@ set @arg00=0 ;
execute stmt1 using @arg00;
ERROR 42S22: Unknown column '?' in 'order clause'
set @arg00=1;
-prepare stmt1 from ' select a,b from t1
+prepare stmt1 from ' select a,b from t1 order by a
limit 1 ';
execute stmt1 ;
a b
@@ -470,7 +470,7 @@ test_sequence
------ join tests ------
select first.a as a1, second.a as a2
from t1 first, t1 second
-where first.a = second.a ;
+where first.a = second.a order by a1 ;
a1 a2
1 1
2 2
@@ -478,7 +478,7 @@ a1 a2
4 4
prepare stmt1 from ' select first.a as a1, second.a as a2
from t1 first, t1 second
- where first.a = second.a ';
+ where first.a = second.a order by a1 ';
execute stmt1 ;
a1 a2
1 1
@@ -517,17 +517,17 @@ a ? a
4 ABC 4
drop table if exists t2 ;
create table t2 as select * from t1 ;
-set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) ' ;
-set @query2= 'SELECT * FROM t2 natural join t1 ' ;
-set @query3= 'SELECT * FROM t2 join t1 using(a) ' ;
-set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) ' ;
-set @query5= 'SELECT * FROM t2 natural left join t1 ' ;
-set @query6= 'SELECT * FROM t2 left join t1 using(a) ' ;
-set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) ' ;
-set @query8= 'SELECT * FROM t2 natural right join t1 ' ;
-set @query9= 'SELECT * FROM t2 right join t1 using(a) ' ;
+set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
+set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
+set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
+set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
+set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
+set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
+set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
+set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
+set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
the join statement is:
-SELECT * FROM t2 right join t1 using(a)
+SELECT * FROM t2 right join t1 using(a) order by t2.a
prepare stmt1 from @query9 ;
execute stmt1 ;
a b a b
@@ -548,7 +548,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 natural right join t1
+SELECT * FROM t2 natural right join t1 order by t2.a
prepare stmt1 from @query8 ;
execute stmt1 ;
a b a b
@@ -569,7 +569,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 right join t1 on(t1.a=t2.a)
+SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a
prepare stmt1 from @query7 ;
execute stmt1 ;
a b a b
@@ -590,7 +590,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 left join t1 using(a)
+SELECT * FROM t2 left join t1 using(a) order by t2.a
prepare stmt1 from @query6 ;
execute stmt1 ;
a b a b
@@ -611,7 +611,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 natural left join t1
+SELECT * FROM t2 natural left join t1 order by t2.a
prepare stmt1 from @query5 ;
execute stmt1 ;
a b a b
@@ -632,7 +632,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 left join t1 on(t1.a=t2.a)
+SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a
prepare stmt1 from @query4 ;
execute stmt1 ;
a b a b
@@ -653,7 +653,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 join t1 using(a)
+SELECT * FROM t2 join t1 using(a) order by t2.a
prepare stmt1 from @query3 ;
execute stmt1 ;
a b a b
@@ -674,7 +674,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 natural join t1
+SELECT * FROM t2 natural join t1 order by t2.a
prepare stmt1 from @query2 ;
execute stmt1 ;
a b
@@ -695,7 +695,7 @@ a b
3 three
4 four
the join statement is:
-SELECT * FROM t2 join t1 on (t1.a=t2.a)
+SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a
prepare stmt1 from @query1 ;
execute stmt1 ;
a b a b
@@ -769,7 +769,7 @@ execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
a ? b
2 1 two
prepare stmt1 from ' select a, b FROM t1 outer_table where
- a = (select a from t1 where b = outer_table.b ) ';
+ a = (select a from t1 where b = outer_table.b ) order by a ';
execute stmt1 ;
a b
1 one
diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result
index 90cd5e39f9c..70ba4a109d7 100644
--- a/mysql-test/r/ps_3innodb.result
+++ b/mysql-test/r/ps_3innodb.result
@@ -165,26 +165,26 @@ execute stmt1 using @arg00 ;
substr('MySQL',1,?)
MyS
set @arg00='MySQL' ;
-select a , concat(@arg00,b) from t1 ;
+select a , concat(@arg00,b) from t1 order by a;
a concat(@arg00,b)
1 MySQLone
2 MySQLtwo
3 MySQLthree
4 MySQLfour
-prepare stmt1 from ' select a , concat(?,b) from t1 ' ;
+prepare stmt1 from ' select a , concat(?,b) from t1 order by a ' ;
execute stmt1 using @arg00;
a concat(?,b)
1 MySQLone
2 MySQLtwo
3 MySQLthree
4 MySQLfour
-select a , concat(b,@arg00) from t1 ;
+select a , concat(b,@arg00) from t1 order by a ;
a concat(b,@arg00)
1 oneMySQL
2 twoMySQL
3 threeMySQL
4 fourMySQL
-prepare stmt1 from ' select a , concat(b,?) from t1 ' ;
+prepare stmt1 from ' select a , concat(b,?) from t1 order by a ' ;
execute stmt1 using @arg00;
a concat(b,?)
1 oneMySQL
@@ -234,14 +234,14 @@ create table t5 (id1 int(11) not null default '0',
value2 varchar(100), value1 varchar(100)) ;
insert into t5 values (1,'hh','hh'),(2,'hh','hh'),
(1,'ii','ii'),(2,'ii','ii') ;
-prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? ' ;
+prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? order by id1,value1 ' ;
set @arg00=1 ;
set @arg01='hh' ;
execute stmt1 using @arg00, @arg01 ;
id1 value1
1 hh
-2 hh
1 ii
+2 hh
drop table t5 ;
drop table if exists t5 ;
create table t5(session_id char(9) not null) ;
@@ -307,11 +307,11 @@ execute stmt1 using @arg00 ;
a
set @arg00=2 ;
set @arg01=3 ;
-select a FROM t1 where a in (@arg00,@arg01);
+select a FROM t1 where a in (@arg00,@arg01) order by a;
a
2
3
-prepare stmt1 from ' select a FROM t1 where a in (?,?) ';
+prepare stmt1 from ' select a FROM t1 where a in (?,?) order by a ';
execute stmt1 using @arg00, @arg01;
a
2
@@ -385,13 +385,13 @@ a b
4 four
set @arg00='two' ;
select a,b FROM t1 where a is not NULL
-AND b is not NULL having b <> @arg00 ;
+AND b is not NULL having b <> @arg00 order by a ;
a b
1 one
3 three
4 four
prepare stmt1 from ' select a,b FROM t1 where a is not NULL
-AND b is not NULL having b <> ? ' ;
+AND b is not NULL having b <> ? order by a ' ;
execute stmt1 using @arg00 ;
a b
1 one
@@ -439,7 +439,7 @@ set @arg00=0 ;
execute stmt1 using @arg00;
ERROR 42S22: Unknown column '?' in 'order clause'
set @arg00=1;
-prepare stmt1 from ' select a,b from t1
+prepare stmt1 from ' select a,b from t1 order by a
limit 1 ';
execute stmt1 ;
a b
@@ -470,7 +470,7 @@ test_sequence
------ join tests ------
select first.a as a1, second.a as a2
from t1 first, t1 second
-where first.a = second.a ;
+where first.a = second.a order by a1 ;
a1 a2
1 1
2 2
@@ -478,7 +478,7 @@ a1 a2
4 4
prepare stmt1 from ' select first.a as a1, second.a as a2
from t1 first, t1 second
- where first.a = second.a ';
+ where first.a = second.a order by a1 ';
execute stmt1 ;
a1 a2
1 1
@@ -517,17 +517,17 @@ a ? a
4 ABC 4
drop table if exists t2 ;
create table t2 as select * from t1 ;
-set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) ' ;
-set @query2= 'SELECT * FROM t2 natural join t1 ' ;
-set @query3= 'SELECT * FROM t2 join t1 using(a) ' ;
-set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) ' ;
-set @query5= 'SELECT * FROM t2 natural left join t1 ' ;
-set @query6= 'SELECT * FROM t2 left join t1 using(a) ' ;
-set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) ' ;
-set @query8= 'SELECT * FROM t2 natural right join t1 ' ;
-set @query9= 'SELECT * FROM t2 right join t1 using(a) ' ;
+set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
+set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
+set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
+set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
+set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
+set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
+set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
+set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
+set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
the join statement is:
-SELECT * FROM t2 right join t1 using(a)
+SELECT * FROM t2 right join t1 using(a) order by t2.a
prepare stmt1 from @query9 ;
execute stmt1 ;
a b a b
@@ -548,7 +548,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 natural right join t1
+SELECT * FROM t2 natural right join t1 order by t2.a
prepare stmt1 from @query8 ;
execute stmt1 ;
a b a b
@@ -569,7 +569,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 right join t1 on(t1.a=t2.a)
+SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a
prepare stmt1 from @query7 ;
execute stmt1 ;
a b a b
@@ -590,7 +590,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 left join t1 using(a)
+SELECT * FROM t2 left join t1 using(a) order by t2.a
prepare stmt1 from @query6 ;
execute stmt1 ;
a b a b
@@ -611,7 +611,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 natural left join t1
+SELECT * FROM t2 natural left join t1 order by t2.a
prepare stmt1 from @query5 ;
execute stmt1 ;
a b a b
@@ -632,7 +632,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 left join t1 on(t1.a=t2.a)
+SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a
prepare stmt1 from @query4 ;
execute stmt1 ;
a b a b
@@ -653,7 +653,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 join t1 using(a)
+SELECT * FROM t2 join t1 using(a) order by t2.a
prepare stmt1 from @query3 ;
execute stmt1 ;
a b a b
@@ -674,7 +674,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 natural join t1
+SELECT * FROM t2 natural join t1 order by t2.a
prepare stmt1 from @query2 ;
execute stmt1 ;
a b
@@ -695,7 +695,7 @@ a b
3 three
4 four
the join statement is:
-SELECT * FROM t2 join t1 on (t1.a=t2.a)
+SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a
prepare stmt1 from @query1 ;
execute stmt1 ;
a b a b
@@ -769,7 +769,7 @@ execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
a ? b
2 1 two
prepare stmt1 from ' select a, b FROM t1 outer_table where
- a = (select a from t1 where b = outer_table.b ) ';
+ a = (select a from t1 where b = outer_table.b ) order by a ';
execute stmt1 ;
a b
1 one
diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result
index feef6bcf83e..4c68f0c8593 100644
--- a/mysql-test/r/ps_4heap.result
+++ b/mysql-test/r/ps_4heap.result
@@ -166,26 +166,26 @@ execute stmt1 using @arg00 ;
substr('MySQL',1,?)
MyS
set @arg00='MySQL' ;
-select a , concat(@arg00,b) from t1 ;
+select a , concat(@arg00,b) from t1 order by a;
a concat(@arg00,b)
1 MySQLone
2 MySQLtwo
3 MySQLthree
4 MySQLfour
-prepare stmt1 from ' select a , concat(?,b) from t1 ' ;
+prepare stmt1 from ' select a , concat(?,b) from t1 order by a ' ;
execute stmt1 using @arg00;
a concat(?,b)
1 MySQLone
2 MySQLtwo
3 MySQLthree
4 MySQLfour
-select a , concat(b,@arg00) from t1 ;
+select a , concat(b,@arg00) from t1 order by a ;
a concat(b,@arg00)
1 oneMySQL
2 twoMySQL
3 threeMySQL
4 fourMySQL
-prepare stmt1 from ' select a , concat(b,?) from t1 ' ;
+prepare stmt1 from ' select a , concat(b,?) from t1 order by a ' ;
execute stmt1 using @arg00;
a concat(b,?)
1 oneMySQL
@@ -235,14 +235,14 @@ create table t5 (id1 int(11) not null default '0',
value2 varchar(100), value1 varchar(100)) ;
insert into t5 values (1,'hh','hh'),(2,'hh','hh'),
(1,'ii','ii'),(2,'ii','ii') ;
-prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? ' ;
+prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? order by id1,value1 ' ;
set @arg00=1 ;
set @arg01='hh' ;
execute stmt1 using @arg00, @arg01 ;
id1 value1
1 hh
-2 hh
1 ii
+2 hh
drop table t5 ;
drop table if exists t5 ;
create table t5(session_id char(9) not null) ;
@@ -308,11 +308,11 @@ execute stmt1 using @arg00 ;
a
set @arg00=2 ;
set @arg01=3 ;
-select a FROM t1 where a in (@arg00,@arg01);
+select a FROM t1 where a in (@arg00,@arg01) order by a;
a
2
3
-prepare stmt1 from ' select a FROM t1 where a in (?,?) ';
+prepare stmt1 from ' select a FROM t1 where a in (?,?) order by a ';
execute stmt1 using @arg00, @arg01;
a
2
@@ -386,13 +386,13 @@ a b
4 four
set @arg00='two' ;
select a,b FROM t1 where a is not NULL
-AND b is not NULL having b <> @arg00 ;
+AND b is not NULL having b <> @arg00 order by a ;
a b
1 one
3 three
4 four
prepare stmt1 from ' select a,b FROM t1 where a is not NULL
-AND b is not NULL having b <> ? ' ;
+AND b is not NULL having b <> ? order by a ' ;
execute stmt1 using @arg00 ;
a b
1 one
@@ -440,7 +440,7 @@ set @arg00=0 ;
execute stmt1 using @arg00;
ERROR 42S22: Unknown column '?' in 'order clause'
set @arg00=1;
-prepare stmt1 from ' select a,b from t1
+prepare stmt1 from ' select a,b from t1 order by a
limit 1 ';
execute stmt1 ;
a b
@@ -471,7 +471,7 @@ test_sequence
------ join tests ------
select first.a as a1, second.a as a2
from t1 first, t1 second
-where first.a = second.a ;
+where first.a = second.a order by a1 ;
a1 a2
1 1
2 2
@@ -479,7 +479,7 @@ a1 a2
4 4
prepare stmt1 from ' select first.a as a1, second.a as a2
from t1 first, t1 second
- where first.a = second.a ';
+ where first.a = second.a order by a1 ';
execute stmt1 ;
a1 a2
1 1
@@ -518,17 +518,17 @@ a ? a
4 ABC 4
drop table if exists t2 ;
create table t2 as select * from t1 ;
-set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) ' ;
-set @query2= 'SELECT * FROM t2 natural join t1 ' ;
-set @query3= 'SELECT * FROM t2 join t1 using(a) ' ;
-set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) ' ;
-set @query5= 'SELECT * FROM t2 natural left join t1 ' ;
-set @query6= 'SELECT * FROM t2 left join t1 using(a) ' ;
-set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) ' ;
-set @query8= 'SELECT * FROM t2 natural right join t1 ' ;
-set @query9= 'SELECT * FROM t2 right join t1 using(a) ' ;
+set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
+set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
+set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
+set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
+set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
+set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
+set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
+set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
+set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
the join statement is:
-SELECT * FROM t2 right join t1 using(a)
+SELECT * FROM t2 right join t1 using(a) order by t2.a
prepare stmt1 from @query9 ;
execute stmt1 ;
a b a b
@@ -549,7 +549,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 natural right join t1
+SELECT * FROM t2 natural right join t1 order by t2.a
prepare stmt1 from @query8 ;
execute stmt1 ;
a b a b
@@ -570,7 +570,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 right join t1 on(t1.a=t2.a)
+SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a
prepare stmt1 from @query7 ;
execute stmt1 ;
a b a b
@@ -591,7 +591,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 left join t1 using(a)
+SELECT * FROM t2 left join t1 using(a) order by t2.a
prepare stmt1 from @query6 ;
execute stmt1 ;
a b a b
@@ -612,7 +612,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 natural left join t1
+SELECT * FROM t2 natural left join t1 order by t2.a
prepare stmt1 from @query5 ;
execute stmt1 ;
a b a b
@@ -633,7 +633,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 left join t1 on(t1.a=t2.a)
+SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a
prepare stmt1 from @query4 ;
execute stmt1 ;
a b a b
@@ -654,7 +654,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 join t1 using(a)
+SELECT * FROM t2 join t1 using(a) order by t2.a
prepare stmt1 from @query3 ;
execute stmt1 ;
a b a b
@@ -675,7 +675,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 natural join t1
+SELECT * FROM t2 natural join t1 order by t2.a
prepare stmt1 from @query2 ;
execute stmt1 ;
a b
@@ -696,7 +696,7 @@ a b
3 three
4 four
the join statement is:
-SELECT * FROM t2 join t1 on (t1.a=t2.a)
+SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a
prepare stmt1 from @query1 ;
execute stmt1 ;
a b a b
@@ -770,7 +770,7 @@ execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
a ? b
2 1 two
prepare stmt1 from ' select a, b FROM t1 outer_table where
- a = (select a from t1 where b = outer_table.b ) ';
+ a = (select a from t1 where b = outer_table.b ) order by a ';
execute stmt1 ;
a b
1 one
diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result
index 8f87343c894..af5d08d5eb4 100644
--- a/mysql-test/r/ps_5merge.result
+++ b/mysql-test/r/ps_5merge.result
@@ -208,26 +208,26 @@ execute stmt1 using @arg00 ;
substr('MySQL',1,?)
MyS
set @arg00='MySQL' ;
-select a , concat(@arg00,b) from t1 ;
+select a , concat(@arg00,b) from t1 order by a;
a concat(@arg00,b)
1 MySQLone
2 MySQLtwo
3 MySQLthree
4 MySQLfour
-prepare stmt1 from ' select a , concat(?,b) from t1 ' ;
+prepare stmt1 from ' select a , concat(?,b) from t1 order by a ' ;
execute stmt1 using @arg00;
a concat(?,b)
1 MySQLone
2 MySQLtwo
3 MySQLthree
4 MySQLfour
-select a , concat(b,@arg00) from t1 ;
+select a , concat(b,@arg00) from t1 order by a ;
a concat(b,@arg00)
1 oneMySQL
2 twoMySQL
3 threeMySQL
4 fourMySQL
-prepare stmt1 from ' select a , concat(b,?) from t1 ' ;
+prepare stmt1 from ' select a , concat(b,?) from t1 order by a ' ;
execute stmt1 using @arg00;
a concat(b,?)
1 oneMySQL
@@ -277,14 +277,14 @@ create table t5 (id1 int(11) not null default '0',
value2 varchar(100), value1 varchar(100)) ;
insert into t5 values (1,'hh','hh'),(2,'hh','hh'),
(1,'ii','ii'),(2,'ii','ii') ;
-prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? ' ;
+prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? order by id1,value1 ' ;
set @arg00=1 ;
set @arg01='hh' ;
execute stmt1 using @arg00, @arg01 ;
id1 value1
1 hh
-2 hh
1 ii
+2 hh
drop table t5 ;
drop table if exists t5 ;
create table t5(session_id char(9) not null) ;
@@ -350,11 +350,11 @@ execute stmt1 using @arg00 ;
a
set @arg00=2 ;
set @arg01=3 ;
-select a FROM t1 where a in (@arg00,@arg01);
+select a FROM t1 where a in (@arg00,@arg01) order by a;
a
2
3
-prepare stmt1 from ' select a FROM t1 where a in (?,?) ';
+prepare stmt1 from ' select a FROM t1 where a in (?,?) order by a ';
execute stmt1 using @arg00, @arg01;
a
2
@@ -428,13 +428,13 @@ a b
4 four
set @arg00='two' ;
select a,b FROM t1 where a is not NULL
-AND b is not NULL having b <> @arg00 ;
+AND b is not NULL having b <> @arg00 order by a ;
a b
1 one
3 three
4 four
prepare stmt1 from ' select a,b FROM t1 where a is not NULL
-AND b is not NULL having b <> ? ' ;
+AND b is not NULL having b <> ? order by a ' ;
execute stmt1 using @arg00 ;
a b
1 one
@@ -482,7 +482,7 @@ set @arg00=0 ;
execute stmt1 using @arg00;
ERROR 42S22: Unknown column '?' in 'order clause'
set @arg00=1;
-prepare stmt1 from ' select a,b from t1
+prepare stmt1 from ' select a,b from t1 order by a
limit 1 ';
execute stmt1 ;
a b
@@ -513,7 +513,7 @@ test_sequence
------ join tests ------
select first.a as a1, second.a as a2
from t1 first, t1 second
-where first.a = second.a ;
+where first.a = second.a order by a1 ;
a1 a2
1 1
2 2
@@ -521,7 +521,7 @@ a1 a2
4 4
prepare stmt1 from ' select first.a as a1, second.a as a2
from t1 first, t1 second
- where first.a = second.a ';
+ where first.a = second.a order by a1 ';
execute stmt1 ;
a1 a2
1 1
@@ -560,17 +560,17 @@ a ? a
4 ABC 4
drop table if exists t2 ;
create table t2 as select * from t1 ;
-set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) ' ;
-set @query2= 'SELECT * FROM t2 natural join t1 ' ;
-set @query3= 'SELECT * FROM t2 join t1 using(a) ' ;
-set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) ' ;
-set @query5= 'SELECT * FROM t2 natural left join t1 ' ;
-set @query6= 'SELECT * FROM t2 left join t1 using(a) ' ;
-set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) ' ;
-set @query8= 'SELECT * FROM t2 natural right join t1 ' ;
-set @query9= 'SELECT * FROM t2 right join t1 using(a) ' ;
+set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
+set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
+set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
+set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
+set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
+set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
+set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
+set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
+set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
the join statement is:
-SELECT * FROM t2 right join t1 using(a)
+SELECT * FROM t2 right join t1 using(a) order by t2.a
prepare stmt1 from @query9 ;
execute stmt1 ;
a b a b
@@ -591,7 +591,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 natural right join t1
+SELECT * FROM t2 natural right join t1 order by t2.a
prepare stmt1 from @query8 ;
execute stmt1 ;
a b a b
@@ -612,7 +612,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 right join t1 on(t1.a=t2.a)
+SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a
prepare stmt1 from @query7 ;
execute stmt1 ;
a b a b
@@ -633,7 +633,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 left join t1 using(a)
+SELECT * FROM t2 left join t1 using(a) order by t2.a
prepare stmt1 from @query6 ;
execute stmt1 ;
a b a b
@@ -654,7 +654,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 natural left join t1
+SELECT * FROM t2 natural left join t1 order by t2.a
prepare stmt1 from @query5 ;
execute stmt1 ;
a b a b
@@ -675,7 +675,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 left join t1 on(t1.a=t2.a)
+SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a
prepare stmt1 from @query4 ;
execute stmt1 ;
a b a b
@@ -696,7 +696,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 join t1 using(a)
+SELECT * FROM t2 join t1 using(a) order by t2.a
prepare stmt1 from @query3 ;
execute stmt1 ;
a b a b
@@ -717,7 +717,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 natural join t1
+SELECT * FROM t2 natural join t1 order by t2.a
prepare stmt1 from @query2 ;
execute stmt1 ;
a b
@@ -738,7 +738,7 @@ a b
3 three
4 four
the join statement is:
-SELECT * FROM t2 join t1 on (t1.a=t2.a)
+SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a
prepare stmt1 from @query1 ;
execute stmt1 ;
a b a b
@@ -812,7 +812,7 @@ execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
a ? b
2 1 two
prepare stmt1 from ' select a, b FROM t1 outer_table where
- a = (select a from t1 where b = outer_table.b ) ';
+ a = (select a from t1 where b = outer_table.b ) order by a ';
execute stmt1 ;
a b
1 one
@@ -3216,26 +3216,26 @@ execute stmt1 using @arg00 ;
substr('MySQL',1,?)
MyS
set @arg00='MySQL' ;
-select a , concat(@arg00,b) from t1 ;
+select a , concat(@arg00,b) from t1 order by a;
a concat(@arg00,b)
1 MySQLone
2 MySQLtwo
3 MySQLthree
4 MySQLfour
-prepare stmt1 from ' select a , concat(?,b) from t1 ' ;
+prepare stmt1 from ' select a , concat(?,b) from t1 order by a ' ;
execute stmt1 using @arg00;
a concat(?,b)
1 MySQLone
2 MySQLtwo
3 MySQLthree
4 MySQLfour
-select a , concat(b,@arg00) from t1 ;
+select a , concat(b,@arg00) from t1 order by a ;
a concat(b,@arg00)
1 oneMySQL
2 twoMySQL
3 threeMySQL
4 fourMySQL
-prepare stmt1 from ' select a , concat(b,?) from t1 ' ;
+prepare stmt1 from ' select a , concat(b,?) from t1 order by a ' ;
execute stmt1 using @arg00;
a concat(b,?)
1 oneMySQL
@@ -3285,14 +3285,14 @@ create table t5 (id1 int(11) not null default '0',
value2 varchar(100), value1 varchar(100)) ;
insert into t5 values (1,'hh','hh'),(2,'hh','hh'),
(1,'ii','ii'),(2,'ii','ii') ;
-prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? ' ;
+prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? order by id1,value1 ' ;
set @arg00=1 ;
set @arg01='hh' ;
execute stmt1 using @arg00, @arg01 ;
id1 value1
1 hh
-2 hh
1 ii
+2 hh
drop table t5 ;
drop table if exists t5 ;
create table t5(session_id char(9) not null) ;
@@ -3358,11 +3358,11 @@ execute stmt1 using @arg00 ;
a
set @arg00=2 ;
set @arg01=3 ;
-select a FROM t1 where a in (@arg00,@arg01);
+select a FROM t1 where a in (@arg00,@arg01) order by a;
a
2
3
-prepare stmt1 from ' select a FROM t1 where a in (?,?) ';
+prepare stmt1 from ' select a FROM t1 where a in (?,?) order by a ';
execute stmt1 using @arg00, @arg01;
a
2
@@ -3436,13 +3436,13 @@ a b
4 four
set @arg00='two' ;
select a,b FROM t1 where a is not NULL
-AND b is not NULL having b <> @arg00 ;
+AND b is not NULL having b <> @arg00 order by a ;
a b
1 one
3 three
4 four
prepare stmt1 from ' select a,b FROM t1 where a is not NULL
-AND b is not NULL having b <> ? ' ;
+AND b is not NULL having b <> ? order by a ' ;
execute stmt1 using @arg00 ;
a b
1 one
@@ -3490,7 +3490,7 @@ set @arg00=0 ;
execute stmt1 using @arg00;
ERROR 42S22: Unknown column '?' in 'order clause'
set @arg00=1;
-prepare stmt1 from ' select a,b from t1
+prepare stmt1 from ' select a,b from t1 order by a
limit 1 ';
execute stmt1 ;
a b
@@ -3521,7 +3521,7 @@ test_sequence
------ join tests ------
select first.a as a1, second.a as a2
from t1 first, t1 second
-where first.a = second.a ;
+where first.a = second.a order by a1 ;
a1 a2
1 1
2 2
@@ -3529,7 +3529,7 @@ a1 a2
4 4
prepare stmt1 from ' select first.a as a1, second.a as a2
from t1 first, t1 second
- where first.a = second.a ';
+ where first.a = second.a order by a1 ';
execute stmt1 ;
a1 a2
1 1
@@ -3568,17 +3568,17 @@ a ? a
4 ABC 4
drop table if exists t2 ;
create table t2 as select * from t1 ;
-set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) ' ;
-set @query2= 'SELECT * FROM t2 natural join t1 ' ;
-set @query3= 'SELECT * FROM t2 join t1 using(a) ' ;
-set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) ' ;
-set @query5= 'SELECT * FROM t2 natural left join t1 ' ;
-set @query6= 'SELECT * FROM t2 left join t1 using(a) ' ;
-set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) ' ;
-set @query8= 'SELECT * FROM t2 natural right join t1 ' ;
-set @query9= 'SELECT * FROM t2 right join t1 using(a) ' ;
+set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
+set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
+set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
+set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
+set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
+set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
+set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
+set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
+set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
the join statement is:
-SELECT * FROM t2 right join t1 using(a)
+SELECT * FROM t2 right join t1 using(a) order by t2.a
prepare stmt1 from @query9 ;
execute stmt1 ;
a b a b
@@ -3599,7 +3599,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 natural right join t1
+SELECT * FROM t2 natural right join t1 order by t2.a
prepare stmt1 from @query8 ;
execute stmt1 ;
a b a b
@@ -3620,7 +3620,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 right join t1 on(t1.a=t2.a)
+SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a
prepare stmt1 from @query7 ;
execute stmt1 ;
a b a b
@@ -3641,7 +3641,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 left join t1 using(a)
+SELECT * FROM t2 left join t1 using(a) order by t2.a
prepare stmt1 from @query6 ;
execute stmt1 ;
a b a b
@@ -3662,7 +3662,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 natural left join t1
+SELECT * FROM t2 natural left join t1 order by t2.a
prepare stmt1 from @query5 ;
execute stmt1 ;
a b a b
@@ -3683,7 +3683,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 left join t1 on(t1.a=t2.a)
+SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a
prepare stmt1 from @query4 ;
execute stmt1 ;
a b a b
@@ -3704,7 +3704,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 join t1 using(a)
+SELECT * FROM t2 join t1 using(a) order by t2.a
prepare stmt1 from @query3 ;
execute stmt1 ;
a b a b
@@ -3725,7 +3725,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 natural join t1
+SELECT * FROM t2 natural join t1 order by t2.a
prepare stmt1 from @query2 ;
execute stmt1 ;
a b
@@ -3746,7 +3746,7 @@ a b
3 three
4 four
the join statement is:
-SELECT * FROM t2 join t1 on (t1.a=t2.a)
+SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a
prepare stmt1 from @query1 ;
execute stmt1 ;
a b a b
@@ -3820,7 +3820,7 @@ execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
a ? b
2 1 two
prepare stmt1 from ' select a, b FROM t1 outer_table where
- a = (select a from t1 where b = outer_table.b ) ';
+ a = (select a from t1 where b = outer_table.b ) order by a ';
execute stmt1 ;
a b
1 one
diff --git a/mysql-test/r/ps_6bdb.result b/mysql-test/r/ps_6bdb.result
index 52009dcf82a..75358bd201b 100644
--- a/mysql-test/r/ps_6bdb.result
+++ b/mysql-test/r/ps_6bdb.result
@@ -165,26 +165,26 @@ execute stmt1 using @arg00 ;
substr('MySQL',1,?)
MyS
set @arg00='MySQL' ;
-select a , concat(@arg00,b) from t1 ;
+select a , concat(@arg00,b) from t1 order by a;
a concat(@arg00,b)
1 MySQLone
2 MySQLtwo
3 MySQLthree
4 MySQLfour
-prepare stmt1 from ' select a , concat(?,b) from t1 ' ;
+prepare stmt1 from ' select a , concat(?,b) from t1 order by a ' ;
execute stmt1 using @arg00;
a concat(?,b)
1 MySQLone
2 MySQLtwo
3 MySQLthree
4 MySQLfour
-select a , concat(b,@arg00) from t1 ;
+select a , concat(b,@arg00) from t1 order by a ;
a concat(b,@arg00)
1 oneMySQL
2 twoMySQL
3 threeMySQL
4 fourMySQL
-prepare stmt1 from ' select a , concat(b,?) from t1 ' ;
+prepare stmt1 from ' select a , concat(b,?) from t1 order by a ' ;
execute stmt1 using @arg00;
a concat(b,?)
1 oneMySQL
@@ -234,14 +234,14 @@ create table t5 (id1 int(11) not null default '0',
value2 varchar(100), value1 varchar(100)) ;
insert into t5 values (1,'hh','hh'),(2,'hh','hh'),
(1,'ii','ii'),(2,'ii','ii') ;
-prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? ' ;
+prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? order by id1,value1 ' ;
set @arg00=1 ;
set @arg01='hh' ;
execute stmt1 using @arg00, @arg01 ;
id1 value1
1 hh
-2 hh
1 ii
+2 hh
drop table t5 ;
drop table if exists t5 ;
create table t5(session_id char(9) not null) ;
@@ -307,11 +307,11 @@ execute stmt1 using @arg00 ;
a
set @arg00=2 ;
set @arg01=3 ;
-select a FROM t1 where a in (@arg00,@arg01);
+select a FROM t1 where a in (@arg00,@arg01) order by a;
a
2
3
-prepare stmt1 from ' select a FROM t1 where a in (?,?) ';
+prepare stmt1 from ' select a FROM t1 where a in (?,?) order by a ';
execute stmt1 using @arg00, @arg01;
a
2
@@ -385,13 +385,13 @@ a b
4 four
set @arg00='two' ;
select a,b FROM t1 where a is not NULL
-AND b is not NULL having b <> @arg00 ;
+AND b is not NULL having b <> @arg00 order by a ;
a b
1 one
3 three
4 four
prepare stmt1 from ' select a,b FROM t1 where a is not NULL
-AND b is not NULL having b <> ? ' ;
+AND b is not NULL having b <> ? order by a ' ;
execute stmt1 using @arg00 ;
a b
1 one
@@ -439,7 +439,7 @@ set @arg00=0 ;
execute stmt1 using @arg00;
ERROR 42S22: Unknown column '?' in 'order clause'
set @arg00=1;
-prepare stmt1 from ' select a,b from t1
+prepare stmt1 from ' select a,b from t1 order by a
limit 1 ';
execute stmt1 ;
a b
@@ -470,7 +470,7 @@ test_sequence
------ join tests ------
select first.a as a1, second.a as a2
from t1 first, t1 second
-where first.a = second.a ;
+where first.a = second.a order by a1 ;
a1 a2
1 1
2 2
@@ -478,7 +478,7 @@ a1 a2
4 4
prepare stmt1 from ' select first.a as a1, second.a as a2
from t1 first, t1 second
- where first.a = second.a ';
+ where first.a = second.a order by a1 ';
execute stmt1 ;
a1 a2
1 1
@@ -517,17 +517,17 @@ a ? a
4 ABC 4
drop table if exists t2 ;
create table t2 as select * from t1 ;
-set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) ' ;
-set @query2= 'SELECT * FROM t2 natural join t1 ' ;
-set @query3= 'SELECT * FROM t2 join t1 using(a) ' ;
-set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) ' ;
-set @query5= 'SELECT * FROM t2 natural left join t1 ' ;
-set @query6= 'SELECT * FROM t2 left join t1 using(a) ' ;
-set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) ' ;
-set @query8= 'SELECT * FROM t2 natural right join t1 ' ;
-set @query9= 'SELECT * FROM t2 right join t1 using(a) ' ;
+set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
+set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
+set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
+set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
+set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
+set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
+set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
+set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
+set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
the join statement is:
-SELECT * FROM t2 right join t1 using(a)
+SELECT * FROM t2 right join t1 using(a) order by t2.a
prepare stmt1 from @query9 ;
execute stmt1 ;
a b a b
@@ -548,7 +548,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 natural right join t1
+SELECT * FROM t2 natural right join t1 order by t2.a
prepare stmt1 from @query8 ;
execute stmt1 ;
a b a b
@@ -569,7 +569,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 right join t1 on(t1.a=t2.a)
+SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a
prepare stmt1 from @query7 ;
execute stmt1 ;
a b a b
@@ -590,7 +590,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 left join t1 using(a)
+SELECT * FROM t2 left join t1 using(a) order by t2.a
prepare stmt1 from @query6 ;
execute stmt1 ;
a b a b
@@ -611,7 +611,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 natural left join t1
+SELECT * FROM t2 natural left join t1 order by t2.a
prepare stmt1 from @query5 ;
execute stmt1 ;
a b a b
@@ -632,7 +632,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 left join t1 on(t1.a=t2.a)
+SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a
prepare stmt1 from @query4 ;
execute stmt1 ;
a b a b
@@ -653,7 +653,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 join t1 using(a)
+SELECT * FROM t2 join t1 using(a) order by t2.a
prepare stmt1 from @query3 ;
execute stmt1 ;
a b a b
@@ -674,7 +674,7 @@ a b a b
3 three 3 three
4 four 4 four
the join statement is:
-SELECT * FROM t2 natural join t1
+SELECT * FROM t2 natural join t1 order by t2.a
prepare stmt1 from @query2 ;
execute stmt1 ;
a b
@@ -695,7 +695,7 @@ a b
3 three
4 four
the join statement is:
-SELECT * FROM t2 join t1 on (t1.a=t2.a)
+SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a
prepare stmt1 from @query1 ;
execute stmt1 ;
a b a b
@@ -769,7 +769,7 @@ execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
a ? b
2 1 two
prepare stmt1 from ' select a, b FROM t1 outer_table where
- a = (select a from t1 where b = outer_table.b ) ';
+ a = (select a from t1 where b = outer_table.b ) order by a ';
execute stmt1 ;
a b
1 one
diff --git a/mysql-test/r/ps_7ndb.result b/mysql-test/r/ps_7ndb.result
index decfc08b555..e33bbd7002c 100644
--- a/mysql-test/r/ps_7ndb.result
+++ b/mysql-test/r/ps_7ndb.result
@@ -166,32 +166,32 @@ execute stmt1 using @arg00 ;
substr('MySQL',1,?)
MyS
set @arg00='MySQL' ;
-select a , concat(@arg00,b) from t1 ;
+select a , concat(@arg00,b) from t1 order by a;
a concat(@arg00,b)
+1 MySQLone
2 MySQLtwo
-4 MySQLfour
3 MySQLthree
-1 MySQLone
-prepare stmt1 from ' select a , concat(?,b) from t1 ' ;
+4 MySQLfour
+prepare stmt1 from ' select a , concat(?,b) from t1 order by a ' ;
execute stmt1 using @arg00;
a concat(?,b)
+1 MySQLone
2 MySQLtwo
-4 MySQLfour
3 MySQLthree
-1 MySQLone
-select a , concat(b,@arg00) from t1 ;
+4 MySQLfour
+select a , concat(b,@arg00) from t1 order by a ;
a concat(b,@arg00)
+1 oneMySQL
2 twoMySQL
-4 fourMySQL
3 threeMySQL
-1 oneMySQL
-prepare stmt1 from ' select a , concat(b,?) from t1 ' ;
+4 fourMySQL
+prepare stmt1 from ' select a , concat(b,?) from t1 order by a ' ;
execute stmt1 using @arg00;
a concat(b,?)
+1 oneMySQL
2 twoMySQL
-4 fourMySQL
3 threeMySQL
-1 oneMySQL
+4 fourMySQL
set @arg00='MySQL' ;
select group_concat(@arg00,b) from t1
group by 'a' ;
@@ -235,14 +235,14 @@ create table t5 (id1 int(11) not null default '0',
value2 varchar(100), value1 varchar(100)) ;
insert into t5 values (1,'hh','hh'),(2,'hh','hh'),
(1,'ii','ii'),(2,'ii','ii') ;
-prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? ' ;
+prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? order by id1,value1 ' ;
set @arg00=1 ;
set @arg01='hh' ;
execute stmt1 using @arg00, @arg01 ;
id1 value1
1 hh
-2 hh
1 ii
+2 hh
drop table t5 ;
drop table if exists t5 ;
create table t5(session_id char(9) not null) ;
@@ -308,11 +308,11 @@ execute stmt1 using @arg00 ;
a
set @arg00=2 ;
set @arg01=3 ;
-select a FROM t1 where a in (@arg00,@arg01);
+select a FROM t1 where a in (@arg00,@arg01) order by a;
a
2
3
-prepare stmt1 from ' select a FROM t1 where a in (?,?) ';
+prepare stmt1 from ' select a FROM t1 where a in (?,?) order by a ';
execute stmt1 using @arg00, @arg01;
a
2
@@ -386,18 +386,18 @@ a b
4 four
set @arg00='two' ;
select a,b FROM t1 where a is not NULL
-AND b is not NULL having b <> @arg00 ;
+AND b is not NULL having b <> @arg00 order by a ;
a b
-4 four
-3 three
1 one
+3 three
+4 four
prepare stmt1 from ' select a,b FROM t1 where a is not NULL
-AND b is not NULL having b <> ? ' ;
+AND b is not NULL having b <> ? order by a ' ;
execute stmt1 using @arg00 ;
a b
-4 four
-3 three
1 one
+3 three
+4 four
set @arg00=1 ;
select a,b FROM t1 where a is not NULL
AND b is not NULL order by a - @arg00 ;
@@ -440,11 +440,11 @@ set @arg00=0 ;
execute stmt1 using @arg00;
ERROR 42S22: Unknown column '?' in 'order clause'
set @arg00=1;
-prepare stmt1 from ' select a,b from t1
+prepare stmt1 from ' select a,b from t1 order by a
limit 1 ';
execute stmt1 ;
a b
-2 two
+1 one
prepare stmt1 from ' select a,b from t1
limit ? ';
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 2
@@ -471,21 +471,21 @@ test_sequence
------ join tests ------
select first.a as a1, second.a as a2
from t1 first, t1 second
-where first.a = second.a ;
+where first.a = second.a order by a1 ;
a1 a2
+1 1
2 2
-4 4
3 3
-1 1
+4 4
prepare stmt1 from ' select first.a as a1, second.a as a2
from t1 first, t1 second
- where first.a = second.a ';
+ where first.a = second.a order by a1 ';
execute stmt1 ;
a1 a2
+1 1
2 2
-4 4
3 3
-1 1
+4 4
set @arg00='ABC';
set @arg01='two';
set @arg02='one';
@@ -518,204 +518,204 @@ a ? a
4 ABC 4
drop table if exists t2 ;
create table t2 as select * from t1 ;
-set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) ' ;
-set @query2= 'SELECT * FROM t2 natural join t1 ' ;
-set @query3= 'SELECT * FROM t2 join t1 using(a) ' ;
-set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) ' ;
-set @query5= 'SELECT * FROM t2 natural left join t1 ' ;
-set @query6= 'SELECT * FROM t2 left join t1 using(a) ' ;
-set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) ' ;
-set @query8= 'SELECT * FROM t2 natural right join t1 ' ;
-set @query9= 'SELECT * FROM t2 right join t1 using(a) ' ;
+set @query1= 'SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a ' ;
+set @query2= 'SELECT * FROM t2 natural join t1 order by t2.a ' ;
+set @query3= 'SELECT * FROM t2 join t1 using(a) order by t2.a ' ;
+set @query4= 'SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a ' ;
+set @query5= 'SELECT * FROM t2 natural left join t1 order by t2.a ' ;
+set @query6= 'SELECT * FROM t2 left join t1 using(a) order by t2.a ' ;
+set @query7= 'SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a ' ;
+set @query8= 'SELECT * FROM t2 natural right join t1 order by t2.a ' ;
+set @query9= 'SELECT * FROM t2 right join t1 using(a) order by t2.a ' ;
the join statement is:
-SELECT * FROM t2 right join t1 using(a)
+SELECT * FROM t2 right join t1 using(a) order by t2.a
prepare stmt1 from @query9 ;
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
the join statement is:
-SELECT * FROM t2 natural right join t1
+SELECT * FROM t2 natural right join t1 order by t2.a
prepare stmt1 from @query8 ;
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
the join statement is:
-SELECT * FROM t2 right join t1 on(t1.a=t2.a)
+SELECT * FROM t2 right join t1 on(t1.a=t2.a) order by t2.a
prepare stmt1 from @query7 ;
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
the join statement is:
-SELECT * FROM t2 left join t1 using(a)
+SELECT * FROM t2 left join t1 using(a) order by t2.a
prepare stmt1 from @query6 ;
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
the join statement is:
-SELECT * FROM t2 natural left join t1
+SELECT * FROM t2 natural left join t1 order by t2.a
prepare stmt1 from @query5 ;
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
the join statement is:
-SELECT * FROM t2 left join t1 on(t1.a=t2.a)
+SELECT * FROM t2 left join t1 on(t1.a=t2.a) order by t2.a
prepare stmt1 from @query4 ;
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
the join statement is:
-SELECT * FROM t2 join t1 using(a)
+SELECT * FROM t2 join t1 using(a) order by t2.a
prepare stmt1 from @query3 ;
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
the join statement is:
-SELECT * FROM t2 natural join t1
+SELECT * FROM t2 natural join t1 order by t2.a
prepare stmt1 from @query2 ;
execute stmt1 ;
a b
+1 one
2 two
-4 four
3 three
-1 one
+4 four
execute stmt1 ;
a b
+1 one
2 two
-4 four
3 three
-1 one
+4 four
execute stmt1 ;
a b
+1 one
2 two
-4 four
3 three
-1 one
+4 four
the join statement is:
-SELECT * FROM t2 join t1 on (t1.a=t2.a)
+SELECT * FROM t2 join t1 on (t1.a=t2.a) order by t2.a
prepare stmt1 from @query1 ;
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
execute stmt1 ;
a b a b
+1 one 1 one
2 two 2 two
-4 four 4 four
3 three 3 three
-1 one 1 one
+4 four 4 four
drop table t2 ;
test_sequence
------ subquery tests ------
@@ -770,13 +770,13 @@ execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
a ? b
2 1 two
prepare stmt1 from ' select a, b FROM t1 outer_table where
- a = (select a from t1 where b = outer_table.b ) ';
+ a = (select a from t1 where b = outer_table.b ) order by a ';
execute stmt1 ;
a b
+1 one
2 two
-4 four
3 three
-1 one
+4 four
prepare stmt1 from ' SELECT a as ccc from t1 where a+1=
(SELECT 1+ccc from t1 where ccc+1=a+1 and a=1) ';
execute stmt1 ;
@@ -1188,7 +1188,7 @@ c29= 'longblob', c30= 'longtext', c31='two', c32= 'tuesday';
commit ;
prepare stmt1 from 'delete from t1 where a=2' ;
execute stmt1;
-select a,b from t1 where a=2;
+select a,b from t1 where a=2 order by b;
a b
execute stmt1;
insert into t1 values(0,NULL);
@@ -1474,7 +1474,7 @@ set @arg02=82 ;
set @arg03='8-2' ;
prepare stmt1 from 'insert into t1 values(?,?),(?,?)';
execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
-select a,b from t1 where a in (@arg00,@arg02) ;
+select a,b from t1 where a in (@arg00,@arg02) order by a ;
a b
81 8-1
82 8-2
diff --git a/mysql-test/t/bdb.test b/mysql-test/t/bdb.test
index 86214ecd5a8..069ec758ba2 100644
--- a/mysql-test/t/bdb.test
+++ b/mysql-test/t/bdb.test
@@ -909,3 +909,23 @@ insert into t1 values (@a);
select a, length(a), char_length(a) from t1;
drop table t1;
SET NAMES latin1;
+
+#
+# Bug #5832 SELECT doesn't return records in some cases
+#
+CREATE TABLE t1 (
+ id int unsigned NOT NULL auto_increment,
+ list_id smallint unsigned NOT NULL,
+ term TEXT NOT NULL,
+ PRIMARY KEY(id),
+ INDEX(list_id, term(4))
+) ENGINE=BDB CHARSET=utf8;
+INSERT INTO t1 SET list_id = 1, term = "letterc";
+INSERT INTO t1 SET list_id = 1, term = "letterb";
+INSERT INTO t1 SET list_id = 1, term = "lettera";
+INSERT INTO t1 SET list_id = 1, term = "letterd";
+SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterc");
+SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterb");
+SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera");
+SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd");
+DROP TABLE t1;
diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test
index 40709443d62..4a8eb63ed36 100644
--- a/mysql-test/t/ctype_utf8.test
+++ b/mysql-test/t/ctype_utf8.test
@@ -572,3 +572,23 @@ INSERT INTO t1 VALUES ('aaa','bbb');
SELECT length(subject) FROM t1;
SELECT length(subject) FROM t1 ORDER BY 1;
DROP TABLE t1;
+
+#
+# Bug #5832 SELECT doesn't return records in some cases
+#
+CREATE TABLE t1 (
+ id int unsigned NOT NULL auto_increment,
+ list_id smallint unsigned NOT NULL,
+ term TEXT NOT NULL,
+ PRIMARY KEY(id),
+ INDEX(list_id, term(4))
+) ENGINE=MYISAM CHARSET=utf8;
+INSERT INTO t1 SET list_id = 1, term = "letterc";
+INSERT INTO t1 SET list_id = 1, term = "letterb";
+INSERT INTO t1 SET list_id = 1, term = "lettera";
+INSERT INTO t1 SET list_id = 1, term = "letterd";
+SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterc");
+SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterb");
+SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera");
+SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd");
+DROP TABLE t1;
diff --git a/mysql-test/t/ndb_subquery.test b/mysql-test/t/ndb_subquery.test
index f1f8edfbd8f..cebc1920eaa 100644
--- a/mysql-test/t/ndb_subquery.test
+++ b/mysql-test/t/ndb_subquery.test
@@ -1,4 +1,4 @@
-#-- source include/have_ndb.inc
+-- source include/have_ndb.inc
--disable_warnings
drop table if exists t1;
@@ -18,19 +18,19 @@ insert into t2 values (1,1,1),(2,2,2),(3,3,3), (4,4,4), (5,5,5);
# Use pk
explain select * from t2 where p NOT IN (select p from t1);
-select * from t2 where p NOT IN (select p from t1);
+select * from t2 where p NOT IN (select p from t1) order by p;
# Use unique index
explain select * from t2 where p NOT IN (select u from t1);
-select * from t2 where p NOT IN (select u from t1);
+select * from t2 where p NOT IN (select u from t1) order by p;
# Use ordered index
explain select * from t2 where p NOT IN (select o from t1);
-select * from t2 where p NOT IN (select o from t1);
+select * from t2 where p NOT IN (select o from t1) order by p;
# Use scan
explain select * from t2 where p NOT IN (select p+0 from t1);
-select * from t2 where p NOT IN (select p+0 from t1);
+select * from t2 where p NOT IN (select p+0 from t1) order by p;
drop table t1;
drop table t2;
diff --git a/mysql-test/t/ps_7ndb.test b/mysql-test/t/ps_7ndb.test
index 848eac0080a..30e45f44a0e 100644
--- a/mysql-test/t/ps_7ndb.test
+++ b/mysql-test/t/ps_7ndb.test
@@ -55,7 +55,7 @@ select '------ delete tests ------' as test_sequence ;
## delete without parameter
prepare stmt1 from 'delete from t1 where a=2' ;
execute stmt1;
-select a,b from t1 where a=2;
+select a,b from t1 where a=2 order by b;
# delete with row not found
execute stmt1;
@@ -270,7 +270,7 @@ set @arg02=82 ;
set @arg03='8-2' ;
prepare stmt1 from 'insert into t1 values(?,?),(?,?)';
execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
-select a,b from t1 where a in (@arg00,@arg02) ;
+select a,b from t1 where a in (@arg00,@arg02) order by a ;
## insert with two parameter in the set part
set @arg00=9 ;
View Lorry Controller Status