From 452b62b81ab51e0c13c150fe0316d6c0798fde97 Mon Sep 17 00:00:00 2001
From: "konstantin@mysql.com" <>
Date: Wed, 15 Sep 2004 14:25:58 +0400
Subject: Fix the test case to make it more predictable (cause: 4.1.5 test
 failure on intelxeon3 (Solaris x86))

---
 mysql-test/include/ps_query.inc | 6 ++++--
 1 file changed, 4 insertions(+), 2 deletions(-)

(limited to 'mysql-test/include/ps_query.inc')

diff --git a/mysql-test/include/ps_query.inc b/mysql-test/include/ps_query.inc
index bc5d3eb6ed1..9c80c7a040c 100644
--- a/mysql-test/include/ps_query.inc
+++ b/mysql-test/include/ps_query.inc
@@ -294,9 +294,11 @@ set @arg00='ABC';
 set @arg01='two';
 set @arg02='one';
 select first.a, @arg00, second.a FROM t1 first, t1 second
-where @arg01 = first.b or first.a = second.a or second.b = @arg02;
+where @arg01 = first.b or first.a = second.a or second.b = @arg02
+order by second.a, first.a;
 prepare stmt1 from ' select first.a, ?, second.a FROM t1 first, t1 second
-                    where ? = first.b or first.a = second.a or second.b = ? ';
+                    where ? = first.b or first.a = second.a or second.b = ?
+                    order by second.a, first.a';
 execute stmt1 using @arg00, @arg01, @arg02;
 
 
-- 
cgit v1.2.1


From d03f447f84936ccd8687d460f23ae020df41b95e Mon Sep 17 00:00:00 2001
From: "konstantin@mysql.com" <>
Date: Sat, 25 Sep 2004 19:08:02 +0400
Subject: Results of WL#1856 "Conversion of client_test.c tests cases to
 mysqltest if possible"         - many new test cases         - more and
 improved comments       New files: t/ps_7ndb.test       test suite for NDB
 tables                  r/ps_7ndb.result     expected results                
  include/ps_conv.inc  conversion test cases + review comments and fixes.

---
 mysql-test/include/ps_query.inc | 203 ++++++++++++++++++++++++++++++++++------
 1 file changed, 174 insertions(+), 29 deletions(-)

(limited to 'mysql-test/include/ps_query.inc')

diff --git a/mysql-test/include/ps_query.inc b/mysql-test/include/ps_query.inc
index 9c80c7a040c..3ebe3c7b5a1 100644
--- a/mysql-test/include/ps_query.inc
+++ b/mysql-test/include/ps_query.inc
@@ -15,13 +15,13 @@
 #
 # Please do not modify (INSERT/UPDATE/DELETE) the content or the 
 # structure (DROP/ALTER..) of the tables
-#     't1' and 't_many_col_types'. 
+#     't1' and 't9'. 
 # Such tests should be done in include/ps_modify.inc .
 #
 # But you are encouraged to use these two tables within your SELECT statements
 # whenever possible. 
-#     t1               - very simple table
-#     t_many_col_types - table with nearly all available column types
+#     t1  - very simple table
+#     t9  - table with nearly all available column types
 #
 # The structure and the content of these tables can be found in
 #     include/ps_create.inc  CREATE TABLE ...
@@ -38,10 +38,18 @@
 
 #-------- Please be very carefull when editing behind this line  ----------#
 
+################ simple select tests ################
 --disable_query_log
 select '------ simple select tests ------' as test_sequence ;
 --enable_query_log
 
+##### many column types, but no parameter
+# heavy modified case derived from client_test.c: test_func_fields()
+prepare stmt1 from ' select * from t9 ' ;
+--enable_metadata
+execute stmt1;
+--disable_metadata
+
 ##### parameter used for keyword like SELECT (must fail)
 set @arg00='SELECT' ;
 # mysqltest gives no output for the next statement, Why ??
@@ -70,6 +78,17 @@ set @arg00=1 ;
 select b, a - @arg00 from t1 where a=1 ;
 prepare stmt1 from ' select b, a - ? from t1 where a=1 ' ;
 execute stmt1 using @arg00 ;
+# case derived from client_test.c: test_ps_null_param()
+set @arg00=null ;
+select @arg00 as my_col ;
+prepare stmt1 from ' select ? as my_col';
+execute stmt1 using @arg00 ;
+select @arg00 + 1 as my_col ;
+prepare stmt1 from ' select ? + 1 as my_col';
+execute stmt1 using @arg00 ;
+select 1 + @arg00 as my_col ;
+prepare stmt1 from ' select 1 + ? as my_col';
+execute stmt1 using @arg00 ;
 ## parameter is within a function
 # variations on 'substr'
 set @arg00='MySQL' ;
@@ -86,7 +105,7 @@ execute stmt1 using @arg00 ;
 # variations on 'concat'
 set @arg00='MySQL' ;
 select a , concat(@arg00,b) from t1 ;
-# BUG#3796
+# BUG#3796 Prepared statement, select concat(<parameter>,<column>),wrong result
 prepare stmt1 from ' select a , concat(?,b) from t1 ' ;
 execute stmt1 using @arg00;
 #
@@ -122,25 +141,25 @@ execute stmt1 using @arg02, @arg02 ;
 # case derived from client_test.c: test_ps_conj_select()
 #      for BUG#3420: select returned all rows of the table
 --disable_warnings
-drop table if exists new_tab ;
+drop table if exists t5 ;
 --enable_warnings
-create table new_tab (id1 int(11) not null default '0',
+create table t5 (id1 int(11) not null default '0',
        value2 varchar(100), value1 varchar(100)) ;
-insert into new_tab values (1,'hh','hh'),(2,'hh','hh'),
+insert into t5 values (1,'hh','hh'),(2,'hh','hh'),
                   (1,'ii','ii'),(2,'ii','ii') ;
-prepare stmt1 from ' select id1,value1 from new_tab where id1=? or value1=? ' ;
+prepare stmt1 from ' select id1,value1 from t5 where id1=? or value1=? ' ;
 set @arg00=1 ;
 set @arg01='hh' ;
 execute stmt1 using @arg00, @arg01 ;
-drop table new_tab ;
+drop table t5 ;
 # case derived from client_test.c: test_bug1180()
 #      for BUG#1180 optimized away part of WHERE clause
 --disable_warnings
-drop table if exists new_tab ;
+drop table if exists t5 ;
 --enable_warnings
-create table new_tab(session_id  char(9) not null) ;
-insert into new_tab values ('abc') ;
-prepare stmt1 from ' select * from new_tab
+create table t5(session_id  char(9) not null) ;
+insert into t5 values ('abc') ;
+prepare stmt1 from ' select * from t5
 where ?=''1111'' and session_id = ''abc'' ' ;
 set @arg00='abc' ;
 execute stmt1 using @arg00 ;
@@ -148,7 +167,7 @@ set @arg00='1111' ;
 execute stmt1 using @arg00 ;
 set @arg00='abc' ;
 execute stmt1 using @arg00 ;
-drop table new_tab ;
+drop table t5 ;
 
 
 ##### parameter used for keyword FROM (must fail)
@@ -200,6 +219,12 @@ set @arg01=3 ;
 select a FROM t1 where a in (@arg00,@arg01);
 prepare stmt1 from ' select a FROM t1 where a in (?,?) ';
 execute stmt1 using @arg00, @arg01;
+# case derived from client_test.c: test_bug1500()
+set @arg00= 'one' ;
+set @arg01= 'two' ;
+set @arg02= 'five' ;
+prepare stmt1 from ' select b FROM t1 where b in (?,?,?) ' ;
+execute stmt1 using @arg00, @arg01, @arg02 ;
 # parameter in LIKE
 prepare stmt1 from ' select b FROM t1 where b like ? ';
 set @arg00='two' ;
@@ -208,6 +233,24 @@ set @arg00='tw%' ;
 execute stmt1 using @arg00 ;
 set @arg00='%wo' ;
 execute stmt1 using @arg00 ;
+# case derived from client_test.c: test_ps_null_param():
+# second part, comparisions with NULL placeholders in prepared
+# mode
+set @arg00=null ;
+insert into t9 set c1= 0, c5 = NULL ;
+select c5 from t9 where c5 > NULL ;
+prepare stmt1 from ' select c5 from t9 where c5 > ? ';
+execute stmt1 using @arg00 ;
+select c5 from t9 where c5 < NULL ;
+prepare stmt1 from ' select c5 from t9 where c5 < ? ';
+execute stmt1 using @arg00 ;
+select c5 from t9 where c5 = NULL ;
+prepare stmt1 from ' select c5 from t9 where c5 = ? ';
+execute stmt1 using @arg00 ;
+select c5 from t9 where c5 <=> NULL ;
+prepare stmt1 from ' select c5 from t9 where c5 <=> ? ';
+execute stmt1 using @arg00 ;
+delete from t9 where c1= 0 ;
 
 ##### parameter used for operator in WHERE clause (must fail)
 set @arg00='>' ;
@@ -276,6 +319,7 @@ having sum(a) <> ? ';
 execute stmt1 using @arg00, @arg01, @arg02, @arg03;
 
 
+################ join tests ################
 --disable_query_log
 select '------ join tests ------' as test_sequence ;
 --enable_query_log
@@ -301,8 +345,39 @@ prepare stmt1 from ' select first.a, ?, second.a FROM t1 first, t1 second
                     order by second.a, first.a';
 execute stmt1 using @arg00, @arg01, @arg02;
 
+# test case derived from client_test.c: test_join()
+--disable_warnings
+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) ' ;
+let $1= 9 ;
+while ($1)
+{
+  --disable_query_log
+  eval select @query$1 as 'the join statement is:' ;
+  --enable_query_log
+  eval prepare stmt1 from @query$1 ;
+  let $2= 3 ;
+  while ($2)
+  {
+    execute stmt1 ;
+    dec $2 ;
+  }
+  dec $1 ;
+}
+drop table t2 ;
 
 
+################ subquery tests ################
 --disable_query_log
 select '------ subquery tests ------' as test_sequence ;
 --enable_query_log
@@ -350,8 +425,20 @@ execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
 # no parameter
 prepare stmt1 from ' select a, b FROM t1 outer_table where
    a = (select a from t1 where b = outer_table.b ) ';
-# also Bug#4000 (only BDB tables) ??
+# also Bug#4000 (only BDB tables)
+# Bug#4106 : ndb table, query with correlated subquery, wrong result
 execute stmt1 ;
+# test case derived from client_test.c: test_subqueries_ref
+let $1= 3 ;
+while ($1)
+{
+  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 ;
+  deallocate prepare stmt1 ;
+  dec $1 ;
+}
+
 
 ###### parameter in the outer part
 set @arg00='two' ;
@@ -360,7 +447,7 @@ select a, b FROM t1 outer_table where
    a = (select a from t1 where b = outer_table.b ) and b=@arg00 ;
 prepare stmt1 from ' select a, b FROM t1 outer_table where
    a = (select a from t1 where b = outer_table.b) and b=? ';
-# also Bug#4000 (only BDB tables) ??
+# also Bug#4000 (only BDB tables)
 execute stmt1 using @arg00;
 
 ###### parameter in the inner part
@@ -390,7 +477,7 @@ select a, @arg00, b FROM t1 outer_table where
 prepare stmt1 from ' select a, ?, b FROM t1 outer_table where
    b=? and a = (select ? from t1 where outer_table.b = ? 
                    and outer_table.a=a ) ' ;
-# also Bug#4000 (only BDB tables) ??
+# also Bug#4000 (only BDB tables)
 execute stmt1 using @arg00, @arg01, @arg02, @arg03 ;
 
 ###### subquery after from
@@ -404,43 +491,76 @@ prepare stmt1 from ' select a, ?
                     where a=? ';
 execute stmt1 using @arg00, @arg00, @arg00, @arg01 ;
 
+###### subquery  in select list
+# test case derived from client_test.c: test_create_drop
+--disable_warnings
+drop table if exists t2 ;
+--enable_warnings
+create table t2 as select * from t1;
+prepare stmt1 from ' select a in (select a from t2) from t1 ' ;
+execute stmt1 ;
+# test case derived from client_test.c: test_selecttmp()
+--disable_warnings
+drop table if exists t5, t6, t7 ;
+--enable_warnings
+create table t5 (a int , b int) ;
+create table t6 like t5 ;
+create table t7 like t5 ;
+insert into t5 values (0, 100), (1, 2), (1, 3), (2, 2), (2, 7),
+                      (2, -1), (3, 10) ;
+insert into t6 values (0, 0), (1, 1), (2, 1), (3, 1), (4, 1) ;
+insert into t7 values (3, 3), (2, 2), (1, 1) ;
+prepare stmt1 from ' select a, (select count(distinct t5.b) as sum from t5, t6
+                     where t5.a=t6.a and t6.b > 0 and t5.a <= t7.b
+                     group by t5.a order by sum limit 1) from t7 ' ;
+let $1= 3 ;
+while ($1)
+{
+  execute stmt1 ;
+  dec $1 ;
+}
+drop table t5, t6, t7 ;
+
+
 ###### heavy modified case derived from client_test.c: test_distinct()
-## no parameters
 --disable_warnings
 drop table if exists t2 ;
 --enable_warnings
-create table t2 as select * from t_many_col_types;
-#insert into t2 select * from t_many_col_types;
+create table t2 as select * from t9;
+## unusual and complex SELECT without parameters
 set @stmt= ' SELECT
    (SELECT SUM(c1 + c12 + 0.0) FROM t2 
-    where (t_many_col_types.c2 - 0e-3) = t2.c2
-    GROUP BY t_many_col_types.c15 LIMIT 1) as scalar_s,
+    where (t9.c2 - 0e-3) = t2.c2
+    GROUP BY t9.c15 LIMIT 1) as scalar_s,
    exists (select 1.0e+0 from t2 
-           where t2.c3 * 9.0000000000 = t_many_col_types.c4) as exists_s,
+           where t2.c3 * 9.0000000000 = t9.c4) as exists_s,
    c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s,
    (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s
-FROM t_many_col_types,
+FROM t9,
 (select c25 x, c32 y from t2) tt WHERE x = c25 ' ;
 --enable_metadata
 prepare stmt1 from @stmt ;
 execute stmt1 ;
 --disable_metadata
 execute stmt1 ;
+## now expand the terrible SELECT to EXPLAIN SELECT
 set @stmt= concat('explain ',@stmt);
 --enable_metadata
 prepare stmt1 from @stmt ;
 execute stmt1 ;
 --disable_metadata
+# Bug#4271 prepared explain complex select, second executes crashes the server
 execute stmt1 ;
 ## many parameters
+## replace the constants of the complex SELECT with parameters
 set @stmt= ' SELECT
-   (SELECT SUM(c1+c12+?) FROM t2 where (t_many_col_types.c2-?)=t2.c2
-    GROUP BY t_many_col_types.c15 LIMIT 1) as scalar_s,
+   (SELECT SUM(c1+c12+?) FROM t2 where (t9.c2-?)=t2.c2
+    GROUP BY t9.c15 LIMIT 1) as scalar_s,
    exists (select ? from t2 
-           where t2.c3*?=t_many_col_types.c4) as exists_s,
+           where t2.c3*?=t9.c4) as exists_s,
    c5*? in (select c6+? from t2) as in_s,
    (c7-?, c8-?) in (select c9+?, c10+? from t2) as in_row_s
-FROM t_many_col_types,
+FROM t9,
 (select c25 x, c32 y from t2) tt WHERE x =c25 ' ;
 set @arg00= 0.0 ;
 set @arg01= 0e-3 ;
@@ -459,6 +579,7 @@ execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
 --disable_metadata
 execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
                     @arg07, @arg08, @arg09 ;
+## now expand the terrible SELECT to EXPLAIN SELECT
 set @stmt= concat('explain ',@stmt);
 --enable_metadata
 prepare stmt1 from @stmt ;
@@ -470,6 +591,17 @@ execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
 drop table t2 ;
 
 
+##### test case derived from client_test.c: test_bug4079()
+--error 1242
+select 1 < (select a from t1) ;
+prepare stmt1 from ' select 1 < (select a from t1) ' ;
+--error 1242
+execute stmt1 ;
+# Bug#5066 embedded server, select after failed subquery provides wrong result
+#          (two additional records, all column values NULL)
+select 1 as my_col ;
+
+################ union tests ################
 --disable_query_log
 select '------ union tests ------' as test_sequence ;
 --enable_query_log
@@ -485,6 +617,16 @@ prepare stmt1 from ' select a FROM t1 where a=1
                      union all
                      select a FROM t1 where a=1 ';
 execute stmt1 ;
+# test case derived from client_test.c: test_bad_union()
+--error 1222
+prepare stmt1 from ' SELECT 1, 2 union SELECT 1 ' ;
+--error 1222
+prepare stmt1 from ' SELECT 1 union SELECT 1, 2 ' ;
+--error 1222
+prepare stmt1 from ' SELECT * from t1 union SELECT 1 ' ;
+--error 1222
+prepare stmt1 from ' SELECT 1 union SELECT * from t1 ' ;
+
 
 ##### everything in the first table
 # one parameter as constant in the first table
@@ -612,10 +754,13 @@ prepare stmt1 from ' select sum(a) + 200 as the_sum, ? as the_town from t1
 execute stmt1 using @Oporto, @1, @2, @Lisboa, @2, @3;
 
 
+################ explain select tests ################
 --disable_query_log
 select '------ explain select tests ------' as test_sequence ;
 --enable_query_log
-prepare stmt1 from ' select * from t_many_col_types ' ;
+--disable_metadata
+# table with many column types
+prepare stmt1 from ' explain select * from t9 ' ;
 --enable_metadata
 execute stmt1;
 --disable_metadata
-- 
cgit v1.2.1


From 07700678b0d0b32c55e49507174ad2ce7101bb79 Mon Sep 17 00:00:00 2001
From: "konstantin@mysql.com" <>
Date: Tue, 28 Sep 2004 21:44:42 +0400
Subject: Some of the recently pushed prepared statements tests were disabled
 due to failures caused by floating point conversion issues on optimized
 builds).

---
 mysql-test/include/ps_query.inc | 5 +++++
 1 file changed, 5 insertions(+)

(limited to 'mysql-test/include/ps_query.inc')

diff --git a/mysql-test/include/ps_query.inc b/mysql-test/include/ps_query.inc
index 3ebe3c7b5a1..e02d0d5bf96 100644
--- a/mysql-test/include/ps_query.inc
+++ b/mysql-test/include/ps_query.inc
@@ -540,6 +540,10 @@ FROM t9,
 (select c25 x, c32 y from t2) tt WHERE x = c25 ' ;
 --enable_metadata
 prepare stmt1 from @stmt ;
+#
+# Result log was disabled upon test case failure in the optimized build.
+#
+--disable_result_log
 execute stmt1 ;
 --disable_metadata
 execute stmt1 ;
@@ -588,6 +592,7 @@ execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
 --disable_metadata
 execute stmt1 using @arg00, @arg01, @arg02, @arg03, @arg04, @arg05, @arg06,
                     @arg07, @arg08, @arg09 ;
+--enable_result_log
 drop table t2 ;
 
 
-- 
cgit v1.2.1


From 690cdfc69778a9c9d89c7d7a17155e46236d34a6 Mon Sep 17 00:00:00 2001
From: "tomas@poseidon.ndb.mysql.com" <>
Date: Thu, 7 Oct 2004 09:27:39 +0000
Subject: added order by to give same order results on different endian and
 different sized clusters

---
 mysql-test/include/ps_query.inc | 42 ++++++++++++++++++++---------------------
 1 file changed, 21 insertions(+), 21 deletions(-)

(limited to 'mysql-test/include/ps_query.inc')

diff --git a/mysql-test/include/ps_query.inc b/mysql-test/include/ps_query.inc
index e02d0d5bf96..d1c54464b54 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
@@ -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 ;
-- 
cgit v1.2.1


From 4afccd761b2df476c06ebecfaac589cd472594ee Mon Sep 17 00:00:00 2001
From: "tomas@poseidon.ndb.mysql.com" <>
Date: Thu, 7 Oct 2004 09:51:30 +0000
Subject: more order by for ndb

---
 mysql-test/include/ps_query.inc | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'mysql-test/include/ps_query.inc')

diff --git a/mysql-test/include/ps_query.inc b/mysql-test/include/ps_query.inc
index d1c54464b54..27ab85410c8 100644
--- a/mysql-test/include/ps_query.inc
+++ b/mysql-test/include/ps_query.inc
@@ -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
-- 
cgit v1.2.1


From a257686692cd6ce59d609bc59cdeee33b0ad4ad4 Mon Sep 17 00:00:00 2001
From: "tomas@poseidon.ndb.mysql.com" <>
Date: Thu, 7 Oct 2004 12:36:37 +0000
Subject: more order by fixes

---
 mysql-test/include/ps_query.inc | 10 +++++-----
 1 file changed, 5 insertions(+), 5 deletions(-)

(limited to 'mysql-test/include/ps_query.inc')

diff --git a/mysql-test/include/ps_query.inc b/mysql-test/include/ps_query.inc
index 27ab85410c8..e807e897161 100644
--- a/mysql-test/include/ps_query.inc
+++ b/mysql-test/include/ps_query.inc
@@ -115,15 +115,15 @@ execute stmt1 using @arg00;
 
 # variations on 'group_concat'
 set @arg00='MySQL' ;
-select group_concat(@arg00,b) from t1 
+select group_concat(@arg00,b order by a) from t1 
 group by 'a' ;
-prepare stmt1 from ' select group_concat(?,b) from t1
+prepare stmt1 from ' select group_concat(?,b order by a) from t1
 group by ''a'' ' ;
 execute stmt1 using @arg00;
 #
-select group_concat(b,@arg00) from t1 
+select group_concat(b,@arg00 order by a) from t1 
 group by 'a' ;
-prepare stmt1 from ' select group_concat(b,?) from t1
+prepare stmt1 from ' select group_concat(b,? order by a) from t1
 group by ''a'' ' ;
 execute stmt1 using @arg00;
 
@@ -223,7 +223,7 @@ execute stmt1 using @arg00, @arg01;
 set @arg00= 'one' ;
 set @arg01= 'two' ;
 set @arg02= 'five' ;
-prepare stmt1 from ' select b FROM t1 where b in (?,?,?) ' ;
+prepare stmt1 from ' select b FROM t1 where b in (?,?,?) order by b ' ;
 execute stmt1 using @arg00, @arg01, @arg02 ;
 # parameter in LIKE
 prepare stmt1 from ' select b FROM t1 where b like ? ';
-- 
cgit v1.2.1


From 1b35f6b469bc413e039822a6cba9ee3e3e7c0acb Mon Sep 17 00:00:00 2001
From: "tomas@poseidon.ndb.mysql.com" <>
Date: Mon, 11 Oct 2004 20:58:48 +0000
Subject: another order by fix for ndb

---
 mysql-test/include/ps_query.inc | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'mysql-test/include/ps_query.inc')

diff --git a/mysql-test/include/ps_query.inc b/mysql-test/include/ps_query.inc
index e807e897161..e6f19c67138 100644
--- a/mysql-test/include/ps_query.inc
+++ b/mysql-test/include/ps_query.inc
@@ -45,7 +45,7 @@ select '------ simple select tests ------' as test_sequence ;
 
 ##### many column types, but no parameter
 # heavy modified case derived from client_test.c: test_func_fields()
-prepare stmt1 from ' select * from t9 ' ;
+prepare stmt1 from ' select * from t9 order by c1 ' ;
 --enable_metadata
 execute stmt1;
 --disable_metadata
-- 
cgit v1.2.1