diff options
Diffstat (limited to 'sql-bench')
41 files changed, 4251 insertions, 408 deletions
diff --git a/sql-bench/Comments/interbase b/sql-bench/Comments/interbase new file mode 100644 index 00000000000..addaf74b63f --- /dev/null +++ b/sql-bench/Comments/interbase @@ -0,0 +1,18 @@ +Running crash-me on Interbase: +I +- got opensource version of interbase 6.0.1 + (both mode, classic and superserver), +- set up DBD::InterBase from cpan, +- created database "test" and set sql_dialect for that database to 3 +- executed crash-me for both interbase's models (classic and superserver). + +There were some problems during the execution: +1) Sometimes client side got SIGSEGV , At that moment server side + writes + gds__alloc: non-positive size allocation request + to log file. + This problem has both models. I am not shure if it's interbase or + DBD:InterBase problem (though DBD::InterBase made all nesessary + tests during the installation without any problem) + +2) In "superserver" mode ibserver several times died (and ibguard restarted it) diff --git a/sql-bench/Makefile.am b/sql-bench/Makefile.am index 9ed0fc29b30..ab6be7269e3 100644 --- a/sql-bench/Makefile.am +++ b/sql-bench/Makefile.am @@ -21,18 +21,22 @@ benchdir_root= $(prefix) benchdir = $(benchdir_root)/sql-bench bench_SCRIPTS = test-ATIS test-connect test-create test-insert \ test-big-tables test-select test-wisconsin \ - test-alter-table graph-compare-results \ + test-alter-table test-transactions \ + graph-compare-results \ bench-init.pl compare-results run-all-tests \ server-cfg crash-me copy-db innotest1 innotest1a \ - innotest1b innotest2 innotest2a innotest2b + innotest1b innotest2 innotest2a innotest2b \ + bench-count-distinct CLEANFILES = $(bench_SCRIPTS) EXTRA_SCRIPTS = test-ATIS.sh test-connect.sh test-create.sh \ test-insert.sh test-big-tables.sh test-select.sh \ test-alter-table.sh test-wisconsin.sh \ + test-transactions.sh \ bench-init.pl.sh compare-results.sh server-cfg.sh \ run-all-tests.sh crash-me.sh copy-db.sh \ graph-compare-results.sh innotest1.sh innotest1a.sh \ - innotest1b.sh innotest2.sh innotest2a.sh innotest2b.sh + innotest1b.sh innotest2.sh innotest2a.sh innotest2b.sh \ + bench-count-distinct.sh EXTRA_DIST = $(EXTRA_SCRIPTS) dist-hook: diff --git a/sql-bench/Results/ATIS-mysql-Linux_2.4.16_64GB_SMP_i686 b/sql-bench/Results/ATIS-mysql-Linux_2.4.16_64GB_SMP_i686 new file mode 100644 index 00000000000..2b0610da26f --- /dev/null +++ b/sql-bench/Results/ATIS-mysql-Linux_2.4.16_64GB_SMP_i686 @@ -0,0 +1,20 @@ +Testing server 'MySQL 4.0.2 alpha' at 2002-05-20 15:34:53 + +ATIS table test + +Creating tables +Time for create_table (28): 0 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Inserting data +Time to insert (9768): 2 wallclock secs ( 0.44 usr 0.21 sys + 0.00 cusr 0.00 csys = 0.65 CPU) + +Retrieving data +Time for select_simple_join (500): 2 wallclock secs ( 0.60 usr 0.19 sys + 0.00 cusr 0.00 csys = 0.79 CPU) +Time for select_join (100): 1 wallclock secs ( 0.46 usr 0.14 sys + 0.00 cusr 0.00 csys = 0.60 CPU) +Time for select_key_prefix_join (100): 10 wallclock secs ( 3.98 usr 1.52 sys + 0.00 cusr 0.00 csys = 5.50 CPU) +Time for select_distinct (800): 9 wallclock secs ( 1.59 usr 0.55 sys + 0.00 cusr 0.00 csys = 2.14 CPU) +Time for select_group (2800): 9 wallclock secs ( 1.34 usr 0.28 sys + 0.00 cusr 0.00 csys = 1.62 CPU) + +Removing tables +Time to drop_table (28): 0 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Total time: 33 wallclock secs ( 8.41 usr 2.89 sys + 0.00 cusr 0.00 csys = 11.30 CPU) diff --git a/sql-bench/Results/ATIS-mysql-Linux_2.4.4_SMP_alpha b/sql-bench/Results/ATIS-mysql-Linux_2.4.4_SMP_alpha new file mode 100644 index 00000000000..e0192d49a3d --- /dev/null +++ b/sql-bench/Results/ATIS-mysql-Linux_2.4.4_SMP_alpha @@ -0,0 +1,20 @@ +Testing server 'MySQL 4.0.5 beta' at 2002-10-23 12:35:44 + +ATIS table test + +Creating tables +Time for create_table (28): 0 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Inserting data +Time to insert (9768): 3 wallclock secs ( 0.52 usr 0.31 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Retrieving data +Time for select_simple_join (500): 1 wallclock secs ( 0.60 usr 0.29 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_join (100): 2 wallclock secs ( 0.44 usr 0.27 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_key_prefix_join (100): 10 wallclock secs ( 3.58 usr 2.30 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_distinct (800): 10 wallclock secs ( 1.60 usr 0.81 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_group (2800): 11 wallclock secs ( 1.44 usr 0.52 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Removing tables +Time to drop_table (28): 0 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Total time: 37 wallclock secs ( 8.20 usr 4.50 sys + 0.00 cusr 0.00 csys = 0.00 CPU) diff --git a/sql-bench/Results/RUN-mysql-Linux_2.4.16_64GB_SMP_i686 b/sql-bench/Results/RUN-mysql-Linux_2.4.16_64GB_SMP_i686 new file mode 100644 index 00000000000..6885ceddcf1 --- /dev/null +++ b/sql-bench/Results/RUN-mysql-Linux_2.4.16_64GB_SMP_i686 @@ -0,0 +1,109 @@ +Benchmark DBD suite: 2.14 +Date of test: 2002-05-20 17:48:45 +Running tests on: Linux 2.4.16-64GB-SMP i686 +Arguments: +Comments: Intel Xeon 2M cache, 4x700 Mhz, 2G, key_buffer=16M, gcc 3.1 +Limits from: +Server version: MySQL 4.0.2 alpha +Optimization: None +Hardware: + +alter-table: Total time: 241 wallclock secs ( 0.17 usr 0.07 sys + 0.00 cusr 0.00 csys = 0.24 CPU) +ATIS: Total time: 33 wallclock secs ( 8.41 usr 2.89 sys + 0.00 cusr 0.00 csys = 11.30 CPU) +big-tables: Total time: 27 wallclock secs ( 9.38 usr 5.17 sys + 0.00 cusr 0.00 csys = 14.55 CPU) +connect: Total time: 156 wallclock secs (71.06 usr 31.02 sys + 0.00 cusr 0.00 csys = 102.08 CPU) +create: Total time: 90 wallclock secs ( 5.77 usr 1.91 sys + 0.00 cusr 0.00 csys = 7.68 CPU) +insert: Total time: 1730 wallclock secs (523.67 usr 128.19 sys + 0.00 cusr 0.00 csys = 651.86 CPU) +select: Total time: 1106 wallclock secs (52.54 usr 11.75 sys + 0.00 cusr 0.00 csys = 64.29 CPU) +transactions: Test skipped because the database doesn't support transactions +wisconsin: Total time: 12 wallclock secs ( 3.07 usr 1.20 sys + 0.00 cusr 0.00 csys = 4.27 CPU) + +All 9 test executed successfully + +Totals per operation: +Operation seconds usr sys cpu tests +alter_table_add 134.00 0.12 0.03 0.15 992 +alter_table_drop 102.00 0.04 0.02 0.06 496 +connect 7.00 4.50 1.62 6.12 10000 +connect+select_1_row 10.00 5.09 2.31 7.40 10000 +connect+select_simple 9.00 5.19 2.02 7.21 10000 +count 33.00 0.04 0.00 0.04 100 +count_distinct 34.00 0.24 0.05 0.29 1000 +count_distinct_2 38.00 0.25 0.07 0.32 1000 +count_distinct_big 50.00 7.15 2.75 9.90 120 +count_distinct_group 47.00 1.00 0.27 1.27 1000 +count_distinct_group_on_key 43.00 0.26 0.05 0.31 1000 +count_distinct_group_on_key_parts 47.00 0.98 0.29 1.27 1000 +count_distinct_key_prefix 28.00 0.29 0.03 0.32 1000 +count_group_on_key_parts 43.00 0.96 0.31 1.27 1000 +count_on_key 416.00 11.73 1.70 13.43 50100 +create+drop 17.00 1.49 0.50 1.99 10000 +create_MANY_tables 25.00 1.12 0.28 1.40 10000 +create_index 3.00 0.00 0.00 0.00 8 +create_key+drop 23.00 1.84 0.62 2.46 10000 +create_table 0.00 0.00 0.00 0.00 31 +delete_all_many_keys 51.00 0.02 0.00 0.02 1 +delete_big 0.00 0.00 0.00 0.00 1 +delete_big_many_keys 51.00 0.02 0.00 0.02 128 +delete_key 2.00 0.39 0.26 0.65 10000 +delete_range 11.00 0.01 0.00 0.01 12 +drop_index 2.00 0.00 0.00 0.00 8 +drop_table 0.00 0.00 0.00 0.00 28 +drop_table_when_MANY_tables 11.00 0.34 0.17 0.51 10000 +insert 78.00 14.11 7.05 21.16 350768 +insert_duplicates 16.00 2.42 2.15 4.57 100000 +insert_key 86.00 7.22 2.27 9.49 100000 +insert_many_fields 7.00 0.19 0.07 0.26 2000 +insert_select_1_key 5.00 0.00 0.00 0.00 1 +insert_select_2_keys 6.00 0.00 0.00 0.00 1 +min_max 23.00 0.02 0.00 0.02 60 +min_max_on_key 159.00 19.21 2.78 21.99 85000 +multiple_value_insert 5.00 1.29 0.00 1.29 100000 +order_by_big 40.00 21.73 8.15 29.88 10 +order_by_big_key 33.00 23.12 7.93 31.05 10 +order_by_big_key2 31.00 21.98 7.84 29.82 10 +order_by_big_key_desc 32.00 23.13 8.16 31.29 10 +order_by_big_key_diff 39.00 22.07 7.71 29.78 10 +order_by_big_key_prefix 31.00 21.76 8.21 29.97 10 +order_by_key2_diff 4.00 2.00 0.58 2.58 500 +order_by_key_prefix 3.00 0.98 0.34 1.32 500 +order_by_range 3.00 0.81 0.41 1.22 500 +outer_join 48.00 0.01 0.00 0.01 10 +outer_join_found 46.00 0.01 0.00 0.01 10 +outer_join_not_found 31.00 0.00 0.00 0.00 500 +outer_join_on_key 40.00 0.00 0.00 0.00 10 +select_1_row 22.00 9.63 4.42 14.05 100000 +select_1_row_cache 19.00 6.75 3.55 10.30 100000 +select_2_rows 24.00 9.99 4.11 14.10 100000 +select_big 31.00 21.97 8.10 30.07 80 +select_big_str 13.00 5.10 3.32 8.42 10000 +select_cache 88.00 2.21 0.38 2.59 10000 +select_cache2 89.00 2.49 0.39 2.88 10000 +select_column+column 23.00 8.62 3.65 12.27 100000 +select_diff_key 138.00 0.12 0.02 0.14 500 +select_distinct 9.00 1.59 0.55 2.14 800 +select_group 51.00 1.40 0.29 1.69 2911 +select_group_when_MANY_tables 14.00 0.98 0.34 1.32 10000 +select_join 1.00 0.46 0.14 0.60 100 +select_key 82.00 51.17 8.13 59.30 200000 +select_key2 86.00 50.00 7.89 57.89 200000 +select_key2_return_key 81.00 48.57 6.51 55.08 200000 +select_key2_return_prim 83.00 49.27 7.43 56.70 200000 +select_key_prefix 87.00 51.79 8.42 60.21 200000 +select_key_prefix_join 10.00 3.98 1.52 5.50 100 +select_key_return_key 79.00 48.93 7.01 55.94 200000 +select_many_fields 19.00 9.18 5.10 14.28 2000 +select_range 103.00 8.73 2.95 11.68 410 +select_range_key2 12.00 4.65 0.92 5.57 25010 +select_range_prefix 13.00 5.01 0.99 6.00 25010 +select_simple 15.00 8.15 2.84 10.99 100000 +select_simple_cache 14.00 8.03 3.18 11.21 100000 +select_simple_join 2.00 0.60 0.19 0.79 500 +update_big 44.00 0.00 0.00 0.00 10 +update_of_key 14.00 1.80 0.94 2.74 50000 +update_of_key_big 20.00 0.01 0.01 0.02 501 +update_of_primary_key_many_keys 25.00 0.01 0.00 0.01 256 +update_with_key 60.00 9.66 6.21 15.87 300000 +update_with_key_prefix 21.00 6.04 1.98 8.02 100000 +wisc_benchmark 4.00 2.17 0.52 2.69 114 +TOTALS 3399.00 664.19 179.00 843.19 3227247 diff --git a/sql-bench/Results/RUN-mysql-Linux_2.4.4_SMP_alpha b/sql-bench/Results/RUN-mysql-Linux_2.4.4_SMP_alpha new file mode 100644 index 00000000000..ba144b8d226 --- /dev/null +++ b/sql-bench/Results/RUN-mysql-Linux_2.4.4_SMP_alpha @@ -0,0 +1,109 @@ +Benchmark DBD suite: 2.14 +Date of test: 2002-10-23 12:35:44 +Running tests on: Linux 2.4.4-SMP alpha +Arguments: +Comments: Alpha DS20 2x500 MHz, 2G memory, key_buffer=16M, query_cache=16M; cxx 6.3 + ccc 6.2.9 +Limits from: +Server version: MySQL 4.0.5 beta +Optimization: None +Hardware: + +ATIS: Total time: 37 wallclock secs ( 8.20 usr 4.50 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +alter-table: Total time: 277 wallclock secs ( 0.33 usr 0.14 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +big-tables: Total time: 39 wallclock secs ( 8.71 usr 8.56 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +connect: Total time: 209 wallclock secs (62.48 usr 49.95 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +create: Total time: 288 wallclock secs (10.88 usr 3.48 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +insert: Total time: 2381 wallclock secs (693.26 usr 241.11 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +select: Total time: 1298 wallclock secs (66.92 usr 20.96 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +transactions: Test skipped because the database doesn't support transactions +wisconsin: Total time: 17 wallclock secs ( 3.66 usr 2.13 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +All 9 test executed successfully + +Totals per operation: +Operation seconds usr sys cpu tests +alter_table_add 154.00 0.18 0.06 0.00 992 +alter_table_drop 116.00 0.07 0.03 0.00 496 +connect 14.00 8.17 2.02 0.00 10000 +connect+select_1_row 17.00 8.80 2.79 0.00 10000 +connect+select_simple 16.00 8.86 2.57 0.00 10000 +count 50.00 0.04 0.00 0.00 100 +count_distinct 32.00 0.44 0.07 0.00 1000 +count_distinct_2 32.00 0.44 0.08 0.00 1000 +count_distinct_big 54.00 7.37 4.25 0.00 120 +count_distinct_group 50.00 1.14 0.52 0.00 1000 +count_distinct_group_on_key 38.00 0.48 0.12 0.00 1000 +count_distinct_group_on_key_parts 50.00 1.15 0.47 0.00 1000 +count_distinct_key_prefix 28.00 0.44 0.09 0.00 1000 +count_group_on_key_parts 37.00 1.02 0.48 0.00 1000 +count_on_key 453.00 16.74 3.59 0.00 50100 +create+drop 15.00 2.92 0.95 0.00 10000 +create_MANY_tables 238.00 1.84 0.51 0.00 10000 +create_index 4.00 0.00 0.00 0.00 8 +create_key+drop 19.00 4.55 0.94 0.00 10000 +create_table 0.00 0.00 0.00 0.00 31 +delete_all_many_keys 47.00 0.02 0.01 0.00 1 +delete_big 0.00 0.00 0.00 0.00 1 +delete_big_many_keys 47.00 0.02 0.01 0.00 128 +delete_key 4.00 0.88 0.55 0.00 10000 +delete_range 9.00 0.00 0.00 0.00 12 +drop_index 3.00 0.00 0.00 0.00 8 +drop_table 0.00 0.00 0.00 0.00 28 +drop_table_when_MANY_tables 10.00 0.67 0.44 0.00 10000 +insert 134.00 28.43 15.57 0.00 350768 +insert_duplicates 30.00 4.71 5.50 0.00 100000 +insert_key 98.00 13.49 3.80 0.00 100000 +insert_many_fields 14.00 0.32 0.11 0.00 2000 +insert_select_1_key 6.00 0.00 0.00 0.00 1 +insert_select_2_keys 8.00 0.00 0.00 0.00 1 +min_max 23.00 0.02 0.00 0.00 60 +min_max_on_key 188.00 27.44 5.93 0.00 85000 +multiple_value_insert 7.00 1.88 0.05 0.00 100000 +order_by_big 40.00 17.31 12.40 0.00 10 +order_by_big_key 31.00 18.84 12.61 0.00 10 +order_by_big_key2 30.00 17.35 12.38 0.00 10 +order_by_big_key_desc 32.00 19.23 12.70 0.00 10 +order_by_big_key_diff 36.00 17.33 12.44 0.00 10 +order_by_big_key_prefix 30.00 17.36 12.49 0.00 10 +order_by_key2_diff 5.00 1.67 1.04 0.00 500 +order_by_key_prefix 2.00 0.92 0.56 0.00 500 +order_by_range 5.00 0.97 0.55 0.00 500 +outer_join 67.00 0.00 0.00 0.00 10 +outer_join_found 63.00 0.01 0.00 0.00 10 +outer_join_not_found 40.00 0.01 0.00 0.00 500 +outer_join_on_key 40.00 0.01 0.00 0.00 10 +select_1_row 27.00 5.70 6.78 0.00 100000 +select_1_row_cache 22.00 3.16 5.87 0.00 100000 +select_2_rows 30.00 5.96 7.15 0.00 100000 +select_big 31.00 18.08 12.50 0.00 80 +select_big_str 20.00 8.04 6.08 0.00 10000 +select_cache 89.00 3.03 0.74 0.00 10000 +select_cache2 91.00 3.53 0.76 0.00 10000 +select_column+column 30.00 5.07 5.64 0.00 100000 +select_diff_key 163.00 0.27 0.04 0.00 500 +select_distinct 10.00 1.60 0.81 0.00 800 +select_group 106.00 1.49 0.53 0.00 2911 +select_group_when_MANY_tables 6.00 0.90 0.65 0.00 10000 +select_join 2.00 0.44 0.27 0.00 100 +select_key 142.00 77.87 17.06 0.00 200000 +select_key2 142.00 74.50 19.30 0.00 200000 +select_key2_return_key 133.00 73.16 13.81 0.00 200000 +select_key2_return_prim 132.00 70.56 13.25 0.00 200000 +select_key_prefix 141.00 73.88 18.25 0.00 200000 +select_key_prefix_join 10.00 3.58 2.30 0.00 100 +select_key_return_key 146.00 82.66 16.24 0.00 200000 +select_many_fields 25.00 8.38 8.45 0.00 2000 +select_range 242.00 8.57 4.60 0.00 410 +select_range_key2 19.00 6.12 1.72 0.00 25010 +select_range_prefix 18.00 6.28 1.70 0.00 25010 +select_simple 18.00 5.08 5.46 0.00 100000 +select_simple_cache 15.00 3.64 5.58 0.00 100000 +select_simple_join 1.00 0.60 0.29 0.00 500 +update_big 22.00 0.00 0.00 0.00 10 +update_of_key 25.00 4.24 1.90 0.00 50000 +update_of_key_big 18.00 0.04 0.03 0.00 501 +update_of_primary_key_many_keys 20.00 0.03 0.01 0.00 256 +update_with_key 116.00 21.90 14.15 0.00 300000 +update_with_key_prefix 36.00 11.11 4.60 0.00 100000 +wisc_benchmark 4.00 1.66 0.73 0.00 114 +TOTALS 4518.00 844.67 325.93 0.00 3227247 diff --git a/sql-bench/Results/alter-table-mysql-Linux_2.4.16_64GB_SMP_i686 b/sql-bench/Results/alter-table-mysql-Linux_2.4.16_64GB_SMP_i686 new file mode 100644 index 00000000000..f0deae1c1c4 --- /dev/null +++ b/sql-bench/Results/alter-table-mysql-Linux_2.4.16_64GB_SMP_i686 @@ -0,0 +1,16 @@ +Testing server 'MySQL 4.0.2 alpha' at 2002-05-20 15:30:52 + +Testing of ALTER TABLE +Testing with 1000 columns and 1000 rows in 20 steps +Insert data into the table +Time for insert (1000) 0 wallclock secs ( 0.01 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.03 CPU) + +Time for alter_table_add (992): 134 wallclock secs ( 0.12 usr 0.03 sys + 0.00 cusr 0.00 csys = 0.15 CPU) + +Time for create_index (8): 3 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Time for drop_index (8): 2 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Time for alter_table_drop (496): 102 wallclock secs ( 0.04 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.06 CPU) + +Total time: 241 wallclock secs ( 0.17 usr 0.07 sys + 0.00 cusr 0.00 csys = 0.24 CPU) diff --git a/sql-bench/Results/alter-table-mysql-Linux_2.4.4_SMP_alpha b/sql-bench/Results/alter-table-mysql-Linux_2.4.4_SMP_alpha new file mode 100644 index 00000000000..1d58effc1a5 --- /dev/null +++ b/sql-bench/Results/alter-table-mysql-Linux_2.4.4_SMP_alpha @@ -0,0 +1,16 @@ +Testing server 'MySQL 4.0.5 beta' at 2002-10-23 12:36:21 + +Testing of ALTER TABLE +Testing with 1000 columns and 1000 rows in 20 steps +Insert data into the table +Time for insert (1000) 0 wallclock secs ( 0.06 usr 0.05 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Time for alter_table_add (992): 154 wallclock secs ( 0.18 usr 0.06 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Time for create_index (8): 4 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Time for drop_index (8): 3 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Time for alter_table_drop (496): 116 wallclock secs ( 0.07 usr 0.03 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Total time: 277 wallclock secs ( 0.33 usr 0.14 sys + 0.00 cusr 0.00 csys = 0.00 CPU) diff --git a/sql-bench/Results/big-tables-mysql-Linux_2.4.16_64GB_SMP_i686 b/sql-bench/Results/big-tables-mysql-Linux_2.4.16_64GB_SMP_i686 new file mode 100644 index 00000000000..afc03860f18 --- /dev/null +++ b/sql-bench/Results/big-tables-mysql-Linux_2.4.16_64GB_SMP_i686 @@ -0,0 +1,19 @@ +Testing server 'MySQL 4.0.2 alpha' at 2002-05-20 15:35:26 + +Testing of some unusual tables +All tests are done 1000 times with 1000 fields + +Testing table with 1000 fields +Testing select * from table with 1 record +Time to select_many_fields(1000): 8 wallclock secs ( 4.72 usr 2.37 sys + 0.00 cusr 0.00 csys = 7.09 CPU) + +Testing select all_fields from table with 1 record +Time to select_many_fields(1000): 11 wallclock secs ( 4.46 usr 2.73 sys + 0.00 cusr 0.00 csys = 7.19 CPU) + +Testing insert VALUES() +Time to insert_many_fields(1000): 2 wallclock secs ( 0.15 usr 0.05 sys + 0.00 cusr 0.00 csys = 0.20 CPU) + +Testing insert (all_fields) VALUES() +Time to insert_many_fields(1000): 5 wallclock secs ( 0.04 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.06 CPU) + +Total time: 27 wallclock secs ( 9.38 usr 5.17 sys + 0.00 cusr 0.00 csys = 14.55 CPU) diff --git a/sql-bench/Results/big-tables-mysql-Linux_2.4.4_SMP_alpha b/sql-bench/Results/big-tables-mysql-Linux_2.4.4_SMP_alpha new file mode 100644 index 00000000000..97f66029696 --- /dev/null +++ b/sql-bench/Results/big-tables-mysql-Linux_2.4.4_SMP_alpha @@ -0,0 +1,19 @@ +Testing server 'MySQL 4.0.5 beta' at 2002-10-23 12:40:58 + +Testing of some unusual tables +All tests are done 1000 times with 1000 fields + +Testing table with 1000 fields +Testing select * from table with 1 record +Time to select_many_fields(1000): 11 wallclock secs ( 4.59 usr 4.21 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing select all_fields from table with 1 record +Time to select_many_fields(1000): 14 wallclock secs ( 3.79 usr 4.24 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing insert VALUES() +Time to insert_many_fields(1000): 5 wallclock secs ( 0.29 usr 0.06 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing insert (all_fields) VALUES() +Time to insert_many_fields(1000): 9 wallclock secs ( 0.03 usr 0.05 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Total time: 39 wallclock secs ( 8.71 usr 8.56 sys + 0.00 cusr 0.00 csys = 0.00 CPU) diff --git a/sql-bench/Results/connect-mysql-Linux_2.4.16_64GB_SMP_i686 b/sql-bench/Results/connect-mysql-Linux_2.4.16_64GB_SMP_i686 new file mode 100644 index 00000000000..d2a4f2eb3b6 --- /dev/null +++ b/sql-bench/Results/connect-mysql-Linux_2.4.16_64GB_SMP_i686 @@ -0,0 +1,35 @@ +Testing server 'MySQL 4.0.2 alpha' at 2002-05-20 15:35:53 + +Testing the speed of connecting to the server and sending of data +Connect tests are done 10000 times and other tests 100000 times + +Testing connection/disconnect +Time to connect (10000): 7 wallclock secs ( 4.50 usr 1.62 sys + 0.00 cusr 0.00 csys = 6.12 CPU) + +Test connect/simple select/disconnect +Time for connect+select_simple (10000): 9 wallclock secs ( 5.19 usr 2.02 sys + 0.00 cusr 0.00 csys = 7.21 CPU) + +Test simple select +Time for select_simple (100000): 15 wallclock secs ( 8.15 usr 2.84 sys + 0.00 cusr 0.00 csys = 10.99 CPU) + +Test simple select +Time for select_simple_cache (100000): 14 wallclock secs ( 8.03 usr 3.18 sys + 0.00 cusr 0.00 csys = 11.21 CPU) + +Testing connect/select 1 row from table/disconnect +Time to connect+select_1_row (10000): 10 wallclock secs ( 5.09 usr 2.31 sys + 0.00 cusr 0.00 csys = 7.40 CPU) + +Testing select 1 row from table +Time to select_1_row (100000): 22 wallclock secs ( 9.63 usr 4.42 sys + 0.00 cusr 0.00 csys = 14.05 CPU) + +Time to select_1_row_cache (100000): 19 wallclock secs ( 6.75 usr 3.55 sys + 0.00 cusr 0.00 csys = 10.30 CPU) + +Testing select 2 rows from table +Time to select_2_rows (100000): 24 wallclock secs ( 9.99 usr 4.11 sys + 0.00 cusr 0.00 csys = 14.10 CPU) + +Test select with aritmetic (+) +Time for select_column+column (100000): 23 wallclock secs ( 8.62 usr 3.65 sys + 0.00 cusr 0.00 csys = 12.27 CPU) + +Testing retrieval of big records (65000 bytes) +Time to select_big_str (10000): 13 wallclock secs ( 5.10 usr 3.32 sys + 0.00 cusr 0.00 csys = 8.42 CPU) + +Total time: 156 wallclock secs (71.06 usr 31.02 sys + 0.00 cusr 0.00 csys = 102.08 CPU) diff --git a/sql-bench/Results/connect-mysql-Linux_2.4.4_SMP_alpha b/sql-bench/Results/connect-mysql-Linux_2.4.4_SMP_alpha new file mode 100644 index 00000000000..a787d311a54 --- /dev/null +++ b/sql-bench/Results/connect-mysql-Linux_2.4.4_SMP_alpha @@ -0,0 +1,35 @@ +Testing server 'MySQL 4.0.5 beta' at 2002-10-23 12:41:37 + +Testing the speed of connecting to the server and sending of data +Connect tests are done 10000 times and other tests 100000 times + +Testing connection/disconnect +Time to connect (10000): 14 wallclock secs ( 8.17 usr 2.02 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Test connect/simple select/disconnect +Time for connect+select_simple (10000): 16 wallclock secs ( 8.86 usr 2.57 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Test simple select +Time for select_simple (100000): 18 wallclock secs ( 5.08 usr 5.46 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Test simple select +Time for select_simple_cache (100000): 15 wallclock secs ( 3.64 usr 5.58 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing connect/select 1 row from table/disconnect +Time to connect+select_1_row (10000): 17 wallclock secs ( 8.80 usr 2.79 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing select 1 row from table +Time to select_1_row (100000): 27 wallclock secs ( 5.70 usr 6.78 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Time to select_1_row_cache (100000): 22 wallclock secs ( 3.16 usr 5.87 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing select 2 rows from table +Time to select_2_rows (100000): 30 wallclock secs ( 5.96 usr 7.15 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Test select with aritmetic (+) +Time for select_column+column (100000): 30 wallclock secs ( 5.07 usr 5.64 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing retrieval of big records (65000 bytes) +Time to select_big_str (10000): 20 wallclock secs ( 8.04 usr 6.08 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Total time: 209 wallclock secs (62.48 usr 49.95 sys + 0.00 cusr 0.00 csys = 0.00 CPU) diff --git a/sql-bench/Results/create-mysql-Linux_2.4.16_64GB_SMP_i686 b/sql-bench/Results/create-mysql-Linux_2.4.16_64GB_SMP_i686 new file mode 100644 index 00000000000..864a4adb7e6 --- /dev/null +++ b/sql-bench/Results/create-mysql-Linux_2.4.16_64GB_SMP_i686 @@ -0,0 +1,18 @@ +Testing server 'MySQL 4.0.2 alpha' at 2002-05-20 15:38:29 + +Testing the speed of creating and droping tables +Testing with 10000 tables and 10000 loop count + +Testing create of tables +Time for create_MANY_tables (10000): 25 wallclock secs ( 1.12 usr 0.28 sys + 0.00 cusr 0.00 csys = 1.40 CPU) + +Accessing tables +Time to select_group_when_MANY_tables (10000): 14 wallclock secs ( 0.98 usr 0.34 sys + 0.00 cusr 0.00 csys = 1.32 CPU) + +Testing drop +Time for drop_table_when_MANY_tables (10000): 11 wallclock secs ( 0.34 usr 0.17 sys + 0.00 cusr 0.00 csys = 0.51 CPU) + +Testing create+drop +Time for create+drop (10000): 17 wallclock secs ( 1.49 usr 0.50 sys + 0.00 cusr 0.00 csys = 1.99 CPU) +Time for create_key+drop (10000): 23 wallclock secs ( 1.84 usr 0.62 sys + 0.00 cusr 0.00 csys = 2.46 CPU) +Total time: 90 wallclock secs ( 5.77 usr 1.91 sys + 0.00 cusr 0.00 csys = 7.68 CPU) diff --git a/sql-bench/Results/create-mysql-Linux_2.4.4_SMP_alpha b/sql-bench/Results/create-mysql-Linux_2.4.4_SMP_alpha new file mode 100644 index 00000000000..c6b2eaf9f23 --- /dev/null +++ b/sql-bench/Results/create-mysql-Linux_2.4.4_SMP_alpha @@ -0,0 +1,18 @@ +Testing server 'MySQL 4.0.5 beta' at 2002-10-23 12:45:06 + +Testing the speed of creating and droping tables +Testing with 10000 tables and 10000 loop count + +Testing create of tables +Time for create_MANY_tables (10000): 238 wallclock secs ( 1.84 usr 0.51 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Accessing tables +Time to select_group_when_MANY_tables (10000): 6 wallclock secs ( 0.90 usr 0.65 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing drop +Time for drop_table_when_MANY_tables (10000): 10 wallclock secs ( 0.67 usr 0.44 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing create+drop +Time for create+drop (10000): 15 wallclock secs ( 2.92 usr 0.95 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for create_key+drop (10000): 19 wallclock secs ( 4.55 usr 0.94 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Total time: 288 wallclock secs (10.88 usr 3.48 sys + 0.00 cusr 0.00 csys = 0.00 CPU) diff --git a/sql-bench/Results/insert-mysql-Linux_2.4.16_64GB_SMP_i686 b/sql-bench/Results/insert-mysql-Linux_2.4.16_64GB_SMP_i686 new file mode 100644 index 00000000000..719ed9f11b9 --- /dev/null +++ b/sql-bench/Results/insert-mysql-Linux_2.4.16_64GB_SMP_i686 @@ -0,0 +1,106 @@ +Testing server 'MySQL 4.0.2 alpha' at 2002-05-20 15:39:59 + +Testing the speed of inserting data into 1 table and do some selects on it. +The tests are done with a table that has 100000 rows. + +Generating random keys +Creating tables +Inserting 100000 rows in order +Inserting 100000 rows in reverse order +Inserting 100000 rows in random order +Time for insert (300000): 65 wallclock secs (12.32 usr 5.91 sys + 0.00 cusr 0.00 csys = 18.23 CPU) + +Testing insert of duplicates +Time for insert_duplicates (100000): 16 wallclock secs ( 2.42 usr 2.15 sys + 0.00 cusr 0.00 csys = 4.57 CPU) + +Retrieving data from the table +Time for select_big (10:3000000): 31 wallclock secs (21.84 usr 8.07 sys + 0.00 cusr 0.00 csys = 29.91 CPU) +Time for order_by_big_key (10:3000000): 33 wallclock secs (23.12 usr 7.93 sys + 0.00 cusr 0.00 csys = 31.05 CPU) +Time for order_by_big_key_desc (10:3000000): 32 wallclock secs (23.13 usr 8.16 sys + 0.00 cusr 0.00 csys = 31.29 CPU) +Time for order_by_big_key_prefix (10:3000000): 31 wallclock secs (21.76 usr 8.21 sys + 0.00 cusr 0.00 csys = 29.97 CPU) +Time for order_by_big_key2 (10:3000000): 31 wallclock secs (21.98 usr 7.84 sys + 0.00 cusr 0.00 csys = 29.82 CPU) +Time for order_by_big_key_diff (10:3000000): 39 wallclock secs (22.07 usr 7.71 sys + 0.00 cusr 0.00 csys = 29.78 CPU) +Time for order_by_big (10:3000000): 40 wallclock secs (21.73 usr 8.15 sys + 0.00 cusr 0.00 csys = 29.88 CPU) +Time for order_by_range (500:125750): 3 wallclock secs ( 0.81 usr 0.41 sys + 0.00 cusr 0.00 csys = 1.22 CPU) +Time for order_by_key_prefix (500:125750): 3 wallclock secs ( 0.98 usr 0.34 sys + 0.00 cusr 0.00 csys = 1.32 CPU) +Time for order_by_key2_diff (500:250500): 4 wallclock secs ( 2.00 usr 0.58 sys + 0.00 cusr 0.00 csys = 2.58 CPU) +Time for select_diff_key (500:1000): 138 wallclock secs ( 0.12 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.14 CPU) +Time for select_range_prefix (5010:42084): 7 wallclock secs ( 2.28 usr 0.46 sys + 0.00 cusr 0.00 csys = 2.74 CPU) +Time for select_range_key2 (5010:42084): 7 wallclock secs ( 2.16 usr 0.40 sys + 0.00 cusr 0.00 csys = 2.56 CPU) +Time for select_key_prefix (200000): 87 wallclock secs (51.79 usr 8.42 sys + 0.00 cusr 0.00 csys = 60.21 CPU) +Time for select_key (200000): 82 wallclock secs (51.17 usr 8.13 sys + 0.00 cusr 0.00 csys = 59.30 CPU) +Time for select_key_return_key (200000): 79 wallclock secs (48.93 usr 7.01 sys + 0.00 cusr 0.00 csys = 55.94 CPU) +Time for select_key2 (200000): 86 wallclock secs (50.00 usr 7.89 sys + 0.00 cusr 0.00 csys = 57.89 CPU) +Time for select_key2_return_key (200000): 81 wallclock secs (48.57 usr 6.51 sys + 0.00 cusr 0.00 csys = 55.08 CPU) +Time for select_key2_return_prim (200000): 83 wallclock secs (49.27 usr 7.43 sys + 0.00 cusr 0.00 csys = 56.70 CPU) + +Test of compares with simple ranges +Time for select_range_prefix (20000:43500): 6 wallclock secs ( 2.73 usr 0.53 sys + 0.00 cusr 0.00 csys = 3.26 CPU) +Time for select_range_key2 (20000:43500): 5 wallclock secs ( 2.49 usr 0.52 sys + 0.00 cusr 0.00 csys = 3.01 CPU) +Time for select_group (111): 42 wallclock secs ( 0.06 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.07 CPU) +Time for min_max_on_key (15000): 6 wallclock secs ( 3.53 usr 0.54 sys + 0.00 cusr 0.00 csys = 4.07 CPU) +Time for min_max (60): 23 wallclock secs ( 0.02 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.02 CPU) +Time for count_on_key (100): 38 wallclock secs ( 0.03 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.04 CPU) +Time for count (100): 33 wallclock secs ( 0.04 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.04 CPU) +Time for count_distinct_big (20): 38 wallclock secs ( 0.01 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.01 CPU) + +Testing update of keys with functions +Time for update_of_key (50000): 14 wallclock secs ( 1.80 usr 0.94 sys + 0.00 cusr 0.00 csys = 2.74 CPU) +Time for update_of_key_big (501): 20 wallclock secs ( 0.01 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.02 CPU) + +Testing update with key +Time for update_with_key (300000): 60 wallclock secs ( 9.66 usr 6.21 sys + 0.00 cusr 0.00 csys = 15.87 CPU) +Time for update_with_key_prefix (100000): 21 wallclock secs ( 6.04 usr 1.98 sys + 0.00 cusr 0.00 csys = 8.02 CPU) + +Testing update of all rows +Time for update_big (10): 44 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing left outer join +Time for outer_join_on_key (10:10): 40 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for outer_join (10:10): 48 wallclock secs ( 0.01 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.01 CPU) +Time for outer_join_found (10:10): 46 wallclock secs ( 0.01 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.01 CPU) +Time for outer_join_not_found (500:10): 31 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing SELECT ... WHERE id in (10 values) +Time for select_in (500:5000) 0 wallclock secs ( 0.19 usr 0.03 sys + 0.00 cusr 0.00 csys = 0.22 CPU) + +Time for select_join_in (500:5000) 1 wallclock secs ( 0.18 usr 0.02 sys + 0.00 cusr 0.00 csys = 0.20 CPU) + +Testing SELECT ... WHERE id in (100 values) +Time for select_in (500:50000) 3 wallclock secs ( 0.57 usr 0.11 sys + 0.00 cusr 0.00 csys = 0.68 CPU) + +Time for select_join_in (500:50000) 1 wallclock secs ( 0.51 usr 0.19 sys + 0.00 cusr 0.00 csys = 0.70 CPU) + +Testing SELECT ... WHERE id in (1000 values) +Time for select_in (500:500000) 26 wallclock secs ( 4.25 usr 1.40 sys + 0.00 cusr 0.00 csys = 5.65 CPU) + +Time for select_join_in (500:500000) 14 wallclock secs ( 4.09 usr 1.42 sys + 0.00 cusr 0.00 csys = 5.51 CPU) + + +Testing INSERT INTO ... SELECT +Time for insert_select_1_key (1): 5 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for insert_select_2_keys (1): 6 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for drop table(2): 0 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing delete +Time for delete_key (10000): 2 wallclock secs ( 0.39 usr 0.26 sys + 0.00 cusr 0.00 csys = 0.65 CPU) +Time for delete_range (12): 11 wallclock secs ( 0.01 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.01 CPU) + +Insert into table with 16 keys and with a primary key with 16 parts +Time for insert_key (100000): 86 wallclock secs ( 7.22 usr 2.27 sys + 0.00 cusr 0.00 csys = 9.49 CPU) + +Testing update of keys +Time for update_of_primary_key_many_keys (256): 25 wallclock secs ( 0.01 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.01 CPU) + +Deleting rows from the table +Time for delete_big_many_keys (128): 51 wallclock secs ( 0.02 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.02 CPU) + +Deleting everything from table +Time for delete_all_many_keys (1): 51 wallclock secs ( 0.02 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.02 CPU) + +Inserting 100000 rows with multiple values +Time for multiple_value_insert (100000): 5 wallclock secs ( 1.29 usr 0.00 sys + 0.00 cusr 0.00 csys = 1.29 CPU) + +Time for drop table(1): 0 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Total time: 1730 wallclock secs (523.67 usr 128.19 sys + 0.00 cusr 0.00 csys = 651.86 CPU) diff --git a/sql-bench/Results/insert-mysql-Linux_2.4.4_SMP_alpha b/sql-bench/Results/insert-mysql-Linux_2.4.4_SMP_alpha new file mode 100644 index 00000000000..0cdf5cbede8 --- /dev/null +++ b/sql-bench/Results/insert-mysql-Linux_2.4.4_SMP_alpha @@ -0,0 +1,106 @@ +Testing server 'MySQL 4.0.5 beta' at 2002-10-23 12:49:54 + +Testing the speed of inserting data into 1 table and do some selects on it. +The tests are done with a table that has 100000 rows. + +Generating random keys +Creating tables +Inserting 100000 rows in order +Inserting 100000 rows in reverse order +Inserting 100000 rows in random order +Time for insert (300000): 114 wallclock secs (25.11 usr 13.48 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing insert of duplicates +Time for insert_duplicates (100000): 30 wallclock secs ( 4.71 usr 5.50 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Retrieving data from the table +Time for select_big (10:3000000): 31 wallclock secs (17.94 usr 12.42 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for order_by_big_key (10:3000000): 31 wallclock secs (18.84 usr 12.61 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for order_by_big_key_desc (10:3000000): 32 wallclock secs (19.23 usr 12.70 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for order_by_big_key_prefix (10:3000000): 30 wallclock secs (17.36 usr 12.49 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for order_by_big_key2 (10:3000000): 30 wallclock secs (17.35 usr 12.38 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for order_by_big_key_diff (10:3000000): 36 wallclock secs (17.33 usr 12.44 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for order_by_big (10:3000000): 40 wallclock secs (17.31 usr 12.40 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for order_by_range (500:125750): 5 wallclock secs ( 0.97 usr 0.55 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for order_by_key_prefix (500:125750): 2 wallclock secs ( 0.92 usr 0.56 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for order_by_key2_diff (500:250500): 5 wallclock secs ( 1.67 usr 1.04 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_diff_key (500:1000): 163 wallclock secs ( 0.27 usr 0.04 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_range_prefix (5010:42084): 10 wallclock secs ( 2.63 usr 0.75 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_range_key2 (5010:42084): 10 wallclock secs ( 2.64 usr 0.76 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_key_prefix (200000): 141 wallclock secs (73.88 usr 18.25 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_key (200000): 142 wallclock secs (77.87 usr 17.06 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_key_return_key (200000): 146 wallclock secs (82.66 usr 16.24 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_key2 (200000): 142 wallclock secs (74.50 usr 19.30 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_key2_return_key (200000): 133 wallclock secs (73.16 usr 13.81 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_key2_return_prim (200000): 132 wallclock secs (70.56 usr 13.25 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Test of compares with simple ranges +Time for select_range_prefix (20000:43500): 8 wallclock secs ( 3.65 usr 0.95 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_range_key2 (20000:43500): 9 wallclock secs ( 3.48 usr 0.96 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_group (111): 95 wallclock secs ( 0.05 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for min_max_on_key (15000): 10 wallclock secs ( 5.77 usr 1.18 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for min_max (60): 23 wallclock secs ( 0.02 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for count_on_key (100): 41 wallclock secs ( 0.04 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for count (100): 50 wallclock secs ( 0.04 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for count_distinct_big (20): 39 wallclock secs ( 0.01 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing update of keys with functions +Time for update_of_key (50000): 25 wallclock secs ( 4.24 usr 1.90 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for update_of_key_big (501): 18 wallclock secs ( 0.04 usr 0.03 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing update with key +Time for update_with_key (300000): 116 wallclock secs (21.90 usr 14.15 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for update_with_key_prefix (100000): 36 wallclock secs (11.11 usr 4.60 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing update of all rows +Time for update_big (10): 22 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing left outer join +Time for outer_join_on_key (10:10): 40 wallclock secs ( 0.01 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for outer_join (10:10): 67 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for outer_join_found (10:10): 63 wallclock secs ( 0.01 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for outer_join_not_found (500:10): 40 wallclock secs ( 0.01 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing SELECT ... WHERE id in (10 values) +Time for select_in (500:5000) 0 wallclock secs ( 0.22 usr 0.06 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Time for select_join_in (500:5000) 1 wallclock secs ( 0.23 usr 0.06 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing SELECT ... WHERE id in (100 values) +Time for select_in (500:50000) 4 wallclock secs ( 0.56 usr 0.26 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Time for select_join_in (500:50000) 2 wallclock secs ( 0.57 usr 0.24 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing SELECT ... WHERE id in (1000 values) +Time for select_in (500:500000) 32 wallclock secs ( 3.96 usr 2.13 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Time for select_join_in (500:500000) 17 wallclock secs ( 4.02 usr 2.07 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + + +Testing INSERT INTO ... SELECT +Time for insert_select_1_key (1): 6 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for insert_select_2_keys (1): 8 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for drop table(2): 0 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing delete +Time for delete_key (10000): 4 wallclock secs ( 0.88 usr 0.55 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for delete_range (12): 9 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Insert into table with 16 keys and with a primary key with 16 parts +Time for insert_key (100000): 98 wallclock secs (13.49 usr 3.80 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing update of keys +Time for update_of_primary_key_many_keys (256): 20 wallclock secs ( 0.03 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Deleting rows from the table +Time for delete_big_many_keys (128): 47 wallclock secs ( 0.02 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Deleting everything from table +Time for delete_all_many_keys (1): 47 wallclock secs ( 0.02 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Inserting 100000 rows with multiple values +Time for multiple_value_insert (100000): 7 wallclock secs ( 1.88 usr 0.05 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Time for drop table(1): 0 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Total time: 2381 wallclock secs (693.26 usr 241.11 sys + 0.00 cusr 0.00 csys = 0.00 CPU) diff --git a/sql-bench/Results/select-mysql-Linux_2.4.16_64GB_SMP_i686 b/sql-bench/Results/select-mysql-Linux_2.4.16_64GB_SMP_i686 new file mode 100644 index 00000000000..d6943e5711a --- /dev/null +++ b/sql-bench/Results/select-mysql-Linux_2.4.16_64GB_SMP_i686 @@ -0,0 +1,30 @@ +Testing server 'MySQL 4.0.2 alpha' at 2002-05-20 16:08:50 + +Testing the speed of selecting on keys that consist of many parts +The test-table has 10000 rows and the test is done with 500 ranges. + +Creating table +Inserting 10000 rows +Time to insert (10000): 3 wallclock secs ( 0.46 usr 0.25 sys + 0.00 cusr 0.00 csys = 0.71 CPU) + +Test if the database has a query cache +Time for select_cache (10000): 88 wallclock secs ( 2.21 usr 0.38 sys + 0.00 cusr 0.00 csys = 2.59 CPU) + +Time for select_cache2 (10000): 89 wallclock secs ( 2.49 usr 0.39 sys + 0.00 cusr 0.00 csys = 2.88 CPU) + +Testing big selects on the table +Time for select_big (70:17207): 0 wallclock secs ( 0.13 usr 0.03 sys + 0.00 cusr 0.00 csys = 0.16 CPU) +Time for select_range (410:1057904): 103 wallclock secs ( 8.73 usr 2.95 sys + 0.00 cusr 0.00 csys = 11.68 CPU) +Time for min_max_on_key (70000): 153 wallclock secs (15.68 usr 2.24 sys + 0.00 cusr 0.00 csys = 17.92 CPU) +Time for count_on_key (50000): 378 wallclock secs (11.70 usr 1.69 sys + 0.00 cusr 0.00 csys = 13.39 CPU) + +Time for count_group_on_key_parts (1000:100000): 43 wallclock secs ( 0.96 usr 0.31 sys + 0.00 cusr 0.00 csys = 1.27 CPU) +Testing count(distinct) on the table +Time for count_distinct_key_prefix (1000:1000): 28 wallclock secs ( 0.29 usr 0.03 sys + 0.00 cusr 0.00 csys = 0.32 CPU) +Time for count_distinct (1000:1000): 34 wallclock secs ( 0.24 usr 0.05 sys + 0.00 cusr 0.00 csys = 0.29 CPU) +Time for count_distinct_2 (1000:1000): 38 wallclock secs ( 0.25 usr 0.07 sys + 0.00 cusr 0.00 csys = 0.32 CPU) +Time for count_distinct_group_on_key (1000:6000): 43 wallclock secs ( 0.26 usr 0.05 sys + 0.00 cusr 0.00 csys = 0.31 CPU) +Time for count_distinct_group_on_key_parts (1000:100000): 47 wallclock secs ( 0.98 usr 0.29 sys + 0.00 cusr 0.00 csys = 1.27 CPU) +Time for count_distinct_group (1000:100000): 47 wallclock secs ( 1.00 usr 0.27 sys + 0.00 cusr 0.00 csys = 1.27 CPU) +Time for count_distinct_big (100:1000000): 12 wallclock secs ( 7.14 usr 2.75 sys + 0.00 cusr 0.00 csys = 9.89 CPU) +Total time: 1106 wallclock secs (52.54 usr 11.75 sys + 0.00 cusr 0.00 csys = 64.29 CPU) diff --git a/sql-bench/Results/select-mysql-Linux_2.4.4_SMP_alpha b/sql-bench/Results/select-mysql-Linux_2.4.4_SMP_alpha new file mode 100644 index 00000000000..939f130f92e --- /dev/null +++ b/sql-bench/Results/select-mysql-Linux_2.4.4_SMP_alpha @@ -0,0 +1,30 @@ +Testing server 'MySQL 4.0.5 beta' at 2002-10-23 13:29:36 + +Testing the speed of selecting on keys that consist of many parts +The test-table has 10000 rows and the test is done with 500 ranges. + +Creating table +Inserting 10000 rows +Time to insert (10000): 4 wallclock secs ( 0.81 usr 0.38 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Test if the database has a query cache +Time for select_cache (10000): 89 wallclock secs ( 3.03 usr 0.74 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Time for select_cache2 (10000): 91 wallclock secs ( 3.53 usr 0.76 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Testing big selects on the table +Time for select_big (70:17207): 0 wallclock secs ( 0.14 usr 0.08 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for select_range (410:1057904): 242 wallclock secs ( 8.57 usr 4.60 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for min_max_on_key (70000): 178 wallclock secs (21.67 usr 4.75 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for count_on_key (50000): 412 wallclock secs (16.70 usr 3.58 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Time for count_group_on_key_parts (1000:100000): 37 wallclock secs ( 1.02 usr 0.48 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Testing count(distinct) on the table +Time for count_distinct_key_prefix (1000:1000): 28 wallclock secs ( 0.44 usr 0.09 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for count_distinct (1000:1000): 32 wallclock secs ( 0.44 usr 0.07 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for count_distinct_2 (1000:1000): 32 wallclock secs ( 0.44 usr 0.08 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for count_distinct_group_on_key (1000:6000): 38 wallclock secs ( 0.48 usr 0.12 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for count_distinct_group_on_key_parts (1000:100000): 50 wallclock secs ( 1.15 usr 0.47 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for count_distinct_group (1000:100000): 50 wallclock secs ( 1.14 usr 0.52 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time for count_distinct_big (100:1000000): 15 wallclock secs ( 7.36 usr 4.25 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Total time: 1298 wallclock secs (66.92 usr 20.96 sys + 0.00 cusr 0.00 csys = 0.00 CPU) diff --git a/sql-bench/Results/transactions-mysql-Linux_2.4.16_64GB_SMP_i686 b/sql-bench/Results/transactions-mysql-Linux_2.4.16_64GB_SMP_i686 new file mode 100644 index 00000000000..821d74a7c01 --- /dev/null +++ b/sql-bench/Results/transactions-mysql-Linux_2.4.16_64GB_SMP_i686 @@ -0,0 +1,3 @@ +Testing server 'MySQL 4.0.2 alpha' at 2002-05-20 16:27:17 + +Test skipped because the database doesn't support transactions diff --git a/sql-bench/Results/transactions-mysql-Linux_2.4.4_SMP_alpha b/sql-bench/Results/transactions-mysql-Linux_2.4.4_SMP_alpha new file mode 100644 index 00000000000..47f81534c1e --- /dev/null +++ b/sql-bench/Results/transactions-mysql-Linux_2.4.4_SMP_alpha @@ -0,0 +1,3 @@ +Testing server 'MySQL 4.0.5 beta' at 2002-10-23 13:51:14 + +Test skipped because the database doesn't support transactions diff --git a/sql-bench/Results/wisconsin-mysql-Linux_2.4.16_64GB_SMP_i686 b/sql-bench/Results/wisconsin-mysql-Linux_2.4.16_64GB_SMP_i686 new file mode 100644 index 00000000000..8b054de0a3d --- /dev/null +++ b/sql-bench/Results/wisconsin-mysql-Linux_2.4.16_64GB_SMP_i686 @@ -0,0 +1,14 @@ +Testing server 'MySQL 4.0.2 alpha' at 2002-05-20 16:27:17 + +Wisconsin benchmark test + +Time for create_table (3): 0 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Inserting data +Time to insert (31000): 8 wallclock secs ( 0.89 usr 0.68 sys + 0.00 cusr 0.00 csys = 1.57 CPU) +Time to delete_big (1): 0 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Running the actual benchmark +Time for wisc_benchmark (114): 4 wallclock secs ( 2.17 usr 0.52 sys + 0.00 cusr 0.00 csys = 2.69 CPU) + +Total time: 12 wallclock secs ( 3.07 usr 1.20 sys + 0.00 cusr 0.00 csys = 4.27 CPU) diff --git a/sql-bench/Results/wisconsin-mysql-Linux_2.4.4_SMP_alpha b/sql-bench/Results/wisconsin-mysql-Linux_2.4.4_SMP_alpha new file mode 100644 index 00000000000..445246a27c7 --- /dev/null +++ b/sql-bench/Results/wisconsin-mysql-Linux_2.4.4_SMP_alpha @@ -0,0 +1,14 @@ +Testing server 'MySQL 4.0.5 beta' at 2002-10-23 13:51:14 + +Wisconsin benchmark test + +Time for create_table (3): 0 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Inserting data +Time to insert (31000): 13 wallclock secs ( 1.99 usr 1.40 sys + 0.00 cusr 0.00 csys = 0.00 CPU) +Time to delete_big (1): 0 wallclock secs ( 0.00 usr 0.00 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Running the actual benchmark +Time for wisc_benchmark (114): 4 wallclock secs ( 1.66 usr 0.73 sys + 0.00 cusr 0.00 csys = 0.00 CPU) + +Total time: 17 wallclock secs ( 3.66 usr 2.13 sys + 0.00 cusr 0.00 csys = 0.00 CPU) diff --git a/sql-bench/as3ap.sh b/sql-bench/as3ap.sh new file mode 100644 index 00000000000..ed796249ebb --- /dev/null +++ b/sql-bench/as3ap.sh @@ -0,0 +1,636 @@ +#!@PERL@ +# Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB +# +# This library is free software; you can redistribute it and/or +# modify it under the terms of the GNU Library General Public +# License as published by the Free Software Foundation; either +# version 2 of the License, or (at your option) any later version. +# +# This library is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +# Library General Public License for more details. +# +# You should have received a copy of the GNU Library General Public +# License along with this library; if not, write to the Free +# Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, +# MA 02111-1307, USA +# +# AS3AP single-user benchmark. +# + +##################### Standard benchmark inits ############################## + +use DBI; +use Benchmark; + +chomp($pwd = `pwd`); $pwd = "." if ($pwd eq ''); +require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n"; + +$opt_loop_count=1; + +#Create tables + +$dbh = $server->connect(); + +#Create Table +$sth = $dbh->do("drop table uniques"); +$sth = $dbh->do("drop table updates"); +$sth = $dbh->do("drop table hundred"); +$sth = $dbh->do("drop table tenpct"); +$sth = $dbh->do("drop table tiny"); + +#Temporary table +$sth = $dbh->do("drop table saveupdates"); + +@fields=("col_key int not null", + "col_int int not null", + "col_signed int not null", + "col_float float not null", + "col_double float not null", + "col_decim numeric(18,2) not null", + "col_date char(20) not null", + "col_code char(10) not null", + "col_name char(20) not null", + "col_address varchar(80) not null"); + +do_many($dbh,$server->create("uniques",\@fields,[])); +do_many($dbh,$server->create("updates",\@fields,[])); +do_many($dbh,$server->create("hundred",\@fields,[])); +do_many($dbh,$server->create("tenpct",\@fields,[])); +do_many($dbh,$server->create("tiny",["col_key int not null"],[])); + +print "Start AS3AP benchmark\n\n"; + +$start_time=new Benchmark; + +print "Load DATA\n"; +#Load DATA + +@table_names=("uniques","updates","hundred","tenpct","tiny"); + +$loop_time=new Benchmark; + +if ($opt_fast && $server->{'limits'}->{'load_data_infile'}) +{ + for ($ti = 0; $ti <= $#table_names; $ti++) + { + my $table_name = $table_names[$ti]; + my $file = "$pwd/Data/AS3AP/${table_name}\.new"; + print "$table_name - $file\n" if ($opt_debug); + $row_count += $server->insert_file($table_name,$file,$dbh); + } +} +else +{ + for ($ti = 0; $ti <= $#table_names; $ti++) + { + my $table_name = $table_names[$ti]; + print "$table_name - $file\n" if ($opt_debug); + my $insert_start = "insert into $table_name values ("; + open(DATA, "$pwd/Data/AS3AP/${table_name}\.new") || die "Can't open text file: $pwd/Data/AS3AP/${table_name}\.new\n"; + while(<DATA>) + { + chomp; + next unless ( $_ =~ /\w/ ); # skip blank lines + $command = $insert_start."$_".")"; + $command =~ $server->fix_to_insert($command); + print "$command\n" if ($opt_debug); + $sth = $dbh->do($command) or die "Got error: $DBI::errstr when executing '$command'\n"; + $row_count++; + } + close(DATA); + } +} + +$end_time=new Benchmark; +print "Time for Load Data - " . "($row_count): " . +timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + + +print "Create Index\n"; + +test_command("create_idx_uniques_key_bt", + "time for create_idx_uniques_key_bt", + "create unique index uniques_key_bt on uniques (col_key)",$dbh,$opt_loop_count); + +test_command("create_idx_updates_key_bt", + "time for create_idx_updates_key_bt", + "create unique index updates_key_bt on updates (col_key)",$dbh,$opt_loop_count); + +test_command("create_idx_hundred_key_bt", + "time for create_idx_hundred_key_bt", + "create unique index hundred_key_bt on hundred (col_key)", + $dbh,$opt_loop_count); + +test_command("create_idx_tenpct_key_bt", + "time for create_idx_tenpct_key_bt", + "create unique index tenpct_key_bt on tenpct (col_key)",$dbh,$opt_loop_count); + +test_command("create_idx_tenpct_key_code_bt", + "time for create_idx_tenpct_key_code_bt", + "create index tenpct_key_code_bt on tenpct (col_key,col_code)", + $dbh,$opt_loop_count); + +test_command("create_idx_tiny_key_bt", + "time for create_idx_tiny_key_bt", + "create index tiny_key_bt on tiny (col_key)",$dbh,$opt_loop_count); + +test_command("create_idx_tenpct_int_bt", + "time for create_idx_tenpct_int_bt", + "create index tenpct_int_bt on tenpct (col_int)",$dbh,$opt_loop_count); + +test_command("create_idx_tenpct_signed_bt", + "time for create_idx_tenpct_signed_bt", + "create index tenpct_signed_bt on tenpct (col_signed)",$dbh,$opt_loop_count); + +test_command("create_idx_uniques_code_h", + "time for create_idx_uniques_code_h", + "create index uniques_code_h on uniques (col_code)",$dbh,$opt_loop_count); + +test_command("create_idx_tenpct_double_bt", + "time for create_idx_tenpct_double_bt", + "create index tenpct_double_bt on tenpct (col_double)",$dbh,$opt_loop_count); + + +test_command("create_idx_updates_decim_bt", + "time for create_idx_updates_decim_bt", + "create index updates_decim_bt on updates (col_decim)",$dbh,$opt_loop_count); + +test_command("create_idx_tenpct_float_bt", + "time for create_idx_tenpct_float_bt", + "create index tenpct_float_bt on tenpct (col_float)",$dbh,$opt_loop_count); + +test_command("create_idx_updates_int_bt", + "time for create_idx_updates_int_bt", + "create index updates_int_bt on updates (col_int)",$dbh,$opt_loop_count); + +test_command("create_idx_tenpct_decim_bt", + "time for create_idx_tenpct_decim_bt", + "create index tenpct_decim_bt on tenpct (col_decim)",$dbh,$opt_loop_count); + +test_command("create_idx_hundred_code_h", + "time for create_idx_hundred_code_h", + "create index hundred_code_h on hundred (col_code)",$dbh,$opt_loop_count); + +test_command("create_idx_tenpct_name_h", + "time for create_idx_tenpct_name_h", + "create index tenpct_name_h on tenpct (col_name)",$dbh,$opt_loop_count); + +test_command("create_idx_updates_code_h", + "time for create_idx_updates_code_h", + "create index updates_code_h on updates (col_code)",$dbh,$opt_loop_count); + +test_command("create_idx_tenpct_code_h", + "time for create_idx_tenpct_code_h", + "create index tenpct_code_h on tenpct (col_code)",$dbh,$opt_loop_count); + +test_command("create_idx_updates_double_bt", + "time for create_idx_updates_double_bt", + "create index updates_double_bt on updates (col_double)",$dbh,$opt_loop_count); + +test_command("create_idx_hundred_foreign", + "time for create_idx_hundred_foreign", + "alter table hundred add constraint fk_hundred_updates foreign key (col_signed) + references updates (col_key)",$dbh,$opt_loop_count); + +test_query("sel_1_cl", + "Time to sel_1_cl", + "select col_key, col_int, col_signed, col_code, col_double, col_name + from updates where col_key = 1000",$dbh,$opt_loop_count); + +test_query("join_3_cl", + "Time to join_3_cl", + "select uniques.col_signed, uniques.col_date, + hundred.col_signed, hundred.col_date, + tenpct.col_signed, tenpct.col_date + from uniques, hundred, tenpct + where uniques.col_key = hundred.col_key + and uniques.col_key = tenpct.col_key + and uniques.col_key = 1000",$dbh,$opt_loop_count); + +test_query("sel_100_ncl", + "Time to sel_100_ncl", + "select col_key, col_int, col_signed, col_code,col_double, col_name + from updates where col_int <= 100",$dbh,$opt_loop_count); + +test_query("table_scan", + "Time to table_scan", + "select * from uniques where col_int = 1",$dbh,$opt_loop_count); + +test_query("agg_func", + "Time for agg_func", + "select min(col_key) from hundred group by col_name",$dbh,$opt_loop_count); + +test_query("agg_scal", + "Time for agg_scal", + "select min(col_key) from uniques",$dbh,$opt_loop_count); + +test_query("sel_100_cl", + "Time for sel_100_cl", + "select col_key, col_int, col_signed, col_code, + col_double, col_name + from updates where col_key <= 100",$dbh,$opt_loop_count); + +test_query("join_3_ncl", + "Time for join_3_ncl", + "select uniques.col_signed, uniques.col_date, + hundred.col_signed, hundred.col_date, + tenpct.col_signed, tenpct.col_date + from uniques, hundred, tenpct + where uniques.col_code = hundred.col_code + and uniques.col_code = tenpct.col_code + and uniques.col_code = 'BENCHMARKS'",$dbh,$opt_loop_count); + +test_query("sel_10pct_ncl", + "Time for sel_10pct_ncl", + "select col_key, col_int, col_signed, col_code, + col_double, col_name + from tenpct + where col_name = 'THE+ASAP+BENCHMARKS+'",$dbh,$opt_loop_count); + +if ($limits->{'subqueries'}){ + test_query("agg_simple_report", + "Time for agg_simple_report", + "select avg(updates.col_decim) + from updates + where updates.col_key in + (select updates.col_key + from updates, hundred + where hundred.col_key = updates.col_key + and updates.col_decim > 980000000)",$dbh,$opt_loop_count); +}else{ + print "agg_simple_report - Failed\n\n"; +} + +test_query("agg_info_retrieval", + "Time for agg_info_retrieval", + "select count(col_key) + from tenpct + where col_name = 'THE+ASAP+BENCHMARKS' + and col_int <= 100000000 + and col_signed between 1 and 99999999 + and not (col_float between -450000000 and 450000000) + and col_double > 600000000 + and col_decim < -600000000",$dbh,$opt_loop_count); + +if ($limits->{'views'}){ + test_query("agg_create_view", + "Time for agg_create_view", + "create view + reportview(col_key,col_signed,col_date,col_decim, + col_name,col_code,col_int) as + select updates.col_key, updates.col_signed, + updates.col_date, updates.col_decim, + hundred.col_name, hundred.col_code, + hundred.col_int + from updates, hundred + where updates.col_key = hundred.col_key",$dbh,$opt_loop_count); + + test_query("agg_subtotal_report", + "Time for agg_subtotal_report", + "select avg(col_signed), min(col_signed), max(col_signed), + max(col_date), min(col_date), + count(distinct col_name), count(col_name), + col_code, col_int + from reportview + where col_decim >980000000 + group by col_code, col_int",$dbh,$opt_loop_count); + + + test_query("agg_total_report", + "Time for agg_total_report", + "select avg(col_signed), min(col_signed), max(col_signed), + max(col_date), min(col_date), + count(distinct col_name), count(col_name), + count(col_code), count(col_int) + from reportview + where col_decim >980000000",$dbh,$opt_loop_count); +}else{ + print "agg_create_view - Failed\n\n"; + print "agg_subtotal_report - Failed\n\n"; + print "agg_total_report - Failed\n\n"; +} + +#fix from here +test_query("join_2_cl", + "Time for join_2_cl", + "select uniques.col_signed, uniques.col_name, + hundred.col_signed, hundred.col_name + from uniques, hundred + where uniques.col_key = hundred.col_key + and uniques.col_key =1000" + ,$dbh,$opt_loop_count); + +test_query("join_2", + "Time for join_2", + "select uniques.col_signed, uniques.col_name, + hundred.col_signed, hundred.col_name + from uniques, hundred + where uniques.col_address = hundred.col_address + and uniques.col_address = 'SILICON VALLEY'" + ,$dbh,$opt_loop_count); + +test_query("sel_variable_select_low", + "Time for sel_variable_select_low", + "select col_key, col_int, col_signed, col_code, + col_double, col_name + from tenpct + where col_signed < -500000000" + ,$dbh,$opt_loop_count); + +test_query("sel_variable_select_high", + "Time for sel_variable_select_high", + "select col_key, col_int, col_signed, col_code, + col_double, col_name + from tenpct + where col_signed < -250000000" + ,$dbh,$opt_loop_count); + +test_query("join_4_cl", + "Time for join_4_cl", + "select uniques.col_date, hundred.col_date, + tenpct.col_date, updates.col_date + from uniques, hundred, tenpct, updates + where uniques.col_key = hundred.col_key + and uniques.col_key = tenpct.col_key + and uniques.col_key = updates.col_key + and uniques.col_key = 1000" + ,$dbh,$opt_loop_count); + +test_query("proj_100", + "Time for proj_100", + "select distinct col_address, col_signed from hundred" + ,$dbh,$opt_loop_count); + +test_query("join_4_ncl", + "Time for join_4_ncl", + "select uniques.col_date, hundred.col_date, + tenpct.col_date, updates.col_date + from uniques, hundred, tenpct, updates + where uniques.col_code = hundred.col_code + and uniques.col_code = tenpct.col_code + and uniques.col_code = updates.col_code + and uniques.col_code = 'BENCHMARKS'" + ,$dbh,$opt_loop_count); + +test_query("proj_10pct", + "Time for proj_10pct", + "select distinct col_signed from tenpct" + ,$dbh,$opt_loop_count); + +test_query("sel_1_ncl", + "Time for sel_1_ncl", + "select col_key, col_int, col_signed, col_code, + col_double, col_name + from updates where col_code = 'BENCHMARKS'" + ,$dbh,$opt_loop_count); + +test_query("join_2_ncl", + "Time for join_2_ncl", + "select uniques.col_signed, uniques.col_name, + hundred.col_signed, hundred.col_name + from uniques, hundred + where uniques.col_code = hundred.col_code + and uniques.col_code = 'BENCHMARKS'" + ,$dbh,$opt_loop_count); + +if ($limits->{'foreign_key'}){ + do_many($dbh,$server->create("integrity_temp",\@fields,[])); + + test_query("integrity_test_1", + "Time for integrity_test", + "insert into integrity_temp select * + from hundred where col_int=0",$dbh,$opt_loop_count); + + test_query("integrity_test_2", + "Time for integrity_test", + "update hundred set col_signed = '-500000000' + where col_int = 0",$dbh,$opt_loop_count); + + test_query("integrity_test_3", + "Time for integrity_test", + "update hundred set col_signed = '-500000000' + where col_int = 0",$dbh,$opt_loop_count); + + +}else{ + print "integrity_test - Failed\n\n"; +} + +push @drop_seq_command,$server->drop_index("updates","updates_int_bt"); +push @drop_seq_command,$server->drop_index("updates","updates_double_bt"); +push @drop_seq_command,$server->drop_index("updates","updates_decim_bt"); +push @drop_seq_command,$server->drop_index("updates","updates_code_h"); + +test_many_command("Drop updates keys", + "Time for drop_updates_keys", + \@drop_seq_command,$dbh,$opt_loop_count); + +do_many($dbh,$server->create("saveupdates",\@fields,[])); + +test_command("bulk_save", + "Time for bulk_save", + "insert into saveupdates select * + from updates where col_key between 5000 and 5999" + ,$dbh,$opt_loop_count); + +test_command("bulk_modify", + "Time for bulk_modify", + "update updates + set col_key = col_key - 100000 + where col_key between 5000 and 5999" + ,$dbh,$opt_loop_count); + +safe_command("upd_append_duplicate", + "Time for upd_append_duplicate", + "insert into updates + values (6000, 0, 60000, 39997.90, + 50005.00, 50005.00, + '11/10/1985', 'CONTROLLER', + 'ALICE IN WONDERLAND', + 'UNIVERSITY OF ILLINOIS AT CHICAGO')" + ,$dbh,$opt_loop_count); + +test_command("upd_remove_duplicate", + "Time for upd_remove_duplicate", + "delete from updates where col_key = 6000 and col_int = 0" + ,$dbh,$opt_loop_count); + +test_command("upd_app_t_mid", + "Time for upd_app_t_mid", + "insert into updates + values (5005, 5005, 50005, 50005.00, 50005.00, + 50005.00, '1/1/1988', 'CONTROLLER', + 'ALICE IN WONDERLAND', + 'UNIVERSITY OF ILLINOIS AT CHICAGO')" + ,$dbh,$opt_loop_count); + +test_command("upd_mod_t_mid", + "Time for upd_mod_t_mid", + "update updates set col_key = '-5000' + where col_key = 5005" + ,$dbh,$opt_loop_count); + +test_command("upd_del_t_mid", + "Time for upd_del_t_mid", + "delete from updates + where (col_key='5005') or (col_key='-5000')" + ,$dbh,$opt_loop_count); + +test_command("upd_app_t_end", + "Time for upd_app_t_end", + "delete from updates + where (col_key='5005') or (col_key='-5000')" + ,$dbh,$opt_loop_count); + +test_command("upd_mod_t_end", + "Time for upd_mod_t_end", + "update updates + set col_key = -1000 + where col_key = 1000000001" + ,$dbh,$opt_loop_count); + +test_command("upd_del_t_end", + "Time for upd_del_t_end", + "delete from updates where col_key = -1000" + ,$dbh,$opt_loop_count); + +test_command("create_idx_updates_code_h", + "time for create_idx_updates_code_h", + "create index updates_code_h on updates (col_code)", + $dbh,$opt_loop_count); + +test_command("upd_app_t_mid", + "Time for upd_app_t_mid", + "insert into updates + values (5005, 5005, 50005, 50005.00, 50005.00, + 50005.00, '1/1/1988', 'CONTROLLER', + 'ALICE IN WONDERLAND', + 'UNIVERSITY OF ILLINOIS AT CHICAGO')" + ,$dbh,$opt_loop_count); + +test_command("upd_mod_t_cod", + "Time for upd_mod_t_cod", + "update updates + set col_code = 'SQL+GROUPS' + where col_key = 5005" + ,$dbh,$opt_loop_count); + +test_command("upd_del_t_mid", + "Time for upd_del_t_mid", + "delete from updates + where (col_key='5005') or (col_key='-5000')" + ,$dbh,$opt_loop_count); + +test_command("create_idx_updates_int_bt", + "time for create_idx_updates_int_bt", + "create index updates_int_bt on updates (col_int)", + $dbh,$opt_loop_count); + +test_command("upd_app_t_mid", + "Time for upd_app_t_mid", + "insert into updates + values (5005, 5005, 50005, 50005.00, 50005.00, + 50005.00, '1/1/1988', 'CONTROLLER', + 'ALICE IN WONDERLAND', + 'UNIVERSITY OF ILLINOIS AT CHICAGO')" + ,$dbh,$opt_loop_count); + +test_command("upd_mod_t_int", + "Time for upd_mod_t_int", + "update updates set col_int = 50015 where col_key = 5005" + ,$dbh,$opt_loop_count); + +test_command("upd_del_t_mid", + "Time for upd_del_t_mid", + "delete from updates + where (col_key='5005') or (col_key='-5000')" + ,$dbh,$opt_loop_count); + +test_command("bulk_append", + "Time for bulk_append", + "insert into updates select * from saveupdates" + ,$dbh,$opt_loop_count); + +test_command("bulk_delete", + "Time for bulk_delete", + "delete from updates where col_key < 0" + ,$dbh,$opt_loop_count); + +################################ END ################################### +#### +#### End of the test...Finally print time used to execute the +#### whole test. + +$dbh->disconnect; + +end_benchmark($start_time); + +############################ HELP FUNCTIONS ############################## + +sub test_query +{ + my($test_text,$result_text,$query,$dbh,$count)=@_; + my($i,$loop_time,$end_time); + + print $test_text . "\n"; + $loop_time=new Benchmark; + for ($i=0 ; $i < $count ; $i++) + { + defined(fetch_all_rows($dbh,$query)) or warn $DBI::errstr; + } + $end_time=new Benchmark; + print $result_text . "($count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; +} + + +sub test_command +{ + my($test_text,$result_text,$query,$dbh,$count)=@_; + my($i,$loop_time,$end_time); + + print $test_text . "\n"; + $loop_time=new Benchmark; + for ($i=0 ; $i < $count ; $i++) + { + $dbh->do($query) or die $DBI::errstr; + } + $end_time=new Benchmark; + print $result_text . "($count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; +} + +sub safe_command +{ + my($test_text,$result_text,$query,$dbh,$count)=@_; + my($i,$loop_time,$end_time); + + print $test_text . "\n"; + $loop_time=new Benchmark; + for ($i=0 ; $i < $count ; $i++) + { + safe_do_many($dbh,$query); + } + $end_time=new Benchmark; + print $result_text . "($count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; +} + +sub test_many_command +{ + my($test_text,$result_text,$query,$dbh,$count)=@_; + my($i,$loop_time,$end_time); + + $loop_time=new Benchmark; + for ($i=0 ; $i < $count ; $i++) + { + safe_do_many($dbh, @$query); + } + $end_time=new Benchmark; + print $result_text . "($count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; +} + + diff --git a/sql-bench/bench-count-distinct.sh b/sql-bench/bench-count-distinct.sh new file mode 100644 index 00000000000..1359a864ac1 --- /dev/null +++ b/sql-bench/bench-count-distinct.sh @@ -0,0 +1,258 @@ +#!@PERL@ +# Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB +# +# This library is free software; you can redistribute it and/or +# modify it under the terms of the GNU Library General Public +# License as published by the Free Software Foundation; either +# version 2 of the License, or (at your option) any later version. +# +# This library is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +# Library General Public License for more details. +# +# You should have received a copy of the GNU Library General Public +# License along with this library; if not, write to the Free +# Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, +# MA 02111-1307, USA +# +# Test of selecting on keys that consist of many parts +# +##################### Standard benchmark inits ############################## + +use DBI; +use Getopt::Long; +use Benchmark; + +$opt_loop_count=10000; +$opt_medium_loop_count=200; +$opt_small_loop_count=10; +$opt_regions=6; +$opt_groups=100; + +chomp($pwd = `pwd`); $pwd = "." if ($pwd eq ''); +require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n"; + +$columns=min($limits->{'max_columns'},500,($limits->{'query_size'}-50)/24, + $limits->{'max_conditions'}/2-3); + +if ($opt_small_test) +{ + $opt_loop_count/=10; + $opt_medium_loop_count/=10; + $opt_small_loop_count/=10; + $opt_groups/=10; +} + +print "Testing the speed of selecting on keys that consist of many parts\n"; +print "The test-table has $opt_loop_count rows and the test is done with $columns ranges.\n\n"; + +#### +#### Connect and start timeing +#### + +$dbh = $server->connect(); +$start_time=new Benchmark; + +#### +#### Create needed tables +#### + +goto select_test if ($opt_skip_create); + +print "Creating table\n"; +$dbh->do("drop table bench1" . $server->{'drop_attr'}); + +do_many($dbh,$server->create("bench1", + ["region char(1) NOT NULL", + "idn integer(6) NOT NULL", + "rev_idn integer(6) NOT NULL", + "grp integer(6) NOT NULL"], + ["primary key (region,idn)", + "unique (region,rev_idn)", + "unique (region,grp,idn)"])); +if ($opt_lock_tables) +{ + do_query($dbh,"LOCK TABLES bench1 WRITE"); +} + +if ($opt_fast && defined($server->{vacuum})) +{ + $server->vacuum(1,\$dbh); +} + +#### +#### Insert $opt_loop_count records with +#### region: "A" -> "E" +#### idn: 0 -> count +#### rev_idn: count -> 0, +#### grp: distributed values 0 - > count/100 +#### + +print "Inserting $opt_loop_count rows\n"; + +$loop_time=new Benchmark; +$query="insert into bench1 values ("; +$half_done=$opt_loop_count/2; +for ($id=0,$rev_id=$opt_loop_count-1 ; $id < $opt_loop_count ; $id++,$rev_id--) +{ + $grp=$id*3 % $opt_groups; + $region=chr(65+$id%$opt_regions); + do_query($dbh,"$query'$region',$id,$rev_id,$grp)"); + if ($id == $half_done) + { # Test with different insert + $query="insert into bench1 (region,idn,rev_idn,grp) values ("; + } +} + +$end_time=new Benchmark; +print "Time to insert ($opt_loop_count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + +if ($opt_lock_tables) +{ + do_query($dbh,"UNLOCK TABLES"); +} + +if ($opt_fast && defined($server->{vacuum})) +{ + $server->vacuum(0,\$dbh,"bench1"); +} + +if ($opt_lock_tables) +{ + do_query($dbh,"LOCK TABLES bench1 WRITE"); +} + +#### +#### Do some selects on the table +#### + +select_test: + + + +if ($limits->{'group_distinct_functions'}) +{ + print "Testing count(distinct) on the table\n"; + $loop_time=new Benchmark; + $rows=$estimated=$count=0; + for ($i=0 ; $i < $opt_medium_loop_count ; $i++) + { + $count++; + $rows+=fetch_all_rows($dbh,"select count(distinct region) from bench1"); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, + $opt_medium_loop_count)); + } + print_time($estimated); + print " for count_distinct_key_prefix ($count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + + $loop_time=new Benchmark; + $rows=$estimated=$count=0; + for ($i=0 ; $i < $opt_medium_loop_count ; $i++) + { + $count++; + $rows+=fetch_all_rows($dbh,"select count(distinct grp) from bench1"); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, + $opt_medium_loop_count)); + } + print_time($estimated); + print " for count_distinct ($count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + + $loop_time=new Benchmark; + $rows=$estimated=$count=0; + for ($i=0 ; $i < $opt_medium_loop_count ; $i++) + { + $count++; + $rows+=fetch_all_rows($dbh,"select count(distinct grp),count(distinct rev_idn) from bench1"); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, + $opt_medium_loop_count)); + } + print_time($estimated); + print " for count_distinct_2 ($count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + + $loop_time=new Benchmark; + $rows=$estimated=$count=0; + for ($i=0 ; $i < $opt_medium_loop_count ; $i++) + { + $count++; + $rows+=fetch_all_rows($dbh,"select region,count(distinct idn) from bench1 group by region"); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, + $opt_medium_loop_count)); + } + print_time($estimated); + print " for count_distinct_group_on_key ($count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + + $loop_time=new Benchmark; + $rows=$estimated=$count=0; + for ($i=0 ; $i < $opt_medium_loop_count ; $i++) + { + $count++; + $rows+=fetch_all_rows($dbh,"select grp,count(distinct idn) from bench1 group by grp"); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, + $opt_medium_loop_count)); + } + print_time($estimated); + print " for count_distinct_group_on_key_parts ($count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + + $loop_time=new Benchmark; + $rows=$estimated=$count=0; + for ($i=0 ; $i < $opt_medium_loop_count ; $i++) + { + $count++; + $rows+=fetch_all_rows($dbh,"select grp,count(distinct rev_idn) from bench1 group by grp"); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, + $opt_medium_loop_count)); + } + print_time($estimated); + print " for count_distinct_group ($count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + + $loop_time=new Benchmark; + $rows=$estimated=$count=0; + $test_count=$opt_medium_loop_count/10; + for ($i=0 ; $i < $test_count ; $i++) + { + $count++; + $rows+=fetch_all_rows($dbh,"select idn,count(distinct region) from bench1 group by idn"); + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i+1, + $test_count)); + } + print_time($estimated); + print " for count_distinct_big ($count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; +} + +#### +#### End of benchmark +#### + +if ($opt_lock_tables) +{ + do_query($dbh,"UNLOCK TABLES"); +} +if (!$opt_skip_delete) +{ + do_query($dbh,"drop table bench1" . $server->{'drop_attr'}); +} + +if ($opt_fast && defined($server->{vacuum})) +{ + $server->vacuum(0,\$dbh); +} + +$dbh->disconnect; # close connection + +end_benchmark($start_time); diff --git a/sql-bench/bench-init.pl.sh b/sql-bench/bench-init.pl.sh index 204d15d7ab3..9b999ee7f95 100644 --- a/sql-bench/bench-init.pl.sh +++ b/sql-bench/bench-init.pl.sh @@ -38,24 +38,28 @@ require "$pwd/server-cfg" || die "Can't read Configuration file: $!\n"; $|=1; # Output data immediately -$opt_skip_test=$opt_skip_create=$opt_skip_delete=$opt_verbose=$opt_fast_insert=$opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=$opt_log=$opt_use_old_results=$opt_help=$opt_odbc=$opt_small_test=$opt_small_tables=$opt_samll_key_tables=$opt_stage=$opt_old_headers=$opt_die_on_errors=$opt_tcpip=0; -$opt_cmp=$opt_user=$opt_password=""; +$opt_skip_test=$opt_skip_create=$opt_skip_delete=$opt_verbose=$opt_fast_insert=$opt_lock_tables=$opt_debug=$opt_skip_delete=$opt_fast=$opt_force=$opt_log=$opt_use_old_results=$opt_help=$opt_odbc=$opt_small_test=$opt_small_tables=$opt_samll_key_tables=$opt_stage=$opt_old_headers=$opt_die_on_errors=$opt_tcpip=$opt_random=0; +$opt_cmp=$opt_user=$opt_password=$opt_connect_options=""; $opt_server="mysql"; $opt_dir="output"; $opt_host="localhost";$opt_database="test"; $opt_machine=""; $opt_suffix=""; $opt_create_options=undef; +$opt_optimization="None"; +$opt_hw=""; +$opt_threads=5; $opt_time_limit=10*60; # Don't wait more than 10 min for some tests $log_prog_args=join(" ", skip_arguments(\@ARGV,"comments","cmp","server", "user", "host", "database", "password", "use-old-results","skip-test", + "optimization","hw", "machine", "dir", "suffix", "log")); -GetOptions("skip-test=s","comments=s","cmp=s","server=s","user=s","host=s","database=s","password=s","loop-count=i","row-count=i","skip-create","skip-delete","verbose","fast-insert","lock-tables","debug","fast","force","field-count=i","regions=i","groups=i","time-limit=i","log","use-old-results","machine=s","dir=s","suffix=s","help","odbc","small-test","small-tables","small-key-tables","stage=i","old-headers","die-on-errors","create-options=s","hires","tcpip","silent") || usage(); +GetOptions("skip-test=s","comments=s","cmp=s","server=s","user=s","host=s","database=s","password=s","loop-count=i","row-count=i","skip-create","skip-delete","verbose","fast-insert","lock-tables","debug","fast","force","field-count=i","regions=i","groups=i","time-limit=i","log","use-old-results","machine=s","dir=s","suffix=s","help","odbc","small-test","small-tables","small-key-tables","stage=i","threads=i","random","old-headers","die-on-errors","create-options=s","hires","tcpip","silent","optimization=s","hw=s","socket=s","connect-options=s") || usage(); usage() if ($opt_help); $server=get_server($opt_server,$opt_host,$opt_database,$opt_odbc, - machine_part()); + machine_part(), $opt_socket, $opt_connect_options); $limits=merge_limits($server,$opt_cmp); $date=date(); @estimated=(0.0,0.0,0.0); # For estimated time support @@ -515,6 +519,10 @@ All benchmarks takes the following options: --password='password' Password for the current user. +--socket='socket' + If the database supports connecting through a Unix socket, + use this socket to connect + --regions This is a test specific option that is only used when debugging a test. This usually means how AND levels should be tested. @@ -553,6 +561,13 @@ All benchmarks takes the following options: different server options without overwritten old files. When using --fast the suffix is automaticly set to '_fast'. +--random + Inform test suite that we are generate random inital values for sequence of + test executions. It should be used for imitation of real conditions. + +--threads=# (Default 5) + Number of threads for multi-user benchmarks. + --tcpip Inform test suite that we are using TCP/IP to connect to the server. In this case we can\t do many new connections in a row as we in this case may @@ -572,6 +587,17 @@ All benchmarks takes the following options: --verbose This is a test specific option that is only used when debugging a test. Print more information about what is going on. + +--optimization='some comments' + Add coments about optimization of DBMS, which was done before the test. + +--hw='some comments' + Add coments about hardware used for this test. + +--connect-options='some connect options' + Add options, which uses at DBI connect. + For example --connect-options=mysql_read_default_file=/etc/my.cnf. + EOF exit(0); } diff --git a/sql-bench/crash-me.sh b/sql-bench/crash-me.sh index f6985adc5c0..d66e8f47d6a 100644 --- a/sql-bench/crash-me.sh +++ b/sql-bench/crash-me.sh @@ -38,7 +38,7 @@ # as such, and clarify ones such as "mediumint" with comments such as # "3-byte int" or "same as xxx". -$version="1.57"; +$version="1.58"; use DBI; use Getopt::Long; @@ -143,7 +143,15 @@ $longreadlen=16000000; # For retrieval buffer # use sigtrap; # Must be removed with perl5.005_2 on Win98 $SIG{PIPE} = 'IGNORE'; -$SIG{SEGV} = sub {warn('SEGFAULT')}; +$problem_counter=0; +$SIG{SEGV} = sub { + $problem_counter +=1; + if ($problem_counter >= 100) { + die("Too many problems, try to restart"); + } else { + warn('SEGFAULT'); + }; +}; $dbh=safe_connect(); # @@ -252,6 +260,8 @@ report("Functions",'functions',"select 1+1 $end_query"); report("Group functions",'group_functions',"select count(*) from crash_me"); report("Group functions with distinct",'group_distinct_functions', "select count(distinct a) from crash_me"); +report("Group functions with several distinct",'group_many_distinct_functions', + "select count(distinct a), count(distinct b) from crash_me"); report("Group by",'group_by',"select a from crash_me group by a"); report("Group by position",'group_by_position', "select a from crash_me group by 1"); @@ -1438,6 +1448,14 @@ if (!report("case independent table names","table_name_case", safe_query("drop table crash_q $drop_attr"); } +if (!report("case independent field names","field_name_case", + "create table crash_q (q integer)", + "insert into crash_q(Q) values (1)", + "drop table crash_q $drop_attr")) +{ + safe_query("drop table crash_q $drop_attr"); +} + if (!report("drop table if exists","drop_if_exists", "create table crash_q (q integer)", "drop table if exists crash_q $drop_attr")) @@ -2168,20 +2186,20 @@ EOF print <<EOF; Some of the tests you are about to execute may require a lot of -memory. Your tests WILL adversely affect system performance. It's +memory. Your tests WILL adversely affect system performance. It\'s not uncommon that either this crash-me test program, or the actual database back-end, will DIE with an out-of-memory error. So might any other program on your system if it requests more memory at the wrong time. Note also that while crash-me tries to find limits for the database server -it will make a lot of queries that can't be categorized as 'normal'. It's +it will make a lot of queries that can\'t be categorized as \'normal\'. It\'s not unlikely that crash-me finds some limit bug in your server so if you run this test you have to be prepared that your server may die during it! We, the creators of this utility, are not responsible in any way if your database server unexpectedly crashes while this program tries to find the -limitations of your server. By accepting the following question with 'yes', +limitations of your server. By accepting the following question with \'yes\', you agree to the above! You have been warned! @@ -2478,6 +2496,7 @@ sub report_one print "$prompt: "; if (!defined($limits{$limit})) { + save_incomplete($limit,$prompt); $result="no"; foreach $query (@$queries) { @@ -2503,6 +2522,7 @@ sub report_result print "$prompt: "; if (!defined($limits{$limit})) { + save_incomplete($limit,$prompt); $error=safe_query_result($query,"1",2); save_config_data($limit,$error ? "not supported" : $last_result,$prompt); } @@ -2515,6 +2535,7 @@ sub report_trans my ($limit,$queries,$check,$clear)=@_; if (!defined($limits{$limit})) { + save_incomplete($limit,$prompt); eval {undef($dbh->{AutoCommit})}; if (!$@) { @@ -2524,21 +2545,21 @@ sub report_trans if ($rc) { $dbh->{AutoCommit} = 1; if (safe_query_result($check,"","")) { - save_config_data($limit,"yes",$prompt); + save_config_data($limit,"yes",$limit); } safe_query($clear); } else { $dbh->{AutoCommit} = 1; - save_config_data($limit,"error",$prompt); + save_config_data($limit,"error",$limit); } } else { - save_config_data($limit,"error",$prompt); + save_config_data($limit,"error",$limit); } $dbh->{AutoCommit} = 1; } else { - save_config_data($limit,"no",$prompt); + save_config_data($limit,"no",$limit); } safe_query($clear); } @@ -2556,9 +2577,11 @@ sub check_and_report print "$prompt: " if (!defined($skip_prompt)); if (!defined($limits{$limit})) { + save_incomplete($limit,$prompt); $tmp=1-safe_query(\@$pre); $tmp=safe_query_result($query,$answer,$string_type) if (!$tmp); safe_query(\@$post); + delete $limits{$limit}; if ($function == 3) # Report error as 'no'. { $function=0; @@ -2587,8 +2610,10 @@ sub try_and_report my ($tmp,$test,$type); print "$prompt: "; + if (!defined($limits{$limit})) { + save_incomplete($limit,$prompt); $type="no"; # Not supported foreach $test (@tests) { @@ -2668,7 +2693,8 @@ sub safe_query_result $sth->finish; return ($result_type == 8) ? 0 : 1; } - if($result_type == 8) { + if ($result_type == 8) + { $sth->finish; return 1; } @@ -2699,7 +2725,7 @@ sub safe_query_result elsif ($result_type == 5) # Result should have given prefix { $result= -1 if (length($row->[0]) < length($answer) && - substring($row->[0],1,length($answer)) ne $answer); + substr($row->[0],1,length($answer)) ne $answer); } elsif ($result_type == 6) # Exact match but ignore errors { @@ -2760,6 +2786,8 @@ sub find_limit() print "$end (cache)\n"; return $end; } + save_incomplete($limit,$prompt); + if (defined($query->{'init'}) && !defined($end=$limits{'restart'}{'tohigh'})) { if (!safe_query($query->{'init'})) @@ -2932,6 +2960,16 @@ sub save_all_config_data close CONFIG_FILE; } +# +# Save 'incomplete' in the limits file to be able to continue if +# crash-me dies because of a bug in perl/DBI + +sub save_incomplete +{ + my ($limit,$prompt)= @_; + save_config_data($limit,"incompleted",$prompt) if ($opt_restart); +} + sub check_repeat { diff --git a/sql-bench/limits/db2.cfg b/sql-bench/limits/db2.cfg index 31280fbe643..5b2c0e172e7 100644 --- a/sql-bench/limits/db2.cfg +++ b/sql-bench/limits/db2.cfg @@ -1,34 +1,68 @@ -#This file is automaticly generated by crash-me 1.19a +#This file is automaticly generated by crash-me 1.57 NEG=yes # update of column= -column +Need_cast_for_null=no # Need to cast NULL for arithmetic alter_add_col=yes # Alter table add column +alter_add_constraint=yes # Alter table add constraint +alter_add_foreign_key=yes # Alter table add foreign key +alter_add_multi_col=no # Alter table add many columns +alter_add_primary_key=with constraint # Alter table add primary key +alter_add_unique=no # Alter table add unique +alter_alter_col=no # Alter table alter column default alter_change_col=no # Alter table change column alter_drop_col=no # Alter table drop column +alter_drop_constraint=yes # Alter table drop constraint +alter_drop_foreign_key=with drop constraint # Alter table drop foreign key +alter_drop_primary_key=drop primary key # Alter table drop primary key +alter_drop_unique=no # Alter table drop unique alter_modify_col=no # Alter table modify column alter_rename_table=no # Alter table rename table atomic_updates=yes # atomic updates atomic_updates_with_rollback=yes # atomic_updates_with_rollback -binary_items=no # binary items (0x41) -case_insensitive_strings=no # case insensitive compare -char_is_space_filled=no # char are space filled +automatic_rowid=no # Automatic row id +binary_numbers=no # binary numbers (0b1001) +binary_strings=no # binary strings (b'0110') +case_insensitive_strings=no # Case insensitive compare +char_is_space_filled=yes # char are space filled column_alias=yes # Column alias +columns_in_group_by=+64 # number of columns in group by +columns_in_order_by=+64 # number of columns in order by comment_#=no # # as comment -comment_--=yes # -- as comment +comment_--=yes # -- as comment (ANSI) comment_/**/=no # /* */ as comment +comment_//=no # // as comment compute=no # Compute -connections=125 # Simultaneous connections +connections=40 # Simultaneous connections (installation default) +constraint_check=yes # Column constraints +constraint_check_table=yes # Table constraints +constraint_null=no # NULL constraint (SyBase style) crash_me_safe=no # crash me safe -crash_me_version=1.19a # crash me version +crash_me_version=1.57 # crash me version create_default=yes # default value for column +create_default_func=no # default value function for column +create_if_not_exists=no # create table if not exists create_index=yes # create index +create_schema=no # Create SCHEMA +create_table_select=no # create table from select cross_join=no # cross join (same as from a,b) date_as_string=no # String functions on date columns +date_infinity=no # Supports 'infinity dates +date_last=no # Supports 9999-12-31 dates +date_one=no # Supports 0001-01-01 dates date_with_YY=no # Supports YY-MM-DD dates date_zero=no # Supports 0000-00-00 dates +domains=no # Domains (ANSI SQL) +dont_require_cast_to_float=yes # No need to cast from integer to float double_quotes=yes # Double '' as ' in strings +drop_if_exists=no # drop table if exists drop_index=yes # drop index +drop_requires_cascade=no # drop table require cascade/restrict +drop_restrict=no # drop table with cascade/restrict +end_colon=yes # allows end ';' except=yes # except except_all=yes # except all +except_all_incompat=no # except all (incompatible lists) +except_incompat=no # except (incompatible lists) float_int_expr=yes # mixing of integer and float in expression foreign_key=yes # foreign keys foreign_key_syntax=yes # foreign key syntax @@ -41,6 +75,8 @@ func_extra_<>=no # Function <> in SELECT func_extra_==no # Function = func_extra_add_months=no # Function ADD_MONTHS func_extra_and_or=no # Function AND and OR in SELECT +func_extra_ascii_char=no # Function ASCII_CHAR +func_extra_ascii_code=no # Function ASCII_CODE func_extra_atn2=no # Function ATN2 func_extra_auto_num2string=no # Function automatic num->string convert func_extra_auto_string2num=no # Function automatic string->num convert @@ -72,6 +108,7 @@ func_extra_in_num=no # Function IN on numbers in SELECT func_extra_in_str=no # Function IN on strings in SELECT func_extra_initcap=no # Function INITCAP func_extra_instr=no # Function LOCATE as INSTR +func_extra_instr_oracle=no # Function INSTR (Oracle syntax) func_extra_instrb=no # Function INSTRB func_extra_interval=no # Function INTERVAL func_extra_last_day=no # Function LAST_DAY @@ -84,16 +121,15 @@ func_extra_ln=yes # Function LN func_extra_log(m_n)=no # Function LOG(m,n) func_extra_logn=no # Function LOGN func_extra_lpad=no # Function LPAD -func_extra_max_num=no # Function MAX on numbers func_extra_mdy=no # Function MDY func_extra_mid=no # Function SUBSTRING as MID -func_extra_min_num=no # Function MIN on numbers func_extra_months_between=no # Function MONTHS_BETWEEN func_extra_not=no # Function NOT in SELECT func_extra_not_between=no # Function NOT BETWEEN in SELECT func_extra_not_like=no # Function NOT LIKE in SELECT func_extra_odbc_convert=no # Function ODBC CONVERT func_extra_password=no # Function PASSWORD +func_extra_paste=no # Function PASTE func_extra_patindex=no # Function PATINDEX func_extra_period_add=no # Function PERIOD_ADD func_extra_period_diff=no # Function PERIOD_DIFF @@ -112,6 +148,8 @@ func_extra_strcmp=no # Function STRCMP func_extra_stuff=no # Function STUFF func_extra_substrb=no # Function SUBSTRB func_extra_substring_index=no # Function SUBSTRING_INDEX +func_extra_sysdate=no # Function SYSDATE +func_extra_tail=no # Function TAIL func_extra_tanh=no # Function TANH func_extra_time_to_sec=no # Function TIME_TO_SEC func_extra_to_days=no # Function TO_DAYS @@ -126,6 +164,7 @@ func_extra_version=no # Function VERSION func_extra_weekday=no # Function WEEKDAY func_extra_|=no # Function | (bitwise or) func_extra_||=no # Function OR as '||' +func_extra_~*=no # Function ~* (case insensitive compare) func_odbc_abs=yes # Function ABS func_odbc_acos=yes # Function ACOS func_odbc_ascii=yes # Function ASCII @@ -133,31 +172,30 @@ func_odbc_asin=yes # Function ASIN func_odbc_atan=yes # Function ATAN func_odbc_atan2=error # Function ATAN2 func_odbc_ceiling=yes # Function CEILING -func_odbc_char=error # Function CHAR +func_odbc_char=yes # Function CHAR func_odbc_concat=yes # Function CONCAT(2 arg) func_odbc_cos=yes # Function COS func_odbc_cot=yes # Function COT -func_odbc_curdate=no # Function CURDATE -func_odbc_curtime=no # Function CURTIME -func_odbc_database=no # Function DATABASE -func_odbc_dayname=no # Function DAYNAME -func_odbc_dayofmonth=no # Function DAYOFMONTH -func_odbc_dayofweek=no # Function DAYOFWEEK -func_odbc_dayofyear=no # Function DAYOFYEAR +func_odbc_curdate=yes # Function CURDATE +func_odbc_curtime=yes # Function CURTIME +func_odbc_database=yes # Function DATABASE +func_odbc_dayname=yes # Function DAYNAME +func_odbc_dayofmonth=yes # Function DAYOFMONTH +func_odbc_dayofweek=yes # Function DAYOFWEEK +func_odbc_dayofyear=yes # Function DAYOFYEAR func_odbc_degrees=yes # Function DEGREES func_odbc_difference=yes # Function DIFFERENCE() func_odbc_exp=yes # Function EXP -func_odbc_extract=no # Function EXTRACT func_odbc_floor=yes # Function FLOOR func_odbc_fn_left=yes # Function ODBC syntax LEFT & RIGHT func_odbc_hour=yes # Function HOUR -func_odbc_hour_time=no # Function ANSI HOUR -func_odbc_ifnull=no # Function IFNULL +func_odbc_hour_time=yes # Function ANSI HOUR +func_odbc_ifnull=yes # Function IFNULL func_odbc_insert=yes # Function INSERT func_odbc_lcase=yes # Function LCASE func_odbc_left=yes # Function LEFT func_odbc_length=yes # Function REAL LENGTH -func_odbc_length_without_space=error # Function ODBC LENGTH +func_odbc_length_without_space=yes # Function ODBC LENGTH func_odbc_locate_2=yes # Function LOCATE(2 arg) func_odbc_locate_3=yes # Function LOCATE(3 arg) func_odbc_log=yes # Function LOG @@ -165,12 +203,12 @@ func_odbc_log10=yes # Function LOG10 func_odbc_ltrim=yes # Function LTRIM func_odbc_minute=yes # Function MINUTE func_odbc_mod=yes # Function MOD -func_odbc_month=no # Function MONTH -func_odbc_monthname=no # Function MONTHNAME -func_odbc_now=no # Function NOW -func_odbc_pi=no # Function PI +func_odbc_month=yes # Function MONTH +func_odbc_monthname=yes # Function MONTHNAME +func_odbc_now=yes # Function NOW +func_odbc_pi=yes # Function PI func_odbc_power=yes # Function POWER -func_odbc_quarter=no # Function QUARTER +func_odbc_quarter=yes # Function QUARTER func_odbc_radians=yes # Function RADIANS func_odbc_rand=yes # Function RAND func_odbc_repeat=yes # Function REPEAT @@ -184,38 +222,43 @@ func_odbc_sin=yes # Function SIN func_odbc_soundex=yes # Function SOUNDEX func_odbc_space=yes # Function SPACE func_odbc_sqrt=yes # Function SQRT -func_odbc_substring=no # Function ODBC SUBSTRING +func_odbc_substring=yes # Function ODBC SUBSTRING func_odbc_tan=yes # Function TAN func_odbc_timestampadd=no # Function TIMESTAMPADD func_odbc_timestampdiff=no # Function TIMESTAMPDIFF func_odbc_truncate=yes # Function TRUNCATE -func_odbc_ucase=no # Function UCASE -func_odbc_user=yes # Function USER -func_odbc_user()=no # Function USER() -func_odbc_week=no # Function WEEK -func_odbc_year=no # Function YEAR +func_odbc_ucase=yes # Function UCASE +func_odbc_user()=yes # Function USER() +func_odbc_week=yes # Function WEEK +func_odbc_year=yes # Function YEAR func_sql_+=yes # Function +, -, * and / func_sql_bit_length=no # Function BIT_LENGTH -func_sql_case=yes # Function CASE func_sql_cast=yes # Function CAST func_sql_char_length=no # Function CHAR_LENGTH +func_sql_char_length(constant)=no # Function CHAR_LENGTH(constant) func_sql_character_length=no # Function CHARACTER_LENGTH +func_sql_coalesce=no # Function COALESCE func_sql_concat_as_||=yes # Function concatenation with || func_sql_current_date=no # Function CURRENT_DATE -func_sql_current_date()=no # Function CURRENT_DATE() func_sql_current_time=no # Function CURRENT_TIME -func_sql_current_time()=no # Function CURRENT_TIME() func_sql_current_timestamp=no # Function CURRENT_TIMESTAMP -func_sql_current_timestamp()=no # Function CURRENT_TIMESTAMP() -func_sql_lower=no # Function LOWER +func_sql_current_user=no # Function CURRENT_USER +func_sql_extract_sql=no # Function EXTRACT +func_sql_localtime=no # Function LOCALTIME +func_sql_localtimestamp=no # Function LOCALTIMESTAMP +func_sql_lower=yes # Function LOWER +func_sql_nullif_num=yes # Function NULLIF with numbers +func_sql_nullif_string=yes # Function NULLIF with strings func_sql_octet_length=no # Function OCTET_LENGTH func_sql_position=no # Function POSITION +func_sql_searched_case=yes # Function searched CASE func_sql_session_user=no # Function SESSION_USER +func_sql_simple_case=yes # Function simple CASE func_sql_substring=no # Function ANSI SQL SUBSTRING -func_extra_sysdate=no # Function SYSDATE func_sql_system_user=no # Function SYSTEM_USER func_sql_trim=no # Function TRIM -func_sql_upper=no # Function UPPER +func_sql_upper=yes # Function UPPER +func_sql_user=yes # Function USER func_where_between=yes # Function BETWEEN func_where_eq_all=yes # Function = ALL func_where_eq_any=yes # Function = ANY @@ -235,36 +278,49 @@ func_where_unique=no # Function UNIQUE functions=yes # Functions group_by=yes # Group by group_by_alias=no # Group by alias -group_by_null=yes # Test nulls in group by +group_by_null=yes # Group on column with null values group_by_position=no # Group by position group_distinct_functions=yes # Group functions with distinct group_func_extra_bit_and=no # Group function BIT_AND group_func_extra_bit_or=no # Group function BIT_OR +group_func_extra_count_distinct_list=no # Group function COUNT(DISTINCT expr,expr,...) group_func_extra_std=no # Group function STD group_func_extra_stddev=yes # Group function STDDEV group_func_extra_variance=yes # Group function VARIANCE +group_func_sql_any=no # Group function ANY group_func_sql_avg=yes # Group function AVG group_func_sql_count_*=yes # Group function COUNT (*) group_func_sql_count_column=yes # Group function COUNT column name -group_func_sql_count_distinct=yes # Group function COUNT DISTINCT column name +group_func_sql_count_distinct=yes # Group function COUNT(DISTINCT expr) +group_func_sql_every=no # Group function EVERY group_func_sql_max=yes # Group function MAX on numbers group_func_sql_max_str=yes # Group function MAX on strings group_func_sql_min=yes # Group function MIN on numbers group_func_sql_min_str=yes # Group function MIN on strings +group_func_sql_some=no # Group function SOME group_func_sql_sum=yes # Group function SUM group_functions=yes # Group functions +group_on_unused=yes # Group on unused column +has_true_false=no # TRUE and FALSE having=yes # Having having_with_alias=no # Having on alias having_with_group=yes # Having with group function -ignore_end_space=yes # ignore end space in compare +hex_numbers=no # hex numbers (0x41) +hex_strings=yes # hex strings (x'1ace') +ignore_end_space=yes # Ignore end space in compare index_in_create=no # index in create table index_namespace=no # different namespace for index index_parts=no # index on column part (extension) +inner_join=yes # inner join insert_empty_string=yes # insert empty string +insert_multi_value=yes # INSERT with Value lists insert_select=yes # insert INTO ... SELECT ... +insert_with_set=no # INSERT with set syntax intersect=yes # intersect intersect_all=yes # intersect all -join_tables=+64 # tables in join +intersect_all_incompat=no # intersect all (incompatible lists) +intersect_incompat=no # intersect (incompatible lists) +join_tables=10 # tables in join left_outer_join=yes # left outer join left_outer_join_using=no # left outer join using like_with_column=no # column LIKE column @@ -273,150 +329,194 @@ lock_tables=yes # lock table logical_value=not supported # Value of logical operation (1=1) max_big_expressions=1 # big expressions max_char_size=254 # max char() size -max_column_name=18 # column name length +max_column_name=30 # column name length max_columns=500 # Columns in table -max_conditions=418 # OR and AND in WHERE -max_expressions=9820 # simple expressions +max_conditions=2082 # OR and AND in WHERE +max_expressions=9543 # simple expressions max_index=+64 # max index -max_index_length=255 # index length +max_index_length=1024 # index length max_index_name=18 # index name length max_index_part_length=254 # max index part length -max_index_parts=15 # index parts -max_index_varchar_part_length=251 # index varchar part length +max_index_parts=16 # index parts +max_index_varchar_part_length=255 # index varchar part length max_row_length=4005 # max table row length (without blobs) max_row_length_with_null=3989 # table row length with nulls (without blobs) -max_select_alias_name=18 # select alias name length +max_select_alias_name=30 # select alias name length max_stack_expression=1363 # stacked expressions -max_table_alias_name=18 # table alias name length -max_table_name=18 # table name length +max_table_alias_name=128 # table alias name length +max_table_name=128 # table name length max_unique_index=+64 # unique indexes max_varchar_size=4000 # max varchar() size minus=no # minus +minus_incompat=no # minus (incompatible lists) minus_neg=no # Calculate 1--1 multi_drop=no # many tables to drop table multi_strings=no # Multiple line strings multi_table_delete=no # DELETE FROM table1,table2... multi_table_update=no # Update with many tables natural_join=no # natural join +natural_join_incompat=no # natural join (incompatible lists) natural_left_outer_join=no # natural left outer join no_primary_key=yes # Tables without primary key null_concat_expr=no # Is 'a' || NULL = NULL null_in_index=yes # null in index -null_in_unique=no # null in unique +null_in_unique=no # null in unique index null_num_expr=no # Is 1+NULL = NULL +nulls_in_unique=no # null combination in unique index odbc_left_outer_join=yes # left outer join odbc style -operating_system=Windows NT Version 4.0 # crash-me tested on +operating_system=Linux 2.4.4-64GB-SMP i686 # crash-me tested on order_by=yes # Order by order_by_alias=yes # Order by alias +order_by_function=yes # Order by function order_by_position=yes # Order by position +order_by_remember_desc=no # Order by DESC is remembered +order_on_unused=yes # Order by on unused column primary_key_in_create=yes # primary key in create table -query_size=19662 # query size +psm_functions=no # PSM functions (ANSI SQL) +psm_modules=no # PSM modules (ANSI SQL) +psm_procedures=no # PSM procedures (ANSI SQL) +psm_trigger=no # Triggers (ANSI SQL) +query_size=65535 # query size +quote_ident_with_"=yes # " as identifier quote (ANSI SQL) +quote_ident_with_[=no # [] as identifier quote +quote_ident_with_`=no # ` as identifier quote quote_with_"=no # Allows ' and " as string markers -recursive_subqueries=22 # recursive subqueries +recursive_subqueries=8 # recursive subqueries remember_end_space=no # Remembers end space in char() remember_end_space_varchar=yes # Remembers end space in varchar() +rename_table=yes # rename table repeat_string_size=4000 # return string size from function right_outer_join=yes # right outer join +rowid=no # Type for row id select_constants=yes # Select constants -select_string_size=4000 # constant string size in SELECT +select_limit=no # LIMIT number of rows +select_limit2=no # SELECT with LIMIT #,# +select_string_size=32672 # constant string size in SELECT select_table_update=yes # Update with sub select select_without_from=no # SELECT without FROM -server_version=IBM DB2 5 # server version +server_version=IBM DB2 7.2 # server version simple_joins=yes # ANSI SQL simple joins +storage_of_float=truncate # Storage of float values subqueries=yes # subqueries table_alias=yes # Table alias +table_name_case=yes # case independent table names table_wildcard=yes # Select table_name.* -transactions=yes # transactions +temporary_table=no # temporary tables +transactions=yes # constant string size in where +truncate_table=no # truncate type_extra_abstime=no # Type abstime type_extra_bfile=no # Type bfile type_extra_blob=no # Type blob type_extra_bool=no # Type bool type_extra_box=no # Type box type_extra_byte=no # Type byte -type_extra_char(1_arg)_binary=no # Type char(1 arg) binary -type_extra_char16=no # Type char16 -type_extra_char2=no # Type char2 -type_extra_char4=no # Type char4 -type_extra_char8=no # Type char8 +type_extra_char(1_arg)_binary=no # Type char(1 arg) binary +type_extra_cidr=no # Type cidr type_extra_circle=no # Type circle type_extra_clob=no # Type clob type_extra_datetime=no # Type datetime -type_extra_enum(1_arg)=no # Type enum(1 arg) -type_sql_float(1_arg)=yes # Type float(1 arg) +type_extra_double=yes # Type double +type_extra_enum(1_arg)=no # Type enum(1 arg) +type_extra_float(2_arg)=no # Type float(2 arg) type_extra_float4=no # Type float4 type_extra_float8=no # Type float8 type_extra_image=no # Type image -type_extra_int(1_arg)_zerofill=no # Type int(1 arg) zerofill +type_extra_inet=no # Type inet +type_extra_int(1_arg)_zerofill=no # Type int(1 arg) zerofill type_extra_int1=no # Type int1 type_extra_int2=no # Type int2 type_extra_int3=no # Type int3 type_extra_int4=no # Type int4 type_extra_int8=no # Type int8 type_extra_int_auto_increment=no # Type int not null auto_increment +type_extra_int_identity=no # Type int not null identity +type_extra_int_unsigned=no # Type int unsigned +type_extra_interval=no # Type interval type_extra_line=no # Type line type_extra_long=no # Type long type_extra_long_raw=no # Type long raw type_extra_long_varbinary=no # Type long varbinary -type_extra_long_varchar(1_arg)=no # Type long varchar(1 arg) +type_extra_long_varchar(1_arg)=no # Type long varchar(1 arg) type_extra_lseg=no # Type lseg +type_extra_macaddr=no # Type macaddr type_extra_mediumint=no # Type mediumint type_extra_mediumtext=no # Type mediumtext type_extra_middleint=no # Type middleint type_extra_mlslabel=no # Type mlslabel type_extra_money=no # Type money -type_sql_nchar(1_arg)=no # Type nchar(1 arg) type_extra_nclob=no # Type nclob type_extra_number=no # Type number -type_extra_number(1_arg)=no # Type number(1 arg) -type_extra_nvarchar(2_arg)=no # Type nvarchar(2 arg) +type_extra_number(1_arg)=no # Type number(1 arg) +type_extra_number(2_arg)=no # Type number(2 arg) type_extra_nvarchar2(1_arg)=no # Type nvarchar2(1 arg) type_extra_path=no # Type path type_extra_point=no # Type point type_extra_polygon=no # Type polygon -type_extra_raw(1_arg)=no # Type raw(1 arg) +type_extra_raw(1_arg)=no # Type raw(1 arg) type_extra_reltime=no # Type reltime type_extra_rowid=no # Type rowid type_extra_serial=no # Type serial -type_extra_set(1_arg)=no # Type set(1 arg) +type_extra_set(1_arg)=no # Type set(1 arg) type_extra_smalldatetime=no # Type smalldatetime type_extra_smallfloat=no # Type smallfloat type_extra_smallmoney=no # Type smallmoney type_extra_text=no # Type text -type_extra_text(1_arg)=no # Type text(1 arg) +type_extra_text(1_arg)=no # Type text(1 arg) type_extra_timespan=no # Type timespan +type_extra_uint=no # Type uint type_extra_varchar2(1_arg)=no # Type varchar2(1 arg) type_extra_year=no # Type year -type_odbc_bigint=no # Type bigint -type_odbc_binary(1_arg)=no # Type binary(1 arg) +type_odbc_bigint=yes # Type bigint +type_odbc_binary(1_arg)=no # Type binary(1 arg) type_odbc_datetime=no # Type datetime -type_sql_smallint=yes # Type smallint type_odbc_tinyint=no # Type tinyint type_odbc_varbinary(1_arg)=no # Type varbinary(1 arg) type_sql_bit=no # Type bit type_sql_bit(1_arg)=no # Type bit(1 arg) type_sql_bit_varying(1_arg)=no # Type bit varying(1 arg) -type_sql_char(1_arg)=yes # Type char(1 arg) -type_sql_char_varying(1_arg)=yes # Type char varying(1 arg) +type_sql_boolean=no # Type boolean +type_sql_char(1_arg)=yes # Type char(1 arg) +type_sql_char_varying(1_arg)=yes # Type char varying(1 arg) type_sql_character(1_arg)=yes # Type character(1 arg) type_sql_character_varying(1_arg)=yes # Type character varying(1 arg) type_sql_date=yes # Type date type_sql_dec(2_arg)=yes # Type dec(2 arg) -type_sql_decimal(2_arg)=yes # Type decimal(2 arg) -type_extra_double=yes # Type double +type_sql_decimal(2_arg)=yes # Type decimal(2 arg) type_sql_double_precision=yes # Type double precision type_sql_float=yes # Type float -type_extra_float(2_arg)=no # Type float(1 arg) +type_sql_float(1_arg)=yes # Type float(1 arg) type_sql_int=yes # Type int type_sql_integer=yes # Type integer +type_sql_interval_day=no # Type interval day +type_sql_interval_day_to_hour=no # Type interval day to hour +type_sql_interval_day_to_minute=no # Type interval day to minute +type_sql_interval_day_to_second=no # Type interval day to second +type_sql_interval_hour=no # Type interval hour +type_sql_interval_hour_to_minute=no # Type interval hour to minute +type_sql_interval_hour_to_second=no # Type interval hour to second +type_sql_interval_minute=no # Type interval minute +type_sql_interval_minute_to_second=no # Type interval minute to second +type_sql_interval_month=no # Type interval month +type_sql_interval_second=no # Type interval second type_sql_interval_year=no # Type interval year -type_sql_numeric(2_arg)=yes # Type numeric(2 arg) +type_sql_interval_year_to_month=no # Type interval year to month +type_sql_national_char_varying(1_arg)=no # Type national char varying(1 arg) +type_sql_national_character(1_arg)=no # Type national character(1 arg) +type_sql_national_character_varying(1_arg)=no # Type national character varying(1 arg) +type_sql_nchar(1_arg)=no # Type nchar(1 arg) +type_sql_nchar_varying(1_arg)=no # Type nchar varying(1 arg) +type_sql_numeric(2_arg)=yes # Type numeric(2 arg) type_sql_real=yes # Type real +type_sql_smallint=yes # Type smallint type_sql_time=yes # Type time type_sql_timestamp=yes # Type timestamp -type_sql_varchar(1_arg)=yes # Type varchar(1 arg) +type_sql_timestamp_with_time_zone=no # Type timestamp with time zone +type_sql_varchar(1_arg)=yes # Type varchar(1 arg) union=yes # union union_all=yes # union all +union_all_incompat=yes # union all (incompatible lists) +union_incompat=yes # union (incompatible lists) unique_in_create=yes # unique in create table unique_null_in_create=no # unique null in create views=yes # views -where_string_size=4000 # constant string size in where +where_string_size=32672 # constant string size in where diff --git a/sql-bench/limits/interbase-dialect1.cfg b/sql-bench/limits/interbase-dialect1.cfg new file mode 100644 index 00000000000..046627be513 --- /dev/null +++ b/sql-bench/limits/interbase-dialect1.cfg @@ -0,0 +1,514 @@ +#This file is automaticly generated by crash-me 1.57 + +NEG=no # update of column= -column +Need_cast_for_null=no # Need to cast NULL for arithmetic +alter_add_col=yes # Alter table add column +alter_add_constraint=yes # Alter table add constraint +alter_add_foreign_key=yes # Alter table add foreign key +alter_add_multi_col=with add # Alter table add many columns +alter_add_primary_key=with constraint # Alter table add primary key +alter_add_unique=no # Alter table add unique +alter_alter_col=no # Alter table alter column default +alter_change_col=no # Alter table change column +alter_drop_col=no # Alter table drop column +alter_drop_constraint=yes # Alter table drop constraint +alter_drop_foreign_key=with drop constraint # Alter table drop foreign key +alter_drop_primary_key=no # Alter table drop primary key +alter_drop_unique=no # Alter table drop unique +alter_modify_col=no # Alter table modify column +alter_rename_table=no # Alter table rename table +atomic_updates=no # atomic updates +automatic_rowid=no # Automatic row id +binary_numbers=yes # binary numbers (0b1001) +binary_strings=no # binary strings (b'0110') +case_insensitive_strings=no # Case insensitive compare +char_is_space_filled=yes # char are space filled +column_alias=yes # Column alias +columns_in_order_by=37 # number of columns in order by +comment_#=no # # as comment +comment_--=no # -- as comment (ANSI) +comment_/**/=yes # /* */ as comment +comment_//=no # // as comment +compute=no # Compute +connections=1000 # Simultaneous connections (installation default) +constraint_check=no # Column constraints +constraint_check_table=no # Table constraints +constraint_null=no # NULL constraint (SyBase style) +crash_me_safe=yes # crash me safe +crash_me_version=1.57 # crash me version +create_default=yes # default value for column +create_default_func=no # default value function for column +create_if_not_exists=no # create table if not exists +create_index=yes # create index +create_schema=no # Create SCHEMA +create_table_select=no # create table from select +cross_join=no # cross join (same as from a,b) +date_infinity=no # Supports 'infinity dates +date_last=no # Supports 9999-12-31 dates +date_one=no # Supports 0001-01-01 dates +date_with_YY=no # Supports YY-MM-DD dates +date_zero=no # Supports 0000-00-00 dates +domains=no # Domains (ANSI SQL) +double_quotes=yes # Double '' as ' in strings +drop_if_exists=no # drop table if exists +drop_index=yes # drop index +drop_requires_cascade=no # drop table require cascade/restrict +drop_restrict=no # drop table with cascade/restrict +end_colon=yes # allows end ';' +except=no # except +except_all=no # except all +except_all_incompat=no # except all (incompatible lists) +except_incompat=no # except (incompatible lists) +float_int_expr=yes # mixing of integer and float in expression +foreign_key_syntax=no # foreign key syntax +full_outer_join=yes # full outer join +func_extra_!=no # Function NOT as '!' in SELECT +func_extra_%=no # Function MOD as % +func_extra_&=no # Function & (bitwise and) +func_extra_&&=no # Function AND as '&&' +func_extra_<>=no # Function <> in SELECT +func_extra_==no # Function = +func_extra_add_months=no # Function ADD_MONTHS +func_extra_and_or=no # Function AND and OR in SELECT +func_extra_ascii_char=no # Function ASCII_CHAR +func_extra_ascii_code=no # Function ASCII_CODE +func_extra_atn2=no # Function ATN2 +func_extra_auto_num2string=no # Function automatic num->string convert +func_extra_auto_string2num=no # Function automatic string->num convert +func_extra_between=no # Function BETWEEN in SELECT +func_extra_binary_shifts=no # Function << and >> (bitwise shifts) +func_extra_bit_count=no # Function BIT_COUNT +func_extra_ceil=no # Function CEIL +func_extra_charindex=no # Function CHARINDEX +func_extra_chr=no # Function CHR +func_extra_concat_as_+=no # Function concatenation with + +func_extra_concat_list=no # Function CONCAT(list) +func_extra_convert=no # Function CONVERT +func_extra_cosh=no # Function COSH +func_extra_date_format=no # Function DATE_FORMAT +func_extra_dateadd=no # Function DATEADD +func_extra_datediff=no # Function DATEDIFF +func_extra_datename=no # Function DATENAME +func_extra_datepart=no # Function DATEPART +func_extra_elt=no # Function ELT +func_extra_encrypt=no # Function ENCRYPT +func_extra_field=no # Function FIELD +func_extra_format=no # Function FORMAT +func_extra_from_days=no # Function FROM_DAYS +func_extra_from_unixtime=no # Function FROM_UNIXTIME +func_extra_getdate=no # Function GETDATE +func_extra_greatest=no # Function GREATEST +func_extra_if=no # Function IF +func_extra_in_num=no # Function IN on numbers in SELECT +func_extra_in_str=no # Function IN on strings in SELECT +func_extra_initcap=no # Function INITCAP +func_extra_instr=no # Function LOCATE as INSTR +func_extra_instr_oracle=no # Function INSTR (Oracle syntax) +func_extra_instrb=no # Function INSTRB +func_extra_interval=no # Function INTERVAL +func_extra_last_day=no # Function LAST_DAY +func_extra_last_insert_id=no # Function LAST_INSERT_ID +func_extra_least=no # Function LEAST +func_extra_lengthb=no # Function LENGTHB +func_extra_like=no # Function LIKE in SELECT +func_extra_like_escape=no # Function LIKE ESCAPE in SELECT +func_extra_ln=no # Function LN +func_extra_log(m_n)=no # Function LOG(m,n) +func_extra_logn=no # Function LOGN +func_extra_lpad=no # Function LPAD +func_extra_mdy=no # Function MDY +func_extra_mid=no # Function SUBSTRING as MID +func_extra_months_between=no # Function MONTHS_BETWEEN +func_extra_not=no # Function NOT in SELECT +func_extra_not_between=no # Function NOT BETWEEN in SELECT +func_extra_not_like=no # Function NOT LIKE in SELECT +func_extra_odbc_convert=no # Function ODBC CONVERT +func_extra_password=no # Function PASSWORD +func_extra_paste=no # Function PASTE +func_extra_patindex=no # Function PATINDEX +func_extra_period_add=no # Function PERIOD_ADD +func_extra_period_diff=no # Function PERIOD_DIFF +func_extra_pow=no # Function POW +func_extra_range=no # Function RANGE +func_extra_regexp=no # Function REGEXP in SELECT +func_extra_replicate=no # Function REPLICATE +func_extra_reverse=no # Function REVERSE +func_extra_root=no # Function ROOT +func_extra_round1=no # Function ROUND(1 arg) +func_extra_rpad=no # Function RPAD +func_extra_sec_to_time=no # Function SEC_TO_TIME +func_extra_sinh=no # Function SINH +func_extra_str=no # Function STR +func_extra_strcmp=no # Function STRCMP +func_extra_stuff=no # Function STUFF +func_extra_substrb=no # Function SUBSTRB +func_extra_substring_index=no # Function SUBSTRING_INDEX +func_extra_sysdate=no # Function SYSDATE +func_extra_tail=no # Function TAIL +func_extra_tanh=no # Function TANH +func_extra_time_to_sec=no # Function TIME_TO_SEC +func_extra_to_days=no # Function TO_DAYS +func_extra_translate=no # Function TRANSLATE +func_extra_trim_many_char=no # Function TRIM; Many char extension +func_extra_trim_substring=no # Function TRIM; Substring extension +func_extra_trunc=no # Function TRUNC +func_extra_uid=no # Function UID +func_extra_unix_timestamp=no # Function UNIX_TIMESTAMP +func_extra_userenv=no # Function USERENV +func_extra_version=no # Function VERSION +func_extra_weekday=no # Function WEEKDAY +func_extra_|=no # Function | (bitwise or) +func_extra_||=no # Function OR as '||' +func_extra_~*=no # Function ~* (case insensitive compare) +func_odbc_abs=no # Function ABS +func_odbc_acos=no # Function ACOS +func_odbc_ascii=no # Function ASCII +func_odbc_asin=no # Function ASIN +func_odbc_atan=no # Function ATAN +func_odbc_atan2=no # Function ATAN2 +func_odbc_ceiling=no # Function CEILING +func_odbc_char=no # Function CHAR +func_odbc_concat=no # Function CONCAT(2 arg) +func_odbc_cos=no # Function COS +func_odbc_cot=no # Function COT +func_odbc_curdate=no # Function CURDATE +func_odbc_curtime=no # Function CURTIME +func_odbc_database=no # Function DATABASE +func_odbc_dayname=no # Function DAYNAME +func_odbc_dayofmonth=no # Function DAYOFMONTH +func_odbc_dayofweek=no # Function DAYOFWEEK +func_odbc_dayofyear=no # Function DAYOFYEAR +func_odbc_degrees=no # Function DEGREES +func_odbc_difference=no # Function DIFFERENCE() +func_odbc_exp=no # Function EXP +func_odbc_floor=no # Function FLOOR +func_odbc_fn_left=no # Function ODBC syntax LEFT & RIGHT +func_odbc_hour=no # Function HOUR +func_odbc_hour_time=no # Function ANSI HOUR +func_odbc_ifnull=no # Function IFNULL +func_odbc_insert=no # Function INSERT +func_odbc_lcase=no # Function LCASE +func_odbc_left=no # Function LEFT +func_odbc_length=no # Function REAL LENGTH +func_odbc_length_without_space=no # Function ODBC LENGTH +func_odbc_locate_2=no # Function LOCATE(2 arg) +func_odbc_locate_3=no # Function LOCATE(3 arg) +func_odbc_log=no # Function LOG +func_odbc_log10=no # Function LOG10 +func_odbc_ltrim=no # Function LTRIM +func_odbc_minute=no # Function MINUTE +func_odbc_mod=no # Function MOD +func_odbc_month=no # Function MONTH +func_odbc_monthname=no # Function MONTHNAME +func_odbc_now=no # Function NOW +func_odbc_pi=no # Function PI +func_odbc_power=no # Function POWER +func_odbc_quarter=no # Function QUARTER +func_odbc_radians=no # Function RADIANS +func_odbc_rand=no # Function RAND +func_odbc_repeat=no # Function REPEAT +func_odbc_replace=no # Function REPLACE +func_odbc_right=no # Function RIGHT +func_odbc_round=no # Function ROUND(2 arg) +func_odbc_rtrim=no # Function RTRIM +func_odbc_second=no # Function SECOND +func_odbc_sign=no # Function SIGN +func_odbc_sin=no # Function SIN +func_odbc_soundex=no # Function SOUNDEX +func_odbc_space=no # Function SPACE +func_odbc_sqrt=no # Function SQRT +func_odbc_substring=no # Function ODBC SUBSTRING +func_odbc_tan=no # Function TAN +func_odbc_timestampadd=no # Function TIMESTAMPADD +func_odbc_timestampdiff=no # Function TIMESTAMPDIFF +func_odbc_truncate=no # Function TRUNCATE +func_odbc_ucase=no # Function UCASE +func_odbc_user()=no # Function USER() +func_odbc_week=no # Function WEEK +func_odbc_year=no # Function YEAR +func_sql_+=yes # Function +, -, * and / +func_sql_bit_length=no # Function BIT_LENGTH +func_sql_cast=yes # Function CAST +func_sql_char_length=no # Function CHAR_LENGTH +func_sql_char_length(constant)=no # Function CHAR_LENGTH(constant) +func_sql_character_length=no # Function CHARACTER_LENGTH +func_sql_coalesce=no # Function COALESCE +func_sql_concat_as_||=yes # Function concatenation with || +func_sql_current_date=no # Function CURRENT_DATE +func_sql_current_time=no # Function CURRENT_TIME +func_sql_current_timestamp=yes # Function CURRENT_TIMESTAMP +func_sql_current_user=no # Function CURRENT_USER +func_sql_extract_sql=yes # Function EXTRACT +func_sql_localtime=no # Function LOCALTIME +func_sql_localtimestamp=no # Function LOCALTIMESTAMP +func_sql_lower=no # Function LOWER +func_sql_nullif_num=no # Function NULLIF with numbers +func_sql_nullif_string=no # Function NULLIF with strings +func_sql_octet_length=no # Function OCTET_LENGTH +func_sql_position=no # Function POSITION +func_sql_searched_case=no # Function searched CASE +func_sql_session_user=no # Function SESSION_USER +func_sql_simple_case=no # Function simple CASE +func_sql_substring=no # Function ANSI SQL SUBSTRING +func_sql_system_user=no # Function SYSTEM_USER +func_sql_trim=no # Function TRIM +func_sql_upper=yes # Function UPPER +func_sql_user=yes # Function USER +func_where_between=yes # Function BETWEEN +func_where_eq_all=yes # Function = ALL +func_where_eq_any=yes # Function = ANY +func_where_eq_some=yes # Function = SOME +func_where_exists=yes # Function EXISTS +func_where_in_num=yes # Function IN on numbers +func_where_like=yes # Function LIKE +func_where_like_escape=yes # Function LIKE ESCAPE +func_where_match=no # Function MATCH +func_where_match_unique=no # Function MATCH UNIQUE +func_where_matches=no # Function MATCHES +func_where_not_between=yes # Function NOT BETWEEN +func_where_not_exists=yes # Function NOT EXISTS +func_where_not_like=yes # Function NOT LIKE +func_where_not_unique=no # Function NOT UNIQUE +func_where_unique=no # Function UNIQUE +functions=yes # Functions +group_by=yes # Group by +group_by_alias=no # Group by alias +group_by_null=yes # Group on column with null values +group_by_position=no # Group by position +group_distinct_functions=yes # Group functions with distinct +group_func_extra_bit_and=no # Group function BIT_AND +group_func_extra_bit_or=no # Group function BIT_OR +group_func_extra_count_distinct_list=no # Group function COUNT(DISTINCT expr,expr,...) +group_func_extra_std=no # Group function STD +group_func_extra_stddev=no # Group function STDDEV +group_func_extra_variance=no # Group function VARIANCE +group_func_sql_any=no # Group function ANY +group_func_sql_avg=yes # Group function AVG +group_func_sql_count_*=yes # Group function COUNT (*) +group_func_sql_count_column=yes # Group function COUNT column name +group_func_sql_count_distinct=yes # Group function COUNT(DISTINCT expr) +group_func_sql_every=no # Group function EVERY +group_func_sql_max=yes # Group function MAX on numbers +group_func_sql_max_str=yes # Group function MAX on strings +group_func_sql_min=yes # Group function MIN on numbers +group_func_sql_min_str=yes # Group function MIN on strings +group_func_sql_some=no # Group function SOME +group_func_sql_sum=yes # Group function SUM +group_functions=yes # Group functions +group_on_unused=yes # Group on unused column +has_true_false=no # TRUE and FALSE +having=yes # Having +having_with_alias=no # Having on alias +having_with_group=yes # Having with group function +hex_numbers=yes # hex numbers (0x41) +hex_strings=no # hex strings (x'1ace') +ignore_end_space=yes # Ignore end space in compare +index_in_create=no # index in create table +index_namespace=no # different namespace for index +index_parts=no # index on column part (extension) +inner_join=yes # inner join +insert_empty_string=no # insert empty string +insert_multi_value=no # INSERT with Value lists +insert_select=no # insert INTO ... SELECT ... +insert_with_set=no # INSERT with set syntax +intersect=no # intersect +intersect_all=no # intersect all +intersect_all_incompat=no # intersect all (incompatible lists) +intersect_incompat=no # intersect (incompatible lists) +join_tables=+64 # tables in join +left_outer_join=yes # left outer join +left_outer_join_using=no # left outer join using +like_with_column=no # column LIKE column +like_with_number=no # LIKE on numbers +lock_tables=no # lock table +logical_value=not supported # Value of logical operation (1=1) +max_big_expressions=1 # big expressions +max_char_size=32767 # max char() size +max_column_name=18 # column name length +max_columns=4759 # Columns in table +max_conditions=2944 # OR and AND in WHERE +max_expressions=+10000 # simple expressions +max_index=+64 # max index +max_index_name=31 # index name length +max_index_parts=16 # index parts +max_index_varchar_part_length=249 # index varchar part length +max_row_length=64744 # max table row length (without blobs) +max_row_length_with_null=64744 # table row length with nulls (without blobs) +max_select_alias_name=132 # select alias name length +max_stack_expression=165 # stacked expressions +max_table_alias_name=255 # table alias name length +max_table_name=31 # table name length +max_unique_index=+64 # unique indexes +max_varchar_size=10923 # max varchar() size +minus=no # minus +minus_incompat=no # minus (incompatible lists) +minus_neg=yes # Calculate 1--1 +multi_drop=no # many tables to drop table +multi_strings=no # Multiple line strings +multi_table_delete=no # DELETE FROM table1,table2... +multi_table_update=no # Update with many tables +natural_join=no # natural join +natural_join_incompat=no # natural join (incompatible lists) +natural_left_outer_join=no # natural left outer join +no_primary_key=yes # Tables without primary key +null_concat_expr=no # Is 'a' || NULL = NULL +null_in_index=yes # null in index +null_in_unique=no # null in unique index +null_num_expr=no # Is 1+NULL = NULL +nulls_in_unique=no # null combination in unique index +odbc_left_outer_join=no # left outer join odbc style +operating_system=Linux 2.4.4-64GB-SMP i686 # crash-me tested on +order_by=yes # Order by +order_by_alias=no # Order by alias +order_by_function=no # Order by function +order_by_position=yes # Order by position +order_by_remember_desc=no # Order by DESC is remembered +order_on_unused=yes # Order by on unused column +primary_key_in_create=yes # primary key in create table +psm_functions=no # PSM functions (ANSI SQL) +psm_modules=no # PSM modules (ANSI SQL) +psm_procedures=no # PSM procedures (ANSI SQL) +psm_trigger=no # Triggers (ANSI SQL) +query_size=65535 # query size +quote_ident_with_"=yes # " as identifier quote (ANSI SQL) +quote_ident_with_[=no # [] as identifier quote +quote_ident_with_`=no # ` as identifier quote +quote_with_"=no # Allows ' and " as string markers +recursive_subqueries=61 # recursive subqueries +remember_end_space=no # Remembers end space in char() +remember_end_space_varchar=yes # Remembers end space in varchar() +rename_table=no # rename table +right_outer_join=yes # right outer join +rowid=no # Type for row id +select_constants=yes # Select constants +select_limit=no # LIMIT number of rows +select_limit2=no # SELECT with LIMIT #,# +select_string_size=32767 # constant string size in SELECT +select_table_update=yes # Update with sub select +select_without_from=no # SELECT without FROM +server_version=6.0.1 # server version +simple_joins=yes # ANSI SQL simple joins +storage_of_float=round # Storage of float values +subqueries=yes # subqueries +table_alias=no # Table alias +table_name_case=yes # case independent table names +table_wildcard=yes # Select table_name.* +temporary_table=no # temporary tables +transactions=error # constant string size in where +truncate_table=no # truncate +type_extra_abstime=no # Type abstime +type_extra_bfile=no # Type bfile +type_extra_blob=yes # Type blob +type_extra_bool=no # Type bool +type_extra_box=no # Type box +type_extra_byte=no # Type byte +type_extra_char(1_arg)_binary=no # Type char(1 arg) binary +type_extra_cidr=no # Type cidr +type_extra_circle=no # Type circle +type_extra_clob=no # Type clob +type_extra_datetime=no # Type datetime +type_extra_double=no # Type double +type_extra_enum(1_arg)=no # Type enum(1 arg) +type_extra_float(2_arg)=no # Type float(2 arg) +type_extra_float4=no # Type float4 +type_extra_float8=no # Type float8 +type_extra_image=no # Type image +type_extra_inet=no # Type inet +type_extra_int(1_arg)_zerofill=no # Type int(1 arg) zerofill +type_extra_int1=no # Type int1 +type_extra_int2=no # Type int2 +type_extra_int3=no # Type int3 +type_extra_int4=no # Type int4 +type_extra_int8=no # Type int8 +type_extra_int_auto_increment=no # Type int not null auto_increment +type_extra_int_identity=no # Type int not null identity +type_extra_int_unsigned=no # Type int unsigned +type_extra_interval=no # Type interval +type_extra_line=no # Type line +type_extra_long=no # Type long +type_extra_long_raw=no # Type long raw +type_extra_long_varbinary=no # Type long varbinary +type_extra_long_varchar(1_arg)=no # Type long varchar(1 arg) +type_extra_lseg=no # Type lseg +type_extra_macaddr=no # Type macaddr +type_extra_mediumint=no # Type mediumint +type_extra_mediumtext=no # Type mediumtext +type_extra_middleint=no # Type middleint +type_extra_mlslabel=no # Type mlslabel +type_extra_money=no # Type money +type_extra_nclob=no # Type nclob +type_extra_number=no # Type number +type_extra_number(1_arg)=no # Type number(1 arg) +type_extra_number(2_arg)=no # Type number(2 arg) +type_extra_nvarchar2(1_arg)=no # Type nvarchar2(1 arg) +type_extra_path=no # Type path +type_extra_point=no # Type point +type_extra_polygon=no # Type polygon +type_extra_raw(1_arg)=no # Type raw(1 arg) +type_extra_reltime=no # Type reltime +type_extra_rowid=no # Type rowid +type_extra_serial=no # Type serial +type_extra_set(1_arg)=no # Type set(1 arg) +type_extra_smalldatetime=no # Type smalldatetime +type_extra_smallfloat=no # Type smallfloat +type_extra_smallmoney=no # Type smallmoney +type_extra_text=no # Type text +type_extra_text(1_arg)=no # Type text(1 arg) +type_extra_timespan=no # Type timespan +type_extra_uint=no # Type uint +type_extra_varchar2(1_arg)=no # Type varchar2(1 arg) +type_extra_year=no # Type year +type_odbc_bigint=no # Type bigint +type_odbc_binary(1_arg)=no # Type binary(1 arg) +type_odbc_datetime=no # Type datetime +type_odbc_tinyint=no # Type tinyint +type_odbc_varbinary(1_arg)=no # Type varbinary(1 arg) +type_sql_bit=no # Type bit +type_sql_bit(1_arg)=no # Type bit(1 arg) +type_sql_bit_varying(1_arg)=no # Type bit varying(1 arg) +type_sql_boolean=no # Type boolean +type_sql_char(1_arg)=yes # Type char(1 arg) +type_sql_char_varying(1_arg)=yes # Type char varying(1 arg) +type_sql_character(1_arg)=yes # Type character(1 arg) +type_sql_character_varying(1_arg)=yes # Type character varying(1 arg) +type_sql_date=no # Type date +type_sql_dec(2_arg)=yes # Type dec(2 arg) +type_sql_decimal(2_arg)=yes # Type decimal(2 arg) +type_sql_double_precision=yes # Type double precision +type_sql_float=yes # Type float +type_sql_float(1_arg)=yes # Type float(1 arg) +type_sql_int=yes # Type int +type_sql_integer=yes # Type integer +type_sql_interval_day=no # Type interval day +type_sql_interval_day_to_hour=no # Type interval day to hour +type_sql_interval_day_to_minute=no # Type interval day to minute +type_sql_interval_day_to_second=no # Type interval day to second +type_sql_interval_hour=no # Type interval hour +type_sql_interval_hour_to_minute=no # Type interval hour to minute +type_sql_interval_hour_to_second=no # Type interval hour to second +type_sql_interval_minute=no # Type interval minute +type_sql_interval_minute_to_second=no # Type interval minute to second +type_sql_interval_month=no # Type interval month +type_sql_interval_second=no # Type interval second +type_sql_interval_year=no # Type interval year +type_sql_interval_year_to_month=no # Type interval year to month +type_sql_national_char_varying(1_arg)=yes # Type national char varying(1 arg) +type_sql_national_character(1_arg)=yes # Type national character(1 arg) +type_sql_national_character_varying(1_arg)=yes # Type national character varying(1 arg) +type_sql_nchar(1_arg)=yes # Type nchar(1 arg) +type_sql_nchar_varying(1_arg)=yes # Type nchar varying(1 arg) +type_sql_numeric(2_arg)=yes # Type numeric(2 arg) +type_sql_real=yes # Type real +type_sql_smallint=yes # Type smallint +type_sql_time=no # Type time +type_sql_timestamp=yes # Type timestamp +type_sql_timestamp_with_time_zone=no # Type timestamp with time zone +type_sql_varchar(1_arg)=yes # Type varchar(1 arg) +union=yes # union +union_all=yes # union all +union_all_incompat=yes # union all (incompatible lists) +union_incompat=yes # union (incompatible lists) +unique_in_create=yes # unique in create table +unique_null_in_create=no # unique null in create +views=no # views +where_string_size=32767 # constant string size in where diff --git a/sql-bench/limits/interbase-dialect3.cfg b/sql-bench/limits/interbase-dialect3.cfg new file mode 100644 index 00000000000..fdc93098688 --- /dev/null +++ b/sql-bench/limits/interbase-dialect3.cfg @@ -0,0 +1,514 @@ +#This file is automaticly generated by crash-me 1.57 + +NEG=no # update of column= -column +Need_cast_for_null=no # Need to cast NULL for arithmetic +alter_add_col=yes # Alter table add column +alter_add_constraint=yes # Alter table add constraint +alter_add_foreign_key=yes # Alter table add foreign key +alter_add_multi_col=with add # Alter table add many columns +alter_add_primary_key=with constraint # Alter table add primary key +alter_add_unique=no # Alter table add unique +alter_alter_col=no # Alter table alter column default +alter_change_col=no # Alter table change column +alter_drop_col=no # Alter table drop column +alter_drop_constraint=yes # Alter table drop constraint +alter_drop_foreign_key=with drop constraint # Alter table drop foreign key +alter_drop_primary_key=no # Alter table drop primary key +alter_drop_unique=no # Alter table drop unique +alter_modify_col=no # Alter table modify column +alter_rename_table=no # Alter table rename table +atomic_updates=no # atomic updates +automatic_rowid=no # Automatic row id +binary_numbers=yes # binary numbers (0b1001) +binary_strings=no # binary strings (b'0110') +case_insensitive_strings=no # Case insensitive compare +char_is_space_filled=yes # char are space filled +column_alias=yes # Column alias +columns_in_order_by=37 # number of columns in order by +comment_#=no # # as comment +comment_--=no # -- as comment (ANSI) +comment_/**/=yes # /* */ as comment +comment_//=no # // as comment +compute=no # Compute +connections=1000 # Simultaneous connections (installation default) +constraint_check=no # Column constraints +constraint_check_table=no # Table constraints +constraint_null=no # NULL constraint (SyBase style) +crash_me_safe=yes # crash me safe +crash_me_version=1.57 # crash me version +create_default=yes # default value for column +create_default_func=no # default value function for column +create_if_not_exists=no # create table if not exists +create_index=yes # create index +create_schema=no # Create SCHEMA +create_table_select=no # create table from select +cross_join=no # cross join (same as from a,b) +date_infinity=no # Supports 'infinity dates +date_last=error # Supports 9999-12-31 dates +date_one=error # Supports 0001-01-01 dates +date_with_YY=no # Supports YY-MM-DD dates +date_zero=no # Supports 0000-00-00 dates +domains=no # Domains (ANSI SQL) +double_quotes=yes # Double '' as ' in strings +drop_if_exists=no # drop table if exists +drop_index=yes # drop index +drop_requires_cascade=no # drop table require cascade/restrict +drop_restrict=no # drop table with cascade/restrict +end_colon=yes # allows end ';' +except=no # except +except_all=no # except all +except_all_incompat=no # except all (incompatible lists) +except_incompat=no # except (incompatible lists) +float_int_expr=yes # mixing of integer and float in expression +foreign_key_syntax=no # foreign key syntax +full_outer_join=yes # full outer join +func_extra_!=no # Function NOT as '!' in SELECT +func_extra_%=no # Function MOD as % +func_extra_&=no # Function & (bitwise and) +func_extra_&&=no # Function AND as '&&' +func_extra_<>=no # Function <> in SELECT +func_extra_==no # Function = +func_extra_add_months=no # Function ADD_MONTHS +func_extra_and_or=no # Function AND and OR in SELECT +func_extra_ascii_char=no # Function ASCII_CHAR +func_extra_ascii_code=no # Function ASCII_CODE +func_extra_atn2=no # Function ATN2 +func_extra_auto_num2string=no # Function automatic num->string convert +func_extra_auto_string2num=no # Function automatic string->num convert +func_extra_between=no # Function BETWEEN in SELECT +func_extra_binary_shifts=no # Function << and >> (bitwise shifts) +func_extra_bit_count=no # Function BIT_COUNT +func_extra_ceil=no # Function CEIL +func_extra_charindex=no # Function CHARINDEX +func_extra_chr=no # Function CHR +func_extra_concat_as_+=no # Function concatenation with + +func_extra_concat_list=no # Function CONCAT(list) +func_extra_convert=no # Function CONVERT +func_extra_cosh=no # Function COSH +func_extra_date_format=no # Function DATE_FORMAT +func_extra_dateadd=no # Function DATEADD +func_extra_datediff=no # Function DATEDIFF +func_extra_datename=no # Function DATENAME +func_extra_datepart=no # Function DATEPART +func_extra_elt=no # Function ELT +func_extra_encrypt=no # Function ENCRYPT +func_extra_field=no # Function FIELD +func_extra_format=no # Function FORMAT +func_extra_from_days=no # Function FROM_DAYS +func_extra_from_unixtime=no # Function FROM_UNIXTIME +func_extra_getdate=no # Function GETDATE +func_extra_greatest=no # Function GREATEST +func_extra_if=no # Function IF +func_extra_in_num=no # Function IN on numbers in SELECT +func_extra_in_str=no # Function IN on strings in SELECT +func_extra_initcap=no # Function INITCAP +func_extra_instr=no # Function LOCATE as INSTR +func_extra_instr_oracle=no # Function INSTR (Oracle syntax) +func_extra_instrb=no # Function INSTRB +func_extra_interval=no # Function INTERVAL +func_extra_last_day=no # Function LAST_DAY +func_extra_last_insert_id=no # Function LAST_INSERT_ID +func_extra_least=no # Function LEAST +func_extra_lengthb=no # Function LENGTHB +func_extra_like=no # Function LIKE in SELECT +func_extra_like_escape=no # Function LIKE ESCAPE in SELECT +func_extra_ln=no # Function LN +func_extra_log(m_n)=no # Function LOG(m,n) +func_extra_logn=no # Function LOGN +func_extra_lpad=no # Function LPAD +func_extra_mdy=no # Function MDY +func_extra_mid=no # Function SUBSTRING as MID +func_extra_months_between=no # Function MONTHS_BETWEEN +func_extra_not=no # Function NOT in SELECT +func_extra_not_between=no # Function NOT BETWEEN in SELECT +func_extra_not_like=no # Function NOT LIKE in SELECT +func_extra_odbc_convert=no # Function ODBC CONVERT +func_extra_password=no # Function PASSWORD +func_extra_paste=no # Function PASTE +func_extra_patindex=no # Function PATINDEX +func_extra_period_add=no # Function PERIOD_ADD +func_extra_period_diff=no # Function PERIOD_DIFF +func_extra_pow=no # Function POW +func_extra_range=no # Function RANGE +func_extra_regexp=no # Function REGEXP in SELECT +func_extra_replicate=no # Function REPLICATE +func_extra_reverse=no # Function REVERSE +func_extra_root=no # Function ROOT +func_extra_round1=no # Function ROUND(1 arg) +func_extra_rpad=no # Function RPAD +func_extra_sec_to_time=no # Function SEC_TO_TIME +func_extra_sinh=no # Function SINH +func_extra_str=no # Function STR +func_extra_strcmp=no # Function STRCMP +func_extra_stuff=no # Function STUFF +func_extra_substrb=no # Function SUBSTRB +func_extra_substring_index=no # Function SUBSTRING_INDEX +func_extra_sysdate=no # Function SYSDATE +func_extra_tail=no # Function TAIL +func_extra_tanh=no # Function TANH +func_extra_time_to_sec=no # Function TIME_TO_SEC +func_extra_to_days=no # Function TO_DAYS +func_extra_translate=no # Function TRANSLATE +func_extra_trim_many_char=no # Function TRIM; Many char extension +func_extra_trim_substring=no # Function TRIM; Substring extension +func_extra_trunc=no # Function TRUNC +func_extra_uid=no # Function UID +func_extra_unix_timestamp=no # Function UNIX_TIMESTAMP +func_extra_userenv=no # Function USERENV +func_extra_version=no # Function VERSION +func_extra_weekday=no # Function WEEKDAY +func_extra_|=no # Function | (bitwise or) +func_extra_||=no # Function OR as '||' +func_extra_~*=no # Function ~* (case insensitive compare) +func_odbc_abs=no # Function ABS +func_odbc_acos=no # Function ACOS +func_odbc_ascii=no # Function ASCII +func_odbc_asin=no # Function ASIN +func_odbc_atan=no # Function ATAN +func_odbc_atan2=no # Function ATAN2 +func_odbc_ceiling=no # Function CEILING +func_odbc_char=no # Function CHAR +func_odbc_concat=no # Function CONCAT(2 arg) +func_odbc_cos=no # Function COS +func_odbc_cot=no # Function COT +func_odbc_curdate=no # Function CURDATE +func_odbc_curtime=no # Function CURTIME +func_odbc_database=no # Function DATABASE +func_odbc_dayname=no # Function DAYNAME +func_odbc_dayofmonth=no # Function DAYOFMONTH +func_odbc_dayofweek=no # Function DAYOFWEEK +func_odbc_dayofyear=no # Function DAYOFYEAR +func_odbc_degrees=no # Function DEGREES +func_odbc_difference=no # Function DIFFERENCE() +func_odbc_exp=no # Function EXP +func_odbc_floor=no # Function FLOOR +func_odbc_fn_left=no # Function ODBC syntax LEFT & RIGHT +func_odbc_hour=no # Function HOUR +func_odbc_hour_time=no # Function ANSI HOUR +func_odbc_ifnull=no # Function IFNULL +func_odbc_insert=no # Function INSERT +func_odbc_lcase=no # Function LCASE +func_odbc_left=no # Function LEFT +func_odbc_length=no # Function REAL LENGTH +func_odbc_length_without_space=no # Function ODBC LENGTH +func_odbc_locate_2=no # Function LOCATE(2 arg) +func_odbc_locate_3=no # Function LOCATE(3 arg) +func_odbc_log=no # Function LOG +func_odbc_log10=no # Function LOG10 +func_odbc_ltrim=no # Function LTRIM +func_odbc_minute=no # Function MINUTE +func_odbc_mod=no # Function MOD +func_odbc_month=no # Function MONTH +func_odbc_monthname=no # Function MONTHNAME +func_odbc_now=no # Function NOW +func_odbc_pi=no # Function PI +func_odbc_power=no # Function POWER +func_odbc_quarter=no # Function QUARTER +func_odbc_radians=no # Function RADIANS +func_odbc_rand=no # Function RAND +func_odbc_repeat=no # Function REPEAT +func_odbc_replace=no # Function REPLACE +func_odbc_right=no # Function RIGHT +func_odbc_round=no # Function ROUND(2 arg) +func_odbc_rtrim=no # Function RTRIM +func_odbc_second=no # Function SECOND +func_odbc_sign=no # Function SIGN +func_odbc_sin=no # Function SIN +func_odbc_soundex=no # Function SOUNDEX +func_odbc_space=no # Function SPACE +func_odbc_sqrt=no # Function SQRT +func_odbc_substring=no # Function ODBC SUBSTRING +func_odbc_tan=no # Function TAN +func_odbc_timestampadd=no # Function TIMESTAMPADD +func_odbc_timestampdiff=no # Function TIMESTAMPDIFF +func_odbc_truncate=no # Function TRUNCATE +func_odbc_ucase=no # Function UCASE +func_odbc_user()=no # Function USER() +func_odbc_week=no # Function WEEK +func_odbc_year=no # Function YEAR +func_sql_+=yes # Function +, -, * and / +func_sql_bit_length=no # Function BIT_LENGTH +func_sql_cast=yes # Function CAST +func_sql_char_length=no # Function CHAR_LENGTH +func_sql_char_length(constant)=no # Function CHAR_LENGTH(constant) +func_sql_character_length=no # Function CHARACTER_LENGTH +func_sql_coalesce=no # Function COALESCE +func_sql_concat_as_||=yes # Function concatenation with || +func_sql_current_date=yes # Function CURRENT_DATE +func_sql_current_time=yes # Function CURRENT_TIME +func_sql_current_timestamp=yes # Function CURRENT_TIMESTAMP +func_sql_current_user=no # Function CURRENT_USER +func_sql_extract_sql=yes # Function EXTRACT +func_sql_localtime=no # Function LOCALTIME +func_sql_localtimestamp=no # Function LOCALTIMESTAMP +func_sql_lower=no # Function LOWER +func_sql_nullif_num=no # Function NULLIF with numbers +func_sql_nullif_string=no # Function NULLIF with strings +func_sql_octet_length=no # Function OCTET_LENGTH +func_sql_position=no # Function POSITION +func_sql_searched_case=no # Function searched CASE +func_sql_session_user=no # Function SESSION_USER +func_sql_simple_case=no # Function simple CASE +func_sql_substring=no # Function ANSI SQL SUBSTRING +func_sql_system_user=no # Function SYSTEM_USER +func_sql_trim=no # Function TRIM +func_sql_upper=yes # Function UPPER +func_sql_user=yes # Function USER +func_where_between=yes # Function BETWEEN +func_where_eq_all=yes # Function = ALL +func_where_eq_any=yes # Function = ANY +func_where_eq_some=yes # Function = SOME +func_where_exists=yes # Function EXISTS +func_where_in_num=yes # Function IN on numbers +func_where_like=yes # Function LIKE +func_where_like_escape=yes # Function LIKE ESCAPE +func_where_match=no # Function MATCH +func_where_match_unique=no # Function MATCH UNIQUE +func_where_matches=no # Function MATCHES +func_where_not_between=yes # Function NOT BETWEEN +func_where_not_exists=yes # Function NOT EXISTS +func_where_not_like=yes # Function NOT LIKE +func_where_not_unique=no # Function NOT UNIQUE +func_where_unique=no # Function UNIQUE +functions=yes # Functions +group_by=yes # Group by +group_by_alias=no # Group by alias +group_by_null=yes # Group on column with null values +group_by_position=no # Group by position +group_distinct_functions=yes # Group functions with distinct +group_func_extra_bit_and=no # Group function BIT_AND +group_func_extra_bit_or=no # Group function BIT_OR +group_func_extra_count_distinct_list=no # Group function COUNT(DISTINCT expr,expr,...) +group_func_extra_std=no # Group function STD +group_func_extra_stddev=no # Group function STDDEV +group_func_extra_variance=no # Group function VARIANCE +group_func_sql_any=no # Group function ANY +group_func_sql_avg=yes # Group function AVG +group_func_sql_count_*=yes # Group function COUNT (*) +group_func_sql_count_column=yes # Group function COUNT column name +group_func_sql_count_distinct=yes # Group function COUNT(DISTINCT expr) +group_func_sql_every=no # Group function EVERY +group_func_sql_max=yes # Group function MAX on numbers +group_func_sql_max_str=yes # Group function MAX on strings +group_func_sql_min=yes # Group function MIN on numbers +group_func_sql_min_str=yes # Group function MIN on strings +group_func_sql_some=no # Group function SOME +group_func_sql_sum=yes # Group function SUM +group_functions=yes # Group functions +group_on_unused=yes # Group on unused column +has_true_false=no # TRUE and FALSE +having=yes # Having +having_with_alias=no # Having on alias +having_with_group=yes # Having with group function +hex_numbers=yes # hex numbers (0x41) +hex_strings=no # hex strings (x'1ace') +ignore_end_space=yes # Ignore end space in compare +index_in_create=no # index in create table +index_namespace=no # different namespace for index +index_parts=no # index on column part (extension) +inner_join=yes # inner join +insert_empty_string=no # insert empty string +insert_multi_value=no # INSERT with Value lists +insert_select=no # insert INTO ... SELECT ... +insert_with_set=no # INSERT with set syntax +intersect=no # intersect +intersect_all=no # intersect all +intersect_all_incompat=no # intersect all (incompatible lists) +intersect_incompat=no # intersect (incompatible lists) +join_tables=+64 # tables in join +left_outer_join=yes # left outer join +left_outer_join_using=no # left outer join using +like_with_column=no # column LIKE column +like_with_number=no # LIKE on numbers +lock_tables=no # lock table +logical_value=not supported # Value of logical operation (1=1) +max_big_expressions=1 # big expressions +max_char_size=32767 # max char() size +max_columns=4759 # Columns in table +max_conditions=5888 # OR and AND in WHERE +max_expressions=+10000 # simple expressions +max_index=+64 # max index +max_index_name=31 # index name length +max_index_parts=16 # index parts +max_index_varchar_part_length=249 # index varchar part length +max_row_length=64744 # max table row length (without blobs) +max_row_length_with_null=64744 # table row length with nulls (without blobs) +max_select_alias_name=132 # select alias name length +max_stack_expression=165 # stacked expressions +max_table_alias_name=255 # table alias name length +max_table_name=31 # table name length +max_unique_index=+64 # unique indexes +max_varchar_size=10923 # max varchar() size +minus=no # minus +minus_incompat=no # minus (incompatible lists) +minus_neg=yes # Calculate 1--1 +multi_drop=no # many tables to drop table +multi_strings=no # Multiple line strings +multi_table_delete=no # DELETE FROM table1,table2... +multi_table_update=no # Update with many tables +natural_join=no # natural join +natural_join_incompat=no # natural join (incompatible lists) +natural_left_outer_join=no # natural left outer join +no_primary_key=yes # Tables without primary key +null_concat_expr=no # Is 'a' || NULL = NULL +null_in_index=yes # null in index +null_in_unique=no # null in unique index +null_num_expr=no # Is 1+NULL = NULL +nulls_in_unique=no # null combination in unique index +odbc_left_outer_join=no # left outer join odbc style +operating_system=Linux 2.4.4-64GB-SMP i686 # crash-me tested on +order_by=yes # Order by +order_by_alias=no # Order by alias +order_by_function=no # Order by function +order_by_position=yes # Order by position +order_by_remember_desc=no # Order by DESC is remembered +order_on_unused=yes # Order by on unused column +primary_key_in_create=yes # primary key in create table +psm_functions=no # PSM functions (ANSI SQL) +psm_modules=no # PSM modules (ANSI SQL) +psm_procedures=no # PSM procedures (ANSI SQL) +psm_trigger=no # Triggers (ANSI SQL) +query_size=65535 # query size +quote_ident_with_"=yes # " as identifier quote (ANSI SQL) +quote_ident_with_[=no # [] as identifier quote +quote_ident_with_`=no # ` as identifier quote +quote_with_"=no # Allows ' and " as string markers +recursive_subqueries=61 # recursive subqueries +remember_end_space=no # Remembers end space in char() +remember_end_space_varchar=yes # Remembers end space in varchar() +rename_table=no # rename table +right_outer_join=yes # right outer join +rowid=no # Type for row id +select_constants=yes # Select constants +select_limit=no # LIMIT number of rows +select_limit2=no # SELECT with LIMIT #,# +select_string_size=32767 # constant string size in SELECT +select_table_update=yes # Update with sub select +select_without_from=no # SELECT without FROM +server_version=6.0.1 # server version +simple_joins=yes # ANSI SQL simple joins +storage_of_float=round # Storage of float values +subqueries=yes # subqueries +table_alias=no # Table alias +table_name_case=yes # case independent table names +table_wildcard=yes # Select table_name.* +temporary_table=no # temporary tables +transactions=error # constant string size in where +truncate_table=no # truncate +type_extra_abstime=no # Type abstime +type_extra_bfile=no # Type bfile +type_extra_blob=yes # Type blob +type_extra_bool=no # Type bool +type_extra_box=no # Type box +type_extra_byte=no # Type byte +type_extra_char(1_arg)_binary=no # Type char(1 arg) binary +type_extra_cidr=no # Type cidr +type_extra_circle=no # Type circle +type_extra_clob=no # Type clob +type_extra_datetime=no # Type datetime +type_extra_double=no # Type double +type_extra_enum(1_arg)=no # Type enum(1 arg) +type_extra_float(2_arg)=no # Type float(2 arg) +type_extra_float4=no # Type float4 +type_extra_float8=no # Type float8 +type_extra_image=no # Type image +type_extra_inet=no # Type inet +type_extra_int(1_arg)_zerofill=no # Type int(1 arg) zerofill +type_extra_int1=no # Type int1 +type_extra_int2=no # Type int2 +type_extra_int3=no # Type int3 +type_extra_int4=no # Type int4 +type_extra_int8=no # Type int8 +type_extra_int_auto_increment=no # Type int not null auto_increment +type_extra_int_identity=no # Type int not null identity +type_extra_int_unsigned=no # Type int unsigned +type_extra_interval=no # Type interval +type_extra_line=no # Type line +type_extra_long=no # Type long +type_extra_long_raw=no # Type long raw +type_extra_long_varbinary=no # Type long varbinary +type_extra_long_varchar(1_arg)=no # Type long varchar(1 arg) +type_extra_lseg=no # Type lseg +type_extra_macaddr=no # Type macaddr +type_extra_mediumint=no # Type mediumint +type_extra_mediumtext=no # Type mediumtext +type_extra_middleint=no # Type middleint +type_extra_mlslabel=no # Type mlslabel +type_extra_money=no # Type money +type_extra_nclob=no # Type nclob +type_extra_number=no # Type number +type_extra_number(1_arg)=no # Type number(1 arg) +type_extra_number(2_arg)=no # Type number(2 arg) +type_extra_nvarchar2(1_arg)=no # Type nvarchar2(1 arg) +type_extra_path=no # Type path +type_extra_point=no # Type point +type_extra_polygon=no # Type polygon +type_extra_raw(1_arg)=no # Type raw(1 arg) +type_extra_reltime=no # Type reltime +type_extra_rowid=no # Type rowid +type_extra_serial=no # Type serial +type_extra_set(1_arg)=no # Type set(1 arg) +type_extra_smalldatetime=no # Type smalldatetime +type_extra_smallfloat=no # Type smallfloat +type_extra_smallmoney=no # Type smallmoney +type_extra_text=no # Type text +type_extra_text(1_arg)=no # Type text(1 arg) +type_extra_timespan=no # Type timespan +type_extra_uint=no # Type uint +type_extra_varchar2(1_arg)=no # Type varchar2(1 arg) +type_extra_year=no # Type year +type_odbc_bigint=no # Type bigint +type_odbc_binary(1_arg)=no # Type binary(1 arg) +type_odbc_datetime=no # Type datetime +type_odbc_tinyint=no # Type tinyint +type_odbc_varbinary(1_arg)=no # Type varbinary(1 arg) +type_sql_=no # Type +type_sql_bit=no # Type bit +type_sql_bit(1_arg)=no # Type bit(1 arg) +type_sql_bit_varying(1_arg)=no # Type bit varying(1 arg) +type_sql_boolean=no # Type boolean +type_sql_char(1_arg)=yes # Type char(1 arg) +type_sql_char_varying(1_arg)=yes # Type char varying(1 arg) +type_sql_character(1_arg)=yes # Type character(1 arg) +type_sql_character_varying(1_arg)=yes # Type character varying(1 arg) +type_sql_date=yes # Type date +type_sql_dec(2_arg)=yes # Type dec(2 arg) +type_sql_decimal(2_arg)=yes # Type decimal(2 arg) +type_sql_double_precision=yes # Type double precision +type_sql_float=yes # Type float +type_sql_float(1_arg)=yes # Type float(1 arg) +type_sql_int=yes # Type int +type_sql_integer=yes # Type integer +type_sql_interval_day=no # Type interval day +type_sql_interval_day_to_hour=no # Type interval day to hour +type_sql_interval_day_to_minute=no # Type interval day to minute +type_sql_interval_day_to_second=no # Type interval day to second +type_sql_interval_hour=no # Type interval hour +type_sql_interval_hour_to_minute=no # Type interval hour to minute +type_sql_interval_hour_to_second=no # Type interval hour to second +type_sql_interval_minute=no # Type interval minute +type_sql_interval_minute_to_second=no # Type interval minute to second +type_sql_interval_month=no # Type interval month +type_sql_interval_second=no # Type interval second +type_sql_interval_year=no # Type interval year +type_sql_interval_year_to_month=no # Type interval year to month +type_sql_national_char_varying(1_arg)=yes # Type national char varying(1 arg) +type_sql_national_character(1_arg)=yes # Type national character(1 arg) +type_sql_national_character_varying(1_arg)=yes # Type national character varying(1 arg) +type_sql_nchar(1_arg)=yes # Type nchar(1 arg) +type_sql_nchar_varying(1_arg)=yes # Type nchar varying(1 arg) +type_sql_numeric(2_arg)=yes # Type numeric(2 arg) +type_sql_real=yes # Type real +type_sql_smallint=yes # Type smallint +type_sql_time=yes # Type time +type_sql_timestamp=yes # Type timestamp +type_sql_timestamp_with_time_zone=no # Type timestamp with time zone +type_sql_varchar(1_arg)=yes # Type varchar(1 arg) +union=yes # union +union_all=yes # union all +union_all_incompat=yes # union all (incompatible lists) +union_incompat=yes # union (incompatible lists) +unique_in_create=yes # unique in create table +unique_null_in_create=no # unique null in create +views=no # views +where_string_size=32767 # constant string size in where diff --git a/sql-bench/limits/interbase-superserver.cfg b/sql-bench/limits/interbase-superserver.cfg new file mode 100644 index 00000000000..87da0d0633c --- /dev/null +++ b/sql-bench/limits/interbase-superserver.cfg @@ -0,0 +1,514 @@ +#This file is automaticly generated by crash-me 1.57 + +NEG=no # update of column= -column +Need_cast_for_null=no # Need to cast NULL for arithmetic +alter_add_col=yes # Alter table add column +alter_add_constraint=yes # Alter table add constraint +alter_add_foreign_key=yes # Alter table add foreign key +alter_add_multi_col=with add # Alter table add many columns +alter_add_primary_key=with constraint # Alter table add primary key +alter_add_unique=no # Alter table add unique +alter_alter_col=no # Alter table alter column default +alter_change_col=no # Alter table change column +alter_drop_col=no # Alter table drop column +alter_drop_constraint=yes # Alter table drop constraint +alter_drop_foreign_key=with drop constraint # Alter table drop foreign key +alter_drop_primary_key=no # Alter table drop primary key +alter_drop_unique=no # Alter table drop unique +alter_modify_col=no # Alter table modify column +alter_rename_table=no # Alter table rename table +atomic_updates=no # atomic updates +automatic_rowid=no # Automatic row id +binary_numbers=yes # binary numbers (0b1001) +binary_strings=no # binary strings (b'0110') +case_insensitive_strings=no # Case insensitive compare +char_is_space_filled=yes # char are space filled +column_alias=yes # Column alias +columns_in_order_by=37 # number of columns in order by +comment_#=no # # as comment +comment_--=no # -- as comment (ANSI) +comment_/**/=yes # /* */ as comment +comment_//=no # // as comment +compute=no # Compute +connections=395 # Simultaneous connections (installation default) +constraint_check=no # Column constraints +constraint_check_table=no # Table constraints +constraint_null=no # NULL constraint (SyBase style) +crash_me_safe=no # crash me safe +crash_me_version=1.57 # crash me version +create_default=yes # default value for column +create_default_func=no # default value function for column +create_if_not_exists=no # create table if not exists +create_index=yes # create index +create_schema=no # Create SCHEMA +create_table_select=no # create table from select +cross_join=no # cross join (same as from a,b) +date_infinity=no # Supports 'infinity dates +date_last=error # Supports 9999-12-31 dates +date_one=error # Supports 0001-01-01 dates +date_with_YY=no # Supports YY-MM-DD dates +date_zero=no # Supports 0000-00-00 dates +domains=no # Domains (ANSI SQL) +double_quotes=yes # Double '' as ' in strings +drop_if_exists=no # drop table if exists +drop_index=yes # drop index +drop_requires_cascade=no # drop table require cascade/restrict +drop_restrict=no # drop table with cascade/restrict +end_colon=yes # allows end ';' +except=no # except +except_all=no # except all +except_all_incompat=no # except all (incompatible lists) +except_incompat=no # except (incompatible lists) +float_int_expr=yes # mixing of integer and float in expression +foreign_key_syntax=no # foreign key syntax +full_outer_join=yes # full outer join +func_extra_!=no # Function NOT as '!' in SELECT +func_extra_%=no # Function MOD as % +func_extra_&=no # Function & (bitwise and) +func_extra_&&=no # Function AND as '&&' +func_extra_<>=no # Function <> in SELECT +func_extra_==no # Function = +func_extra_add_months=no # Function ADD_MONTHS +func_extra_and_or=no # Function AND and OR in SELECT +func_extra_ascii_char=no # Function ASCII_CHAR +func_extra_ascii_code=no # Function ASCII_CODE +func_extra_atn2=no # Function ATN2 +func_extra_auto_num2string=no # Function automatic num->string convert +func_extra_auto_string2num=no # Function automatic string->num convert +func_extra_between=no # Function BETWEEN in SELECT +func_extra_binary_shifts=no # Function << and >> (bitwise shifts) +func_extra_bit_count=no # Function BIT_COUNT +func_extra_ceil=no # Function CEIL +func_extra_charindex=no # Function CHARINDEX +func_extra_chr=no # Function CHR +func_extra_concat_as_+=no # Function concatenation with + +func_extra_concat_list=no # Function CONCAT(list) +func_extra_convert=no # Function CONVERT +func_extra_cosh=no # Function COSH +func_extra_date_format=no # Function DATE_FORMAT +func_extra_dateadd=no # Function DATEADD +func_extra_datediff=no # Function DATEDIFF +func_extra_datename=no # Function DATENAME +func_extra_datepart=no # Function DATEPART +func_extra_elt=no # Function ELT +func_extra_encrypt=no # Function ENCRYPT +func_extra_field=no # Function FIELD +func_extra_format=no # Function FORMAT +func_extra_from_days=no # Function FROM_DAYS +func_extra_from_unixtime=no # Function FROM_UNIXTIME +func_extra_getdate=no # Function GETDATE +func_extra_greatest=no # Function GREATEST +func_extra_if=no # Function IF +func_extra_in_num=no # Function IN on numbers in SELECT +func_extra_in_str=no # Function IN on strings in SELECT +func_extra_initcap=no # Function INITCAP +func_extra_instr=no # Function LOCATE as INSTR +func_extra_instr_oracle=no # Function INSTR (Oracle syntax) +func_extra_instrb=no # Function INSTRB +func_extra_interval=no # Function INTERVAL +func_extra_last_day=no # Function LAST_DAY +func_extra_last_insert_id=no # Function LAST_INSERT_ID +func_extra_least=no # Function LEAST +func_extra_lengthb=no # Function LENGTHB +func_extra_like=no # Function LIKE in SELECT +func_extra_like_escape=no # Function LIKE ESCAPE in SELECT +func_extra_ln=no # Function LN +func_extra_log(m_n)=no # Function LOG(m,n) +func_extra_logn=no # Function LOGN +func_extra_lpad=no # Function LPAD +func_extra_mdy=no # Function MDY +func_extra_mid=no # Function SUBSTRING as MID +func_extra_months_between=no # Function MONTHS_BETWEEN +func_extra_not=no # Function NOT in SELECT +func_extra_not_between=no # Function NOT BETWEEN in SELECT +func_extra_not_like=no # Function NOT LIKE in SELECT +func_extra_odbc_convert=no # Function ODBC CONVERT +func_extra_password=no # Function PASSWORD +func_extra_paste=no # Function PASTE +func_extra_patindex=no # Function PATINDEX +func_extra_period_add=no # Function PERIOD_ADD +func_extra_period_diff=no # Function PERIOD_DIFF +func_extra_pow=no # Function POW +func_extra_range=no # Function RANGE +func_extra_regexp=no # Function REGEXP in SELECT +func_extra_replicate=no # Function REPLICATE +func_extra_reverse=no # Function REVERSE +func_extra_root=no # Function ROOT +func_extra_round1=no # Function ROUND(1 arg) +func_extra_rpad=no # Function RPAD +func_extra_sec_to_time=no # Function SEC_TO_TIME +func_extra_sinh=no # Function SINH +func_extra_str=no # Function STR +func_extra_strcmp=no # Function STRCMP +func_extra_stuff=no # Function STUFF +func_extra_substrb=no # Function SUBSTRB +func_extra_substring_index=no # Function SUBSTRING_INDEX +func_extra_sysdate=no # Function SYSDATE +func_extra_tail=no # Function TAIL +func_extra_tanh=no # Function TANH +func_extra_time_to_sec=no # Function TIME_TO_SEC +func_extra_to_days=no # Function TO_DAYS +func_extra_translate=no # Function TRANSLATE +func_extra_trim_many_char=no # Function TRIM; Many char extension +func_extra_trim_substring=no # Function TRIM; Substring extension +func_extra_trunc=no # Function TRUNC +func_extra_uid=no # Function UID +func_extra_unix_timestamp=no # Function UNIX_TIMESTAMP +func_extra_userenv=no # Function USERENV +func_extra_version=no # Function VERSION +func_extra_weekday=no # Function WEEKDAY +func_extra_|=no # Function | (bitwise or) +func_extra_||=no # Function OR as '||' +func_extra_~*=no # Function ~* (case insensitive compare) +func_odbc_abs=no # Function ABS +func_odbc_acos=no # Function ACOS +func_odbc_ascii=no # Function ASCII +func_odbc_asin=no # Function ASIN +func_odbc_atan=no # Function ATAN +func_odbc_atan2=no # Function ATAN2 +func_odbc_ceiling=no # Function CEILING +func_odbc_char=no # Function CHAR +func_odbc_concat=no # Function CONCAT(2 arg) +func_odbc_cos=no # Function COS +func_odbc_cot=no # Function COT +func_odbc_curdate=no # Function CURDATE +func_odbc_curtime=no # Function CURTIME +func_odbc_database=no # Function DATABASE +func_odbc_dayname=no # Function DAYNAME +func_odbc_dayofmonth=no # Function DAYOFMONTH +func_odbc_dayofweek=no # Function DAYOFWEEK +func_odbc_dayofyear=no # Function DAYOFYEAR +func_odbc_degrees=no # Function DEGREES +func_odbc_difference=no # Function DIFFERENCE() +func_odbc_exp=no # Function EXP +func_odbc_floor=no # Function FLOOR +func_odbc_fn_left=no # Function ODBC syntax LEFT & RIGHT +func_odbc_hour=no # Function HOUR +func_odbc_hour_time=no # Function ANSI HOUR +func_odbc_ifnull=no # Function IFNULL +func_odbc_insert=no # Function INSERT +func_odbc_lcase=no # Function LCASE +func_odbc_left=no # Function LEFT +func_odbc_length=no # Function REAL LENGTH +func_odbc_length_without_space=no # Function ODBC LENGTH +func_odbc_locate_2=no # Function LOCATE(2 arg) +func_odbc_locate_3=no # Function LOCATE(3 arg) +func_odbc_log=no # Function LOG +func_odbc_log10=no # Function LOG10 +func_odbc_ltrim=no # Function LTRIM +func_odbc_minute=no # Function MINUTE +func_odbc_mod=no # Function MOD +func_odbc_month=no # Function MONTH +func_odbc_monthname=no # Function MONTHNAME +func_odbc_now=no # Function NOW +func_odbc_pi=no # Function PI +func_odbc_power=no # Function POWER +func_odbc_quarter=no # Function QUARTER +func_odbc_radians=no # Function RADIANS +func_odbc_rand=no # Function RAND +func_odbc_repeat=no # Function REPEAT +func_odbc_replace=no # Function REPLACE +func_odbc_right=no # Function RIGHT +func_odbc_round=no # Function ROUND(2 arg) +func_odbc_rtrim=no # Function RTRIM +func_odbc_second=no # Function SECOND +func_odbc_sign=no # Function SIGN +func_odbc_sin=no # Function SIN +func_odbc_soundex=no # Function SOUNDEX +func_odbc_space=no # Function SPACE +func_odbc_sqrt=no # Function SQRT +func_odbc_substring=no # Function ODBC SUBSTRING +func_odbc_tan=no # Function TAN +func_odbc_timestampadd=no # Function TIMESTAMPADD +func_odbc_timestampdiff=no # Function TIMESTAMPDIFF +func_odbc_truncate=no # Function TRUNCATE +func_odbc_ucase=no # Function UCASE +func_odbc_user()=no # Function USER() +func_odbc_week=no # Function WEEK +func_odbc_year=no # Function YEAR +func_sql_+=yes # Function +, -, * and / +func_sql_bit_length=no # Function BIT_LENGTH +func_sql_cast=yes # Function CAST +func_sql_char_length=no # Function CHAR_LENGTH +func_sql_char_length(constant)=no # Function CHAR_LENGTH(constant) +func_sql_character_length=no # Function CHARACTER_LENGTH +func_sql_coalesce=no # Function COALESCE +func_sql_concat_as_||=yes # Function concatenation with || +func_sql_current_date=yes # Function CURRENT_DATE +func_sql_current_time=yes # Function CURRENT_TIME +func_sql_current_timestamp=yes # Function CURRENT_TIMESTAMP +func_sql_current_user=no # Function CURRENT_USER +func_sql_extract_sql=yes # Function EXTRACT +func_sql_localtime=no # Function LOCALTIME +func_sql_localtimestamp=no # Function LOCALTIMESTAMP +func_sql_lower=no # Function LOWER +func_sql_nullif_num=no # Function NULLIF with numbers +func_sql_nullif_string=no # Function NULLIF with strings +func_sql_octet_length=no # Function OCTET_LENGTH +func_sql_position=no # Function POSITION +func_sql_searched_case=no # Function searched CASE +func_sql_session_user=no # Function SESSION_USER +func_sql_simple_case=no # Function simple CASE +func_sql_substring=no # Function ANSI SQL SUBSTRING +func_sql_system_user=no # Function SYSTEM_USER +func_sql_trim=no # Function TRIM +func_sql_upper=yes # Function UPPER +func_sql_user=yes # Function USER +func_where_between=yes # Function BETWEEN +func_where_eq_all=yes # Function = ALL +func_where_eq_any=yes # Function = ANY +func_where_eq_some=yes # Function = SOME +func_where_exists=yes # Function EXISTS +func_where_in_num=yes # Function IN on numbers +func_where_like=yes # Function LIKE +func_where_like_escape=yes # Function LIKE ESCAPE +func_where_match=no # Function MATCH +func_where_match_unique=no # Function MATCH UNIQUE +func_where_matches=no # Function MATCHES +func_where_not_between=yes # Function NOT BETWEEN +func_where_not_exists=yes # Function NOT EXISTS +func_where_not_like=yes # Function NOT LIKE +func_where_not_unique=no # Function NOT UNIQUE +func_where_unique=no # Function UNIQUE +functions=yes # Functions +group_by=yes # Group by +group_by_alias=no # Group by alias +group_by_null=yes # Group on column with null values +group_by_position=no # Group by position +group_distinct_functions=yes # Group functions with distinct +group_func_extra_bit_and=no # Group function BIT_AND +group_func_extra_bit_or=no # Group function BIT_OR +group_func_extra_count_distinct_list=no # Group function COUNT(DISTINCT expr,expr,...) +group_func_extra_std=no # Group function STD +group_func_extra_stddev=no # Group function STDDEV +group_func_extra_variance=no # Group function VARIANCE +group_func_sql_any=no # Group function ANY +group_func_sql_avg=yes # Group function AVG +group_func_sql_count_*=yes # Group function COUNT (*) +group_func_sql_count_column=yes # Group function COUNT column name +group_func_sql_count_distinct=yes # Group function COUNT(DISTINCT expr) +group_func_sql_every=no # Group function EVERY +group_func_sql_max=yes # Group function MAX on numbers +group_func_sql_max_str=yes # Group function MAX on strings +group_func_sql_min=yes # Group function MIN on numbers +group_func_sql_min_str=yes # Group function MIN on strings +group_func_sql_some=no # Group function SOME +group_func_sql_sum=yes # Group function SUM +group_functions=yes # Group functions +group_on_unused=yes # Group on unused column +has_true_false=no # TRUE and FALSE +having=yes # Having +having_with_alias=no # Having on alias +having_with_group=yes # Having with group function +hex_numbers=yes # hex numbers (0x41) +hex_strings=no # hex strings (x'1ace') +ignore_end_space=yes # Ignore end space in compare +index_in_create=no # index in create table +index_namespace=no # different namespace for index +index_parts=no # index on column part (extension) +inner_join=yes # inner join +insert_empty_string=no # insert empty string +insert_multi_value=no # INSERT with Value lists +insert_select=no # insert INTO ... SELECT ... +insert_with_set=no # INSERT with set syntax +intersect=no # intersect +intersect_all=no # intersect all +intersect_all_incompat=no # intersect all (incompatible lists) +intersect_incompat=no # intersect (incompatible lists) +join_tables=+64 # tables in join +left_outer_join=yes # left outer join +left_outer_join_using=no # left outer join using +like_with_column=no # column LIKE column +like_with_number=no # LIKE on numbers +lock_tables=no # lock table +logical_value=not supported # Value of logical operation (1=1) +max_big_expressions=1 # big expressions +max_char_size=32745 # max char() size +max_column_name=18 # column name length +max_columns=4759 # Columns in table +max_conditions=441504 # OR and AND in WHERE +max_expressions=5486 # simple expressions +max_index=+64 # max index +max_index_name=31 # index name length +max_index_parts=16 # index parts +max_index_varchar_part_length=218 # index varchar part length +max_row_length=64744 # max table row length (without blobs) +max_row_length_with_null=64744 # table row length with nulls (without blobs) +max_select_alias_name=132 # select alias name length +max_stack_expression=165 # stacked expressions +max_table_alias_name=255 # table alias name length +max_table_name=31 # table name length +max_unique_index=+64 # unique indexes +max_varchar_size=32745 # max varchar() size +minus=no # minus +minus_incompat=no # minus (incompatible lists) +minus_neg=yes # Calculate 1--1 +multi_drop=no # many tables to drop table +multi_strings=no # Multiple line strings +multi_table_delete=no # DELETE FROM table1,table2... +multi_table_update=no # Update with many tables +natural_join=no # natural join +natural_join_incompat=no # natural join (incompatible lists) +natural_left_outer_join=no # natural left outer join +no_primary_key=yes # Tables without primary key +null_concat_expr=no # Is 'a' || NULL = NULL +null_in_index=yes # null in index +null_in_unique=no # null in unique index +null_num_expr=no # Is 1+NULL = NULL +nulls_in_unique=no # null combination in unique index +odbc_left_outer_join=no # left outer join odbc style +operating_system=Linux 2.4.4-64GB-SMP i686 # crash-me tested on +order_by=yes # Order by +order_by_alias=no # Order by alias +order_by_function=no # Order by function +order_by_position=yes # Order by position +order_by_remember_desc=no # Order by DESC is remembered +order_on_unused=yes # Order by on unused column +primary_key_in_create=yes # primary key in create table +psm_functions=no # PSM functions (ANSI SQL) +psm_modules=no # PSM modules (ANSI SQL) +psm_procedures=no # PSM procedures (ANSI SQL) +psm_trigger=no # Triggers (ANSI SQL) +query_size=16777216 # query size +quote_ident_with_"=yes # " as identifier quote (ANSI SQL) +quote_ident_with_[=no # [] as identifier quote +quote_ident_with_`=no # ` as identifier quote +quote_with_"=no # Allows ' and " as string markers +recursive_subqueries=61 # recursive subqueries +remember_end_space=no # Remembers end space in char() +remember_end_space_varchar=yes # Remembers end space in varchar() +rename_table=no # rename table +right_outer_join=yes # right outer join +rowid=no # Type for row id +select_constants=yes # Select constants +select_limit=no # LIMIT number of rows +select_limit2=no # SELECT with LIMIT #,# +select_string_size=32767 # constant string size in SELECT +select_table_update=yes # Update with sub select +select_without_from=no # SELECT without FROM +server_version=6.0.1 # server version +simple_joins=yes # ANSI SQL simple joins +storage_of_float=round # Storage of float values +subqueries=yes # subqueries +table_alias=no # Table alias +table_name_case=yes # case independent table names +table_wildcard=yes # Select table_name.* +temporary_table=no # temporary tables +transactions=error # constant string size in where +truncate_table=no # truncate +type_extra_abstime=no # Type abstime +type_extra_bfile=no # Type bfile +type_extra_blob=yes # Type blob +type_extra_bool=no # Type bool +type_extra_box=no # Type box +type_extra_byte=no # Type byte +type_extra_char(1_arg)_binary=no # Type char(1 arg) binary +type_extra_cidr=no # Type cidr +type_extra_circle=no # Type circle +type_extra_clob=no # Type clob +type_extra_datetime=no # Type datetime +type_extra_double=no # Type double +type_extra_enum(1_arg)=no # Type enum(1 arg) +type_extra_float(2_arg)=no # Type float(2 arg) +type_extra_float4=no # Type float4 +type_extra_float8=no # Type float8 +type_extra_image=no # Type image +type_extra_inet=no # Type inet +type_extra_int(1_arg)_zerofill=no # Type int(1 arg) zerofill +type_extra_int1=no # Type int1 +type_extra_int2=no # Type int2 +type_extra_int3=no # Type int3 +type_extra_int4=no # Type int4 +type_extra_int8=no # Type int8 +type_extra_int_auto_increment=no # Type int not null auto_increment +type_extra_int_identity=no # Type int not null identity +type_extra_int_unsigned=no # Type int unsigned +type_extra_interval=no # Type interval +type_extra_line=no # Type line +type_extra_long=no # Type long +type_extra_long_raw=no # Type long raw +type_extra_long_varbinary=no # Type long varbinary +type_extra_long_varchar(1_arg)=no # Type long varchar(1 arg) +type_extra_lseg=no # Type lseg +type_extra_macaddr=no # Type macaddr +type_extra_mediumint=no # Type mediumint +type_extra_mediumtext=no # Type mediumtext +type_extra_middleint=no # Type middleint +type_extra_mlslabel=no # Type mlslabel +type_extra_money=no # Type money +type_extra_nclob=no # Type nclob +type_extra_number=no # Type number +type_extra_number(1_arg)=no # Type number(1 arg) +type_extra_number(2_arg)=no # Type number(2 arg) +type_extra_nvarchar2(1_arg)=no # Type nvarchar2(1 arg) +type_extra_path=no # Type path +type_extra_point=no # Type point +type_extra_polygon=no # Type polygon +type_extra_raw(1_arg)=no # Type raw(1 arg) +type_extra_reltime=no # Type reltime +type_extra_rowid=no # Type rowid +type_extra_serial=no # Type serial +type_extra_set(1_arg)=no # Type set(1 arg) +type_extra_smalldatetime=no # Type smalldatetime +type_extra_smallfloat=no # Type smallfloat +type_extra_smallmoney=no # Type smallmoney +type_extra_text=no # Type text +type_extra_text(1_arg)=no # Type text(1 arg) +type_extra_timespan=no # Type timespan +type_extra_uint=no # Type uint +type_extra_varchar2(1_arg)=no # Type varchar2(1 arg) +type_extra_year=no # Type year +type_odbc_bigint=no # Type bigint +type_odbc_binary(1_arg)=no # Type binary(1 arg) +type_odbc_datetime=no # Type datetime +type_odbc_tinyint=no # Type tinyint +type_odbc_varbinary(1_arg)=no # Type varbinary(1 arg) +type_sql_bit=no # Type bit +type_sql_bit(1_arg)=no # Type bit(1 arg) +type_sql_bit_varying(1_arg)=no # Type bit varying(1 arg) +type_sql_boolean=no # Type boolean +type_sql_char(1_arg)=yes # Type char(1 arg) +type_sql_char_varying(1_arg)=yes # Type char varying(1 arg) +type_sql_character(1_arg)=yes # Type character(1 arg) +type_sql_character_varying(1_arg)=yes # Type character varying(1 arg) +type_sql_date=yes # Type date +type_sql_dec(2_arg)=yes # Type dec(2 arg) +type_sql_decimal(2_arg)=yes # Type decimal(2 arg) +type_sql_double_precision=yes # Type double precision +type_sql_float=yes # Type float +type_sql_float(1_arg)=yes # Type float(1 arg) +type_sql_int=yes # Type int +type_sql_integer=yes # Type integer +type_sql_interval_day=no # Type interval day +type_sql_interval_day_to_hour=no # Type interval day to hour +type_sql_interval_day_to_minute=no # Type interval day to minute +type_sql_interval_day_to_second=no # Type interval day to second +type_sql_interval_hour=no # Type interval hour +type_sql_interval_hour_to_minute=no # Type interval hour to minute +type_sql_interval_hour_to_second=no # Type interval hour to second +type_sql_interval_minute=no # Type interval minute +type_sql_interval_minute_to_second=no # Type interval minute to second +type_sql_interval_month=no # Type interval month +type_sql_interval_second=no # Type interval second +type_sql_interval_year=no # Type interval year +type_sql_interval_year_to_month=no # Type interval year to month +type_sql_national_char_varying(1_arg)=yes # Type national char varying(1 arg) +type_sql_national_character(1_arg)=yes # Type national character(1 arg) +type_sql_national_character_varying(1_arg)=yes # Type national character varying(1 arg) +type_sql_nchar(1_arg)=yes # Type nchar(1 arg) +type_sql_nchar_varying(1_arg)=yes # Type nchar varying(1 arg) +type_sql_numeric(2_arg)=yes # Type numeric(2 arg) +type_sql_real=yes # Type real +type_sql_smallint=yes # Type smallint +type_sql_time=yes # Type time +type_sql_timestamp=yes # Type timestamp +type_sql_timestamp_with_time_zone=no # Type timestamp with time zone +type_sql_varchar(1_arg)=yes # Type varchar(1 arg) +union=yes # union +union_all=yes # union all +union_all_incompat=yes # union all (incompatible lists) +union_incompat=yes # union (incompatible lists) +unique_in_create=yes # unique in create table +unique_null_in_create=no # unique null in create +views=no # views +where_string_size=32767 # constant string size in where diff --git a/sql-bench/limits/mysql.cfg b/sql-bench/limits/mysql.cfg index a496bd7bf4c..306ce2d7b33 100644 --- a/sql-bench/limits/mysql.cfg +++ b/sql-bench/limits/mysql.cfg @@ -1,4 +1,4 @@ -#This file is automaticly generated by crash-me 1.57 +#This file is automaticly generated by crash-me 1.58 NEG=yes # update of column= -column Need_cast_for_null=no # Need to cast NULL for arithmetic @@ -29,14 +29,14 @@ columns_in_order_by=+64 # number of columns in order by comment_#=yes # # as comment comment_--=yes # -- as comment (ANSI) comment_/**/=yes # /* */ as comment -comment_//=no # // as comment (ANSI) +comment_//=no # // as comment compute=no # Compute connections=101 # Simultaneous connections (installation default) constraint_check=no # Column constraints constraint_check_table=no # Table constraints constraint_null=yes # NULL constraint (SyBase style) crash_me_safe=yes # crash me safe -crash_me_version=1.57 # crash me version +crash_me_version=1.58 # crash me version create_default=yes # default value for column create_default_func=no # default value function for column create_if_not_exists=yes # create table if not exists @@ -62,6 +62,7 @@ except=no # except except_all=no # except all except_all_incompat=no # except all (incompatible lists) except_incompat=no # except (incompatible lists) +field_name_case=yes # case independent field names float_int_expr=yes # mixing of integer and float in expression foreign_key=no # foreign keys foreign_key_syntax=yes # foreign key syntax @@ -228,10 +229,10 @@ func_odbc_timestampdiff=no # Function TIMESTAMPDIFF func_odbc_truncate=yes # Function TRUNCATE func_odbc_ucase=yes # Function UCASE func_odbc_user()=yes # Function USER() -func_odbc_week=yes # Function WEEK +func_odbc_week=error # Function WEEK func_odbc_year=yes # Function YEAR func_sql_+=yes # Function +, -, * and / -func_sql_bit_length=no # Function BIT_LENGTH +func_sql_bit_length=yes # Function BIT_LENGTH func_sql_cast=no # Function CAST func_sql_char_length=error # Function CHAR_LENGTH func_sql_char_length(constant)=yes # Function CHAR_LENGTH(constant) @@ -299,13 +300,14 @@ group_func_sql_min_str=yes # Group function MIN on strings group_func_sql_some=no # Group function SOME group_func_sql_sum=yes # Group function SUM group_functions=yes # Group functions +group_many_distinct_functions=yes # Group functions with several distinct group_on_unused=yes # Group on unused column has_true_false=no # TRUE and FALSE having=yes # Having having_with_alias=yes # Having on alias having_with_group=yes # Having with group function hex_numbers=yes # hex numbers (0x41) -hex_strings=no # hex strings (x'1ace') +hex_strings=yes # hex strings (x'1ace') ignore_end_space=yes # Ignore end space in compare index_in_create=yes # index in create table index_namespace=yes # different namespace for index @@ -319,7 +321,7 @@ intersect=no # intersect intersect_all=no # intersect all intersect_all_incompat=no # intersect all (incompatible lists) intersect_incompat=no # intersect (incompatible lists) -join_tables=63 # tables in join +join_tables=31 # tables in join left_outer_join=yes # left outer join left_outer_join_using=yes # left outer join using like_with_column=yes # column LIKE column @@ -331,7 +333,7 @@ max_char_size=255 # max char() size max_column_name=64 # column name length max_columns=3398 # Columns in table max_conditions=85660 # OR and AND in WHERE -max_expressions=1837 # simple expressions +max_expressions=686 # simple expressions max_index=32 # max index max_index_length=500 # index length max_index_name=64 # index name length @@ -341,7 +343,7 @@ max_index_varchar_part_length=255 # index varchar part length max_row_length=65534 # max table row length (without blobs) max_row_length_with_null=65502 # table row length with nulls (without blobs) max_select_alias_name=+512 # select alias name length -max_stack_expression=1837 # stacked expressions +max_stack_expression=490 # stacked expressions max_table_alias_name=+512 # table alias name length max_table_name=64 # table name length max_text_size=1048543 # max text or blob size @@ -353,8 +355,8 @@ minus_neg=yes # Calculate 1--1 multi_drop=yes # many tables to drop table multi_null_in_unique=yes # null in unique index multi_strings=yes # Multiple line strings -multi_table_delete=no # DELETE FROM table1,table2... -multi_table_update=no # Update with many tables +multi_table_delete=yes # DELETE FROM table1,table2... +multi_table_update=yes # Update with many tables natural_join=yes # natural join natural_join_incompat=yes # natural join (incompatible lists) natural_left_outer_join=yes # natural left outer join @@ -365,7 +367,7 @@ null_in_unique=yes # null in unique index null_num_expr=yes # Is 1+NULL = NULL nulls_in_unique=yes # null combination in unique index odbc_left_outer_join=yes # left outer join odbc style -operating_system=Linux 2.2.13-SMP alpha # crash-me tested on +operating_system=Linux 2.4.16-64GB-SMP i686 # crash-me tested on order_by=yes # Order by order_by_alias=yes # Order by alias order_by_function=yes # Order by function @@ -385,7 +387,7 @@ quote_with_"=yes # Allows ' and " as string markers remember_end_space=no # Remembers end space in char() remember_end_space_varchar=no # Remembers end space in varchar() rename_table=yes # rename table -repeat_string_size=1047552 # return string size from function +repeat_string_size=1048576 # return string size from function right_outer_join=yes # right outer join rowid=auto_increment # Type for row id select_constants=yes # Select constants @@ -394,7 +396,7 @@ select_limit2=yes # SELECT with LIMIT #,# select_string_size=1048565 # constant string size in SELECT select_table_update=no # Update with sub select select_without_from=yes # SELECT without FROM -server_version=MySQL 3.23.39 debug # server version +server_version=MySQL 4.0.2 alpha # server version simple_joins=yes # ANSI SQL simple joins storage_of_float=round # Storage of float values subqueries=no # subqueries @@ -402,7 +404,7 @@ table_alias=yes # Table alias table_name_case=no # case independent table names table_wildcard=yes # Select table_name.* temporary_table=yes # temporary tables -transactions=yes # constant string size in where +transactions=yes # transactions truncate_table=yes # truncate type_extra_abstime=no # Type abstime type_extra_bfile=no # Type bfile @@ -512,12 +514,11 @@ type_sql_time=yes # Type time type_sql_timestamp=yes # Type timestamp type_sql_timestamp_with_time_zone=no # Type timestamp with time zone type_sql_varchar(1_arg)=yes # Type varchar(1 arg) -union=no # union -union_all=no # union all -union_all_incompat=no # union all (incompatible lists) -union_incompat=no # union (incompatible lists) +union=yes # union +union_all=yes # union all +union_all_incompat=yes # union all (incompatible lists) +union_incompat=yes # union (incompatible lists) unique_in_create=yes # unique in create table unique_null_in_create=yes # unique null in create -user_comment=Alpha DS20 2x500 MHz, 2G memory, key_buffer=16M; ccc + cxx # comment views=no # views where_string_size=1048539 # constant string size in where diff --git a/sql-bench/run-all-tests.sh b/sql-bench/run-all-tests.sh index c2f687a9375..f392431140c 100644 --- a/sql-bench/run-all-tests.sh +++ b/sql-bench/run-all-tests.sh @@ -109,7 +109,9 @@ print "Running tests on: $machine\n"; print "Arguments: $log_prog_args\n"; print "Comments: $opt_comments\n"; print "Limits from: $opt_cmp\n"; -print "Server version: $server_version\n\n"; +print "Server version: $server_version\n"; +print "Optimization: $opt_optimization\n"; +print "Hardware: $opt_hw\n\n"; $estimated=$warning=$got_warning=0; @@ -195,6 +197,10 @@ while (<test-*>) print "Summary for $prog: ", join(" ",@prog_sum), "\n"; } } + elsif ($last_line =~ /^Test skipped/i) + { + print "$last_line\n"; + } else { $errors++; @@ -281,10 +287,6 @@ sub read_headers { $log_prog_args=$1; } - elsif (/^Comments.*:\s+(.*)$/) - { - $opt_comments=$1; - } elsif (/^Limits.*:\s+(.*)$/) { $opt_cmp=$1; @@ -293,6 +295,14 @@ sub read_headers { $server_version=$1; } + elsif (/^Optimiz.*:\s+(.*)$/) + { + $opt_optimization=$1; + } + elsif (/^Hardwar.*:\s+(.*)$/) + { + $opt_hw=$1; + } } close(TMP); } diff --git a/sql-bench/server-cfg.sh b/sql-bench/server-cfg.sh index 886c428e3b9..e53190a75ec 100644 --- a/sql-bench/server-cfg.sh +++ b/sql-bench/server-cfg.sh @@ -33,10 +33,10 @@ sub get_server { - my ($name,$host,$database,$odbc,$machine)=@_; + my ($name,$host,$database,$odbc,$machine,$socket,$connect_options)=@_; my ($server); if ($name =~ /mysql/i) - { $server=new db_MySQL($host, $database, $machine); } + { $server=new db_MySQL($host, $database, $machine, $socket,$connect_options); } elsif ($name =~ /pg/i) { $server= new db_Pg($host,$database); } elsif ($name =~ /msql/i) @@ -73,7 +73,7 @@ sub get_server { $server= new db_interbase($host,$database); } else { - die "Unknown sql server name used: $name\nUse one of: Access, Adabas, AdabasD, Empress, FrontBase, Oracle, Informix, DB2, mSQL, Mimer, MS-SQL, MySQL, Pg, Solid or Sybase.\nIf the connection is done trough ODBC the name must end with _ODBC\n"; + die "Unknown sql server name used: $name\nUse one of: Access, Adabas, AdabasD, Empress, FrontBase, Oracle, Informix, InterBase, DB2, mSQL, Mimer, MS-SQL, MySQL, Pg, Solid or Sybase.\nIf the connection is done trough ODBC the name must end with _ODBC\n"; } if ($name =~ /_ODBC$/i || defined($odbc) && $odbc) { @@ -106,13 +106,15 @@ package db_MySQL; sub new { - my ($type,$host,$database,$machine)= @_; + my ($type,$host,$database,$machine,$socket,$connect_options)= @_; my $self= {}; my %limits; bless $self; $self->{'cmp_name'} = "mysql"; - $self->{'data_source'} = "DBI:mysql:$database:$host"; + $self->{'data_source'} = "DBI:mysql:database=$database;host=$host"; + $self->{'data_source'} .= ";mysql_socket=$socket" if($socket); + $self->{'data_source'} .= ";$connect_options" if($connect_options); $self->{'limits'} = \%limits; $self->{'smds'} = \%smds; $self->{'blob'} = "blob"; @@ -120,6 +122,7 @@ sub new $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'vacuum'} = 1; # When using with --fast $self->{'drop_attr'} = ""; + $self->{'transactions'} = 0; # Transactions disabled by default $limits{'NEG'} = 1; # Supports -id $limits{'alter_add_multi_col'}= 1; #Have ALTER TABLE t add a int,add b int; @@ -144,7 +147,8 @@ sub new $limits{'join_optimizer'} = 1; # Can optimize FROM tables $limits{'left_outer_join'} = 1; # Supports left outer joins $limits{'like_with_column'} = 1; # Can use column1 LIKE column2 - $limits{'limit'} = 1; # supports the limit attribute + $limits{'limit'} = 1; # supports the limit attribute + $limits{'truncate_table'} = 1; $limits{'load_data_infile'} = 1; # Has load data infile $limits{'lock_tables'} = 1; # Has lock tables $limits{'max_column_name'} = 64; # max table and column name @@ -165,25 +169,6 @@ sub new $limits{'working_all_fields'} = 1; $limits{'working_blobs'} = 1; # If big varchar/blobs works - $smds{'time'} = 1; - $smds{'q1'} = 'b'; # with time not supp by mysql ('') - $smds{'q2'} = 'b'; - $smds{'q3'} = 'b'; # with time ('') - $smds{'q4'} = 'c'; # with time not supp by mysql (d) - $smds{'q5'} = 'b'; # with time not supp by mysql ('') - $smds{'q6'} = 'c'; # with time not supp by mysql ('') - $smds{'q7'} = 'c'; - $smds{'q8'} = 'f'; - $smds{'q9'} = 'c'; - $smds{'q10'} = 'b'; - $smds{'q11'} = 'b'; - $smds{'q12'} = 'd'; - $smds{'q13'} = 'c'; - $smds{'q14'} = 'd'; - $smds{'q15'} = 'd'; - $smds{'q16'} = 'a'; - $smds{'q17'} = 'c'; - # Some fixes that depends on the environment if (defined($main::opt_create_options) && $main::opt_create_options =~ /type=heap/i) @@ -194,12 +179,19 @@ sub new $main::opt_create_options =~ /type=innodb/i) { $limits{'max_text_size'} = 8000; # Limit in Innobase + $self->{'transactions'} = 1; # Transactions enabled + } + if (defined($main::opt_create_options) && + $main::opt_create_options =~ /type=bdb/i) + { + $self->{'transactions'} = 1; # Transactions enabled } if (defined($main::opt_create_options) && $main::opt_create_options =~ /type=gemini/i) { $limits{'working_blobs'} = 0; # Blobs not implemented yet $limits{'max_tables'} = 500; + $self->{'transactions'} = 1; # Transactions enabled } return $self; @@ -261,7 +253,6 @@ sub create { $field =~ s/ decimal/ double(10,2)/i; $field =~ s/ big_decimal/ double(10,2)/i; - $field =~ s/ date/ int/i; # Because of tcp ? $query.= $field . ','; } foreach $index (@$index) @@ -330,6 +321,12 @@ sub reconnect_on_errors return 0; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + # # Optimize tables for better performance # @@ -349,7 +346,6 @@ sub vacuum } } - ############################################################################# # Definitions for mSQL ############################################################################# @@ -368,6 +364,7 @@ sub new $self->{'limits'} = \%limits; $self->{'double_quotes'} = 0; $self->{'drop_attr'} = ""; + $self->{'transactions'} = 0; # No transactions $self->{'blob'} = "text(" . $limits{'max_text_size'} .")"; $self->{'text'} = "text(" . $limits{'max_text_size'} .")"; @@ -542,6 +539,12 @@ sub reconnect_on_errors return 0; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + ############################################################################# # Definitions for PostgreSQL # ############################################################################# @@ -558,11 +561,11 @@ sub new $self->{'cmp_name'} = "pg"; $self->{'data_source'} = "DBI:Pg:dbname=$database"; $self->{'limits'} = \%limits; - $self->{'smds'} = \%smds; $self->{'blob'} = "text"; $self->{'text'} = "text"; $self->{'double_quotes'} = 1; $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $self->{"vacuum"} = 1; $limits{'join_optimizer'} = 1; # Can optimize FROM tables $limits{'load_data_infile'} = 0; @@ -603,31 +606,11 @@ sub new $limits{'select_without_from'}= 1; $limits{'subqueries'} = 1; $limits{'table_wildcard'} = 1; + $limits{'truncate_table'} = 1; $limits{'unique_index'} = 1; # Unique index works or not $limits{'working_all_fields'} = 1; $limits{'working_blobs'} = 1; # If big varchar/blobs works - # the different cases per query ... - $smds{'q1'} = 'b'; # with time - $smds{'q2'} = 'b'; - $smds{'q3'} = 'b'; # with time - $smds{'q4'} = 'c'; # with time - $smds{'q5'} = 'b'; # with time - $smds{'q6'} = 'c'; # strange error .... - $smds{'q7'} = 'c'; - $smds{'q8'} = 'f'; # needs 128M to execute - can't do insert ...group by - $smds{'q9'} = 'c'; - $smds{'q10'} = 'b'; - $smds{'q11'} = 'b'; # can't do float8 * int4 - create operator - $smds{'q12'} = 'd'; # strange error??? - $smds{'q13'} = 'c'; - $smds{'q14'} = 'd'; # strange error??? - $smds{'q15'} = 'd'; # strange error??? - $smds{'q16'} = 'a'; - $smds{'q17'} = 'c'; - $smds{'time'} = 1; # the use of the time table -> 1 is on. - # when 0 then the date field must be a - # date field not a int field!!! return $self; } @@ -798,6 +781,12 @@ sub reconnect_on_errors return 0; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + sub vacuum { my ($self,$full_vacuum,$dbh_ref,@tables)=@_; @@ -846,11 +835,11 @@ sub new $self->{'cmp_name'} = "solid"; $self->{'data_source'} = "DBI:Solid:"; $self->{'limits'} = \%limits; - $self->{'smds'} = \%smds; $self->{'blob'} = "long varchar"; $self->{'text'} = "long varchar"; $self->{'double_quotes'} = 1; $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $limits{'max_conditions'} = 9999; # Probably big enough $limits{'max_columns'} = 2000; # From crash-me @@ -896,28 +885,6 @@ sub new $limits{'order_by_unused'} = 1; $limits{'working_all_fields'} = 1; - # for the smds small benchmark test .... - # the different cases per query ... - $smds{'q1'} = 'a'; - $smds{'q2'} = ''; - $smds{'q3'} = 'b'; #doesn't work -> strange error about column -fixed - $smds{'q4'} = 'a'; - $smds{'q5'} = 'b'; - $smds{'q6'} = 'c'; - $smds{'q7'} = 'b'; - $smds{'q8'} = 'f'; - $smds{'q9'} = 'b'; - $smds{'q10'} = 'b'; - $smds{'q11'} = ''; - $smds{'q12'} = 'd'; - $smds{'q13'} = 'b'; - $smds{'q14'} = 'd'; - $smds{'q15'} = 'd'; - $smds{'q16'} = ''; - $smds{'q17'} = ''; - $smds{'time'} = 1; # the use of the time table -> 1 is on. - # when 0 then the date field must be a - # date field not a int field!!! return $self; } @@ -1063,6 +1030,12 @@ sub small_rollback_segment return 0; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + sub reconnect_on_errors { return 0; @@ -1087,11 +1060,11 @@ sub new $self->{'cmp_name'} = "empress"; $self->{'data_source'} = "DBI:EmpressNet:SERVER=$host;Database=/usr/local/empress/rdbms/bin/$database"; $self->{'limits'} = \%limits; - $self->{'smds'} = \%smds; $self->{'blob'} = "text"; $self->{'text'} = "text"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $limits{'max_conditions'} = 1258; $limits{'max_columns'} = 226; # server is disconnecting???? @@ -1139,28 +1112,6 @@ sub new $limits{'order_by_unused'} = 1; $limits{'working_all_fields'} = 1; - # for the smds small benchmark test .... - # the different cases per query ... EMPRESS - $smds{'q1'} = 'a'; - $smds{'q2'} = ''; - $smds{'q3'} = 'a'; - $smds{'q4'} = 'a'; - $smds{'q5'} = 'a'; - $smds{'q6'} = 'a'; - $smds{'q7'} = 'b'; - $smds{'q8'} = 'd'; - $smds{'q9'} = 'b'; - $smds{'q10'} = 'a'; - $smds{'q11'} = ''; - $smds{'q12'} = 'd'; - $smds{'q13'} = 'b'; - $smds{'q14'} = 'b'; - $smds{'q15'} = 'a'; - $smds{'q16'} = ''; - $smds{'q17'} = ''; - $smds{'time'} = 1; # the use of the time table -> 1 is on. - # when 0 then the date field must be a - # date field not a int field!!! return $self; } @@ -1325,6 +1276,14 @@ sub query { return $sql; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + $cmd =~ s/\'\'/\' \'/g; + return $cmd; +} + + sub drop_index { my ($self,$table,$index) = @_; @@ -1369,11 +1328,11 @@ sub new $self->{'cmp_name'} = "Oracle"; $self->{'data_source'} = "DBI:Oracle:$database"; $self->{'limits'} = \%limits; - $self->{'smds'} = \%smds; $self->{'blob'} = "long"; $self->{'text'} = "long"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $self->{"vacuum"} = 1; $limits{'max_conditions'} = 9999; # (Actually not a limit) @@ -1385,6 +1344,7 @@ sub new $limits{'max_index_parts'} = 16; # Max segments/key $limits{'max_column_name'} = 32; # max table and column name + $limits{'truncate_table'} = 1; $limits{'join_optimizer'} = 1; # Can optimize FROM tables $limits{'load_data_infile'} = 0; # Has load data infile $limits{'lock_tables'} = 0; # Has lock tables @@ -1421,24 +1381,6 @@ sub new $limits{'order_by_unused'} = 1; $limits{'working_all_fields'} = 1; - $smds{'time'} = 1; - $smds{'q1'} = 'b'; # with time not supp by mysql ('') - $smds{'q2'} = 'b'; - $smds{'q3'} = 'b'; # with time ('') - $smds{'q4'} = 'c'; # with time not supp by mysql (d) - $smds{'q5'} = 'b'; # with time not supp by mysql ('') - $smds{'q6'} = 'c'; # with time not supp by mysql ('') - $smds{'q7'} = 'c'; - $smds{'q8'} = 'f'; - $smds{'q9'} = 'c'; - $smds{'q10'} = 'b'; - $smds{'q11'} = 'b'; - $smds{'q12'} = 'd'; - $smds{'q13'} = 'c'; - $smds{'q14'} = 'd'; - $smds{'q15'} = 'd'; - $smds{'q16'} = 'a'; - $smds{'q17'} = 'c'; return $self; } @@ -1546,6 +1488,14 @@ sub query { return $sql; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + $cmd =~ s/\'\'/\' \'/g; + return $cmd; +} + + sub drop_index { my ($self,$table,$index) = @_; @@ -1624,11 +1574,11 @@ sub new $self->{'cmp_name'} = "Informix"; $self->{'data_source'} = "DBI:Informix:$database"; $self->{'limits'} = \%limits; - $self->{'smds'} = \%smds; $self->{'blob'} = "byte in table"; $self->{'text'} = "byte in table"; $self->{'double_quotes'} = 0; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $self->{'host'} = $host; $limits{'NEG'} = 1; # Supports -id @@ -1778,6 +1728,16 @@ sub query { return $sql; } + +sub fix_for_insert +{ + my ($self,$cmd) = @_; + $cmd =~ s/\\\'//g; + return $cmd; +} + + + sub drop_index { my ($self,$table,$index) = @_; @@ -1805,6 +1765,7 @@ sub reconnect_on_errors return 0; } + ############################################################################# # Configuration for Access ############################################################################# @@ -1825,11 +1786,11 @@ sub new $self->{'data_source'} .= ":$host"; } $self->{'limits'} = \%limits; - $self->{'smds'} = \%smds; $self->{'blob'} = "blob"; $self->{'text'} = "blob"; # text ? $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $limits{'max_conditions'} = 97; # We get 'Query is too complex' $limits{'max_columns'} = 255; # Max number of columns in table @@ -1981,6 +1942,12 @@ sub reconnect_on_errors return 1; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + ############################################################################# # Configuration for Microsoft SQL server ############################################################################# @@ -2001,11 +1968,11 @@ sub new $self->{'data_source'} .= ":$host"; } $self->{'limits'} = \%limits; - $self->{'smds'} = \%smds; $self->{'blob'} = "text"; $self->{'text'} = "text"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $limits{'max_conditions'} = 1030; # We get 'Query is too complex' $limits{'max_columns'} = 250; # Max number of columns in table @@ -2168,10 +2135,15 @@ sub reconnect_on_errors return 0; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + ############################################################################# # Configuration for Sybase ############################################################################# - package db_sybase; sub new @@ -2182,17 +2154,17 @@ sub new bless $self; $self->{'cmp_name'} = "sybase"; - $self->{'data_source'} = "DBI:ODBC:$database"; + $self->{'data_source'} = "DBI:Sybase:database=$database"; if (defined($host) && $host ne "") { - $self->{'data_source'} .= ":$host"; + $self->{'data_source'} .= ";hostname=$host"; } $self->{'limits'} = \%limits; - $self->{'smds'} = \%smds; $self->{'blob'} = "text"; $self->{'text'} = "text"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $self->{"vacuum"} = 1; $limits{'max_conditions'} = 1030; # We get 'Query is too complex' @@ -2249,7 +2221,19 @@ sub new sub version { my ($self)=@_; - return "Sybase enterprise 11.5 NT"; #DBI/ODBC can't return the server version + my ($dbh,$sth,$version,@row); + + $dbh=$self->connect(); + $sth = $dbh->prepare('SELECT @@version') or die $DBI::errstr; + $version="Sybase (unknown)"; + if ($sth->execute && (@row = $sth->fetchrow_array)) + { + $version=$row[0]; + } + $sth->finish; + $dbh->disconnect; + return $version; + } sub connect @@ -2257,7 +2241,7 @@ sub connect my ($self)=@_; my ($dbh); $dbh=DBI->connect($self->{'data_source'}, $main::opt_user, - $main::opt_password,{ PrintError => 0}) || + $main::opt_password,{ PrintError => 0 , AutoCommit => 1}) || die "Got error: '$DBI::errstr' when connecting to " . $self->{'data_source'} ." with user: '$main::opt_user' password: '$main::opt_password'\n"; return $dbh; } @@ -2342,9 +2326,17 @@ sub reconnect_on_errors return 0; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + # # optimize the tables .... -# +# WARNING (from walrus)! This sub will work only from DBD:sybase +# driver. Because if we use ODBC we don't know actual database name +# (but DSN name only) sub vacuum { my ($self,$full_vacuum,$dbh_ref)=@_; @@ -2356,7 +2348,25 @@ sub vacuum } $dbh=$$dbh_ref; $loop_time=new Benchmark; - $dbh->do("analyze table ?? compute statistics") || die "Got error: $DBI::errstr when executing 'vacuum'\n"; + my (@tables,$sth,$current_table,$current_base); + $dbh->do("dump tran $database with truncate_only"); + $sth=$dbh->prepare("sp_tables" ) or die "prepere"; + $sth->execute() or die "execute"; + while (@row = $sth->fetchrow_array()) { + $current_table = $row[2]; + $current_base = $row[0]; + next if ($current_table =~ /^sys/); + push(@tables,$current_table) if ($database == $current_base); + } + + $sth->finish(); + + foreach $table (@tables) { +# print "$table: \n"; + $dbh->do("update statistics $table") or print "Oops!"; + } + +# $dbh->do("analyze table ?? compute statistics") || die "Got error: $DBI::errstr when executing 'vacuum'\n"; $end_time=new Benchmark; print "Time for book-keeping (1): " . Benchmark::timestr(Benchmark::timediff($end_time, $loop_time),"all") . "\n\n"; @@ -2364,6 +2374,8 @@ sub vacuum } + + ############################################################################# # Definitions for Adabas ############################################################################# @@ -2380,11 +2392,11 @@ sub new $self->{'cmp_name'} = "Adabas"; $self->{'data_source'} = "DBI:Adabas:$database"; $self->{'limits'} = \%limits; - $self->{'smds'} = \%smds; $self->{'blob'} = "long"; $self->{'text'} = "long"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $limits{'max_conditions'} = 50; # (Actually not a limit) $limits{'max_columns'} = 254; # Max number of columns in table @@ -2431,24 +2443,6 @@ sub new $limits{'order_by_unused'} = 1; $limits{'working_all_fields'} = 1; - $smds{'time'} = 1; - $smds{'q1'} = 'b'; # with time not supp by mysql ('') - $smds{'q2'} = 'b'; - $smds{'q3'} = 'b'; # with time ('') - $smds{'q4'} = 'c'; # with time not supp by mysql (d) - $smds{'q5'} = 'b'; # with time not supp by mysql ('') - $smds{'q6'} = 'c'; # with time not supp by mysql ('') - $smds{'q7'} = 'c'; - $smds{'q8'} = 'f'; - $smds{'q9'} = 'c'; - $smds{'q10'} = 'b'; - $smds{'q11'} = 'b'; - $smds{'q12'} = 'd'; - $smds{'q13'} = 'c'; - $smds{'q14'} = 'd'; - $smds{'q15'} = 'd'; - $smds{'q16'} = 'a'; - $smds{'q17'} = 'c'; return $self; } @@ -2574,6 +2568,12 @@ sub reconnect_on_errors return 0; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + ############################################################################# # Configuration for IBM DB2 ############################################################################# @@ -2594,11 +2594,11 @@ sub new $self->{'data_source'} .= ":$host"; } $self->{'limits'} = \%limits; - $self->{'smds'} = \%smds; $self->{'blob'} = "varchar(255)"; $self->{'text'} = "varchar(255)"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $limits{'max_conditions'} = 418; # We get 'Query is too complex' $limits{'max_columns'} = 500; # Max number of columns in table @@ -2747,6 +2747,12 @@ sub reconnect_on_errors return 0; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + ############################################################################# # Configuration for MIMER ############################################################################# @@ -2763,11 +2769,11 @@ sub new $self->{'cmp_name'} = "mimer"; $self->{'data_source'} = "DBI:mimer:$database:$host"; $self->{'limits'} = \%limits; - $self->{'smds'} = \%smds; $self->{'blob'} = "binary varying(15000)"; $self->{'text'} = "character varying(15000)"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $self->{'char_null'} = "cast(NULL as char(1))"; $self->{'numeric_null'} = "cast(NULL as int)"; @@ -2818,25 +2824,6 @@ sub new $limits{'order_by_unused'} = 1; $limits{'working_all_fields'} = 1; - $smds{'time'} = 1; - $smds{'q1'} = 'b'; # with time not supp by mysql ('') - $smds{'q2'} = 'b'; - $smds{'q3'} = 'b'; # with time ('') - $smds{'q4'} = 'c'; # with time not supp by mysql (d) - $smds{'q5'} = 'b'; # with time not supp by mysql ('') - $smds{'q6'} = 'c'; # with time not supp by mysql ('') - $smds{'q7'} = 'c'; - $smds{'q8'} = 'f'; - $smds{'q9'} = 'c'; - $smds{'q10'} = 'b'; - $smds{'q11'} = 'b'; - $smds{'q12'} = 'd'; - $smds{'q13'} = 'c'; - $smds{'q14'} = 'd'; - $smds{'q15'} = 'd'; - $smds{'q16'} = 'a'; - $smds{'q17'} = 'c'; - return $self; } @@ -2885,23 +2872,35 @@ sub connect sub create { my($self,$table_name,$fields,$index,$options) = @_; - my($query,@queries); + my($query,@queries,@indexes); $query="create table $table_name ("; foreach $field (@$fields) { $field =~ s/ decimal/ double(10,2)/i; $field =~ s/ big_decimal/ double(10,2)/i; - $field =~ s/ date/ int/i; # Because of tcp ? + $field =~ s/ tinyint\(.*\)/ smallint/i; + $field =~ s/ smallint\(.*\)/ smallint/i; + $field =~ s/ mediumint/ integer/i; + $field =~ s/ float\(.*\)/ float/i; +# $field =~ s/ date/ int/i; # Because of tcp ? $query.= $field . ','; } foreach $index (@$index) { - $query.= $index . ','; + if ( $index =~ /\bINDEX\b/i ) + { + my @fields = split(' ',$index); + my $query="CREATE INDEX $fields[1] ON $table_name $fields[2]"; + push(@indexes,$query); + + } else { + $query.= $index . ','; + } } substr($query,-1)=")"; # Remove last ','; $query.=" $options" if (defined($options)); - push(@queries,$query); + push(@queries,$query,@indexes); return @queries; } @@ -2947,6 +2946,12 @@ sub reconnect_on_errors return 0; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + ############################################################################# # Configuration for InterBase ############################################################################# @@ -2961,13 +2966,13 @@ sub new bless $self; $self->{'cmp_name'} = "interbase"; - $self->{'data_source'} = "DBI:InterBase:database=$database"; + $self->{'data_source'} = "DBI:InterBase:database=$database:ib_dialect=3"; $self->{'limits'} = \%limits; - $self->{'smds'} = \%smds; $self->{'blob'} = "blob"; $self->{'text'} = ""; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ""; + $self->{'transactions'} = 1; # Transactions enabled $self->{'char_null'} = ""; $self->{'numeric_null'} = ""; @@ -3016,25 +3021,6 @@ sub new $limits{'order_by_unused'} = 1; $limits{'working_all_fields'} = 1; - $smds{'time'} = 1; - $smds{'q1'} = 'b'; # with time not supp by mysql ('') - $smds{'q2'} = 'b'; - $smds{'q3'} = 'b'; # with time ('') - $smds{'q4'} = 'c'; # with time not supp by mysql (d) - $smds{'q5'} = 'b'; # with time not supp by mysql ('') - $smds{'q6'} = 'c'; # with time not supp by mysql ('') - $smds{'q7'} = 'c'; - $smds{'q8'} = 'f'; - $smds{'q9'} = 'c'; - $smds{'q10'} = 'b'; - $smds{'q11'} = 'b'; - $smds{'q12'} = 'd'; - $smds{'q13'} = 'c'; - $smds{'q14'} = 'd'; - $smds{'q15'} = 'd'; - $smds{'q16'} = 'a'; - $smds{'q17'} = 'c'; - return $self; } @@ -3159,6 +3145,12 @@ sub reconnect_on_errors return 1; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + ############################################################################# # Configuration for FrontBase ############################################################################# @@ -3175,11 +3167,11 @@ sub new $self->{'cmp_name'} = "FrontBase"; $self->{'data_source'} = "DBI:FB:dbname=$database;host=$host"; $self->{'limits'} = \%limits; - $self->{'smds'} = \%smds; $self->{'blob'} = "varchar(8000000)"; $self->{'text'} = "varchar(8000000)"; $self->{'double_quotes'} = 1; # Can handle: 'Walker''s' $self->{'drop_attr'} = ' restrict'; + $self->{'transactions'} = 1; # Transactions enabled $self->{'error_on_execute_means_zero_rows'}=1; $limits{'max_conditions'} = 5427; # (Actually not a limit) @@ -3247,13 +3239,13 @@ sub version my ($self)=@_; my ($dbh,$sth,$version,@row); - $dbh=$self->connect(); +# $dbh=$self->connect(); # # Pick up SQLGetInfo option SQL_DBMS_VER (18) # #$version = $dbh->func(18, GetInfo); - $version="FrontBase 2.1"; - $dbh->disconnect; + $version="FrontBase 3.3"; +# $dbh->disconnect; return $version; } @@ -3288,7 +3280,7 @@ sub connect sub create { my($self,$table_name,$fields,$index,$options) = @_; - my($query,@queries); + my($query,@queries,@indexes,@keys); $query="create table $table_name ("; foreach $field (@$fields) @@ -3306,18 +3298,18 @@ sub create } foreach $ind (@$index) { - my @index; - if ( $ind =~ /\bKEY\b/i ){ +# my @index; + if ( $ind =~ /(\bKEY\b)|(\bUNIQUE\b)/i ){ push(@keys,"ALTER TABLE $table_name ADD $ind"); }else{ - my @fields = split(' ',$index); + my @fields = split(' ',$ind); my $query="CREATE INDEX $fields[1] ON $table_name $fields[2]"; - push(@index,$query); + push(@indexes,$query); } } substr($query,-1)=")"; # Remove last ','; $query.=" $options" if (defined($options)); - push(@queries,$query); + push(@queries,$query,@keys,@indexes); return @queries; } @@ -3363,4 +3355,10 @@ sub reconnect_on_errors return 1; } +sub fix_for_insert +{ + my ($self,$cmd) = @_; + return $cmd; +} + 1; diff --git a/sql-bench/test-ATIS.sh b/sql-bench/test-ATIS.sh index 77e0e7d79bf..ac3d7360198 100644 --- a/sql-bench/test-ATIS.sh +++ b/sql-bench/test-ATIS.sh @@ -106,6 +106,12 @@ if (!$opt_skip_create) } else { + if ($opt_fast && $server->{transactions}) + { + $dbh->{AutoCommit} = 0; + print "Transactions enabled\n" if ($opt_debug); + } + for ($ti = 0; $ti <= $#table_names; $ti++) { my $table_name = $table_names[$ti]; @@ -119,8 +125,7 @@ if (!$opt_skip_create) chomp; next unless ( $_ =~ /\w/ ); # skip blank lines my $command = $insert_start . $_ . ")"; - $command =~ s/\'\'/\' \'/g if ($opt_server =~ /empress/i || $opt_server =~ /oracle/i); - $command =~ s/\\'//g if ($opt_server =~ /informix/i); + $command = $server->fix_for_insert($command); print "$command\n" if ($opt_debug); $command =~ s/\\'/\'\'/g if ($double_quotes); @@ -128,8 +133,14 @@ if (!$opt_skip_create) $row_count++; } } + if ($opt_fast && $server->{transactions}) + { + $dbh->commit; + $dbh->{AutoCommit} = 1; + } close(DATA); } + if ($opt_lock_tables) { $dbh->do("UNLOCK TABLES"); @@ -213,7 +224,7 @@ print "Retrieving data\n"; "SELECT airport.country_name,state.state_name,city.city_name,airport_service.direction FROM airport_service,state,airport,city WHERE airport_service.city_code=city.city_code AND airport_service.airport_code=airport.airport_code AND state.state_code=airport.state_code AND state.state_code=city.state_code AND airport.state_code=city.state_code AND airport.country_name=city.country_name AND airport.country_name=state.country_name AND city.time_zone_code=airport.time_zone_code GROUP BY airport.country_name,state.state_name,city.city_name,airport_service.direction ORDER BY state_name DESC",11,$limits->{'group_functions'}, "SELECT airport.country_name,state.state_name,city.city_name,airport_service.direction FROM airport_service,state,airport,city WHERE airport_service.city_code=city.city_code AND airport_service.airport_code=airport.airport_code AND state.state_code=airport.state_code AND state.state_code=city.state_code AND airport.state_code=city.state_code AND airport.country_name=city.country_name AND airport.country_name=state.country_name AND city.time_zone_code=airport.time_zone_code GROUP BY airport.country_name,state.state_name,city.city_name,airport_service.direction ORDER BY state_name",11,$limits->{'group_functions'}, "SELECT from_airport,to_airport,fare.fare_class,night,one_way_cost,rnd_trip_cost,class_days FROM compound_class,fare WHERE compound_class.fare_class=fare.fare_class AND one_way_cost <= 825 AND one_way_cost >= 280 AND from_airport='SFO' AND to_airport='DFW' GROUP BY from_airport,to_airport,fare.fare_class,night,one_way_cost,rnd_trip_cost,class_days ORDER BY one_way_cost",10,$limits->{'group_functions'}, - "select engines,category,cruising_speed,from_airport,to_airport FROM aircraft,flight WHERE category='JET' AND ENGINES >= 1 AND aircraft.aircraft_code=flight.aircraft_code AND to_airport NOT LIKE from_airport AND stops>0 GROUP BY engines,category,cruising_speed,from_airport,to_airport ORDER BY engines DESC",29,$limits->{'group_functions'} && $limits->{'like_with_column'}, + "select engines,category,cruising_speed,from_airport,to_airport FROM aircraft,flight WHERE category='JET' AND engines >= 1 AND aircraft.aircraft_code=flight.aircraft_code AND to_airport NOT LIKE from_airport AND stops>0 GROUP BY engines,category,cruising_speed,from_airport,to_airport ORDER BY engines DESC",29,$limits->{'group_functions'} && $limits->{'like_with_column'}, ); @Q=(\@Q1,\@Q2,\@Q21,\@Q3,\@Q4); diff --git a/sql-bench/test-alter-table.sh b/sql-bench/test-alter-table.sh index 276c4863d8c..cc6453188de 100644 --- a/sql-bench/test-alter-table.sh +++ b/sql-bench/test-alter-table.sh @@ -74,11 +74,25 @@ do_many($dbh,$server->create("bench",\@fields,\@index)); print "Insert data into the table\n"; $loop_time=new Benchmark; + +if ($opt_fast && $server->{transactions}) +{ + $dbh->{AutoCommit} = 0; + print "Transactions enabled\n" if ($opt_debug); +} + for ($i=0 ; $i < $opt_row_count ; $i++) { $query="insert into bench values ( " . ("$i," x ($opt_start_field_count-1)) . "$i)"; $dbh->do($query) or die $DBI::errstr; } + +if ($opt_fast && $server->{transactions}) +{ + $dbh->commit; + $dbh->{AutoCommit} = 1; +} + $end_time=new Benchmark; print "Time for insert ($opt_row_count)", diff --git a/sql-bench/test-big-tables.sh b/sql-bench/test-big-tables.sh index ac942f2b571..151a169d31f 100644 --- a/sql-bench/test-big-tables.sh +++ b/sql-bench/test-big-tables.sh @@ -127,14 +127,28 @@ end_benchmark($start_time); sub test_query { my($test_text,$result_text,$query,$dbh,$count)=@_; - my($i,$loop_time,$end_time); + my($i,$loop_time,$end_time, $using_transactions); print $test_text . "\n"; $loop_time=new Benchmark; + + $using_transactions=0; + if ($opt_fast && server->{transactions} && $query=~ /^insert /i) + { + $using_transactions=1; + $dbh->{AutoCommit} = 0; + print "Transactions enabled\n" if ($opt_debug); + } for ($i=0 ; $i < $count ; $i++) { defined(fetch_all_rows($dbh,$query)) or die $DBI::errstr; } + if ($using_transactions) + { + $dbh->commit; + $dbh->{AutoCommit} = 1; + } + $end_time=new Benchmark; print $result_text . "($count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; diff --git a/sql-bench/test-connect.sh b/sql-bench/test-connect.sh index 862161e3a03..0f1aca5aaa7 100644 --- a/sql-bench/test-connect.sh +++ b/sql-bench/test-connect.sh @@ -27,7 +27,7 @@ use DBI; use Benchmark; -$opt_loop_count=10000; # Change this to make test harder/easier +$opt_loop_count=100000; # Change this to make test harder/easier $str_length=65000; # This is the length of blob strings in PART:5 $max_test=20; # How many times to test if the server is busy @@ -43,9 +43,10 @@ if ($opt_small_test) } $opt_loop_count=min(1000, $opt_loop_count) if ($opt_tcpip); +$small_loop_count=$opt_loop_count/10; # For connect tests print "Testing the speed of connecting to the server and sending of data\n"; -print "All tests are done $opt_loop_count times\n\n"; +print "Connect tests are done $small_loop_count times and other tests $opt_loop_count times\n\n"; ################################# PART:1 ################################### #### @@ -59,7 +60,7 @@ print "Testing connection/disconnect\n"; $loop_time=new Benchmark; $errors=0; -for ($i=0 ; $i < $opt_loop_count ; $i++) +for ($i=0 ; $i < $small_loop_count ; $i++) { print "$i " if (($opt_debug)); for ($j=0; $j < $max_test ; $j++) @@ -80,27 +81,27 @@ for ($i=0 ; $i < $opt_loop_count ; $i++) } $end_time=new Benchmark; print "Warning: $errors connections didn't work without a time delay\n" if ($errors); -print "Time to connect ($opt_loop_count): " . +print "Time to connect ($small_loop_count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; ################################# PART:2 ################################### #### Now we shall do first one connect, then simple select #### (select 1..) and then close connection. This will be -#### done $opt_loop_count times. +#### done $small_loop_count times. if ($limits->{'select_without_from'}) { print "Test connect/simple select/disconnect\n"; $loop_time=new Benchmark; - for ($i=0; $i<$opt_loop_count; $i++) + for ($i=0; $i < $small_loop_count; $i++) { $dbh = DBI->connect($server->{'data_source'}, $opt_user, $opt_password) || die $DBI::errstr; - $sth = $dbh->do("select 1") or die $DBI::errstr; + $sth = $dbh->do("select $i") or die $DBI::errstr; $dbh->disconnect; } $end_time=new Benchmark; - print "Time for connect+select_simple ($opt_loop_count): " . + print "Time for connect+select_simple ($small_loop_count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; } @@ -116,16 +117,33 @@ if ($limits->{'select_without_from'}) { print "Test simple select\n"; $loop_time=new Benchmark; - for ($i=0 ; $i<$opt_loop_count ; $i++) + for ($i=0 ; $i < $opt_loop_count ; $i++) { - $sth = $dbh->do("select 1") or die $DBI::errstr; + $sth = $dbh->do("select $i") or die $DBI::errstr; } $end_time=new Benchmark; print "Time for select_simple ($opt_loop_count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; } -################################# PART:4 ################################### +########################################################################### +#### The same as the previous test, but always execute the same select +#### This is done to test the query cache for real simple selects. + +if ($limits->{'select_without_from'}) +{ + print "Test simple select\n"; + $loop_time=new Benchmark; + for ($i=0 ; $i < $opt_loop_count ; $i++) + { + $sth = $dbh->do("select 10000") or die $DBI::errstr; + } + $end_time=new Benchmark; + print "Time for select_simple_cache ($opt_loop_count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; +} + +########################################################################## #### First, we'll create a simple table 'bench1' #### Then we shall do $opt_loop_count selects from this table. #### Table will contain very simple data. @@ -152,7 +170,7 @@ print "Testing connect/select 1 row from table/disconnect\n"; $loop_time=new Benchmark; $errors=0; -for ($i=0 ; $i<$opt_loop_count ; $i++) +for ($i=0 ; $i < $small_loop_count ; $i++) { for ($j=0; $j < $max_test ; $j++) { @@ -161,14 +179,14 @@ for ($i=0 ; $i<$opt_loop_count ; $i++) } die $DBI::errstr if ($j == $max_test); - $sth = $dbh->do("select * from bench1") #Select * from table with 1 record + $sth = $dbh->do("select a,i,s,$i from bench1") # Select * from table with 1 record or die $DBI::errstr; $dbh->disconnect; } $end_time=new Benchmark; print "Warning: $errors connections didn't work without a time delay\n" if ($errors); -print "Time to connect+select_1_row ($opt_loop_count): " . +print "Time to connect+select_1_row ($small_loop_count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; # @@ -179,9 +197,9 @@ print "Testing select 1 row from table\n"; $dbh = $server->connect(); $loop_time=new Benchmark; -for ($i=0 ; $i<$opt_loop_count ; $i++) +for ($i=0 ; $i < $opt_loop_count ; $i++) { - $sth = $dbh->do("select * from bench1") # Select * from table with 1 record + $sth = $dbh->do("select a,i,s,$i from bench1") # Select * from table with 1 record or die $DBI::errstr; } @@ -190,8 +208,24 @@ print "Time to select_1_row ($opt_loop_count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; # -# The same test, but with 2 rows. +# Same test (as with one row) but now with a cacheable query # + +$loop_time=new Benchmark; + +for ($i=0 ; $i < $opt_loop_count ; $i++) +{ + $sth = $dbh->do("select a,i,s from bench1") # Select * from table with 1 record + or die $DBI::errstr; +} +$end_time=new Benchmark; +print "Time to select_1_row_cache ($opt_loop_count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + +# +# The same test, but with 2 rows (not cacheable). +# + print "Testing select 2 rows from table\n"; $sth = $dbh->do("insert into bench1 values(2,200,'BBB')") @@ -199,9 +233,9 @@ $sth = $dbh->do("insert into bench1 values(2,200,'BBB')") $loop_time=new Benchmark; -for ($i=0 ; $i<$opt_loop_count ; $i++) +for ($i=0 ; $i < $opt_loop_count ; $i++) { - $sth = $dbh->do("select * from bench1") # Select * from table with 2 record + $sth = $dbh->do("select a,i,s,$i from bench1") # Select * from table with 2 record or die $DBI::errstr; } @@ -209,14 +243,18 @@ $end_time=new Benchmark; print "Time to select_2_rows ($opt_loop_count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; +# +# Simple test to test speed of functions. +# + if ($limits->{'functions'}) { print "Test select with aritmetic (+)\n"; $loop_time=new Benchmark; - for ($i=0; $i<$opt_loop_count; $i++) + for ($i=0; $i < $opt_loop_count; $i++) { - $sth = $dbh->do("select a+a+a+a+a+a+a+a+a+a from bench1") or die $DBI::errstr; + $sth = $dbh->do("select a+a+a+a+a+a+a+a+a+$i from bench1") or die $DBI::errstr; } $end_time=new Benchmark; print "Time for select_column+column ($opt_loop_count): " . @@ -254,9 +292,9 @@ if ($opt_fast && defined($server->{vacuum})) $loop_time=new Benchmark; -for ($i=0 ; $i < $opt_loop_count ; $i++) +for ($i=0 ; $i < $small_loop_count ; $i++) { - $sth = $dbh->prepare("select * from bench1"); + $sth = $dbh->prepare("select b,$i from bench1"); if (!$sth->execute || !(@row = $sth->fetchrow_array) || length($row[0]) != $str_length) { @@ -266,14 +304,14 @@ for ($i=0 ; $i < $opt_loop_count ; $i++) } $end_time=new Benchmark; -print "Time to select_big_str ($opt_loop_count): " . +print "Time to select_big_str ($small_loop_count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; $sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}) or do { # Fix for Access 2000 - die $dbh->errstr if (!$dbh->abort_if_fatal_error()); + die $dbh->errstr if (!$server->abort_if_fatal_error()); }; if ($opt_fast && defined($server->{vacuum})) diff --git a/sql-bench/test-insert.sh b/sql-bench/test-insert.sh index 655e38b1b0e..085d7cce7f3 100644 --- a/sql-bench/test-insert.sh +++ b/sql-bench/test-insert.sh @@ -29,6 +29,7 @@ use DBI; use Benchmark; +use Data::Dumper; $opt_loop_count=100000; # number of rows/3 $small_loop_count=10; # Loop for full table retrieval @@ -132,6 +133,12 @@ else $query="insert into bench1 (id,id2,id3,dummy1) values "; } +if ($opt_fast && $server->{transactions}) +{ + $dbh->{AutoCommit} = 0; + print "Transactions enabled\n" if ($opt_debug); +} + if (($opt_fast || $opt_fast_insert) && $server->{'limits'}->{'insert_multi_value'}) { $query_size=$server->{'limits'}->{'query_size'}; @@ -209,6 +216,12 @@ else } } +if ($opt_fast && $server->{transactions}) +{ + $dbh->commit; + $dbh->{AutoCommit} = 1; +} + $end_time=new Benchmark; print "Time for insert (" . ($total_rows) . "): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; @@ -234,6 +247,12 @@ if ($limits->{'unique_index'}) { print "Testing insert of duplicates\n"; $loop_time=new Benchmark; + + if ($opt_fast && $server->{transactions}) + { + $dbh->{AutoCommit} = 0; + } + for ($i=0 ; $i < $opt_loop_count ; $i++) { $tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count); @@ -244,6 +263,11 @@ if ($limits->{'unique_index'}) die "Didn't get an error when inserting duplicate record $tmp\n"; } } + if ($opt_fast && $server->{transactions}) + { + $dbh->commit; + $dbh->{AutoCommit} = 1; + } $end_time=new Benchmark; print "Time for insert_duplicates (" . ($opt_loop_count) . "): " . @@ -577,7 +601,6 @@ if ($limits->{'group_functions'}) } $sth->finish; - $count++; $sth=$dbh->prepare($query="select count(*),sum(id+0.0),min(id),max(id),avg(id-0.0) from bench1") or die $DBI::errstr; $sth->execute or die $DBI::errstr; @@ -917,13 +940,19 @@ print "Time for update_with_key (" . ($opt_loop_count*3) . "): " . timestr(timediff($end_time, $loop_time),"all") . "\n"; $loop_time=new Benchmark; -for ($i=0 ; $i < $opt_loop_count*3 ; $i+=3) +$count=0; +for ($i=1 ; $i < $opt_loop_count*3 ; $i+=3) { $sth = $dbh->do("update bench1 set dummy1='updated' where id=$i") or die $DBI::errstr; + $end_time=new Benchmark; + last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$tests, + $opt_loop_count)); } - -$end_time=new Benchmark; -print "Time for update_with_key_prefix (" . ($opt_loop_count) . "): " . +if ($estimated) +{ print "Estimated time"; } +else +{ print "Time"; } +print " for update_with_key_prefix (" . ($opt_loop_count) . "): " . timestr(timediff($end_time, $loop_time),"all") . "\n"; print "\nTesting update of all rows\n"; @@ -1167,7 +1196,7 @@ if (!$opt_skip_delete) } $end_time=new Benchmark; - print "Time for delete_all ($count): " . + print "Time for delete_range ($count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; if ($opt_lock_tables) @@ -1203,6 +1232,7 @@ print "Insert into table with $keys keys and with a primary key with $seg parts\ # Make keys on the most important types @types=(0,0,0,1,0,0,0,1,1,1,1,1,1,1,1,1,1); # A 1 for each char field push(@fields,"field1 tinyint not null"); +push(@fields,"field_search tinyint not null"); push(@fields,"field2 mediumint not null"); push(@fields,"field3 smallint not null"); push(@fields,"field4 char(16) not null"); @@ -1222,9 +1252,10 @@ for ($i= 1 ; $i <= $seg ; $i++) } substr($query,-1)=")"; push (@keys,$query); +push (@keys,"index index2 (field_search)"); #Create other keys -for ($i=2 ; $i <= $keys ; $i++) +for ($i=3 ; $i <= $keys ; $i++) { push(@keys,"index index$i (field$i)"); } @@ -1242,6 +1273,11 @@ if ($server->small_rollback_segment()) } $loop_time=new Benchmark; +if ($opt_fast && $server->{transactions}) +{ + $dbh->{AutoCommit} = 0; +} + $fields=$#fields; if (($opt_fast || $opt_fast_insert) && $server->{'limits'}->{'insert_multi_value'}) { @@ -1250,11 +1286,11 @@ if (($opt_fast || $opt_fast_insert) && $server->{'limits'}->{'insert_multi_value $res=$query; for ($i=0; $i < $many_keys_loop_count; $i++) { + $id= $i & 127; $rand=$random[$i]; - $tmp="(" . ($i & 127) . ",$rand," . ($i & 32766) . - ",'ABCDEF$rand',0,"; + $tmp="($id,$id,$rand," . ($i & 32766) . ",'ABCDEF$rand',0,"; - for ($j=5; $j <= $fields ; $j++) + for ($j=6; $j <= $fields ; $j++) { $tmp.= ($types[$j] == 0) ? "$rand," : "'$rand',"; } @@ -1275,11 +1311,12 @@ else { for ($i=0; $i < $many_keys_loop_count; $i++) { + $id= $i & 127; $rand=$random[$i]; - $query="insert into bench1 values (" . ($i & 127) . ",$rand," . ($i & 32767) . + $query="insert into bench1 values ($id,$id,$rand," . ($i & 32767) . ",'ABCDEF$rand',0,"; - for ($j=5; $j <= $fields ; $j++) + for ($j=6; $j <= $fields ; $j++) { $query.= ($types[$j] == 0) ? "$rand," : "'$rand',"; } @@ -1288,6 +1325,13 @@ else $dbh->do($query) or die "Got error $DBI::errstr with query: $query\n"; } } + +if ($opt_fast && $server->{transactions}) +{ + $dbh->commit; + $dbh->{AutoCommit} = 1; +} + $end_time=new Benchmark; print "Time for insert_key ($many_keys_loop_count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; @@ -1316,11 +1360,24 @@ if ($opt_fast && defined($server->{vacuum})) print "Testing update of keys\n"; $loop_time=new Benchmark; + +if ($opt_fast && $server->{transactions}) +{ + $dbh->{AutoCommit} = 0; +} + for ($i=0 ; $i< 256; $i++) { - $dbh->do("update bench1 set field5=1 where field1=$i") - or die "Got error $DBI::errstr with query: update bench1 set field5=1 where field1=$i\n"; + $dbh->do("update bench1 set field5=1 where field_search=$i") + or die "Got error $DBI::errstr with query: update bench1 set field5=1 where field_search=$i\n"; +} + +if ($opt_fast && $server->{transactions}) +{ + $dbh->commit; + $dbh->{AutoCommit} = 1; } + $end_time=new Benchmark; print "Time for update_of_primary_key_many_keys (256): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; @@ -1360,29 +1417,31 @@ $count=0; for ($i=0 ; $i < 128 ; $i++) { $count++; - $dbh->do("delete from bench1 where field1 = $i") or die $DBI::errstr; + $dbh->do("delete from bench1 where field_search = $i") or die $DBI::errstr; } $end_time=new Benchmark; print "Time for delete_big_many_keys ($count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; +if ($opt_lock_tables) +{ + $sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr; +} + print "Deleting everything from table\n"; $count=1; if ($opt_fast) { - $dbh->do("delete from bench1") or die $DBI::errstr; + $query= ($limits->{'truncate_table'} ? "truncate table bench1" : + "delete from bench1"); + $dbh->do($query) or die $DBI::errstr; } else { $dbh->do("delete from bench1 where field1 > 0") or die $DBI::errstr; } -if ($opt_lock_tables) -{ - $sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr; -} - $end_time=new Benchmark; print "Time for delete_all_many_keys ($count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; @@ -1409,12 +1468,18 @@ if ($limits->{'insert_multi_value'}) "dummy1 char(30)"], ["primary key (id,id2)", "index index_id3 (id3)"])); + + $loop_time=new Benchmark; + if ($opt_lock_tables) { $sth = $dbh->do("LOCK TABLES bench1 write") || die $DBI::errstr; } + if ($opt_fast && $server->{transactions}) + { + $dbh->{AutoCommit} = 0; + } - $loop_time=new Benchmark; print "Inserting $opt_loop_count rows with multiple values\n"; $query="insert into bench1 values "; $res=$query; @@ -1437,6 +1502,11 @@ if ($limits->{'insert_multi_value'}) { $sth = $dbh->do("UNLOCK TABLES ") || die $DBI::errstr; } + if ($opt_fast && $server->{transactions}) + { + $dbh->commit; + $dbh->{AutoCommit} = 1; + } $end_time=new Benchmark; print "Time for multiple_value_insert (" . ($opt_loop_count) . "): " . diff --git a/sql-bench/test-select.sh b/sql-bench/test-select.sh index 1ecad5804c5..a5ae1da7283 100644 --- a/sql-bench/test-select.sh +++ b/sql-bench/test-select.sh @@ -92,6 +92,12 @@ if ($opt_fast && defined($server->{vacuum})) print "Inserting $opt_loop_count rows\n"; $loop_time=new Benchmark; + +if ($opt_fast && $server->{transactions}) +{ + $dbh->{AutoCommit} = 0; +} + $query="insert into bench1 values ("; $half_done=$opt_loop_count/2; for ($id=0,$rev_id=$opt_loop_count-1 ; $id < $opt_loop_count ; $id++,$rev_id--) @@ -105,6 +111,12 @@ for ($id=0,$rev_id=$opt_loop_count-1 ; $id < $opt_loop_count ; $id++,$rev_id--) } } +if ($opt_fast && $server->{transactions}) +{ + $dbh->commit; + $dbh->{AutoCommit} = 1; +} + $end_time=new Benchmark; print "Time to insert ($opt_loop_count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; @@ -141,10 +153,10 @@ if ($limits->{'group_functions'}) $loop_time=new Benchmark; for ($tests=0 ; $tests < $opt_loop_count ; $tests++) { - fetch_all_rows($dbh,"select sum(idn+$tests),sum(rev_idn-$tests) from bench1"); + fetch_all_rows($dbh,"select sum(idn+100),sum(rev_idn-100) from bench1"); } $end_time=new Benchmark; - print "Time for select_query_cache ($opt_loop_count): " . + print "Time for select_cache ($opt_loop_count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; # If the database has a query cache, the following loop should be much @@ -156,7 +168,7 @@ if ($limits->{'group_functions'}) fetch_all_rows($dbh,"select sum(idn+$tests),sum(rev_idn-$tests) from bench1"); } $end_time=new Benchmark; - print "Time for select_query_cache2 ($opt_loop_count): " . + print "Time for select_cache2 ($opt_loop_count): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; } diff --git a/sql-bench/test-transactions.sh b/sql-bench/test-transactions.sh new file mode 100644 index 00000000000..b081b89fb69 --- /dev/null +++ b/sql-bench/test-transactions.sh @@ -0,0 +1,297 @@ +#!@PERL@ +# Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB +# +# This library is free software; you can redistribute it and/or +# modify it under the terms of the GNU Library General Public +# License as published by the Free Software Foundation; either +# version 2 of the License, or (at your option) any later version. +# +# This library is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +# Library General Public License for more details. +# +# You should have received a copy of the GNU Library General Public +# License along with this library; if not, write to the Free +# Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, +# MA 02111-1307, USA +# +# Test of transactions performance. +# + +##################### Standard benchmark inits ############################## + +use DBI; +use Benchmark; +#use warnings; + +$opt_groups=27; # Characters are 'A' -> Z + +$opt_loop_count=10000; # Change this to make test harder/easier +$opt_medium_loop_count=100; # Change this to make test harder/easier + +chomp($pwd = `pwd`); $pwd = "." if ($pwd eq ''); +require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n"; + +# Avoid warnings for variables in bench-init.pl +# (Only works with perl 5.6) +#our ($opt_small_test, $opt_small_tables, $opt_debug, $opt_force); + +if ($opt_small_test || $opt_small_tables) +{ + $opt_loop_count/=100; + $opt_medium_loop_count/=10; +} + + +if (!$server->{transactions} && !$opt_force) +{ + print "Test skipped because the database doesn't support transactions\n"; + exit(0); +} + +#### +#### Connect and start timeing +#### + +$start_time=new Benchmark; +$dbh = $server->connect(); + +### +### Create Table +### + +print "Creating tables\n"; +$dbh->do("drop table bench1"); +$dbh->do("drop table bench2"); + +do_many($dbh,$server->create("bench1", + ["idn int NOT NULL", + "rev_idn int NOT NULL", + "region char(1) NOT NULL", + "grp int NOT NULL", + "updated tinyint NOT NULL"], + ["primary key (idn)", + "unique (region,grp)"])); +do_many($dbh,$server->create("bench2", + ["idn int NOT NULL", + "rev_idn int NOT NULL", + "region char(1) NOT NULL", + "grp int NOT NULL", + "updated tinyint NOT NULL"], + ["primary key (idn)", + "unique (region,grp)"])); + +$dbh->{AutoCommit} = 0; + +### +### Test insert perfomance +### + +test_insert("bench1","insert_commit",0); +test_insert("bench2","insert_autocommit",1); + +sub test_insert +{ + my ($table, $test_name, $auto_commit)= @_; + my ($loop_time,$end_time,$id,$rev_id,$grp,$region); + + $dbh->{AutoCommit}= $auto_commit; + $loop_time=new Benchmark; + + for ($id=0,$rev_id=$opt_loop_count-1 ; $id < $opt_loop_count ; + $id++,$rev_id--) + { + $grp=$id/$opt_groups; + $region=chr(65+$id%$opt_groups); + do_query($dbh,"insert into $table values ($id,$rev_id,'$region',$grp,0)"); + } + + $dbh->commit if (!$auto_commit); + $end_time=new Benchmark; + print "Time for $test_name ($opt_loop_count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; +} + +### +### Test rollback performance +### + +print "Test transactions rollback performance\n" if($opt_debug); + +## +## Insert rollback test +## + +# +# Test is done by inserting 100 rows in a table with lots of rows and +# then doing a rollback on these +# + +{ + my ($id,$rev_id,$grp,$region,$end,$loop_time,$end_time,$commit_loop,$count); + + $dbh->{AutoCommit} = 0; + $loop_time=new Benchmark; + $end=$opt_loop_count*2; + $count=0; + + for ($commit_loop=1, $id=$opt_loop_count ; $id < $end ; + $id++, $commit_loop++) + { + $rev_id=$end-$id; + $grp=$id/$opt_groups; + $region=chr(65+$id%$opt_groups); + do_query($dbh,"insert into bench1 values ($id,$rev_id,'$region',$grp,0)"); + if ($commit_loop >= $opt_medium_loop_count) + { + $dbh->rollback; + $commit_loop=0; + $count++; + } + } + if ($commit_loop > 1) + { + $dbh->rollback; + $count++; + } + $end_time=new Benchmark; + print "Time for insert_rollback ($count:$opt_loop_count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; +} + +## +## Update rollback test +## + +# +# Test is done by updating 100 rows in a table with lots of rows and +# then doing a rollback on these +# + +{ + my ($id,$loop_time,$end_time,$commit_loop,$count); + + $dbh->{AutoCommit} = 0; + $loop_time=new Benchmark; + $end=$opt_loop_count*2; + $count=0; + + for ($commit_loop=1, $id=0 ; $id < $opt_loop_count ; $id++, $commit_loop++) + { + do_query($dbh,"update bench1 set updated=2 where idn=$id"); + if ($commit_loop >= $opt_medium_loop_count) + { + $dbh->rollback; + $commit_loop=0; + $count++; + } + } + if ($commit_loop > 1) + { + $dbh->rollback; + $count++; + } + $end_time=new Benchmark; + print "Time for update_rollback ($count:$opt_loop_count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; +} + +## +## Delete rollback test +## + +# +# Test is done by deleting 100 rows in a table with lots of rows and +# then doing a rollback on these +# + +{ + my ($id,$loop_time,$end_time,$commit_loop,$count); + + $dbh->{AutoCommit} = 0; + $loop_time=new Benchmark; + $end=$opt_loop_count*2; + $count=0; + + for ($commit_loop=1, $id=0 ; $id < $opt_loop_count ; $id++, $commit_loop++) + { + do_query($dbh,"delete from bench1 where idn=$id"); + if ($commit_loop >= $opt_medium_loop_count) + { + $dbh->rollback; + $commit_loop=0; + $count++; + } + } + if ($commit_loop > 1) + { + $dbh->rollback; + $count++; + } + $end_time=new Benchmark; + print "Time for delete_rollback ($count:$opt_loop_count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; +} + + +### +### Test update perfomance +### + +test_update("bench1","update_commit",0); +test_update("bench2","update_autocommit",1); + +sub test_update +{ + my ($table, $test_name, $auto_commit)= @_; + my ($loop_time,$end_time,$id); + + $dbh->{AutoCommit}= $auto_commit; + $loop_time=new Benchmark; + + for ($id=0 ; $id < $opt_loop_count ; $id++) + { + do_query($dbh,"update bench1 set updated=1 where idn=$id"); + } + + $dbh->commit if (!$auto_commit); + $end_time=new Benchmark; + print "Time for $test_name ($opt_loop_count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; +} + +### +### Test delete perfomance +### + +test_delete("bench1","delete_commit",0); +test_delete("bench2","delete_autocommit",1); + +sub test_delete +{ + my ($table, $test_name, $auto_commit)= @_; + my ($loop_time,$end_time,$id); + + $dbh->{AutoCommit}= $auto_commit; + $loop_time=new Benchmark; + + for ($id=0 ; $id < $opt_loop_count ; $id++) + { + do_query($dbh,"delete from $table where idn=$id"); + } + $dbh->commit if (!$auto_commit); + $end_time=new Benchmark; + print "Time for $test_name ($opt_loop_count): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; +} + +#### +#### End of benchmark +#### + +$sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}) or die $DBI::errstr; +$sth = $dbh->do("drop table bench2" . $server->{'drop_attr'}) or die $DBI::errstr; + +$dbh->disconnect; # close connection +end_benchmark($start_time); diff --git a/sql-bench/test-wisconsin.sh b/sql-bench/test-wisconsin.sh index a017120259e..adc3e5b5aa4 100644 --- a/sql-bench/test-wisconsin.sh +++ b/sql-bench/test-wisconsin.sh @@ -48,27 +48,36 @@ init_query(); print "Wisconsin benchmark test\n\n"; -if (!$opt_skip_create) +if ($opt_skip_create) { - $loop_time= new Benchmark; - for($ti = 0; $ti <= $#table_names; $ti++) + if ($opt_lock_tables) { - my $table_name = $table_names[$ti]; - my $array_ref = $tables[$ti]; - - # This may fail if we have no table so do not check answer - $sth = $dbh->do("drop table $table_name" . $server->{'drop_attr'}); - print "Creating table $table_name\n" if ($opt_verbose); - do_many($dbh,@$array_ref); + @tmp=@table_names; push(@tmp,@extra_names); + $sth = $dbh->do("LOCK TABLES " . join(" WRITE,", @tmp) . " WRITE") || + die $DBI::errstr; } - $end_time=new Benchmark; - print "Time for create_table ($#tables): " . - timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + goto start_benchmark; +} - if ($opt_fast && defined($server->{vacuum})) - { - $server->vacuum(1,\$dbh); - } +$loop_time= new Benchmark; +for($ti = 0; $ti <= $#table_names; $ti++) +{ + my $table_name = $table_names[$ti]; + my $array_ref = $tables[$ti]; + + # This may fail if we have no table so do not check answer + $sth = $dbh->do("drop table $table_name" . $server->{'drop_attr'}); + print "Creating table $table_name\n" if ($opt_verbose); + do_many($dbh,@$array_ref); +} +$end_time=new Benchmark; +print "Time for create_table ($#tables): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + +if ($opt_fast && defined($server->{vacuum})) +{ + $server->vacuum(1,\$dbh); +} #### @@ -100,6 +109,11 @@ if ($opt_fast && $server->{'limits'}->{'load_data_infile'}) } else { + if ($opt_fast && $server->{transactions}) + { + $dbh->{AutoCommit} = 0; + } + for ($ti = 0; $ti <= $#table_names; $ti++) { my $table_name = $table_names[$ti]; @@ -124,10 +138,17 @@ else } close(DATA); } + if ($opt_lock_tables) { do_query($dbh,"UNLOCK TABLES"); } +if ($opt_fast && $server->{transactions}) +{ + $dbh->commit; + $dbh->{AutoCommit} = 1; +} + $end_time=new Benchmark; print "Time to insert ($row_count): " . timestr(timediff($end_time, $loop_time),"all") . "\n"; @@ -159,13 +180,6 @@ $sth = $dbh->do("delete from Bprime where Bprime.unique2 >= 1000") or $end_time=new Benchmark; print "Time to delete_big (1): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; -} -elsif ($opt_lock_tables) -{ - @tmp=@table_names; push(@tmp,@extra_names); - $sth = $dbh->do("LOCK TABLES " . join(" WRITE,", @tmp) . " WRITE") || - die $DBI::errstr; -} if ($opt_fast && defined($server->{vacuum})) { @@ -176,7 +190,9 @@ if ($opt_fast && defined($server->{vacuum})) #### Running the benchmark #### -print "Running actual benchmark\n"; +start_benchmark: + +print "Running the actual benchmark\n"; $loop_time= new Benchmark; $count=0; @@ -226,7 +242,7 @@ sub init_data { @onek= $server->create("onek", - ["unique1 int(4) NOT NULL", + ["unique1 int(5) NOT NULL", "unique2 int(4) NOT NULL", "two int(4)", "four int(4)", |