From 329e5f2f35fa6640ae7dd480c9a47dd42341dafc Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 2 Aug 2001 06:29:50 +0300 Subject: Fix UNION New faster list iterators Change list code to be simpler and faster Optimize count(distinct) New error messages for UNION Make create_tmp_table more general to be usable by UNION Docs/manual.texi: Changelog include/mysqld_error.h: Add new error messages needed for UNION mysql-test/r/union.result: New tests for UNION mysql-test/t/analyse.test: Add missing drop table mysql-test/t/union.test: new tests for UNION sql/Makefile.am: Change name of sql_unions.cc to sql_union.cc sql/item.cc: Use List_iterator_fast sql/item_cmpfunc.cc: Use List_iterator_fast sql/item_func.cc: Use List_iterator_fast sql/item_sum.cc: Use List_iterator_fast Optimize count(distinct) Cleanup of indentation and comments sql/item_sum.h: Optimize count(distinct) sql/key.cc: Use List_iterator_fast sql/mysql_priv.h: Add new option bits sql/opt_sum.cc: Use List_iterator_fast sql/share/Makefile.am: Add 'fix_errors' label sql/share/czech/errmsg.txt: Add new error messages needed for UNION sql/share/danish/errmsg.txt: Add new error messages needed for UNION sql/share/dutch/errmsg.txt: Add new error messages needed for UNION sql/share/english/errmsg.txt: Add new error messages needed for UNION sql/share/estonian/errmsg.txt: Add new error messages needed for UNION sql/share/french/errmsg.txt: Add new error messages needed for UNION sql/share/german/errmsg.txt: Add new error messages needed for UNION sql/share/greek/errmsg.txt: Add new error messages needed for UNION sql/share/hungarian/errmsg.txt: Add new error messages needed for UNION sql/share/italian/errmsg.txt: Add new error messages needed for UNION sql/share/japanese/errmsg.txt: Add new error messages needed for UNION sql/share/korean/errmsg.txt: Add new error messages needed for UNION sql/share/norwegian-ny/errmsg.txt: Add new error messages needed for UNION sql/share/norwegian/errmsg.txt: Add new error messages needed for UNION sql/share/polish/errmsg.txt: Add new error messages needed for UNION sql/share/portuguese/errmsg.txt: Add new error messages needed for UNION sql/share/romanian/errmsg.txt: Add new error messages needed for UNION sql/share/russian/errmsg.txt: Add new error messages needed for UNION sql/share/slovak/errmsg.txt: Add new error messages needed for UNION sql/share/spanish/errmsg.txt: Add new error messages needed for UNION sql/share/swedish/errmsg.txt: Add new error messages needed for UNION sql/sql_analyse.cc: Use List_iterator_fast sql/sql_base.cc: Use List_iterator_fast Add new argument to setup_fields sql/sql_class.cc: Use List_iterator_fast sql/sql_class.h: Create new class for UNION sql/sql_handler.cc: Use List_iterator_fast sql/sql_insert.cc: Use List_iterator_fast sql/sql_lex.h: Cleanup sql/sql_list.cc: Faster iteration of lists sql/sql_list.h: Faster iterations of lists sql/sql_load.cc: Use List_iterator_fast sql/sql_parse.cc: Fix UNION code sql/sql_select.cc: Use List_iterator_fast Make create_tmp_table more general to be usable by UNION sql/sql_select.h: Changes to speed up copy_fields() sql/sql_show.cc: Use List_iterator_fast sql/sql_table.cc: Use List_iterator_fast sql/sql_union.cc: Fix UNION code sql/sql_update.cc: Use List_iterator_fast sql/sql_yacc.yy: Fix UNION code --- mysql-test/r/union.result | 60 ++++++++++++++++++++++++++++++++++++++++++ mysql-test/r/unions_one.result | 15 ----------- mysql-test/t/analyse.test | 1 + mysql-test/t/union.test | 29 ++++++++++++++++++++ mysql-test/t/unions_one.test | 16 ----------- 5 files changed, 90 insertions(+), 31 deletions(-) create mode 100644 mysql-test/r/union.result delete mode 100644 mysql-test/r/unions_one.result create mode 100644 mysql-test/t/union.test delete mode 100644 mysql-test/t/unions_one.test (limited to 'mysql-test') diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result new file mode 100644 index 00000000000..a25ea3f4dcd --- /dev/null +++ b/mysql-test/r/union.result @@ -0,0 +1,60 @@ +a b +1 a +2 b +3 c +4 d +5 f +6 e +a b +1 a +2 b +3 c +3 c +3 c +4 d +5 f +6 e +a b +1 a +2 b +3 c +3 c +3 c +4 d +6 e +5 f +a b +1 a +2 b +3 c +3 c +3 c +4 d +5 f +6 e +7 g +0 # +0 # +1 a +2 b +3 c +3 c +3 c +4 d +5 f +6 e +7 g +a b +1 a +2 b +3 c +t1 b count(*) +t1 a 1 +t1 b 1 +t1 c 2 +t2 c 1 +t2 d 1 +t2 e 1 +t2 f 1 +table type possible_keys key key_len ref rows Extra +t2 ALL NULL NULL NULL NULL 4 diff --git a/mysql-test/r/unions_one.result b/mysql-test/r/unions_one.result deleted file mode 100644 index 66cffd9aec1..00000000000 --- a/mysql-test/r/unions_one.result +++ /dev/null @@ -1,15 +0,0 @@ -a b -1 a -2 b -3 c -4 d -5 e -6 f -a b -1 a -2 b -3 c -3 c -4 d -5 e -6 f diff --git a/mysql-test/t/analyse.test b/mysql-test/t/analyse.test index 1b5022f6e4c..3f56b3e47ce 100644 --- a/mysql-test/t/analyse.test +++ b/mysql-test/t/analyse.test @@ -2,6 +2,7 @@ # Test of procedure analyse # +drop table if exists t1,t2; create table t1 (i int, j int); insert into t1 values (1,2), (3,4), (5,6), (7,8); select * from t1 procedure analyse(); diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test new file mode 100644 index 00000000000..b831be7db28 --- /dev/null +++ b/mysql-test/t/union.test @@ -0,0 +1,29 @@ +# +# Test of unions +# + +drop table if exists t1,t2; +CREATE TABLE t1 (a int not null, b char (10) not null); +insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c'); +CREATE TABLE t2 (a int not null, b char (10) not null); +insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e'); + +select a,b from t1 union select a,b from t2; +select a,b from t1 union all select a,b from t2; +select a,b from t1 union all select a,b from t2 order by b; +select a,b from t1 union all select a,b from t2 union select 7,'g'; +select 0,'#' union select a,b from t1 union all select a,b from t2 union select 7,'gg'; +select a,b from t1 union select a,b from t1; +select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 group by b; + +explain select a,b from t1 union all select a,b from t2; + +# Test some error conditions with UNION +--error 1213 +select a,b from t1 into outfile 'skr' union select a,b from t2; +--error 1213 +select a,b from t1 order by a union select a,b from t2; +--error 1214 +select a,b from t1 union select a from t2; + +drop table t1,t2; diff --git a/mysql-test/t/unions_one.test b/mysql-test/t/unions_one.test deleted file mode 100644 index 54ade3aa453..00000000000 --- a/mysql-test/t/unions_one.test +++ /dev/null @@ -1,16 +0,0 @@ -# -# Test of unions -# - -drop table if exists t1,t2; -CREATE TABLE t1 (a int not null, b char (10) not null); -insert into t1 values(1,"a"),(2,"b"),(3,"c"); -CREATE TABLE t2 (a int not null, b char (10) not null); -insert into t2 values (3,"c"),(4,"d"),(5,"e"),(6,"f"); - - -select a,b from t1 union select a,b from t2; - -select a,b from t1 union all select a,b from t2; - -drop table t1,t2; -- cgit v1.2.1