diff options
Diffstat (limited to 'mysql-test/t')
26 files changed, 614 insertions, 45 deletions
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 1ff38e4807f..a84b22c69b6 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1746,5 +1746,3 @@ DROP TABLE t1; --error ER_DUP_CONSTRAINT_NAME CREATE TABLE t1(a INT, b INT, CONSTRAINT min check (a>5), CONSTRAINT min check (b>5)); - - diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index 47477c72004..7e342928ef8 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -1567,6 +1567,37 @@ EXECUTE stmt; DROP TABLE t1,t2,t3,t4,t5,t6; --echo # +--echo # MDEV-10500 CASE/IF Statement returns multiple values and shifts further result values to the next column +--echo # + +CREATE TABLE t1 ( + id int not null AUTO_INCREMENT, + active bool not null, + data1 bigint, + data2 bigint, + data3 bigint, + primary key (id) +); +INSERT INTO t1 (active,data1,data2,data3) VALUES (1,null,100,200); +SELECT + CASE WHEN active THEN SUM(data1) END AS C_1, + SUM(data2) AS C_2, + SUM(data3) AS C_3 +FROM t1; +SELECT + IF(active, SUM(data1), 5) AS C_1, + SUM(data2) AS C_2, + SUM(data3) AS C_3 +FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-10468 Assertion `nr >= 0.0' failed in Item_sum_std::val_real() +--echo # +SELECT STDDEV_POP(f) FROM (SELECT "1e+309" AS f UNION SELECT "-1e+309" AS f) tbl; +SELECT STDDEV(f) FROM (SELECT 1.7976931348623157e+308 AS f UNION SELECT -1.7976931348623157e+308 AS f) tbl; + +--echo # --echo # MDEV-8852 Implicit or explicit CAST from MAX(string) to INT,DOUBLE,DECIMAL does not produce warnings --echo # SELECT MAX('x') << 1, CAST(MAX('x') AS DOUBLE), CAST(MAX('x') AS DECIMAL); @@ -1617,5 +1648,37 @@ SELECT companynr, AVG(fld1), AVG(fld1)<<0 AS avg1, CAST(AVG(fld1) AS UNSIGNED)<< DROP TABLE t1; --echo # +--echo # case where aggregate resolved in the local SELECT +--echo # but outer ones are checked +--echo # +create table t10 (a int , b int, c int); +insert into t10 values (0,0,0),(1,1,1); +create table t11 as select * from t10; +create table t12 as select * from t10; +explain extended select a from t10 where c<3 or a in (select c from t12 union select max(t10.b) from t11 group by t11.c); +drop table t10,t11,t12; +--echo # +--echo # MDEV-10017: Get unexpected `Empty Set` for correlated subquery +--echo # with aggregate functions +--echo # + +create table t1(c1 int, c2 int, c3 int); +insert into t1 values(1,1,1),(2,2,2),(3,3,3); +select * from t1; +create table t2(c1 int, c2 int); +insert into t2 values(2,2); +select * from t2; +--error ER_INVALID_GROUP_FUNC_USE +explain extended +select c1 from t1 having c1 >= (select t.c1 as c from t2 t order by (select min(t1.c1+c) from t2 tt)); +--error ER_INVALID_GROUP_FUNC_USE +select c1 from t1 having c1 >= (select t.c1 as c from t2 t order by (select min(t1.c1+c) from t2 tt)); + +explain extended +select c1 from t1 having c1 >= (select t.c1 as c from t2 t order by (select min(t1.c1+tt.c1) from t2 tt)); +select c1 from t1 having c1 >= (select t.c1 as c from t2 t order by (select min(t1.c1+tt.c1) from t2 tt)); +drop table t1,t2; + +--echo # --echo # End of 10.1 tests --echo # diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index ad821645aeb..08349f007e1 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -580,11 +580,15 @@ select 5 div 2.0; select 5.9 div 2, 1.23456789e3 DIV 2, 1.23456789e9 DIV 2, 1.23456789e19 DIV 2; --echo # ---echo # End of 5.5 tests +--echo # MDEV-10467 Assertion `nr >= 0.0' failed in Item_sum_std::val_real() --echo # +CREATE TABLE t1 (i INT); +INSERT INTO t1 VALUES (1),(2); +SELECT STDDEV_SAMP(ROUND('0', 309)) FROM t1; +DROP TABLE t1; --echo # ---echo # Start of 10.0 tests +--echo # End of 5.5 tests --echo # --echo # @@ -592,7 +596,6 @@ select 5.9 div 2, 1.23456789e3 DIV 2, 1.23456789e9 DIV 2, 1.23456789e19 DIV 2; --echo # SELECT STDDEV_POP(ROUND(0,@A:=2009)) FROM (SELECT 1 UNION SELECT 2) fake_table; - --echo # --echo # Start of 10.2 tests --echo # diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index f24576cc992..f931352c0e2 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -596,6 +596,16 @@ AND 57813X540X1723 = 'Test'; drop table t1; +# +# Bug#12735545 - PARSER STACK OVERFLOW WITH NAME_CONST +# CONTAINING OR EXPRESSION +# +--error ER_WRONG_ARGUMENTS +SELECT NAME_CONST('a', -(1 OR 2)) OR 1; +--error ER_WRONG_ARGUMENTS +SELECT NAME_CONST('a', -(1 AND 2)) OR 1; +SELECT NAME_CONST('a', -(1)) OR 1; + --echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 18293f31dd2..12b7c92688f 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -1712,3 +1712,77 @@ SELECT --echo # --echo # End of 10.0 tests --echo # + +--echo # +--echo # Start of 10.1 tests +--echo # + +--echo # +--echo # MDEV-10317 EXCTACT(MINUTE_MICROSECOND) truncates data +--echo # + +let $query= +SELECT + a, + EXTRACT(YEAR FROM a), + EXTRACT(YEAR_MONTH FROM a), + EXTRACT(QUARTER FROM a), + EXTRACT(MONTH FROM a), + EXTRACT(WEEK FROM a), + EXTRACT(DAY FROM a), + EXTRACT(DAY_HOUR FROM a), + EXTRACT(DAY_MINUTE FROM a), + EXTRACT(DAY_SECOND FROM a), + EXTRACT(HOUR FROM a), + EXTRACT(HOUR_MINUTE FROM a), + EXTRACT(HOUR_SECOND FROM a), + EXTRACT(MINUTE FROM a), + EXTRACT(MINUTE_SECOND FROM a), + EXTRACT(SECOND FROM a), + EXTRACT(MICROSECOND FROM a), + EXTRACT(DAY_MICROSECOND FROM a), + EXTRACT(HOUR_MICROSECOND FROM a), + EXTRACT(MINUTE_MICROSECOND FROM a), + EXTRACT(SECOND_MICROSECOND FROM a) +FROM t1; + + +CREATE TABLE t1 (a DATETIME(6)); +INSERT INTO t1 VALUES ('1999-12-31 23:59:59.999999'); + +--vertical_results +--enable_metadata +--disable_ps_protocol +--eval $query +--enable_ps_protocol +--disable_metadata +--horizontal_results + +--eval CREATE TABLE t2 AS $query +--vertical_results +SELECT * FROM t2; +--horizontal_results +SHOW CREATE TABLE t2; +DROP TABLE t1,t2; + +CREATE TABLE t1 (a TIME(6)); +INSERT INTO t1 VALUES ('-838:59:59.999999'),('838:59:59.999999'); + +--vertical_results +--enable_metadata +--disable_ps_protocol +--eval $query +--enable_ps_protocol +--disable_metadata +--horizontal_results + +--eval CREATE TABLE t2 AS $query +--vertical_results +SELECT * FROM t2; +--horizontal_results +SHOW CREATE TABLE t2; +DROP TABLE t1,t2; + +--echo # +--echo # End of 10.1 tests +--echo # diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index f0007186ab2..a1a73d15e1a 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1828,3 +1828,39 @@ select distinct a from t1 group by 'a'; insert into t1 values("2001-02-02"),("2001-02-03"); select distinct a from t1 group by 'a'; drop table t1; + +--echo # +--echo # MDEV-10324: Server crash in get_sel_arg_for_keypart or Assertion `n < size()' failed in Mem_root_array +--echo # +CREATE TABLE t1 ( + job_id int(10) unsigned NOT NULL AUTO_INCREMENT, + job_cmd varbinary(60) NOT NULL DEFAULT '', + job_namespace int(11) NOT NULL, + job_title varbinary(255) NOT NULL, + job_params blob NOT NULL, + job_timestamp varbinary(14) DEFAULT NULL, + job_random int(10) unsigned NOT NULL DEFAULT '0', + job_token varbinary(32) NOT NULL DEFAULT '', + job_token_timestamp varbinary(14) DEFAULT NULL, + job_sha1 varbinary(32) NOT NULL DEFAULT '', + job_attempts int(10) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (job_id), + KEY job_cmd (job_cmd,job_namespace,job_title,job_params(128)), + KEY job_timestamp (job_timestamp), + KEY job_sha1 (job_sha1), + KEY job_cmd_token (job_cmd,job_token,job_random), + KEY job_cmd_token_id (job_cmd,job_token,job_id) +); + +INSERT INTO t1 VALUES + (NULL, 'foo', 1, 'foo', 'foo', 'foo', 1, 'foo', 'foo', 'foo', 1), + (NULL, 'bar', 2, 'bar', 'bar', 'bar', 2, 'bar', 'bar', 'bar', 2); + +SELECT DISTINCT job_cmd FROM t1 WHERE job_cmd IN ('foobar','null'); +drop table t1; + +CREATE TABLE t1 (f1 INT NOT NULL, f2 VARCHAR(3) NOT NULL, KEY(f1), KEY(f2, f1)); +INSERT INTO t1 VALUES (0,'foo'),(1,'bar'); +SELECT 1 IN ( SELECT COUNT( DISTINCT f2 ) FROM t1 WHERE f1 <= 4 ); +drop table t1; + diff --git a/mysql-test/t/group_by_innodb.test b/mysql-test/t/group_by_innodb.test index e072a94fada..ed65e0c3e57 100644 --- a/mysql-test/t/group_by_innodb.test +++ b/mysql-test/t/group_by_innodb.test @@ -125,4 +125,34 @@ ORDER BY id DESC; DROP TABLE t1, t2; +--echo # Port of testcase: +--echo # +--echo # Bug#20819199 ASSERTION FAILED IN TEST_IF_SKIP_SORT_ORDER +--echo # + +CREATE TABLE t0 ( a INT ); +INSERT INTO t0 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); + +CREATE TABLE t1 ( + pk INT NOT NULL AUTO_INCREMENT, + a INT, + b INT, + PRIMARY KEY (pk), + KEY idx1 (a), + KEY idx2 (b, a), + KEY idx3 (a, b) +) ENGINE = InnoDB; + +INSERT INTO t1 (a, b) SELECT t01.a, t02.a FROM t0 t01, t0 t02; + +ANALYZE TABLE t1; + +let $query= +SELECT DISTINCT a, MAX(b) FROM t1 WHERE a >= 0 GROUP BY a,a; + +eval EXPLAIN $query; +eval $query; + +DROP TABLE t0, t1; + --echo # End of tests diff --git a/mysql-test/t/information_schema_stats.test b/mysql-test/t/information_schema_stats.test index 38248063d68..c7f39894ce7 100644 --- a/mysql-test/t/information_schema_stats.test +++ b/mysql-test/t/information_schema_stats.test @@ -13,6 +13,7 @@ alter table just_a_test add key IND_just_a_test_first_name_last_name(first_name, alter table just_a_test add key IND_just_a_test_state(state); select count(*) from just_a_test where first_name='fc' and last_name='lc'; select count(*) from just_a_test where state = 'California'; +--sorted_result select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; alter table just_a_test drop key IND_just_a_test_first_name_last_name; @@ -36,6 +37,7 @@ insert into just_a_test values(1,'fa','la','china_a',11111111,'fa_la@163.com','C select count(*) from just_a_test where first_name='fc' and last_name='lc'; select count(*) from just_a_test where state = 'California'; select count(*) from just_a_test where id between 2 and 4; +--sorted_result select * from information_schema.index_statistics where table_schema='test' and table_name='just_a_test'; select * from information_schema.table_statistics where table_schema='test' and table_name='just_a_test'; drop table just_a_test; diff --git a/mysql-test/t/innodb_ext_key.test b/mysql-test/t/innodb_ext_key.test index 9f3a89ff948..bf94b7dd3d5 100644 --- a/mysql-test/t/innodb_ext_key.test +++ b/mysql-test/t/innodb_ext_key.test @@ -693,5 +693,90 @@ drop table t1, t2; set optimizer_switch=@save_optimizer_switch; +--echo # +--echo # MDEV-10325: Queries examines all rows of a tables when it should not +--echo # +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 ( + pk int not null, + col1 varchar(32), + filler varchar(100), + key idx1(col1(10)), + primary key (pk) +)engine=innodb; + +insert into t1 +select + A.a + 10*B.a + 100*C.a, + concat('1234567890-', 1000+ A.a + 10*B.a + 100*C.a), + repeat('filler-data-', 4) +from + t0 A, t0 B, t0 C; + +let $q=explain select * from t1 where col1='1234567890-a'; +let $rows=query_get_value($q, rows, 1); +if ($rows < 2) +{ + --echo The EXPLAIN should not produce a query plan with type=ref, rows=1 + --die Fix for MDEV-10325 didnt work; +} + +drop table t0,t1; + +--echo # +--echo # MDEV-10360: Extended keys: index properties depend on index order +--echo # +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 ( + index_id bigint(20) unsigned NOT NULL, + index_class varchar(265) COLLATE latin1_general_ci DEFAULT NULL , + index_object_id int(10) unsigned NOT NULL DEFAULT '0' , + index_date_updated int(10) unsigned DEFAULT NULL , + + PRIMARY KEY (index_id), + KEY object (index_class(181),index_object_id), + KEY index_date_updated (index_date_updated) +) engine=innodb; + +create table t2 ( + index_id bigint(20) unsigned NOT NULL, + index_class varchar(265) COLLATE latin1_general_ci DEFAULT NULL , + index_object_id int(10) unsigned NOT NULL DEFAULT '0' , + index_date_updated int(10) unsigned DEFAULT NULL , + + PRIMARY KEY (index_id), + KEY index_date_updated (index_date_updated), + KEY object (index_class(181),index_object_id) +) engine=innodb; + +insert into t1 select + @a:=A.a + 10*B.a + 100*C.a, + concat('val-', @a), + 123456, + A.a + 10*B.a +from + t0 A, t0 B, t0 C; + +insert into t2 select * from t1; + +--echo # This must have the same query plan as the query below it: +--echo # type=range, key=index_date_updated, key_len=13 +--replace_column 9 # +explain +select * from t1 force index(index_date_updated) +where index_date_updated= 10 and index_id < 800; + +--echo # This used to work from the start: +--replace_column 9 # +explain +select * from t2 force index(index_date_updated) +where index_date_updated= 10 and index_id < 800; + +drop table t0,t1,t2; + set optimizer_switch=@save_ext_key_optimizer_switch; SET SESSION STORAGE_ENGINE=DEFAULT; diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test index 35243864c04..7e529194303 100644 --- a/mysql-test/t/loaddata.test +++ b/mysql-test/t/loaddata.test @@ -612,7 +612,7 @@ disconnect con1; --echo # CREATE TABLE t1(f1 INT); -EVAL SELECT 0xE1BB30 INTO OUTFILE 't1.dat'; +EVAL SELECT 0xE1C330 INTO OUTFILE 't1.dat'; --disable_warnings LOAD DATA INFILE 't1.dat' IGNORE INTO TABLE t1 CHARACTER SET utf8; --enable_warnings @@ -658,3 +658,27 @@ SET @@sql_mode= @old_mode; --remove_file $MYSQLTEST_VARDIR/mysql DROP TABLE t1; +--echo +--echo # +--echo # Bug#23080148 - Backport of Bug#20683959. +--echo # Bug#20683959 LOAD DATA INFILE IGNORES A SPECIFIC ROW SILENTLY +--echo # UNDER DB CHARSET IS UTF8. +--echo # + +CREATE DATABASE d1 CHARSET latin1; +USE d1; +CREATE TABLE t1 (val TEXT); +LOAD DATA INFILE '../../std_data/bug20683959loaddata.txt' INTO TABLE t1; +SELECT COUNT(*) FROM t1; +SELECT HEX(val) FROM t1; + +CREATE DATABASE d2 CHARSET utf8; +USE d2; +CREATE TABLE t1 (val TEXT); +LOAD DATA INFILE '../../std_data/bug20683959loaddata.txt' INTO TABLE t1; +SELECT COUNT(*) FROM t1; +SELECT HEX(val) FROM t1; + +DROP TABLE d1.t1, d2.t1; +DROP DATABASE d1; +DROP DATABASE d2; diff --git a/mysql-test/t/myisam_enable_keys-10506.test b/mysql-test/t/myisam_enable_keys-10506.test new file mode 100644 index 00000000000..8e1c058c3f0 --- /dev/null +++ b/mysql-test/t/myisam_enable_keys-10506.test @@ -0,0 +1,117 @@ +# +# MDEV-10506 Protocol::end_statement(): Assertion `0' failed upon ALTER TABLE +# +CREATE TABLE t1 ( + pk INT AUTO_INCREMENT, + i INT, + d DATE, + dt DATETIME, + v VARCHAR(1), + PRIMARY KEY (pk), + KEY (dt) +) ENGINE=MyISAM; +INSERT INTO t1 (i, d, dt, v) VALUES + (9, '2005-07-23', '2004-05-13 01:01:39', 't'), + (2, '2009-11-01', '2003-12-24 07:39:29', 'h'), + (6, NULL, '2008-07-03 05:32:22', 'l'), + (6, '2007-07-16', '2008-08-28 18:46:11', 'j'), + (5, NULL, '2001-07-12 21:27:00', 'h'), + (3, '2007-07-22', '1900-01-01 00:00:00', 'p'), + (2, '2000-11-21', '2007-05-25 11:58:54', 'g'), + (6, '1900-01-01', '2009-06-03 17:11:10', 'i'), + (2, '2008-02-10', '2001-06-15 16:20:07', 'p'), + (3, '2009-06-04', '1900-01-01 00:00:00', 'h'), + (9, '2007-04-25', '1900-01-01 00:00:00', 'e'), + (9, '2006-03-02', '1900-01-01 00:00:00', 'e'), + (1, '1900-01-01', '2002-11-08 09:33:27', 'u'), + (7, '2008-07-13', '2007-08-07 17:35:52', 'j'), + (0, '2004-11-12', '2006-05-01 00:00:00', 'e'), + (0, '1900-01-01', '2003-05-01 00:00:00', 'z'), + (1, '2009-09-02', '2007-02-12 09:30:49', 'w'), + (0, '2004-11-06', '1900-01-01 00:00:00', 't'), + (4, '2003-01-06', '2002-07-03 02:51:11', 'i'), + (6, '2006-01-14', '2008-02-26 04:57:32', 'i'), + (0, '2002-01-19', '2009-02-12 00:00:00', 'i'), + (8, '2007-02-12', '1900-01-01 00:00:00', 'b'), + (4, '1900-01-01', '2001-05-16 05:28:40', 'm'), + (2, '2005-07-16', NULL, 'j'), + (1, '2004-09-04', '2001-01-24 21:45:18', 'v'), + (3, '2009-07-01', NULL, NULL), + (2, '2009-07-21', '2002-07-24 00:00:00', 'h'), + (4, NULL, '2001-11-03 12:22:30', 'q'), + (1, '2002-06-22', '2008-06-17 03:17:59', 'f'), + (7, '2005-06-23', '2005-12-24 00:00:00', 'p'), + (6, '2001-05-20', '2008-10-23 00:00:00', NULL), + (3, '2001-10-01', '2000-10-12 16:32:35', 'o'), + (3, '2001-01-07', '2005-09-11 10:09:54', 'w'), + (6, '2007-11-02', '2009-09-10 01:44:18', 'l'), + (6, NULL, NULL, 'i'), + (9, NULL, '2002-05-18 15:21:55', 'd'), + (4, '2008-12-21', '2004-10-15 10:09:54', 'j'), + (6, '2003-10-05', '2009-07-13 03:51:02', 'e'), + (2, '2001-03-03', '1900-01-01 00:00:00', 'e'), + (2, '2007-04-04', '2001-11-08 21:14:52', 'q'), + (5, NULL, '2006-12-02 00:00:00', 'm'), + (0, '2009-01-04', '1900-01-01 00:00:00', NULL), + (8, '2008-04-03', '2005-01-01 11:55:18', 'q'), + (8, NULL, '2005-02-28 03:44:02', 'w'), + (0, '2003-08-22', NULL, 'c'), + (9, '1900-01-01', NULL, 'y'), + (NULL, NULL, '2006-08-25 16:28:09', 'g'), + (5, '2004-07-04', '2002-08-11 00:00:00', 'z'), + (1, '1900-01-01', '2007-07-22 21:19:18', 'm'), + (2, '2007-02-04', '2006-02-10 18:41:38', 't'), + (2, '1900-01-01', '2009-02-16 14:58:58', 'd'), + (7, '2001-03-14', '2007-08-14 00:00:00', 'h'), + (0, NULL, '1900-01-01 00:00:00', NULL), + (1, '2008-10-05', NULL, 'f'), + (6, '2001-11-25', '2008-12-03 06:59:23', 'l'), + (NULL, '2003-01-27', '2008-10-04 00:00:00', 'g'), + (8, '2008-08-08', '2009-07-07 07:00:21', 'v'), + (8, '2006-07-03', '2001-04-15 00:00:00', NULL), + (5, '2002-11-21', '2007-07-08 04:01:58', 'm'), + (5, '2006-04-08', '2007-09-23 00:01:35', 'i'), + (5, '2001-05-06', '2008-05-15 00:00:00', 'h'), + (7, '1900-01-01', '1900-01-01 00:00:00', 'u'), + (30, '2007-04-16', '2004-03-05 23:35:38', 'o'), + (NULL, '1900-01-01', '2007-08-25 01:32:47', 'z'), + (6, '2004-12-03', '1900-01-01 00:00:00', 'o'), + (8, '2001-06-23', '1900-01-01 00:00:00', 'f'), + (NULL, '2008-12-15', '2001-05-19 08:28:28', 'a'), + (9, '2000-02-15', '2009-09-03 06:07:22', 'd'), + (2, '2001-08-05', '2006-10-08 07:17:27', 'k'), + (5, '2004-01-17', '2003-09-06 20:36:01', 'd'), + (4, '2003-10-01', '2001-02-05 18:10:49', 'u'), + (4, '2003-07-28', '2001-01-07 16:11:37', 'h'), + (0, '1900-01-01', '2008-08-01 05:26:38', 'w'), + (9, '1900-01-01', '2001-05-08 00:00:00', 't'), + (1, '2000-04-17', '2008-07-10 21:26:28', 'i'), + (8, '2002-01-05', '2006-08-06 20:56:35', 'k'), + (9, '2001-04-10', '2003-02-17 00:00:00', 'z'), + (0, '2009-12-04', NULL, 'h'), + (7, NULL, '2004-10-27 00:29:57', 'h'), + (2, '2006-03-07', '2008-03-04 06:14:13', 'b'), + (0, '2001-10-15', '2001-03-17 00:00:00', 'm'), + (5, '1900-01-01', '2009-02-21 11:35:50', 'i'), + (4, NULL, '1900-01-01 00:00:00', 'w'), + (5, '2009-04-05', '1900-01-01 00:00:00', 'm'), + (6, '2001-03-19', '2001-04-12 00:00:00', 'q'), + (NULL, '2009-12-08', '2001-12-04 20:21:01', 'k'), + (2, '2005-02-09', '2001-05-27 08:41:01', 'l'), + (9, '2004-05-25', '2004-09-18 00:00:00', 'c'), + (3, '2005-01-17', '2002-09-12 11:18:48', 'd'), + (0, '2003-08-28', '1900-01-01 00:00:00', 'k'), + (6, '2006-10-11', '2003-10-28 03:31:02', 'a'), + (5, '1900-01-01', '2001-08-22 10:20:09', 'p'), + (8, '1900-01-01', '2008-04-24 00:00:00', 'o'), + (4, '2005-08-18', '2006-11-10 10:08:49', 'e'), + (NULL, '2007-03-12', '2007-10-16 00:00:00', 'n'), + (1, '2000-11-18', '2009-05-27 12:25:07', 't'), + (4, '2001-03-03', NULL, 'u'), + (3, '2003-09-11', '2001-09-10 18:10:10', 'f'), + (4, '2007-06-17', '1900-01-01 00:00:00', 't'), + (NULL, '2008-09-11', '2004-06-07 23:17:09', 'k'); +ALTER TABLE t1 ADD UNIQUE KEY ind1 (pk, d, i, v); +--error ER_DUP_ENTRY +ALTER TABLE t1 ADD UNIQUE KEY ind2 (d, v); +DROP TABLE t1; diff --git a/mysql-test/t/mysqlcheck.test b/mysql-test/t/mysqlcheck.test index e479a2232f0..779ea8d13d4 100644 --- a/mysql-test/t/mysqlcheck.test +++ b/mysql-test/t/mysqlcheck.test @@ -309,15 +309,36 @@ CHECK TABLE bug47205 FOR UPGRADE; DROP TABLE bug47205; + --echo # --echo #MDEV-6128:[PATCH] mysqlcheck wrongly escapes '.' in table names --echo # -CREATE TABLE test.`t.1` (id int); +create table `t.1` (id int); +create view `v.1` as select 1; --echo mysqlcheck test t.1 --exec $MYSQL_CHECK test t.1 +--echo mysqlcheck --all-in-1 test t.1 +--exec $MYSQL_CHECK --all-in-1 test t.1 +--echo mysqlcheck --all-in-1 --databases --process-views test +--exec $MYSQL_CHECK --all-in-1 --databases --process-views test + +create table `t.2`(a varchar(20) primary key) default character set utf8 collate utf8_general_ci engine=innodb; +flush table `t.2`; +--remove_file $MYSQLD_DATADIR/test/t@002e2.frm +--copy_file std_data/bug47205.frm $MYSQLD_DATADIR/test/t@002e2.frm + +--copy_file std_data/bug36055.frm $MYSQLD_DATADIR/test/t@002e3.frm +--copy_file std_data/bug36055.MYD $MYSQLD_DATADIR/test/t@002e3.MYD +--copy_file std_data/bug36055.MYI $MYSQLD_DATADIR/test/t@002e3.MYI -drop table test.`t.1`; +--echo mysqlcheck --check-upgrade --auto-repair test +--exec $MYSQL_CHECK --check-upgrade --auto-repair test + +check table `t.1`, `t.2`, `t.3`; +check table `t.1`, `t.2`, `t.3` for upgrade; +drop view `v.1`; +drop table test.`t.1`, `t.2`, `t.3`; --echo # --echo # MDEV-8123 mysqlcheck: new --process-views option conflicts with --quick, --extended and such @@ -355,6 +376,28 @@ create table `#mysql50#t1``1` (a int) engine=myisam; show tables; drop table `t1``1`; +# +# MDEV-9440 mysqlcheck -A --auto-repair selects wrong database when trying to repair broken table +# +call mtr.add_suppression("ha_myisam"); +call mtr.add_suppression("Checking table"); +create database mysqltest1; +create table mysqltest1.t1 (a int) engine=myisam; +create table t2 (a int); + +let $datadir= `select @@datadir`; +remove_file $datadir/mysqltest1/t1.MYD; +write_file $datadir/mysqltest1/t1.MYD; +foo +EOF + +check table mysqltest1.t1; + +--exec $MYSQL_CHECK -A --auto-repair --fast + +drop table t2; +drop database mysqltest1; + --echo # --echo #MDEV-7384 [PATCH] add PERSISENT FOR ALL option to mysqlanalyze/mysqlcheck --echo # diff --git a/mysql-test/t/named_pipe.test b/mysql-test/t/named_pipe.test index 8dcab3329e4..af74c200e96 100644 --- a/mysql-test/t/named_pipe.test +++ b/mysql-test/t/named_pipe.test @@ -22,3 +22,12 @@ connect(pipe_con,localhost,root,,,,,PIPE); connection default; disconnect pipe_con; + +# MDEV-10383 : check that other server cannot 'bind' on the same pipe +let $MYSQLD_DATADIR= `select @@datadir`; +--error 1 +--exec $MYSQLD_CMD --enable-named-pipe --skip-networking --log-error=second-mysqld.err +let SEARCH_FILE=$MYSQLD_DATADIR/second-mysqld.err; +let SEARCH_RANGE= -50; +let SEARCH_PATTERN=\[ERROR\] Create named pipe failed; +source include/search_pattern_in_file.inc; diff --git a/mysql-test/t/openssl_1.test b/mysql-test/t/openssl_1.test index 837206e2717..eea74b5b012 100644 --- a/mysql-test/t/openssl_1.test +++ b/mysql-test/t/openssl_1.test @@ -134,8 +134,7 @@ drop table t1; # verification of servers certificate by setting both ca certificate # and ca path to NULL # ---replace_result DHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-SHA DHE-RSA-CHACHA20-POLY1305 DHE-RSA-AES256-SHA ---exec $MYSQL --ssl --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem -e "SHOW STATUS LIKE 'ssl_Cipher'" 2>&1 +--exec $MYSQL --ssl --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem -e "SELECT (VARIABLE_VALUE <> '') AS have_ssl FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='Ssl_cipher'" 2>&1 --echo End of 5.0 tests # @@ -259,8 +258,7 @@ select 'is still running; no cipher request crashed the server' as result from d GRANT SELECT ON test.* TO bug42158@localhost REQUIRE X509; FLUSH PRIVILEGES; connect(con1,localhost,bug42158,,,,,SSL); ---replace_result DHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-SHA DHE-RSA-CHACHA20-POLY1305 DHE-RSA-AES256-SHA -SHOW STATUS LIKE 'Ssl_cipher'; +SELECT (VARIABLE_VALUE <> '') AS have_ssl FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='Ssl_cipher'; disconnect con1; connection default; DROP USER bug42158@localhost; diff --git a/mysql-test/t/ps_1general.test b/mysql-test/t/ps_1general.test index 5fedc787e01..9dfab0f07ba 100644 --- a/mysql-test/t/ps_1general.test +++ b/mysql-test/t/ps_1general.test @@ -935,3 +935,10 @@ drop table t1; # Matthias # End of 4.1 tests + +# +# MDEV-10318 unset params in --ps --embedded +# +--error ER_PARSE_ERROR,2031 +select ?+1; + diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 7f0e063124e..30f4419bd7e 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -1691,6 +1691,35 @@ select a, b from t2 where (a, b) in ((0, 0), (1, 1)); drop table t2; --echo # +--echo # MDEV-10228: Delete missing rows with OR conditions +--echo # (The example uses UPDATE, because UPDATE allows to use index hints +--echo # and so it's possible to make an example that works with any storage +--echo # engine) +--echo # + +CREATE TABLE t1 ( + key1varchar varchar(14) NOT NULL, + key2int int(11) NOT NULL DEFAULT '0', + col1 int, + PRIMARY KEY (key1varchar,key2int), + KEY key1varchar (key1varchar), + KEY key2int (key2int) +) DEFAULT CHARSET=utf8; + +insert into t1 values + ('value1',0, 0), + ('value1',1, 0), + ('value1',1000685, 0), + ('value1',1003560, 0), + ('value1',1004807, 0); + +update t1 force index (PRIMARY) set col1=12345 +where (key1varchar='value1' AND (key2int <=1 OR key2int > 1)); +--echo # The following must show col1=12345 for all rows: +select * from t1; +drop table t1; + +--echo # --echo # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE --echo # diff --git a/mysql-test/t/sp-prelocking.test b/mysql-test/t/sp-prelocking.test index 966c59a5789..c1378d59196 100644 --- a/mysql-test/t/sp-prelocking.test +++ b/mysql-test/t/sp-prelocking.test @@ -388,3 +388,29 @@ DROP TABLE t1, t2; --echo End of 5.0 tests +--echo # +--echo # Bug#21142859: FUNCTION UPDATING A VIEW FAILS TO FIND TABLE THAT ACTUALLY EXISTS +--echo # + +CREATE TABLE t1 SELECT 1 AS fld1, 'A' AS fld2; +CREATE TABLE t2 (fld3 INT, fld4 CHAR(1)); + +CREATE VIEW v1 AS SELECT * FROM t1; + +CREATE TRIGGER t1_au AFTER UPDATE ON t1 +FOR EACH ROW INSERT INTO t2 VALUES (new.fld1, new.fld2); + +DELIMITER !; +CREATE FUNCTION f1() RETURNS INT +BEGIN + UPDATE v1 SET fld2='B' WHERE fld1=1; + RETURN row_count(); +END ! +DELIMITER ;! + +--echo # Without the patch, an error was getting reported. +SELECT f1(); + +DROP FUNCTION f1; +DROP VIEW v1; +DROP TABLE t1,t2; diff --git a/mysql-test/t/ssl.test b/mysql-test/t/ssl.test index 88766e7cf39..9a08b273b6b 100644 --- a/mysql-test/t/ssl.test +++ b/mysql-test/t/ssl.test @@ -11,8 +11,7 @@ connect (ssl_con,localhost,root,,,,,SSL); # Check ssl turned on ---replace_result DHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-SHA DHE-RSA-CHACHA20-POLY1305 DHE-RSA-AES256-SHA -SHOW STATUS LIKE 'Ssl_cipher'; +SELECT (VARIABLE_VALUE <> '') AS have_ssl FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='Ssl_cipher'; # Check ssl expiration SHOW STATUS LIKE 'Ssl_server_not_before'; @@ -22,8 +21,7 @@ SHOW STATUS LIKE 'Ssl_server_not_after'; -- source include/common-tests.inc # Check ssl turned on ---replace_result DHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-SHA DHE-RSA-CHACHA20-POLY1305 DHE-RSA-AES256-SHA -SHOW STATUS LIKE 'Ssl_cipher'; +SELECT (VARIABLE_VALUE <> '') AS have_ssl FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='Ssl_cipher'; # # MDEV-7697 Client reports ERROR 2006 (MySQL server has gone away) or ERROR 2013 (Lost connection to MySQL server during query) while executing AES* functions under SSL diff --git a/mysql-test/t/ssl_ca.test b/mysql-test/t/ssl_ca.test index 8e81f44e61c..8d830a75879 100644 --- a/mysql-test/t/ssl_ca.test +++ b/mysql-test/t/ssl_ca.test @@ -7,11 +7,10 @@ --echo # try to connect with wrong '--ssl-ca' path : should fail --error 1 ---exec $MYSQL --ssl-ca=$MYSQL_TEST_DIR/std_data/wrong-cacert.pem --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test -e "SHOW STATUS LIKE 'Ssl_cipher'" 2>&1 +--exec $MYSQL --ssl-ca=$MYSQL_TEST_DIR/std_data/wrong-cacert.pem --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test -e "SELECT (VARIABLE_VALUE <> '') AS have_ssl FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='Ssl_cipher'" 2>&1 --echo # try to connect with correct '--ssl-ca' path : should connect ---replace_result DHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-SHA ---exec $MYSQL --ssl-ca=$MYSQL_TEST_DIR/std_data/cacert.pem --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test -e "SHOW STATUS LIKE 'Ssl_cipher'" +--exec $MYSQL --ssl-ca=$MYSQL_TEST_DIR/std_data/cacert.pem --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test -e "SELECT (VARIABLE_VALUE <> '') AS have_ssl FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='Ssl_cipher'" --echo # --echo # Bug#21920678: SSL-CA DOES NOT ACCEPT ~USER TILDE HOME DIRECTORY @@ -22,15 +21,12 @@ --echo # try to connect with '--ssl-ca' option using tilde home directoy --echo # path substitution : should connect ---replace_result DHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-SHA ---exec $MYSQL --ssl-ca$mysql_test_dir_path/std_data/cacert.pem --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test -e "SHOW STATUS LIKE 'Ssl_cipher'" +--exec $MYSQL --ssl-ca$mysql_test_dir_path/std_data/cacert.pem --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test -e "SELECT (VARIABLE_VALUE <> '') AS have_ssl FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='Ssl_cipher'" --echo # try to connect with '--ssl-key' option using tilde home directoy --echo # path substitution : should connect ---replace_result DHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-SHA ---exec $MYSQL --ssl-ca=$MYSQL_TEST_DIR/std_data/cacert.pem --ssl-key$mysql_test_dir_path/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test -e "SHOW STATUS LIKE 'Ssl_cipher'" +--exec $MYSQL --ssl-ca=$MYSQL_TEST_DIR/std_data/cacert.pem --ssl-key$mysql_test_dir_path/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem test -e "SELECT (VARIABLE_VALUE <> '') AS have_ssl FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='Ssl_cipher'" --echo # try to connect with '--ssl-cert' option using tilde home directoy --echo # path substitution : should connect ---replace_result DHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-SHA ---exec $MYSQL --ssl-ca=$MYSQL_TEST_DIR/std_data/cacert.pem --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert$mysql_test_dir_path/std_data/client-cert.pem test -e "SHOW STATUS LIKE 'Ssl_cipher'" +--exec $MYSQL --ssl-ca=$MYSQL_TEST_DIR/std_data/cacert.pem --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert$mysql_test_dir_path/std_data/client-cert.pem test -e "SELECT (VARIABLE_VALUE <> '') AS have_ssl FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='Ssl_cipher'" diff --git a/mysql-test/t/ssl_compress.test b/mysql-test/t/ssl_compress.test index 28f3453c23e..588d4555db8 100644 --- a/mysql-test/t/ssl_compress.test +++ b/mysql-test/t/ssl_compress.test @@ -11,8 +11,7 @@ connect (ssl_compress_con,localhost,root,,,,,SSL COMPRESS); # Check ssl turned on ---replace_result DHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-SHA DHE-RSA-CHACHA20-POLY1305 DHE-RSA-AES256-SHA -SHOW STATUS LIKE 'Ssl_cipher'; +SELECT (VARIABLE_VALUE <> '') AS have_ssl FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='Ssl_cipher'; # Check compression turned on SHOW STATUS LIKE 'Compression'; @@ -21,8 +20,7 @@ SHOW STATUS LIKE 'Compression'; -- source include/common-tests.inc # Check ssl turned on ---replace_result DHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-SHA DHE-RSA-CHACHA20-POLY1305 DHE-RSA-AES256-SHA -SHOW STATUS LIKE 'Ssl_cipher'; +SELECT (VARIABLE_VALUE <> '') AS have_ssl FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='Ssl_cipher'; # Check compression turned on SHOW STATUS LIKE 'Compression'; diff --git a/mysql-test/t/ssl_timeout.test b/mysql-test/t/ssl_timeout.test index 806b928aca0..430fe7130de 100644 --- a/mysql-test/t/ssl_timeout.test +++ b/mysql-test/t/ssl_timeout.test @@ -7,8 +7,7 @@ connect (ssl_con,localhost,root,,,,,SSL read_timeout=5); --echo # Check ssl turned on ---replace_result DHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-SHA DHE-RSA-CHACHA20-POLY1305 DHE-RSA-AES256-SHA -SHOW STATUS LIKE 'Ssl_cipher'; +SELECT (VARIABLE_VALUE <> '') AS have_ssl FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='Ssl_cipher'; # --error CR_SERVER_LOST --error 2013 diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 67af426f5fe..aaecaf020a0 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -6009,5 +6009,22 @@ SET NAMES utf8; CREATE TABLE t1 (f VARCHAR(8)) ENGINE=MyISAM; INSERT INTO t1 VALUES ('foo'); SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar' UNION SELECT 'baz' ); +SELECT f FROM t1 WHERE f > ALL ( SELECT 'bar'); drop table t1; SET NAMES default; + +--echo # +--echo # MDEV-10045: Server crashes in Time_and_counter_tracker::incr_loops +--echo # +SET NAMES utf8; + +CREATE TABLE t1 (f1 VARCHAR(3), f2 INT UNSIGNED) ENGINE=MyISAM; +CREATE TABLE t2 (f3 INT) ENGINE=MyISAM; + +SELECT * FROM t1, t2 WHERE f3 = f2 AND f1 > ANY ( SELECT 'foo' UNION SELECT 'bar' ); +SELECT * FROM t1, t2 WHERE f3 = f2 AND f1 > ANY ( SELECT 'foo'); + +DROP TABLE t1, t2; +SET NAMES default; + +--echo End of 10.1 tests diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index d2bafa86028..559b380a7dc 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1925,3 +1925,15 @@ EXECUTE stmt; DROP TABLE t1,t2,t3; +--echo # +--echo # MDEV-10389: Query returns different results on a debug vs non-debug build of the same revision +--echo # +CREATE TABLE t1 (i1 INT, i2 INT NOT NULL); +INSERT INTO t1 VALUES (1,4),(2,6); + +SELECT * FROM t1 AS alias1 +WHERE alias1.i1 IN ( + SELECT i1 FROM t1 WHERE alias1.i2 IN ( SELECT i2 FROM t1 HAVING i2 <> 7 ) +); +DROP TABLE t1; + diff --git a/mysql-test/t/temp_table.test b/mysql-test/t/temp_table.test index fc1b8fad514..7ba4de34ea9 100644 --- a/mysql-test/t/temp_table.test +++ b/mysql-test/t/temp_table.test @@ -321,6 +321,10 @@ REPAIR TABLE t1, t2, t3; DROP TABLES t1, t2, t3; +CREATE TEMPORARY TABLE t1 (a int); +RENAME TABLE t1 TO t2; +DROP TABLE t2; + # # CREATE TEMPORARY TEMPORARY TABLE # diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test index 71b1e76ac47..63c89f318fc 100644 --- a/mysql-test/t/type_date.test +++ b/mysql-test/t/type_date.test @@ -169,18 +169,8 @@ DROP TABLE t1; # CREATE TABLE t1 (a DATE, b INT, PRIMARY KEY (a,b)); -## The current sub test could fail (difference to expected result) if we -## have just reached midnight. -## (Bug#41776 type_date.test may fail if run around midnight) -## Therefore we sleep a bit if we are too close to midnight. -## The complete test itself needs in average less than 1 second. -## Therefore a time_distance to midnight of 5 seconds should be sufficient. -if (`SELECT CURTIME() > SEC_TO_TIME(24 * 3600 - 5)`) -{ - # We are here when CURTIME() is between '23:59:56' and '23:59:59'. - # So a sleep time of 5 seconds brings us between '00:00:01' and '00:00:04'. - --real_sleep 5 -} + +SET timestamp=UNIX_TIMESTAMP('2016-07-21 14:48:18'); INSERT INTO t1 VALUES (DATE(NOW()), 1); SELECT COUNT(*) FROM t1 WHERE a = NOW(); EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); @@ -192,6 +182,7 @@ EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1; ALTER TABLE t1 DROP PRIMARY KEY; SELECT COUNT(*) FROM t1 WHERE a = NOW(); EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); +SET timestamp=DEFAULT; DROP TABLE t1; diff --git a/mysql-test/t/xtradb_mrr.test b/mysql-test/t/xtradb_mrr.test index 260eb9f3955..d994c182ccc 100644 --- a/mysql-test/t/xtradb_mrr.test +++ b/mysql-test/t/xtradb_mrr.test @@ -33,8 +33,8 @@ insert into t2 select from t1 A, t1 B, t1 C; explain -select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a'; -select count(length(a) + length(filler)) from t2 where a>='a-1000-a' and a <'a-1001-a'; +select count(length(a) + length(filler)) from t2 force index (a) where a>='a-1000-a' and a <'a-1001-a'; +select count(length(a) + length(filler)) from t2 force index (a) where a>='a-1000-a' and a <'a-1001-a'; drop table t2; # Try a very big rowid |