Added START TRANSACTION syntax

Added ALL as parameter option for all group functions.
Make join handling uniform. This allows us to use ',', JOIN and INNER JOIN the same way.
Sort NULL last if DESC is used (ANSI SQL 99 requirement)


include/my_global.h:
  Moved LL from mysql_priv (as this is also in config-win.h)
mysql-test/r/distinct.result:
  Updated results
mysql-test/r/func_group.result:
  Updated results
mysql-test/r/innodb.result:
  Updated results
mysql-test/r/join.result:
  Updated results
mysql-test/r/order_by.result:
  Updated results
mysql-test/t/func_group.test:
  Added test for SUM(ALL ...)
mysql-test/t/innodb.test:
  Added test for START TRANSACTION
mysql-test/t/join.test:
  Test different join syntaxes
mysql-test/t/order_by.test:
  Added new test of NULL ordering.
sql/filesort.cc:
  Sort NULL last if DESC is used
sql/lex.h:
  Added OLD_PASSWORD() as synonym for PASSWORD.
sql/mysql_priv.h:
  Removed LL()
sql/opt_range.cc:
  Sort NULL last if DESC is used
sql/opt_range.h:
  Sort NULL last if DESC is used
sql/slave.cc:
  Indentation changes
sql/sql_parse.cc:
  After merge fix
sql/sql_select.cc:
  Added comment
sql/sql_yacc.yy:
  Added START TRANSACTION syntax
  Added ALL as parameter option for all group functions.
  Make join handling uniform.

9 files changed, 75 insertions, 10 deletions

diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 020d6c6534f..5f4f7cced1e 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -77,7 +77,6 @@ NULL	NULL
 10	VMT
 select id+0 as a,max(id),concat(facility) as b from t1 group by a order by b desc,a;
 a	max(id)	b
-NULL	NULL	NULL
 10	10	VMT
 9	9	SRV
 8	8	RV
@@ -90,6 +89,7 @@ NULL	NULL	NULL
 1	1	/L
 -1	-1	
 0	0	
+NULL	NULL	NULL
 select id >= 0 and id <= 5 as grp,count(*) from t1 group by grp;
 grp	count(*)
 NULL	1
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 8263df36105..b129beaac81 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -42,8 +42,8 @@ insert into t1 values (null,null,'');
 select count(distinct a),count(distinct grp) from t1;
 count(distinct a)	count(distinct grp)
 6	3
-select sum(a),count(a),avg(a),std(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1;
-sum(a)	count(a)	avg(a)	std(a)	bit_or(a)	bit_and(a)	min(a)	max(a)	min(c)	max(c)
+select sum(all a),count(all a),avg(all a),std(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1;
+sum(all a)	count(all a)	avg(all a)	std(all a)	bit_or(all a)	bit_and(all a)	min(all a)	max(all a)	min(all c)	max(all c)
 21	6	3.5000	1.7078	7	0	1	6		E
 select grp, sum(a),count(a),avg(a),std(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1 group by grp;
 grp	sum(a)	count(a)	avg(a)	std(a)	bit_or(a)	bit_and(a)	min(a)	max(a)	min(c)	max(c)
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index 9d50a6a86e9..828bd2bab83 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -290,7 +290,7 @@ select * from t1;
 id	val
 drop table t1;
 create table t1 (a integer) type=innodb;
-begin;
+start transaction;
 rename table t1 to t2;
 create table t1 (b integer) type=innodb;
 insert into t1 values (1);
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index ff608825b9c..ddea0ac1a57 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -1,4 +1,34 @@
 drop table if exists t1,t2,t3;
+CREATE TABLE t1 (S1 INT);
+CREATE TABLE t2 (S1 INT);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+SELECT * FROM t1 JOIN t2;
+S1	S1
+1	2
+SELECT * FROM t1 INNER JOIN t2;
+S1	S1
+1	2
+SELECT * from t1 JOIN t2 USING (S1);
+S1	S1
+SELECT * FROM t1 INNER JOIN t2 USING (S1);
+S1	S1
+SELECT * from t1 CROSS JOIN t2;
+S1	S1
+1	2
+SELECT * from t1 LEFT JOIN t2 USING(S1);
+S1	S1
+1	NULL
+SELECT * from t1 LEFT JOIN t2 ON(t2.S1=2);
+S1	S1
+1	2
+SELECT * from t1 RIGHT JOIN t2 USING(S1);
+S1	S1
+NULL	2
+SELECT * from t1 RIGHT JOIN t2 ON(t1.S1=1);
+S1	S1
+1	2
+drop table t1,t2;
 create table t1 (id int primary key);
 create table t2 (id int);
 insert into t1 values (75);
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 48773bfa916..9238d9eafcb 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -304,7 +304,7 @@ a	b	c
 1	NULL	b
 explain select * from t1 where a >= 1 and a < 3 and b >0 order by a desc,b desc;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	range	a	a	9	NULL	8	Using where; Using index; Using filesort
+t1	range	a	a	9	NULL	8	Using where; Using index
 explain select * from t1 where a = 2 and b >0 order by a desc,b desc;
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	range	a	a	9	NULL	5	Using where; Using index
@@ -320,7 +320,18 @@ table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	range	a	a	9	NULL	5	Using where; Using index
 explain select * from t1 where a = 2 and b < 2 order by a desc,b desc;
 table	type	possible_keys	key	key_len	ref	rows	Extra
-t1	range	a	a	9	NULL	2	Using where; Using index; Using filesort
+t1	range	a	a	9	NULL	2	Using where; Using index
+explain select * from t1 where a = 1 order by b desc;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ref	a	a	4	const	5	Using where; Using index
+select * from t1 where a = 1 order by b desc;
+a	b	c
+1	3	b
+1	1	b
+1	1	b
+1	1	NULL
+1	NULL	b
+1	NULL	NULL
 alter table t1 modify b int not null, modify c varchar(10) not null;
 explain select * from t1 order by a, b, c;
 table	type	possible_keys	key	key_len	ref	rows	Extra
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index 04a2ff68a92..1915c2172ad 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -21,7 +21,7 @@ select count(distinct a),count(distinct grp) from t1;
 insert into t1 values (null,null,'');
 select count(distinct a),count(distinct grp) from t1;
 
-select sum(a),count(a),avg(a),std(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1;
+select sum(all a),count(all a),avg(all a),std(all a),bit_or(all a),bit_and(all a),min(all a),max(all a),min(all c),max(all c) from t1;
 select grp, sum(a),count(a),avg(a),std(a),bit_or(a),bit_and(a),min(a),max(a),min(c),max(c) from t1 group by grp;
 select grp, sum(a)+count(a)+avg(a)+std(a)+bit_or(a)+bit_and(a)+min(a)+max(a)+min(c)+max(c) as sum from t1 group by grp;
 
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index eb5b0c9efd2..250b68a3845 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -167,7 +167,7 @@ drop table t1;
 #
 
 create table t1 (a integer) type=innodb;
-begin;
+start transaction;
 rename table t1 to t2;
 create table t1 (b integer) type=innodb;
 insert into t1 values (1);
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index f58281af003..70980f656ab 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -1,7 +1,30 @@
 #
+# Initialization
+drop table if exists t1,t2,t3;
+
+#
+# Test different join syntaxes
+#
+
+CREATE TABLE t1 (S1 INT);
+CREATE TABLE t2 (S1 INT);
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2);
+SELECT * FROM t1 JOIN t2;
+SELECT * FROM t1 INNER JOIN t2;
+SELECT * from t1 JOIN t2 USING (S1);
+SELECT * FROM t1 INNER JOIN t2 USING (S1);
+SELECT * from t1 CROSS JOIN t2;
+SELECT * from t1 LEFT JOIN t2 USING(S1);
+SELECT * from t1 LEFT JOIN t2 ON(t2.S1=2);
+SELECT * from t1 RIGHT JOIN t2 USING(S1);
+SELECT * from t1 RIGHT JOIN t2 ON(t1.S1=1);
+drop table t1,t2;
+
+#
 # This failed for lia Perminov
 #
-drop table if exists t1,t2,t3;
+
 
 create table t1 (id int primary key);
 create table t2 (id int);
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 0ee6f901aae..8291a475d5e 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -227,7 +227,8 @@ explain select * from t1 where a = 2 and (b is null or b > 0) order by a
 desc,b desc;
 explain select * from t1 where a = 2 and b > 0 order by a desc,b desc;
 explain select * from t1 where a = 2 and b < 2 order by a desc,b desc;
-
+explain select * from t1 where a = 1 order by b desc;
+select * from t1 where a = 1 order by b desc;
 #
 # Test things when we don't have NULL keys
 #