diff options
153 files changed, 3231 insertions, 499 deletions
diff --git a/client/client_priv.h b/client/client_priv.h index a2f61b9e9ca..c205d07c34c 100644 --- a/client/client_priv.h +++ b/client/client_priv.h @@ -80,5 +80,6 @@ enum options_client OPT_MYSQL_REPLACE_INTO, OPT_BASE64_OUTPUT, OPT_SERVER_ID, OPT_FIX_TABLE_NAMES, OPT_FIX_DB_NAMES, OPT_SSL_VERIFY_SERVER_CERT, OPT_DEBUG_INFO, OPT_DEBUG_CHECK, OPT_COLUMN_TYPES, OPT_ERROR_LOG_FILE, - OPT_WRITE_BINLOG, OPT_MAX_CLIENT_OPTION + OPT_WRITE_BINLOG, OPT_DUMP_DATE, + OPT_MAX_CLIENT_OPTION }; diff --git a/client/mysqldump.c b/client/mysqldump.c index 9a83b609d0e..d504d177490 100644 --- a/client/mysqldump.c +++ b/client/mysqldump.c @@ -90,7 +90,7 @@ static my_bool verbose= 0, opt_no_create_info= 0, opt_no_data= 0, opt_drop=1,opt_keywords=0,opt_lock=1,opt_compress=0, opt_delayed=0,create_options=1,opt_quoted=0,opt_databases=0, opt_alldbs=0,opt_create_db=0,opt_lock_all_tables=0, - opt_set_charset=0, + opt_set_charset=0, opt_dump_date=1, opt_autocommit=0,opt_disable_keys=1,opt_xml=0, opt_delete_master_logs=0, tty_password=0, opt_single_transaction=0, opt_comments= 0, opt_compact= 0, @@ -424,6 +424,9 @@ static struct my_option my_long_options[] = "automatically turns off --lock-tables.", (uchar**) &opt_single_transaction, (uchar**) &opt_single_transaction, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, + {"dump-date", OPT_DUMP_DATE, "Put a dump date to the end of the output.", + (uchar**) &opt_dump_date, (uchar**) &opt_dump_date, 0, + GET_BOOL, NO_ARG, 1, 0, 0, 0, 0, 0}, {"skip-opt", OPT_SKIP_OPTIMIZATION, "Disable --opt. Disables --add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, and --disable-keys.", 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0}, @@ -651,10 +654,15 @@ static void write_footer(FILE *sql_file) fputs("\n", sql_file); if (opt_comments) { - char time_str[20]; - get_date(time_str, GETDATE_DATE_TIME, 0); - fprintf(sql_file, "-- Dump completed on %s\n", - time_str); + if (opt_dump_date) + { + char time_str[20]; + get_date(time_str, GETDATE_DATE_TIME, 0); + fprintf(sql_file, "-- Dump completed on %s\n", + time_str); + } + else + fprintf(sql_file, "-- Dump completed\n"); } check_io(sql_file); } diff --git a/client/mysqlslap.c b/client/mysqlslap.c index 02a0cbd04c5..1c54ba86718 100644 --- a/client/mysqlslap.c +++ b/client/mysqlslap.c @@ -140,7 +140,8 @@ static my_bool opt_compress= FALSE, tty_password= FALSE, auto_generate_sql= FALSE; const char *auto_generate_sql_type= "mixed"; -static unsigned long connect_flags= CLIENT_MULTI_RESULTS; +static unsigned long connect_flags= CLIENT_MULTI_RESULTS | + CLIENT_MULTI_STATEMENTS; static int verbose, delimiter_length; static uint commit_rate; @@ -1877,13 +1878,16 @@ limit_not_met: } } - if (mysql_field_count(mysql)) + do { - result= mysql_store_result(mysql); - while ((row = mysql_fetch_row(result))) - counter++; - mysql_free_result(result); - } + if (mysql_field_count(mysql)) + { + result= mysql_store_result(mysql); + while ((row = mysql_fetch_row(result))) + counter++; + mysql_free_result(result); + } + } while(mysql_next_result(mysql) == 0); queries++; if (commit_rate && commit_rate <= trans_counter) diff --git a/dbug/dbug.c b/dbug/dbug.c index 9d638c299d3..7f4292d18b1 100644 --- a/dbug/dbug.c +++ b/dbug/dbug.c @@ -1826,7 +1826,13 @@ static void DBUGOpenFile(CODE_STATE *cs, else { newfile= !EXISTS(name); - if (!(fp= fopen(name, append ? "a+" : "w"))) + if (!(fp= fopen(name, +#if defined(MSDOS) || defined(__WIN__) + append ? "a+c" : "wc" +#else + append ? "a+" : "w" +#endif + ))) { (void) fprintf(stderr, ERR_OPEN, cs->process, name); perror(""); @@ -2231,23 +2237,9 @@ static void dbug_flush(CODE_STATE *cs) if (cs->stack->flags & FLUSH_ON_WRITE) #endif { -#if defined(MSDOS) || defined(__WIN__) - if (cs->stack->out_file != stdout && cs->stack->out_file != stderr) - { - if (!(freopen(cs->stack->name,"a",cs->stack->out_file))) - { - (void) fprintf(stderr, ERR_OPEN, cs->process, cs->stack->name); - fflush(stderr); - cs->stack->out_file= stderr; - } - } - else -#endif - { - (void) fflush(cs->stack->out_file); - if (cs->stack->delay) - (void) Delay(cs->stack->delay); - } + (void) fflush(cs->stack->out_file); + if (cs->stack->delay) + (void) Delay(cs->stack->delay); } if (!cs->locked) pthread_mutex_unlock(&THR_LOCK_dbug); diff --git a/include/my_getopt.h b/include/my_getopt.h index c74f3ed672e..30c2eb9531a 100644 --- a/include/my_getopt.h +++ b/include/my_getopt.h @@ -68,6 +68,7 @@ extern my_error_reporter my_getopt_error_reporter; extern int handle_options (int *argc, char ***argv, const struct my_option *longopts, my_get_one_option); +extern void my_cleanup_options(const struct my_option *options); extern void my_print_help(const struct my_option *options); extern void my_print_variables(const struct my_option *options); extern void my_getopt_register_get_addr(uchar ** (*func_addr)(const char *, uint, diff --git a/libmysql/libmysql.c b/libmysql/libmysql.c index 7807982df1a..e2e42fe4a2d 100644 --- a/libmysql/libmysql.c +++ b/libmysql/libmysql.c @@ -4401,6 +4401,7 @@ static my_bool setup_one_fetch_function(MYSQL_BIND *param, MYSQL_FIELD *field) case MYSQL_TYPE_STRING: case MYSQL_TYPE_DECIMAL: case MYSQL_TYPE_NEWDECIMAL: + case MYSQL_TYPE_NEWDATE: DBUG_ASSERT(param->buffer_length != 0); param->fetch_result= fetch_result_str; break; @@ -4473,6 +4474,7 @@ static my_bool setup_one_fetch_function(MYSQL_BIND *param, MYSQL_FIELD *field) case MYSQL_TYPE_VAR_STRING: case MYSQL_TYPE_STRING: case MYSQL_TYPE_BIT: + case MYSQL_TYPE_NEWDATE: param->skip_result= skip_result_string; break; default: diff --git a/mysql-test/include/gis_keys.inc b/mysql-test/include/gis_keys.inc new file mode 100644 index 00000000000..295e0c48234 --- /dev/null +++ b/mysql-test/include/gis_keys.inc @@ -0,0 +1,46 @@ +--source include/have_geometry.inc + +# +# Spatial objects with keys +# + +# +# Bug #30825: Problems when putting a non-spatial index on a GIS column +# + +CREATE TABLE t1 (p POINT); +CREATE TABLE t2 (p POINT, INDEX(p)); +INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)')); +INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)')); + +-- no index, returns 1 as expected +SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); + +-- with index, returns 1 as expected +-- EXPLAIN shows that the index is not used though +-- due to the "most rows covered anyway, so a scan is more effective" rule +EXPLAIN +SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); +SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); + +-- adding another row to the table so that +-- the "most rows covered" rule doesn't kick in anymore +-- now EXPLAIN shows the index used on the table +-- and we're getting the wrong result again +INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)')); +INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)')); +EXPLAIN +SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); +SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); + +EXPLAIN +SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); +SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); + +EXPLAIN +SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); +SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); + +DROP TABLE t1, t2; + +--echo End of 5.0 tests diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc index df9d79157af..d55b404621d 100644 --- a/mysql-test/include/mix1.inc +++ b/mysql-test/include/mix1.inc @@ -29,6 +29,7 @@ eval SET SESSION STORAGE_ENGINE = $engine_type; --disable_warnings drop table if exists t1,t2,t3,t1m,t1i,t2m,t2i,t4; +drop procedure if exists p1; --enable_warnings @@ -1196,6 +1197,129 @@ select if(@a=@b,"ok","wrong"); drop table t1; # +# Bug #31310: Locked rows silently skipped in read-committed isolation level. +# + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); +SET SESSION AUTOCOMMIT = 0; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +--echo # Switch to connection con1 +connection con1; + +eval +CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256)) +ENGINE = $engine_type; +INSERT INTO t1 VALUES (1,2); + +--#echo 1. test for locking: + +BEGIN; +--enable_info +UPDATE t1 SET b = 12 WHERE a = 1; +--disable_info +SELECT * FROM t1; + +--echo # Switch to connection con2 +connection con2; + +--enable_info +--disable_abort_on_error +--error ER_LOCK_WAIT_TIMEOUT +UPDATE t1 SET b = 21 WHERE a = 1; +--disable_info + +--echo # Switch to connection con1 +connection con1; +SELECT * FROM t1; +ROLLBACK; + +--echo # 2. test for serialized update: + +CREATE TABLE t2 (a INT); + +TRUNCATE t1; +INSERT INTO t1 VALUES (1,'init'); + +DELIMITER |; +CREATE PROCEDURE p1() +BEGIN + UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1; + INSERT INTO t2 VALUES (); +END| +DELIMITER ;| + +BEGIN; +--enable_info +UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1; +--disable_info +SELECT * FROM t1; + +--echo # Switch to connection con2 +connection con2; + +--send CALL p1; + +--echo # Switch to connection con1 +connection con1; +SELECT * FROM t1; +COMMIT; + +let $bug31310 = 1; +while ($bug31310) +{ + let $bug31310= `SELECT 1 - COUNT(*) FROM t2`; +} + +SELECT * FROM t1; + +--echo # Switch to connection con2 +connection con2; +SELECT * FROM t1; + +--echo # Switch to connection con1 +connection con1; + +--echo # 3. test for updated key column: + +TRUNCATE t1; +TRUNCATE t2; + +INSERT INTO t1 VALUES (1,'init'); + +BEGIN; +--enable_info +UPDATE t1 SET a = 2, b = CONCAT(b, '+con1') WHERE a = 1; +--disable_info +SELECT * FROM t1; + +--echo # Switch to connection con2 +connection con2; + +--send CALL p1; + +--echo # Switch to connection con1 +connection con1; +SELECT * FROM t1; +COMMIT; + +let $bug31310 = 1; +while ($bug31310) +{ + let $bug31310= `SELECT 1 - COUNT(*) FROM t2`; +} + +SELECT * FROM t1; + +--echo # Switch to connection con2 +connection con2; +SELECT * FROM t1; + +connection default; +disconnect con1; +disconnect con2; +DROP PROCEDURE p1; +DROP TABLE t1, t2; # Bug#30747 Create table with identical constraint names behaves incorrectly # diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index e2ee11aa7e3..eda268bcedf 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -1019,12 +1019,14 @@ sub command_line_setup () { { $opt_testcase_timeout= $default_testcase_timeout; $opt_testcase_timeout*= 10 if $opt_valgrind; + $opt_testcase_timeout*= 10 if ($opt_debug and $glob_win32); } if ( ! $opt_suite_timeout ) { $opt_suite_timeout= $default_suite_timeout; $opt_suite_timeout*= 6 if $opt_valgrind; + $opt_suite_timeout*= 6 if ($opt_debug and $glob_win32); } if ( ! $opt_user ) diff --git a/mysql-test/r/compress.result b/mysql-test/r/compress.result index 68206c0dc0e..a71d840d229 100644 --- a/mysql-test/r/compress.result +++ b/mysql-test/r/compress.result @@ -611,11 +611,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL period NULL NULL NULL 41810 Using filesort +1 SIMPLE t3 index period period 4 NULL 1 1 SIMPLE t1 ref period period 4 test.t3.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort +1 SIMPLE t1 index period period 4 NULL 1 1 SIMPLE t3 ref period period 4 test.t1.period 4181 select period from t1; period diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index e79a255967b..ca3b344af53 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -438,7 +438,7 @@ explain t2; Field Type Null Key Default Extra a int(11) YES NULL b bigint(11) NO 0 -c bigint(11) NO 0 +c bigint(11) unsigned NO 0 d date YES NULL e varchar(1) NO f datetime YES NULL diff --git a/mysql-test/r/csv.result b/mysql-test/r/csv.result index 86ba5002af8..69f77dc3cd8 100644 --- a/mysql-test/r/csv.result +++ b/mysql-test/r/csv.result @@ -4929,7 +4929,7 @@ Note 1051 Unknown table 't2' Note 1051 Unknown table 't3' Note 1051 Unknown table 't4' DROP TABLE IF EXISTS bug13894; -CREATE TABLE bug13894 ( val integer ) ENGINE = CSV; +CREATE TABLE bug13894 ( val integer not null ) ENGINE = CSV; INSERT INTO bug13894 VALUES (5); INSERT INTO bug13894 VALUES (10); INSERT INTO bug13894 VALUES (11); @@ -4949,7 +4949,7 @@ val 11 DROP TABLE bug13894; DROP TABLE IF EXISTS bug14672; -CREATE TABLE bug14672 (c1 integer) engine = CSV; +CREATE TABLE bug14672 (c1 integer not null) engine = CSV; INSERT INTO bug14672 VALUES (1), (2), (3); SELECT * FROM bug14672; c1 @@ -4975,7 +4975,7 @@ c1 4 5 DROP TABLE bug14672; -CREATE TABLE test_concurrent_insert ( val integer ) ENGINE = CSV; +CREATE TABLE test_concurrent_insert ( val integer not null ) ENGINE = CSV; LOCK TABLES test_concurrent_insert READ LOCAL; INSERT INTO test_concurrent_insert VALUES (1); SELECT * FROM test_concurrent_insert; @@ -4992,7 +4992,7 @@ val 2 UNLOCK TABLES; DROP TABLE test_concurrent_insert; -CREATE TABLE test_repair_table ( val integer ) ENGINE = CSV; +CREATE TABLE test_repair_table ( val integer not null ) ENGINE = CSV; CHECK TABLE test_repair_table; Table Op Msg_type Msg_text test.test_repair_table check status OK @@ -5000,7 +5000,7 @@ REPAIR TABLE test_repair_table; Table Op Msg_type Msg_text test.test_repair_table repair status OK DROP TABLE test_repair_table; -CREATE TABLE test_repair_table2 ( val integer ) ENGINE = CSV; +CREATE TABLE test_repair_table2 ( val integer not null ) ENGINE = CSV; SELECT * from test_repair_table2; val Warnings: @@ -5011,7 +5011,7 @@ CHECK TABLE test_repair_table2; Table Op Msg_type Msg_text test.test_repair_table2 check status OK DROP TABLE test_repair_table2; -CREATE TABLE test_repair_table3 ( val integer ) ENGINE = CSV; +CREATE TABLE test_repair_table3 ( val integer not null ) ENGINE = CSV; CHECK TABLE test_repair_table3; Table Op Msg_type Msg_text test.test_repair_table3 check error Corrupt @@ -5114,7 +5114,7 @@ num magic_no company_name founded 1 0102 CORRECT 1876 1 0102 CORRECT2 1876 DROP TABLE test_repair_table5; -create table t1 (a int) engine=csv; +create table t1 (a int not null) engine=csv; insert t1 values (1); delete from t1; affected rows: 1 @@ -5138,7 +5138,7 @@ insert t1 values (1),(2),(3),(4),(5); truncate table t1; affected rows: 0 drop table t1; -create table t1 (v varchar(32)); +create table t1 (v varchar(32) not null); insert into t1 values ('def'),('abc'),('hij'),('3r4f'); select * from t1; v @@ -5193,8 +5193,8 @@ select * from t1 where i between 2 and 4 and v in ('def','3r4f','lmn'); i v 4 3r4f drop table t1; -create table bug15205 (val int(11) default null) engine=csv; -create table bug15205_2 (val int(11) default null) engine=csv; +create table bug15205 (val int(11) not null) engine=csv; +create table bug15205_2 (val int(11) not null) engine=csv; select * from bug15205; ERROR HY000: Can't get stat of './test/bug15205.CSV' (Errcode: 2) select * from bug15205_2; @@ -5205,8 +5205,8 @@ drop table bug15205; drop table bug15205_2; set names latin1; create table t1 ( -c varchar(1), -name varchar(64) +c varchar(1) not null, +name varchar(64) not null ) character set latin1 engine=csv; insert into t1 values (0xC0,'LATIN CAPITAL LETTER A WITH GRAVE'); insert into t1 values (0xE0,'LATIN SMALL LETTER A WITH GRAVE'); @@ -5224,9 +5224,9 @@ FE þ LATIN SMALL LETTER THORN FF ÿ LATIN SMALL LETTER Y WITH DIAERESIS drop table t1; End of 5.0 tests -create table bug22080_1 (id int,string varchar(64)) Engine=CSV; -create table bug22080_2 (id int,string varchar(64)) Engine=CSV; -create table bug22080_3 (id int,string varchar(64)) Engine=CSV; +create table bug22080_1 (id int not null,string varchar(64) not null) Engine=CSV; +create table bug22080_2 (id int not null,string varchar(64) not null) Engine=CSV; +create table bug22080_3 (id int not null,string varchar(64) not null) Engine=CSV; insert into bug22080_1 values(1,'string'); insert into bug22080_1 values(2,'string'); insert into bug22080_1 values(3,'string'); @@ -5237,7 +5237,7 @@ check table bug22080_3; Table Op Msg_type Msg_text test.bug22080_3 check error Corrupt drop tables bug22080_1,bug22080_2,bug22080_3; -create table float_test (id float,string varchar(64)) Engine=CSV; +create table float_test (id float not null,string varchar(64) not null) Engine=CSV; insert into float_test values(1.0,'string'); insert into float_test values(2.23,'serg.g'); insert into float_test values(0.03,'string'); @@ -5254,14 +5254,14 @@ id string 9.67 string drop table float_test; CREATE TABLE `bug21328` ( -`col1` int(11) DEFAULT NULL, -`col2` int(11) DEFAULT NULL, -`col3` int(11) DEFAULT NULL +`col1` int(11) NOT NULL, +`col2` int(11) NOT NULL, +`col3` int(11) NOT NULL ) ENGINE=CSV; -insert into bug21328 values (1,NULL,NULL); +insert into bug21328 values (1,0,0); alter table bug21328 engine=myisam; drop table bug21328; -create table t1(a blob, b int) engine=csv; +create table t1(a blob not null, b int not null) engine=csv; insert into t1 values('a', 1); flush tables; update t1 set b=2; @@ -5269,7 +5269,7 @@ select * from t1; a b a 2 drop table t1; -create table t1(a int) engine=csv; +create table t1(a int not null) engine=csv; insert into t1 values(-1), (-123.34), (2), (-23); select * from t1; a @@ -5281,7 +5281,7 @@ check table t1; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; -create table t1(a int, b int) engine=csv; +create table t1(a int not null, b int not null) engine=csv; repair table t1; Table Op Msg_type Msg_text test.t1 repair Warning Data truncated for column 'a' at row 5 @@ -5299,7 +5299,7 @@ check table t1; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; -create table t1(a int) engine=csv; +create table t1(a int not null) engine=csv; insert into t1 values (0), (1), (2); delete from t1 limit 2; check table t1; @@ -5315,4 +5315,62 @@ test.t1 check status OK select * from t1; a drop table t1; +create table t1(a datetime not null) engine=csv; +insert into t1 values(); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +select * from t1; +a +0000-00-00 00:00:00 +drop table t1; +create table t1(a set('foo','bar') not null) engine=csv; +insert into t1 values(); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +select * from t1; +a + +drop table t1; +create table t1(a varchar(32) not null) engine=csv; +insert into t1 values(); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +select * from t1; +a + +drop table t1; +create table t1(a int not null) engine=csv; +insert into t1 values(); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +select * from t1; +a +0 +drop table t1; +create table t1(a blob not null) engine=csv; +insert into t1 values(); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +select * from t1; +a + +drop table t1; +create table t1(a bit(1) not null) engine=csv; +insert into t1 values(); +Warnings: +Warning 1364 Field 'a' doesn't have a default value +select BIN(a) from t1; +BIN(a) +0 +drop table t1; +create table t1(a enum('foo','bar') default null) engine=csv; +ERROR HY000: Can't create table 'test.t1' (errno: -1) +create table t1(a enum('foo','bar') default 'foo') engine=csv; +ERROR HY000: Can't create table 'test.t1' (errno: -1) +create table t1(a enum('foo','bar') default 'foo' not null) engine=csv; +insert into t1 values(); +select * from t1; +a +foo +drop table t1; End of 5.1 tests diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 1097486f185..a0a8455b496 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -922,4 +922,7 @@ ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_gen select * from t1 where a=if(b<10,_ucs2 0x0062,_ucs2 0x00C0); ERROR HY000: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (ucs2_general_ci,COERCIBLE) for operation '=' drop table t1; +select hex(char(0x41 using ucs2)); +hex(char(0x41 using ucs2)) +0041 End of 5.0 tests diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 715dfb1b7d8..8243b2bc015 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -1538,12 +1538,12 @@ char(53647 using utf8) Ñ select char(0xff,0x8f using utf8); char(0xff,0x8f using utf8) -ÿ + Warnings: Warning 1300 Invalid utf8 character string: 'FF8F' select convert(char(0xff,0x8f) using utf8); convert(char(0xff,0x8f) using utf8) -ÿ + Warnings: Warning 1300 Invalid utf8 character string: 'FF8F' set sql_mode=traditional; @@ -1730,3 +1730,41 @@ i 1 н1234567890 DROP TABLE t1, t2; +set sql_mode=traditional; +select hex(char(0xFF using utf8)); +hex(char(0xFF using utf8)) +NULL +Warnings: +Error 1300 Invalid utf8 character string: 'FF' +select hex(convert(0xFF using utf8)); +hex(convert(0xFF using utf8)) +NULL +Warnings: +Error 1300 Invalid utf8 character string: 'FF' +select hex(_utf8 0x616263FF); +ERROR HY000: Invalid utf8 character string: 'FF' +select hex(_utf8 X'616263FF'); +ERROR HY000: Invalid utf8 character string: 'FF' +select hex(_utf8 B'001111111111'); +ERROR HY000: Invalid utf8 character string: 'FF' +select (_utf8 X'616263FF'); +ERROR HY000: Invalid utf8 character string: 'FF' +set sql_mode=default; +select hex(char(0xFF using utf8)); +hex(char(0xFF using utf8)) + +Warnings: +Warning 1300 Invalid utf8 character string: 'FF' +select hex(convert(0xFF using utf8)); +hex(convert(0xFF using utf8)) + +Warnings: +Warning 1300 Invalid utf8 character string: 'FF' +select hex(_utf8 0x616263FF); +ERROR HY000: Invalid utf8 character string: 'FF' +select hex(_utf8 X'616263FF'); +ERROR HY000: Invalid utf8 character string: 'FF' +select hex(_utf8 B'001111111111'); +ERROR HY000: Invalid utf8 character string: 'FF' +select (_utf8 X'616263FF'); +ERROR HY000: Invalid utf8 character string: 'FF' diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result index c1c8b7b060e..fbe46e9fa21 100644 --- a/mysql-test/r/date_formats.result +++ b/mysql-test/r/date_formats.result @@ -478,7 +478,7 @@ str_to_date(a,b) create table t2 select str_to_date(a,b) from t1; describe t2; Field Type Null Key Default Extra -str_to_date(a,b) binary(29) YES NULL +str_to_date(a,b) datetime YES NULL select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") as f1, str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S") as f2, str_to_date("2003-01-02", "%Y-%m-%d") as f3, diff --git a/mysql-test/r/ddl_i18n_koi8r.result b/mysql-test/r/ddl_i18n_koi8r.result index 3d38319df21..fbaf2f2fd4a 100644 --- a/mysql-test/r/ddl_i18n_koi8r.result +++ b/mysql-test/r/ddl_i18n_koi8r.result @@ -29,7 +29,8 @@ v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VI SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE table_name = 'v1'| TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION -NULL mysqltest1 v1 SELECT 'ÔÅÓÔ' AS c1, ËÏÌ AS c2 NONE YES root@localhost DEFINER koi8r koi8r_general_ci +NULL mysqltest1 v1 SELECT 'ÔÅÓÔ' AS c1, ËÏÌ AS c2 +FROM t1 NONE YES root@localhost DEFINER koi8r koi8r_general_ci SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE table_name = 'v2'| TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION @@ -68,7 +69,8 @@ v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VI SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE table_name = 'v1'| TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION -NULL mysqltest1 v1 SELECT 'ÔÅÓÔ' AS c1, ËÏÌ AS c2 NONE YES root@localhost DEFINER koi8r koi8r_general_ci +NULL mysqltest1 v1 SELECT 'ÔÅÓÔ' AS c1, ËÏÌ AS c2 +FROM t1 NONE YES root@localhost DEFINER koi8r koi8r_general_ci SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE table_name = 'v2'| TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION diff --git a/mysql-test/r/ddl_i18n_utf8.result b/mysql-test/r/ddl_i18n_utf8.result index b8e690bfeb7..d0f176c1985 100644 --- a/mysql-test/r/ddl_i18n_utf8.result +++ b/mysql-test/r/ddl_i18n_utf8.result @@ -29,7 +29,8 @@ v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VI SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE table_name = 'v1'| TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION -NULL mysqltest1 v1 SELECT 'теÑÑ‚' AS c1, кол AS c2 NONE YES root@localhost DEFINER utf8 utf8_general_ci +NULL mysqltest1 v1 SELECT 'теÑÑ‚' AS c1, кол AS c2 +FROM t1 NONE YES root@localhost DEFINER utf8 utf8_general_ci SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE table_name = 'v2'| TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION @@ -68,7 +69,8 @@ v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VI SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE table_name = 'v1'| TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION -NULL mysqltest1 v1 SELECT 'теÑÑ‚' AS c1, кол AS c2 NONE YES root@localhost DEFINER utf8 utf8_general_ci +NULL mysqltest1 v1 SELECT 'теÑÑ‚' AS c1, кол AS c2 +FROM t1 NONE YES root@localhost DEFINER utf8 utf8_general_ci SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE table_name = 'v2'| TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION diff --git a/mysql-test/r/delete.result b/mysql-test/r/delete.result index 5084498c01c..eb93c69d960 100644 --- a/mysql-test/r/delete.result +++ b/mysql-test/r/delete.result @@ -271,3 +271,11 @@ a DROP TABLE t1, t2; DROP DATABASE db1; DROP DATABASE db2; +CREATE FUNCTION f1() RETURNS INT RETURN 1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0); +DELETE FROM t1 ORDER BY (f1(10)) LIMIT 1; +ERROR 42000: Incorrect number of arguments for FUNCTION test.f1; expected 0, got 1 +DROP TABLE t1; +DROP FUNCTION f1; +End of 5.0 tests diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 7da82ada121..306c51fb8cf 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -326,7 +326,8 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DERIVED t2 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index drop table t2; CREATE TABLE `t1` ( `itemid` int(11) NOT NULL default '0', `grpid` varchar(15) NOT NULL default '', `vendor` int(11) NOT NULL default '0', `date_` date NOT NULL default '0000-00-00', `price` decimal(12,2) NOT NULL default '0.00', PRIMARY KEY (`itemid`,`grpid`,`vendor`,`date_`), KEY `itemid` (`itemid`,`vendor`), KEY `itemid_2` (`itemid`,`date_`)); -insert into t1 values (128, 'rozn', 2, now(), 10),(128, 'rozn', 1, now(), 10); +insert into t1 values (128, 'rozn', 2, curdate(), 10), +(128, 'rozn', 1, curdate(), 10); SELECT MIN(price) min, MAX(price) max, AVG(price) avg FROM (SELECT SUBSTRING( MAX(concat(date_,";",price)), 12) price FROM t1 WHERE itemid=128 AND grpid='rozn' GROUP BY itemid, grpid, vendor) lastprices; min max avg 10.00 10.00 10 diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index fce01ffd171..ce29d25b736 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1368,4 +1368,14 @@ SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1; MIN(a) MIN(b) 1 2 DROP TABLE t1, t2, t3, t4, t5; +CREATE TABLE t1 (a INT); +INSERT INTO t1 values (),(),(); +SELECT (SELECT SLEEP(0) FROM t1 ORDER BY AVG(DISTINCT a) ) as x FROM t1 +GROUP BY x; +x +0 +SELECT 1 FROM t1 GROUP BY (SELECT SLEEP(0) FROM t1 ORDER BY AVG(DISTINCT a) ); +1 +1 +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 4f64d39ccc0..80fa8f17a2a 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -564,4 +564,9 @@ explain select f2 from t2 where f2 in (1,'b'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index t2f2 t2f2 5 NULL 3 Using where; Using index drop table t1, t2; +create table t1 (a time, key(a)); +insert into t1 values (),(),(),(),(),(),(),(),(),(); +select a from t1 where a not in (a,a,a) group by a; +a +drop table t1; End of 5.1 tests diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index e2e93796afe..6a476d12896 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -369,4 +369,42 @@ mod(5, cast(-2 as unsigned)) mod(5, 18446744073709551614) mod(5, -2) select pow(cast(-2 as unsigned), 5), pow(18446744073709551614, 5), pow(-2, 5); pow(cast(-2 as unsigned), 5) pow(18446744073709551614, 5) pow(-2, 5) 2.1359870359209e+96 2.1359870359209e+96 -32 +CREATE TABLE t1 (a timestamp, b varchar(20), c bit(1)); +INSERT INTO t1 VALUES('1998-09-23', 'str1', 1), ('2003-03-25', 'str2', 0); +SELECT a DIV 900 y FROM t1 GROUP BY y; +y +22201025555 +22255916666 +SELECT DISTINCT a DIV 900 y FROM t1; +y +22201025555 +22255916666 +SELECT b DIV 900 y FROM t1 GROUP BY y; +y +0 +SELECT c DIV 900 y FROM t1 GROUP BY y; +y +0 +DROP TABLE t1; +CREATE TABLE t1(a LONGBLOB); +INSERT INTO t1 VALUES('1'),('2'),('3'); +SELECT DISTINCT (a DIV 254576881) FROM t1; +(a DIV 254576881) +0 +SELECT (a DIV 254576881) FROM t1 UNION ALL +SELECT (a DIV 254576881) FROM t1; +(a DIV 254576881) +0 +0 +0 +0 +0 +0 +DROP TABLE t1; +CREATE TABLE t1(a SET('a','b','c')); +INSERT INTO t1 VALUES ('a'); +SELECT a DIV 2 FROM t1 UNION SELECT a DIV 2 FROM t1; +a DIV 2 +0 +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index 39bf1470afe..aa75cde0525 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -190,6 +190,28 @@ ERROR 21000: Operand should contain 1 column(s) drop table table_26093; drop function func_26093_a; drop function func_26093_b; +SELECT NAME_CONST('test', NOW()); +ERROR HY000: Incorrect arguments to NAME_CONST +SELECT NAME_CONST('test', UPPER('test')); +ERROR HY000: Incorrect arguments to NAME_CONST +SELECT NAME_CONST('test', NULL); +test +NULL +SELECT NAME_CONST('test', 1); +test +1 +SELECT NAME_CONST('test', -1); +test +-1 +SELECT NAME_CONST('test', 1.0); +test +1.0 +SELECT NAME_CONST('test', -1.0); +test +-1.0 +SELECT NAME_CONST('test', 'test'); +test +test End of 5.0 tests select connection_id() > 0; connection_id() > 0 diff --git a/mysql-test/r/func_regexp.result b/mysql-test/r/func_regexp.result index 4e35c2a1348..05e56f05457 100644 --- a/mysql-test/r/func_regexp.result +++ b/mysql-test/r/func_regexp.result @@ -98,3 +98,8 @@ R2 R3 deallocate prepare stmt1; drop table t1; +End of 4.1 tests +SELECT 1 REGEXP NULL; +1 REGEXP NULL +NULL +End of 5.0 tests diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result index 5d7a564b187..942073e3fbd 100644 --- a/mysql-test/r/func_sapdb.result +++ b/mysql-test/r/func_sapdb.result @@ -199,7 +199,7 @@ f2 datetime YES NULL f3 time YES NULL f4 time YES NULL f5 time YES NULL -f6 time NO 00:00:00 +f6 time YES NULL f7 datetime YES NULL f8 date YES NULL f9 time YES NULL diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 6fbb9c811a7..78d86d3f342 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -726,7 +726,7 @@ t1 CREATE TABLE `t1` ( `oct(130)` varchar(64) NOT NULL DEFAULT '', `conv(130,16,10)` varchar(64) NOT NULL DEFAULT '', `hex(130)` varchar(6) NOT NULL DEFAULT '', - `char(130)` varbinary(1) NOT NULL DEFAULT '', + `char(130)` varbinary(4) NOT NULL DEFAULT '', `format(130,10)` varchar(4) NOT NULL DEFAULT '', `left(_latin2'a',1)` varchar(1) CHARACTER SET latin2 NOT NULL DEFAULT '', `right(_latin2'a',1)` varchar(1) CHARACTER SET latin2 NOT NULL DEFAULT '', @@ -2486,4 +2486,14 @@ SUBSTR(a,1,len) ba DROP TABLE t1; +CREATE TABLE t1 AS SELECT CHAR(0x414243) as c1; +SELECT HEX(c1) from t1; +HEX(c1) +414243 +DROP TABLE t1; +CREATE VIEW v1 AS SELECT CHAR(0x414243) as c1; +SELECT HEX(c1) from v1; +HEX(c1) +414243 +DROP VIEW v1; End of 5.0 tests diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 3105bc3b578..a355d7929b5 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1027,6 +1027,15 @@ fmtddate field2 Sep-4 12:00AM abcd DROP TABLE testBug8868; SET NAMES DEFAULT; +CREATE TABLE t1 ( +a TIMESTAMP +); +INSERT INTO t1 VALUES (now()), (now()); +SELECT 1 FROM t1 ORDER BY MAKETIME(1, 1, a); +1 +1 +1 +DROP TABLE t1; (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H) union (select time_format(timediff(now(), DATE_SUB(now(),INTERVAL 5 DAY)),'%H') As H); diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result index 6e3aebe8d47..f8e0085bf59 100644 --- a/mysql-test/r/gis-rtree.result +++ b/mysql-test/r/gis-rtree.result @@ -167,7 +167,7 @@ count(*) 150 EXPLAIN SELECT fid, AsText(g) FROM t1 WHERE Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))')); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range g g 32 NULL 8 Using where +1 SIMPLE t1 range g g 34 NULL 8 Using where SELECT fid, AsText(g) FROM t1 WHERE Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))')); fid AsText(g) 1 LINESTRING(150 150,150 150) @@ -301,7 +301,7 @@ count(*) EXPLAIN SELECT fid, AsText(g) FROM t2 WHERE Within(g, GeomFromText('Polygon((40 40,60 40,60 60,40 60,40 40))')); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range g g 32 NULL 4 Using where +1 SIMPLE t2 range g g 34 NULL 4 Using where SELECT fid, AsText(g) FROM t2 WHERE Within(g, GeomFromText('Polygon((40 40,60 40,60 60,40 60,40 40))')); fid AsText(g) @@ -1425,6 +1425,37 @@ CHECK TABLE t1 EXTENDED; Table Op Msg_type Msg_text test.t1 check status OK DROP TABLE t1; +create table t1 (a geometry not null, spatial index(a)); +insert into t1 values (PointFromWKB(POINT(1.1517219314031e+164, 131072))); +insert into t1 values (PointFromWKB(POINT(9.1248812352444e+192, 2.9740338169556e+284))); +insert into t1 values (PointFromWKB(POINT(4.7783097267365e-299, -0))); +insert into t1 values (PointFromWKB(POINT(1.49166814624e-154, 2.0880974297595e-53))); +insert into t1 values (PointFromWKB(POINT(4.0917382598702e+149, 1.2024538023802e+111))); +insert into t1 values (PointFromWKB(POINT(2.0349165139404e+236, 2.9993936277913e-241))); +insert into t1 values (PointFromWKB(POINT(2.5243548967072e-29, 1.2024538023802e+111))); +insert into t1 values (PointFromWKB(POINT(0, 6.9835074892995e-251))); +insert into t1 values (PointFromWKB(POINT(2.0880974297595e-53, 3.1050361846014e+231))); +insert into t1 values (PointFromWKB(POINT(2.8728483499323e-188, 2.4600631144627e+260))); +insert into t1 values (PointFromWKB(POINT(3.0517578125e-05, 2.0349165139404e+236))); +insert into t1 values (PointFromWKB(POINT(1.1517219314031e+164, 1.1818212630766e-125))); +insert into t1 values (PointFromWKB(POINT(2.481040258324e-265, 5.7766220027675e-275))); +insert into t1 values (PointFromWKB(POINT(2.0880974297595e-53, 2.5243548967072e-29))); +insert into t1 values (PointFromWKB(POINT(5.7766220027675e-275, 9.9464647281957e+86))); +insert into t1 values (PointFromWKB(POINT(2.2181357552967e+130, 3.7857669957337e-270))); +insert into t1 values (PointFromWKB(POINT(4.5767114681874e-246, 3.6893488147419e+19))); +insert into t1 values (PointFromWKB(POINT(4.5767114681874e-246, 3.7537584144024e+255))); +insert into t1 values (PointFromWKB(POINT(3.7857669957337e-270, 1.8033161362863e-130))); +insert into t1 values (PointFromWKB(POINT(0, 5.8774717541114e-39))); +insert into t1 values (PointFromWKB(POINT(1.1517219314031e+164, 2.2761049594727e-159))); +insert into t1 values (PointFromWKB(POINT(6.243497100632e+144, 3.7857669957337e-270))); +insert into t1 values (PointFromWKB(POINT(3.7857669957337e-270, 2.6355494858076e-82))); +insert into t1 values (PointFromWKB(POINT(2.0349165139404e+236, 3.8518598887745e-34))); +insert into t1 values (PointFromWKB(POINT(4.6566128730774e-10, 2.0880974297595e-53))); +insert into t1 values (PointFromWKB(POINT(2.0880974297595e-53, 1.8827498946116e-183))); +insert into t1 values (PointFromWKB(POINT(1.8033161362863e-130, 9.1248812352444e+192))); +insert into t1 values (PointFromWKB(POINT(4.7783097267365e-299, 2.2761049594727e-159))); +insert into t1 values (PointFromWKB(POINT(1.94906280228e+289, 1.2338789709327e-178))); +drop table t1; CREATE TABLE t1(foo GEOMETRY NOT NULL, SPATIAL INDEX(foo) ); INSERT INTO t1(foo) VALUES (NULL); ERROR 23000: Column 'foo' cannot be null diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index 7b4b02f3ff3..61394409947 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -736,6 +736,12 @@ SELECT * FROM t1; a NULL DROP TABLE t1; +CREATE TABLE `t1` ( `col9` set('a'), `col89` date); +INSERT INTO `t1` VALUES ('','0000-00-00'); +select geomfromtext(col9,col89) as a from t1; +a +NULL +DROP TABLE t1; End of 4.1 tests create table t1 (s1 geometry not null,s2 char(100)); create trigger t1_bu before update on t1 for each row set new.s1 = null; @@ -889,6 +895,45 @@ drop table t1, t2; SELECT 1; 1 1 +CREATE TABLE t1 (p POINT); +CREATE TABLE t2 (p POINT, INDEX(p)); +INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)')); +INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)')); +SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); +COUNT(*) +1 +EXPLAIN +SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 system p NULL NULL NULL 1 +SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); +COUNT(*) +1 +INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)')); +INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)')); +EXPLAIN +SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); +COUNT(*) +2 +EXPLAIN +SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref p p 28 const 1 Using where +SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); +COUNT(*) +2 +EXPLAIN +SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where +SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); +COUNT(*) +2 +DROP TABLE t1, t2; +End of 5.0 tests End of 5.0 tests create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime); create view v1 as select * from t1; diff --git a/mysql-test/r/grant2.result b/mysql-test/r/grant2.result index 21bad4fddcb..b94de265d0c 100644 --- a/mysql-test/r/grant2.result +++ b/mysql-test/r/grant2.result @@ -422,4 +422,22 @@ revoke all privileges, grant option from mysqltest_1@localhost; revoke all privileges, grant option from mysqltest_2@localhost; drop user mysqltest_1@localhost; drop user mysqltest_2@localhost; +CREATE DATABASE db1; +USE db1; +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(2,2); +CREATE TABLE t2 (b INT, c INT); +INSERT INTO t2 VALUES (1,100),(2,200); +GRANT SELECT ON t1 TO mysqltest1@localhost; +GRANT SELECT (b) ON t2 TO mysqltest1@localhost; +USE db1; +SELECT c FROM t2; +ERROR 42000: SELECT command denied to user 'mysqltest1'@'localhost' for column 'c' in table 't2' +SELECT * FROM t2; +ERROR 42000: SELECT command denied to user 'mysqltest1'@'localhost' for column 'c' in table 't2' +SELECT * FROM t1 JOIN t2 USING (b); +ERROR 42000: SELECT command denied to user 'mysqltest1'@'localhost' for column 'c' in table 't2' +DROP TABLE db1.t1, db1.t2; +DROP USER mysqltest1@localhost; +DROP DATABASE db1; End of 5.0 tests diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 2faf7832aca..7498267c91d 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -1093,10 +1093,156 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 144 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index; Using filesort +1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index; Using filesort +1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index +SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a; +a +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +15 +16 +17 +18 +19 +20 +21 +22 +23 +24 +25 +26 +27 +28 +29 +30 +31 +32 +33 +34 +35 +36 +37 +38 +39 +40 +41 +42 +43 +44 +45 +46 +47 +48 +49 +50 +51 +52 +53 +54 +55 +56 +57 +58 +59 +60 +61 +62 +63 +64 +65 +66 +67 +68 +69 +70 +71 +72 +73 +74 +75 +76 +77 +78 +79 +80 +81 +82 +83 +84 +85 +86 +87 +88 +89 +90 +91 +92 +93 +94 +95 +96 +97 +98 +99 +100 +101 +102 +103 +104 +105 +106 +107 +108 +109 +110 +111 +112 +113 +114 +115 +116 +117 +118 +119 +120 +121 +122 +123 +124 +125 +126 +127 +128 +129 +130 +131 +132 +133 +134 +135 +136 +137 +138 +139 +140 +141 +142 +143 +144 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) IGNORE INDEX FOR GROUP BY (i2) GROUP BY a; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/r/heap_btree.result b/mysql-test/r/heap_btree.result index 44241563dda..9db03855c01 100644 --- a/mysql-test/r/heap_btree.result +++ b/mysql-test/r/heap_btree.result @@ -307,6 +307,13 @@ UNIQUE USING BTREE(c1) ) ENGINE= MEMORY DEFAULT CHARSET= utf8; INSERT INTO t1 VALUES('1'), ('2'); DROP TABLE t1; +CREATE TABLE t1 (a INT, KEY USING BTREE(a)) ENGINE=MEMORY; +INSERT INTO t1 VALUES(1),(2),(2); +DELETE FROM t1 WHERE a=2; +SELECT * FROM t1; +a +1 +DROP TABLE t1; End of 4.1 tests CREATE TABLE t1(val INT, KEY USING BTREE(val)) ENGINE=memory; INSERT INTO t1 VALUES(0); diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index d1120d24884..7693fe628ef 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -387,10 +387,14 @@ Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_par select * from information_schema.views where TABLE_NAME like "v%"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION NULL test v0 select schema_name from information_schema.schemata NONE NO root@localhost DEFINER latin1 latin1_swedish_ci -NULL test v1 select table_name from information_schema.tables NONE NO root@localhost DEFINER latin1 latin1_swedish_ci -NULL test v2 select column_name from information_schema.columns NONE NO root@localhost DEFINER latin1 latin1_swedish_ci -NULL test v3 select CHARACTER_SET_NAME from information_schema.character_sets NONE NO root@localhost DEFINER latin1 latin1_swedish_ci -NULL test v4 select COLLATION_NAME from information_schema.collations NONE NO root@localhost DEFINER latin1 latin1_swedish_ci +NULL test v1 select table_name from information_schema.tables +where table_name="v1" NONE NO root@localhost DEFINER latin1 latin1_swedish_ci +NULL test v2 select column_name from information_schema.columns +where table_name="v2" NONE NO root@localhost DEFINER latin1 latin1_swedish_ci +NULL test v3 select CHARACTER_SET_NAME from information_schema.character_sets +where CHARACTER_SET_NAME like "latin1%" NONE NO root@localhost DEFINER latin1 latin1_swedish_ci +NULL test v4 select COLLATION_NAME from information_schema.collations +where COLLATION_NAME like "latin1%" NONE NO root@localhost DEFINER latin1 latin1_swedish_ci drop view v0, v1, v2, v3, v4; create table t1 (a int); grant select,update,insert on t1 to mysqltest_1@localhost; @@ -1466,6 +1470,10 @@ f7 datetime NO NULL f8 datetime YES 2006-01-01 00:00:00 drop table t1; End of 5.0 tests. +show fields from information_schema.table_names; +ERROR 42S02: Unknown table 'table_names' in information_schema +show keys from information_schema.table_names; +ERROR 42S02: Unknown table 'table_names' in information_schema select * from information_schema.engines WHERE ENGINE="MyISAM"; ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS MyISAM DEFAULT Default engine as of MySQL 3.23 with great performance NO NO NO @@ -1540,4 +1548,60 @@ count(*) select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='' AND TABLE_NAME='nonexisting'; count(*) 0 +CREATE VIEW v1 +AS SELECT * +FROM INFORMATION_SCHEMA.TABLES; +SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'v1'; +VIEW_DEFINITION +SELECT * +FROM INFORMATION_SCHEMA.TABLES +DROP VIEW v1; +SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA +WHERE SCHEMA_NAME ='information_schema'; +SCHEMA_NAME +information_schema +SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES +WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db'; +TABLE_COLLATION +utf8_bin +select * from information_schema.columns where table_schema = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT +select * from `information_schema`.`COLUMNS` where `TABLE_NAME` = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT +select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME +select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME +select * from `information_schema`.`PARTITIONS` where `TABLE_SCHEMA` = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME +select * from `information_schema`.`PARTITIONS` where `TABLE_NAME` = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_ORDINAL_POSITION SUBPARTITION_ORDINAL_POSITION PARTITION_METHOD SUBPARTITION_METHOD PARTITION_EXPRESSION SUBPARTITION_EXPRESSION PARTITION_DESCRIPTION TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE CREATE_TIME UPDATE_TIME CHECK_TIME CHECKSUM PARTITION_COMMENT NODEGROUP TABLESPACE_NAME +select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME +select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE TABLE_NAME REFERENCED_TABLE_NAME +select * from information_schema.schemata where schema_name = NULL; +CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH +select * from `information_schema`.`STATISTICS` where `TABLE_SCHEMA` = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT +select * from `information_schema`.`STATISTICS` where `TABLE_NAME` = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT +select * from information_schema.tables where table_schema = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT +select * from information_schema.tables where table_catalog = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT +select * from information_schema.tables where table_name = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT +select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE +select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL; +CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE +select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_SCHEMA` = NULL; +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION +select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_TABLE` = NULL; +TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION +select * from `information_schema`.`VIEWS` where `TABLE_SCHEMA` = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION +select * from `information_schema`.`VIEWS` where `TABLE_NAME` = NULL; +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION End of 5.1 tests. diff --git a/mysql-test/r/innodb_gis.result b/mysql-test/r/innodb_gis.result index 7f54a78087b..ef8bdcc5f3e 100644 --- a/mysql-test/r/innodb_gis.result +++ b/mysql-test/r/innodb_gis.result @@ -546,5 +546,44 @@ Overlaps(@horiz1, @point2) 0 DROP TABLE t1; End of 5.0 tests +CREATE TABLE t1 (p POINT); +CREATE TABLE t2 (p POINT, INDEX(p)); +INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)')); +INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)')); +SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); +COUNT(*) +1 +EXPLAIN +SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref p p 28 const 1 Using where +SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); +COUNT(*) +1 +INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)')); +INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)')); +EXPLAIN +SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); +COUNT(*) +2 +EXPLAIN +SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref p p 28 const 1 Using where +SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); +COUNT(*) +2 +EXPLAIN +SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where +SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); +COUNT(*) +2 +DROP TABLE t1, t2; +End of 5.0 tests create table t1 (g geometry not null, spatial gk(g)) engine=innodb; ERROR HY000: The used table type doesn't support SPATIAL indexes diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result index 2a0f9a930b8..05db4436a65 100644 --- a/mysql-test/r/innodb_mysql.result +++ b/mysql-test/r/innodb_mysql.result @@ -1,5 +1,6 @@ SET SESSION STORAGE_ENGINE = InnoDB; drop table if exists t1,t2,t3,t1m,t1i,t2m,t2i,t4; +drop procedure if exists p1; create table t1 ( c_id int(11) not null default '0', org_id int(11) default null, @@ -1453,6 +1454,85 @@ select if(@a=@b,"ok","wrong"); if(@a=@b,"ok","wrong") ok drop table t1; +SET SESSION AUTOCOMMIT = 0; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +# Switch to connection con1 +CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256)) +ENGINE = InnoDB; +INSERT INTO t1 VALUES (1,2); +BEGIN; +UPDATE t1 SET b = 12 WHERE a = 1; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +SELECT * FROM t1; +a b +1 12 +# Switch to connection con2 +UPDATE t1 SET b = 21 WHERE a = 1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +# Switch to connection con1 +SELECT * FROM t1; +a b +1 12 +ROLLBACK; +# 2. test for serialized update: +CREATE TABLE t2 (a INT); +TRUNCATE t1; +INSERT INTO t1 VALUES (1,'init'); +CREATE PROCEDURE p1() +BEGIN +UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1; +INSERT INTO t2 VALUES (); +END| +BEGIN; +UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +SELECT * FROM t1; +a b +1 init+con1 +# Switch to connection con2 +CALL p1;; +# Switch to connection con1 +SELECT * FROM t1; +a b +1 init+con1 +COMMIT; +SELECT * FROM t1; +a b +1 init+con1 +# Switch to connection con2 +SELECT * FROM t1; +a b +1 init+con1+con2 +# Switch to connection con1 +# 3. test for updated key column: +TRUNCATE t1; +TRUNCATE t2; +INSERT INTO t1 VALUES (1,'init'); +BEGIN; +UPDATE t1 SET a = 2, b = CONCAT(b, '+con1') WHERE a = 1; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +SELECT * FROM t1; +a b +2 init+con1 +# Switch to connection con2 +CALL p1;; +# Switch to connection con1 +SELECT * FROM t1; +a b +2 init+con1 +COMMIT; +SELECT * FROM t1; +a b +2 init+con1 +# Switch to connection con2 +SELECT * FROM t1; +a b +2 init+con1 +DROP PROCEDURE p1; +DROP TABLE t1, t2; CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb; CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), CONSTRAINT c2 FOREIGN KEY f2 (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb; diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index 2604e4bf648..780e91ea73f 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -816,3 +816,15 @@ id prev_id join_id 3 2 0 4 3 0 DROP TABLE t1,t2; +# +# Bug#30384: Having SQL_BUFFER_RESULT option in the +# CREATE .. KEY(..) .. SELECT led to creating corrupted index. +# +create table t1(f1 int); +insert into t1 values(1),(2),(3); +create table t2 (key(f1)) engine=myisam select sql_buffer_result f1 from t1; +check table t2 extended; +Table Op Msg_type Msg_text +test.t2 check status OK +drop table t1,t2; +################################################################## diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index fcb141a3510..9b799fccf06 100644 --- a/mysql-test/r/join.result +++ b/mysql-test/r/join.result @@ -897,4 +897,168 @@ select '^^: The above should be ~= 20 + cost(select * from t1). Value less than Z ^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error drop table t1, t2; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT); +CREATE TABLE t2 (c INT PRIMARY KEY, d INT); +INSERT INTO t1 VALUES(1,NULL),(2,NULL),(3,NULL),(4,NULL); +INSERT INTO t1 SELECT a + 4, b FROM t1; +INSERT INTO t1 SELECT a + 8, b FROM t1; +INSERT INTO t1 SELECT a + 16, b FROM t1; +INSERT INTO t1 SELECT a + 32, b FROM t1; +INSERT INTO t1 SELECT a + 64, b FROM t1; +INSERT INTO t2 SELECT a, b FROM t1; +EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 2 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 +EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2; +a b c d +SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2; +a b c d +1 NULL 1 NULL +2 NULL 2 NULL +EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 +EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 128 Using filesort +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a; +a b c d +SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a; +a b c d +1 NULL 1 NULL +2 NULL 2 NULL +3 NULL 3 NULL +4 NULL 4 NULL +5 NULL 5 NULL +6 NULL 6 NULL +7 NULL 7 NULL +8 NULL 8 NULL +9 NULL 9 NULL +10 NULL 10 NULL +11 NULL 11 NULL +12 NULL 12 NULL +13 NULL 13 NULL +14 NULL 14 NULL +15 NULL 15 NULL +16 NULL 16 NULL +17 NULL 17 NULL +18 NULL 18 NULL +19 NULL 19 NULL +20 NULL 20 NULL +21 NULL 21 NULL +22 NULL 22 NULL +23 NULL 23 NULL +24 NULL 24 NULL +25 NULL 25 NULL +26 NULL 26 NULL +27 NULL 27 NULL +28 NULL 28 NULL +29 NULL 29 NULL +30 NULL 30 NULL +31 NULL 31 NULL +32 NULL 32 NULL +33 NULL 33 NULL +34 NULL 34 NULL +35 NULL 35 NULL +36 NULL 36 NULL +37 NULL 37 NULL +38 NULL 38 NULL +39 NULL 39 NULL +40 NULL 40 NULL +41 NULL 41 NULL +42 NULL 42 NULL +43 NULL 43 NULL +44 NULL 44 NULL +45 NULL 45 NULL +46 NULL 46 NULL +47 NULL 47 NULL +48 NULL 48 NULL +49 NULL 49 NULL +50 NULL 50 NULL +51 NULL 51 NULL +52 NULL 52 NULL +53 NULL 53 NULL +54 NULL 54 NULL +55 NULL 55 NULL +56 NULL 56 NULL +57 NULL 57 NULL +58 NULL 58 NULL +59 NULL 59 NULL +60 NULL 60 NULL +61 NULL 61 NULL +62 NULL 62 NULL +63 NULL 63 NULL +64 NULL 64 NULL +65 NULL 65 NULL +66 NULL 66 NULL +67 NULL 67 NULL +68 NULL 68 NULL +69 NULL 69 NULL +70 NULL 70 NULL +71 NULL 71 NULL +72 NULL 72 NULL +73 NULL 73 NULL +74 NULL 74 NULL +75 NULL 75 NULL +76 NULL 76 NULL +77 NULL 77 NULL +78 NULL 78 NULL +79 NULL 79 NULL +80 NULL 80 NULL +81 NULL 81 NULL +82 NULL 82 NULL +83 NULL 83 NULL +84 NULL 84 NULL +85 NULL 85 NULL +86 NULL 86 NULL +87 NULL 87 NULL +88 NULL 88 NULL +89 NULL 89 NULL +90 NULL 90 NULL +91 NULL 91 NULL +92 NULL 92 NULL +93 NULL 93 NULL +94 NULL 94 NULL +95 NULL 95 NULL +96 NULL 96 NULL +97 NULL 97 NULL +98 NULL 98 NULL +99 NULL 99 NULL +100 NULL 100 NULL +101 NULL 101 NULL +102 NULL 102 NULL +103 NULL 103 NULL +104 NULL 104 NULL +105 NULL 105 NULL +106 NULL 106 NULL +107 NULL 107 NULL +108 NULL 108 NULL +109 NULL 109 NULL +110 NULL 110 NULL +111 NULL 111 NULL +112 NULL 112 NULL +113 NULL 113 NULL +114 NULL 114 NULL +115 NULL 115 NULL +116 NULL 116 NULL +117 NULL 117 NULL +118 NULL 118 NULL +119 NULL 119 NULL +120 NULL 120 NULL +121 NULL 121 NULL +122 NULL 122 NULL +123 NULL 123 NULL +124 NULL 124 NULL +125 NULL 125 NULL +126 NULL 126 NULL +127 NULL 127 NULL +128 NULL 128 NULL +DROP TABLE IF EXISTS t1,t2; End of 5.0 tests. diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result index 337d6b0eca8..261e3292f4d 100644 --- a/mysql-test/r/log_tables.result +++ b/mysql-test/r/log_tables.result @@ -42,20 +42,20 @@ show create table mysql.general_log; Table Create Table general_log CREATE TABLE `general_log` ( `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - `user_host` mediumtext, - `thread_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, - `command_type` varchar(64) DEFAULT NULL, - `argument` mediumtext + `user_host` mediumtext NOT NULL, + `thread_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, + `command_type` varchar(64) NOT NULL, + `argument` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log' show fields from mysql.general_log; Field Type Null Key Default Extra event_time timestamp NO CURRENT_TIMESTAMP -user_host mediumtext YES NULL -thread_id int(11) YES NULL -server_id int(11) YES NULL -command_type varchar(64) YES NULL -argument mediumtext YES NULL +user_host mediumtext NO NULL +thread_id int(11) NO NULL +server_id int(11) NO NULL +command_type varchar(64) NO NULL +argument mediumtext NO NULL show create table mysql.slow_log; Table Create Table slow_log CREATE TABLE `slow_log` ( @@ -65,10 +65,10 @@ slow_log CREATE TABLE `slow_log` ( `lock_time` time NOT NULL, `rows_sent` int(11) NOT NULL, `rows_examined` int(11) NOT NULL, - `db` varchar(512) DEFAULT NULL, - `last_insert_id` int(11) DEFAULT NULL, - `insert_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, + `db` varchar(512) NOT NULL, + `last_insert_id` int(11) NOT NULL, + `insert_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' show fields from mysql.slow_log; @@ -79,10 +79,10 @@ query_time time NO NULL lock_time time NO NULL rows_sent int(11) NO NULL rows_examined int(11) NO NULL -db varchar(512) YES NULL -last_insert_id int(11) YES NULL -insert_id int(11) YES NULL -server_id int(11) YES NULL +db varchar(512) NO NULL +last_insert_id int(11) NO NULL +insert_id int(11) NO NULL +server_id int(11) NO NULL sql_text mediumtext NO NULL flush logs; flush tables; @@ -141,11 +141,11 @@ show create table mysql.general_log; Table Create Table general_log CREATE TABLE `general_log` ( `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - `user_host` mediumtext, - `thread_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, - `command_type` varchar(64) DEFAULT NULL, - `argument` mediumtext + `user_host` mediumtext NOT NULL, + `thread_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, + `command_type` varchar(64) NOT NULL, + `argument` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log' show create table mysql.slow_log; Table Create Table @@ -156,10 +156,10 @@ slow_log CREATE TABLE `slow_log` ( `lock_time` time NOT NULL, `rows_sent` int(11) NOT NULL, `rows_examined` int(11) NOT NULL, - `db` varchar(512) DEFAULT NULL, - `last_insert_id` int(11) DEFAULT NULL, - `insert_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, + `db` varchar(512) NOT NULL, + `last_insert_id` int(11) NOT NULL, + `insert_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' alter table mysql.general_log engine=myisam; @@ -168,11 +168,11 @@ show create table mysql.general_log; Table Create Table general_log CREATE TABLE `general_log` ( `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - `user_host` mediumtext, - `thread_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, - `command_type` varchar(64) DEFAULT NULL, - `argument` mediumtext + `user_host` mediumtext NOT NULL, + `thread_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, + `command_type` varchar(64) NOT NULL, + `argument` mediumtext NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log' show create table mysql.slow_log; Table Create Table @@ -183,10 +183,10 @@ slow_log CREATE TABLE `slow_log` ( `lock_time` time NOT NULL, `rows_sent` int(11) NOT NULL, `rows_examined` int(11) NOT NULL, - `db` varchar(512) DEFAULT NULL, - `last_insert_id` int(11) DEFAULT NULL, - `insert_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, + `db` varchar(512) NOT NULL, + `last_insert_id` int(11) NOT NULL, + `insert_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log' set global general_log='ON'; @@ -241,11 +241,11 @@ use mysql; CREATE TABLE `general_log` ( `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -`user_host` mediumtext, -`thread_id` int(11) DEFAULT NULL, -`server_id` int(11) DEFAULT NULL, -`command_type` varchar(64) DEFAULT NULL, -`argument` mediumtext +`user_host` mediumtext NOT NULL, +`thread_id` int(11) NOT NULL, +`server_id` int(11) NOT NULL, +`command_type` varchar(64) NOT NULL, +`argument` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'; CREATE TABLE `slow_log` ( `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP @@ -255,10 +255,10 @@ ON UPDATE CURRENT_TIMESTAMP, `lock_time` time NOT NULL, `rows_sent` int(11) NOT NULL, `rows_examined` int(11) NOT NULL, -`db` varchar(512) DEFAULT NULL, -`last_insert_id` int(11) DEFAULT NULL, -`insert_id` int(11) DEFAULT NULL, -`server_id` int(11) DEFAULT NULL, +`db` varchar(512) NOT NULL, +`last_insert_id` int(11) NOT NULL, +`insert_id` int(11) NOT NULL, +`server_id` int(11) NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'; set global general_log='ON'; @@ -403,9 +403,9 @@ My own slow query sleep(2) My own slow query 0 SELECT * FROM mysql.slow_log WHERE seq >= 2 LIMIT 3; start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text seq -START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test NULL NULL 1 SELECT "My own slow query", sleep(2) 2 -START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test NULL NULL 1 SELECT "My own slow query", sleep(2) 3 -START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test NULL NULL 1 SELECT "My own slow query", sleep(2) 4 +START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 2 +START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 3 +START_TIME USER_HOST QUERY_TIME 00:00:00 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 4 SET GLOBAL slow_query_log = 0; SET SESSION long_query_time =@old_long_query_time; FLUSH LOGS; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 5aa4288500c..d6e19107ec4 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -879,4 +879,9 @@ CHECK TABLE tm1; Table Op Msg_type Msg_text test.tm1 check status OK DROP TABLE tm1, t1, t2; +CREATE TABLE t1(c1 INT); +CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST; +CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2; +ERROR HY000: You can't specify target table 't1' for update in FROM clause +DROP TABLE t1, t2; End of 5.0 tests diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index d8688ea5509..c0856314ba5 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -3805,6 +3805,28 @@ c1 2 DROP TABLE t1,t2; # +# Bug#29815: new option for suppressing last line of mysqldump: +# "Dump completed on" +# +# --skip-dump-date: +-- + + + +-- Dump completed +# --dump-date: +-- + + + +-- Dump completed on DATE +# --dump-date (default): +-- + + + +-- Dump completed on DATE +# # End of 5.0 tests # drop table if exists t1; diff --git a/mysql-test/r/mysqlslap.result b/mysql-test/r/mysqlslap.result index dfe721d10d2..9207dd48d0e 100644 --- a/mysql-test/r/mysqlslap.result +++ b/mysql-test/r/mysqlslap.result @@ -211,3 +211,9 @@ COMMIT; COMMIT; SHOW TABLES; DROP SCHEMA IF EXISTS `mysqlslap`; +# +# Bug #29985: mysqlslap -- improper handling of resultsets in SPROCs +# +DROP PROCEDURE IF EXISTS p1; +CREATE PROCEDURE p1() SELECT 1; +DROP PROCEDURE p1; diff --git a/mysql-test/r/named_pipe.result b/mysql-test/r/named_pipe.result index 588b7570d8f..eb93c5f6344 100644 --- a/mysql-test/r/named_pipe.result +++ b/mysql-test/r/named_pipe.result @@ -605,11 +605,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL period NULL NULL NULL 41810 Using filesort +1 SIMPLE t3 index period period 4 NULL 1 1 SIMPLE t1 ref period period 4 test.t3.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort +1 SIMPLE t1 index period period 4 NULL 1 1 SIMPLE t3 ref period period 4 test.t1.period 4181 select period from t1; period diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index bf9d9764db2..345c9b07b98 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -1,4 +1,4 @@ -drop table if exists t1; +drop table if exists t1, t2; select null,\N,isnull(null),isnull(1/0),isnull(1/0 = null),ifnull(null,1),ifnull(null,"TRUE"),ifnull("TRUE","ERROR"),1/0 is null,1 is not null; NULL NULL isnull(null) isnull(1/0) isnull(1/0 = null) ifnull(null,1) ifnull(null,"TRUE") ifnull("TRUE","ERROR") 1/0 is null 1 is not null NULL NULL 1 1 1 1 TRUE TRUE 1 1 @@ -320,3 +320,26 @@ bug19145c CREATE TABLE `bug19145c` ( drop table bug19145a; drop table bug19145b; drop table bug19145c; +# End of 4.1 tests +# +# Bug #31471: decimal_bin_size: Assertion `scale >= 0 && +# precision > 0 && scale <= precision' +# +CREATE TABLE t1 (a DECIMAL (1, 0) ZEROFILL, b DECIMAL (1, 0) ZEROFILL); +INSERT INTO t1 (a, b) VALUES (0, 0); +CREATE TABLE t2 SELECT IFNULL(a, b) FROM t1; +DESCRIBE t2; +Field Type Null Key Default Extra +IFNULL(a, b) decimal(1,0) unsigned YES NULL +DROP TABLE t2; +CREATE TABLE t2 SELECT IFNULL(a, NULL) FROM t1; +DESCRIBE t2; +Field Type Null Key Default Extra +IFNULL(a, NULL) decimal(1,0) YES NULL +DROP TABLE t2; +CREATE TABLE t2 SELECT IFNULL(NULL, b) FROM t1; +DESCRIBE t2; +Field Type Null Key Default Extra +IFNULL(NULL, b) decimal(1,0) YES NULL +DROP TABLE t1, t2; +# End of 5.0 tests diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result index 67c45b698ce..63c9590401c 100644 --- a/mysql-test/r/olap.result +++ b/mysql-test/r/olap.result @@ -715,3 +715,14 @@ a SUM(a) 4 4 NULL 14 DROP TABLE t1; +# +# Bug#31095: Unexpected NULL constant caused server crash. +# +create table t1(a int); +insert into t1 values (1),(2),(3); +select count(a) from t1 group by null with rollup; +count(a) +3 +3 +drop table t1; +############################################################## diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index ff4882d6cd8..3d0f4915d0a 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1131,3 +1131,288 @@ id c3 186 14 196 14 DROP TABLE t1,t2; +CREATE TABLE t1 ( +a INT, +b INT, +PRIMARY KEY (a), +KEY ab(a, b) +); +INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4); +INSERT INTO t1 SELECT a + 4, b + 4 FROM t1; +INSERT INTO t1 SELECT a + 8, b + 8 FROM t1; +INSERT INTO t1 SELECT a +16, b +16 FROM t1; +INSERT INTO t1 SELECT a +32, b +32 FROM t1; +INSERT INTO t1 SELECT a +64, b +64 FROM t1; +EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL ab 4 NULL 10 Using index for group-by +SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a; +a +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +15 +16 +17 +18 +19 +20 +21 +22 +23 +24 +25 +26 +27 +28 +29 +30 +31 +32 +33 +34 +35 +36 +37 +38 +39 +40 +41 +42 +43 +44 +45 +46 +47 +48 +49 +50 +51 +52 +53 +54 +55 +56 +57 +58 +59 +60 +61 +62 +63 +64 +65 +66 +67 +68 +69 +70 +71 +72 +73 +74 +75 +76 +77 +78 +79 +80 +81 +82 +83 +84 +85 +86 +87 +88 +89 +90 +91 +92 +93 +94 +95 +96 +97 +98 +99 +100 +101 +102 +103 +104 +105 +106 +107 +108 +109 +110 +111 +112 +113 +114 +115 +116 +117 +118 +119 +120 +121 +122 +123 +124 +125 +126 +127 +128 +SELECT @tmp_tables_after = @tmp_tables_before ; +@tmp_tables_after = @tmp_tables_before +1 +EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using index +SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a; +a +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +15 +16 +17 +18 +19 +20 +21 +22 +23 +24 +25 +26 +27 +28 +29 +30 +31 +32 +33 +34 +35 +36 +37 +38 +39 +40 +41 +42 +43 +44 +45 +46 +47 +48 +49 +50 +51 +52 +53 +54 +55 +56 +57 +58 +59 +60 +61 +62 +63 +64 +65 +66 +67 +68 +69 +70 +71 +72 +73 +74 +75 +76 +77 +78 +79 +80 +81 +82 +83 +84 +85 +86 +87 +88 +89 +90 +91 +92 +93 +94 +95 +96 +97 +98 +99 +100 +101 +102 +103 +104 +105 +106 +107 +108 +109 +110 +111 +112 +113 +114 +115 +116 +117 +118 +119 +120 +121 +122 +123 +124 +125 +126 +127 +128 +SELECT @tmp_tables_after = @tmp_tables_before; +@tmp_tables_after = @tmp_tables_before +1 +DROP TABLE t1; diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index 7120e3ea9e6..4e4bd0bbc0a 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -1259,6 +1259,10 @@ INSERT INTO t1 SELECT a + 8, b FROM t1; ALTER TABLE t1 ADD PARTITION (PARTITION p1 VALUES LESS THAN (64)); ALTER TABLE t1 DROP PARTITION p1; DROP TABLE t1; +create table t (s1 int) engine=myisam partition by key (s1); +create trigger t_ad after delete on t for each row insert into t values (old.s1); +insert into t values (1); +drop table t; USE mysql; SET GLOBAL general_log = 0; ALTER TABLE general_log ENGINE = MyISAM; diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index 8282cfc212a..5b755b6bfd5 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -129,3 +129,10 @@ insert into t1 (time, first_name, last_name) values ('2007-02-07', 'Q', 'Robert' SELECT * FROM t1 WHERE first_name='Andy' OR last_name='Jake'; id time first_name last_name drop table t1; +CREATE TABLE t1 (a DOUBLE NOT NULL, KEY(a)) ENGINE=InnoDB +PARTITION BY KEY(a) PARTITIONS 10; +INSERT INTO t1 VALUES(1),(2); +SELECT COUNT(*) FROM t1; +COUNT(*) +2 +DROP TABLE t1; diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result index afc248c027e..6f0bad71f23 100644 --- a/mysql-test/r/ps_2myisam.result +++ b/mysql-test/r/ps_2myisam.result @@ -2973,11 +2973,13 @@ Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 @@ -3011,7 +3013,6 @@ Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value for column 'c16' at row 1 Warning 1264 Out of range value for column 'c17' at row 1 Warnings: -Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value for column 'c16' at row 1 Warning 1264 Out of range value for column 'c17' at row 1 diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result index e3f55cee898..85a109b0630 100644 --- a/mysql-test/r/ps_3innodb.result +++ b/mysql-test/r/ps_3innodb.result @@ -2956,11 +2956,13 @@ Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 @@ -2994,7 +2996,6 @@ Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value for column 'c16' at row 1 Warning 1264 Out of range value for column 'c17' at row 1 Warnings: -Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value for column 'c16' at row 1 Warning 1264 Out of range value for column 'c17' at row 1 diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result index fa33dcc395a..980b89ad504 100644 --- a/mysql-test/r/ps_4heap.result +++ b/mysql-test/r/ps_4heap.result @@ -2957,11 +2957,13 @@ Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 @@ -2995,7 +2997,6 @@ Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value for column 'c16' at row 1 Warning 1264 Out of range value for column 'c17' at row 1 Warnings: -Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value for column 'c16' at row 1 Warning 1264 Out of range value for column 'c17' at row 1 diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result index 968342c8a93..e95f8766a24 100644 --- a/mysql-test/r/ps_5merge.result +++ b/mysql-test/r/ps_5merge.result @@ -2893,11 +2893,13 @@ Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 @@ -2931,7 +2933,6 @@ Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value for column 'c16' at row 1 Warning 1264 Out of range value for column 'c17' at row 1 Warnings: -Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value for column 'c16' at row 1 Warning 1264 Out of range value for column 'c17' at row 1 @@ -5914,11 +5915,13 @@ Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 @@ -5952,7 +5955,6 @@ Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value for column 'c16' at row 1 Warning 1264 Out of range value for column 'c17' at row 1 Warnings: -Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value for column 'c16' at row 1 Warning 1264 Out of range value for column 'c17' at row 1 diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 73436dc392f..1a728354c7b 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -1650,8 +1650,23 @@ a (select count(*) from t2) 3 0 4 0 drop table t1,t2; -End of 5.0 tests set GLOBAL query_cache_type=default; set GLOBAL query_cache_limit=default; set GLOBAL query_cache_min_res_unit=default; set GLOBAL query_cache_size=default; +End of 5.0 tests +CREATE TABLE t1 (a ENUM('rainbow')); +INSERT INTO t1 VALUES (),(),(),(),(); +SELECT 1 FROM t1 GROUP BY (SELECT 1 FROM t1 ORDER BY AVG(LAST_INSERT_ID())); +1 +1 +DROP TABLE t1; +CREATE TABLE t1 (a LONGBLOB); +INSERT INTO t1 SET a = 'aaaa'; +INSERT INTO t1 SET a = 'aaaa'; +SELECT 1 FROM t1 GROUP BY +(SELECT LAST_INSERT_ID() FROM t1 ORDER BY MIN(a) ASC LIMIT 1); +1 +1 +DROP TABLE t1; +End of 5.1 tests diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index e8d9502e823..1acb82e4137 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -607,11 +607,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL period NULL NULL NULL 41810 Using filesort +1 SIMPLE t3 index period period 4 NULL 1 1 SIMPLE t1 ref period period 4 test.t3.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort +1 SIMPLE t1 index period period 4 NULL 1 1 SIMPLE t3 ref period period 4 test.t1.period 4181 select period from t1; period diff --git a/mysql-test/r/shm.result b/mysql-test/r/shm.result index a99a8b0df5d..b42de1e0f70 100644 --- a/mysql-test/r/shm.result +++ b/mysql-test/r/shm.result @@ -605,11 +605,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL period NULL NULL NULL 41810 Using filesort +1 SIMPLE t3 index period period 4 NULL 1 1 SIMPLE t1 ref period period 4 test.t3.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort +1 SIMPLE t1 index period period 4 NULL 1 1 SIMPLE t3 ref period period 4 test.t1.period 4181 select period from t1; period diff --git a/mysql-test/r/ssl.result b/mysql-test/r/ssl.result index 1f1a6ec9e22..3622fb51c99 100644 --- a/mysql-test/r/ssl.result +++ b/mysql-test/r/ssl.result @@ -608,11 +608,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL period NULL NULL NULL 41810 Using filesort +1 SIMPLE t3 index period period 4 NULL 1 1 SIMPLE t1 ref period period 4 test.t3.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort +1 SIMPLE t1 index period period 4 NULL 1 1 SIMPLE t3 ref period period 4 test.t1.period 4181 select period from t1; period diff --git a/mysql-test/r/ssl_compress.result b/mysql-test/r/ssl_compress.result index e77fcefeafd..3018fce5cad 100644 --- a/mysql-test/r/ssl_compress.result +++ b/mysql-test/r/ssl_compress.result @@ -611,11 +611,11 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 ref period period 4 test.t1.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL period NULL NULL NULL 41810 Using filesort +1 SIMPLE t3 index period period 4 NULL 1 1 SIMPLE t1 ref period period 4 test.t3.period 4181 explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using filesort +1 SIMPLE t1 index period period 4 NULL 1 1 SIMPLE t3 ref period period 4 test.t1.period 4181 select period from t1; period diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index 93694db629d..d1aff0fa657 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -240,11 +240,11 @@ show create table general_log; Table Create Table general_log CREATE TABLE `general_log` ( `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - `user_host` mediumtext, - `thread_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, - `command_type` varchar(64) DEFAULT NULL, - `argument` mediumtext + `user_host` mediumtext NOT NULL, + `thread_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, + `command_type` varchar(64) NOT NULL, + `argument` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log' show create table slow_log; Table Create Table @@ -255,10 +255,10 @@ slow_log CREATE TABLE `slow_log` ( `lock_time` time NOT NULL, `rows_sent` int(11) NOT NULL, `rows_examined` int(11) NOT NULL, - `db` varchar(512) DEFAULT NULL, - `last_insert_id` int(11) DEFAULT NULL, - `insert_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, + `db` varchar(512) NOT NULL, + `last_insert_id` int(11) NOT NULL, + `insert_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' show tables; diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index d6a01727813..904db1a14d0 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -145,3 +145,82 @@ d dt ts 0000-00-00 0000-00-00 00:00:00 0000-00-00 00:00:00 2001-11-11 2001-11-11 00:00:00 2001-11-11 00:00:00 drop table t1; +CREATE TABLE t1 ( +a INT +); +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (NULL); +SELECT str_to_date( '', a ) FROM t1; +str_to_date( '', a ) +0000-00-00 00:00:00 +NULL +DROP TABLE t1; +CREATE TABLE t1 (a DATE, b int, PRIMARY KEY (a,b)); +INSERT INTO t1 VALUES (DATE(NOW()), 1); +SELECT COUNT(*) FROM t1 WHERE a = NOW(); +COUNT(*) +0 +EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +INSERT INTO t1 VALUES (DATE(NOW()), 2); +SELECT COUNT(*) FROM t1 WHERE a = NOW(); +COUNT(*) +0 +EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1; +COUNT(*) +0 +EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +ALTER TABLE t1 DROP PRIMARY KEY; +SELECT COUNT(*) FROM t1 WHERE a = NOW(); +COUNT(*) +0 +EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +DROP TABLE t1; +CREATE TABLE t1 (a DATE); +CREATE TABLE t2 (a DATE); +CREATE INDEX i ON t1 (a); +INSERT INTO t1 VALUES ('0000-00-00'),('0000-00-00'); +INSERT INTO t2 VALUES ('0000-00-00'),('0000-00-00'); +SELECT * FROM t1 WHERE a = '0000-00-00'; +a +0000-00-00 +0000-00-00 +SELECT * FROM t2 WHERE a = '0000-00-00'; +a +0000-00-00 +0000-00-00 +SET SQL_MODE=TRADITIONAL; +EXPLAIN SELECT * FROM t1 WHERE a = '0000-00-00'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref i i 4 const 1 Using where; Using index +Warnings: +Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1 +Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1 +SELECT * FROM t1 WHERE a = '0000-00-00'; +a +0000-00-00 +0000-00-00 +Warnings: +Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1 +Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1 +Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1 +SELECT * FROM t2 WHERE a = '0000-00-00'; +a +0000-00-00 +0000-00-00 +Warnings: +Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1 +Warning 1292 Incorrect date value: '0000-00-00' for column 'a' at row 1 +INSERT INTO t1 VALUES ('0000-00-00'); +ERROR 22007: Incorrect date value: '0000-00-00' for column 'a' at row 1 +SET SQL_MODE=DEFAULT; +DROP TABLE t1,t2; +End of 5.0 tests diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 432fe1e6c4a..c15deb8b1e5 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -59,6 +59,8 @@ t drop table t1; CREATE TABLE t1 (a timestamp, b date, c time, d datetime); insert into t1 (b,c,d) values(now(),curtime(),now()); +Warnings: +Note 1265 Data truncated for column 'b' at row 1 select date_format(a,"%Y-%m-%d")=b,right(a+0,6)=c+0,a=d+0 from t1; date_format(a,"%Y-%m-%d")=b right(a+0,6)=c+0 a=d+0 1 1 1 @@ -427,6 +429,67 @@ f1 Warnings: Warning 1292 Incorrect datetime value: '2007010100000' for column 'f1' at row 1 drop table t1; +# +# Bug#27216: functions with parameters of different date types may +# return wrong type of the result. +# +create table t1 (f1 date, f2 datetime, f3 varchar(20)); +create table t2 as select coalesce(f1,f1) as f4 from t1; +desc t2; +Field Type Null Key Default Extra +f4 date YES NULL +create table t3 as select coalesce(f1,f2) as f4 from t1; +desc t3; +Field Type Null Key Default Extra +f4 datetime YES NULL +create table t4 as select coalesce(f2,f2) as f4 from t1; +desc t4; +Field Type Null Key Default Extra +f4 datetime YES NULL +create table t5 as select coalesce(f1,f3) as f4 from t1; +desc t5; +Field Type Null Key Default Extra +f4 varbinary(20) YES NULL +create table t6 as select coalesce(f2,f3) as f4 from t1; +desc t6; +Field Type Null Key Default Extra +f4 varbinary(20) YES NULL +create table t7 as select coalesce(makedate(1997,1),f2) as f4 from t1; +desc t7; +Field Type Null Key Default Extra +f4 datetime YES NULL +create table t8 as select coalesce(cast('01-01-01' as datetime),f2) as f4 +from t1; +desc t8; +Field Type Null Key Default Extra +f4 datetime YES NULL +create table t9 as select case when 1 then cast('01-01-01' as date) +when 0 then cast('01-01-01' as date) end as f4 from t1; +desc t9; +Field Type Null Key Default Extra +f4 date YES NULL +create table t10 as select case when 1 then cast('01-01-01' as datetime) +when 0 then cast('01-01-01' as datetime) end as f4 from t1; +desc t10; +Field Type Null Key Default Extra +f4 datetime YES NULL +create table t11 as select if(1, cast('01-01-01' as datetime), +cast('01-01-01' as date)) as f4 from t1; +desc t11; +Field Type Null Key Default Extra +f4 datetime YES NULL +create table t12 as select least(cast('01-01-01' as datetime), +cast('01-01-01' as date)) as f4 from t1; +desc t12; +Field Type Null Key Default Extra +f4 datetime YES NULL +create table t13 as select ifnull(cast('01-01-01' as datetime), +cast('01-01-01' as date)) as f4 from t1; +desc t13; +Field Type Null Key Default Extra +f4 datetime YES NULL +drop tables t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13; +################################################################### create table t1 (f1 time); insert into t1 set f1 = '45:44:44'; insert into t1 set f1 = '15:44:44'; diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result index fa7bf91b113..42276a004bb 100644 --- a/mysql-test/r/type_decimal.result +++ b/mysql-test/r/type_decimal.result @@ -800,6 +800,12 @@ SELECT ROUND(qty,3), dps, ROUND(qty,dps) FROM t1; ROUND(qty,3) dps ROUND(qty,dps) 1.133 3 1.133 DROP TABLE t1; +SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS '%'; +% +0.012345687012345687012345687012345687012345687012345687012345687012345687000000000 +SELECT MOD(1, .123456789123456789123456789123456789123456789123456789123456789123456789123456789) AS 'MOD()'; +MOD() +0.012345687012345687012345687012345687012345687012345687012345687012345687000000000 create table t1 (f1 decimal(6,6),f2 decimal(6,6) zerofill); insert into t1 values (-0.123456,0.123456); select group_concat(f1),group_concat(f2) from t1; diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 638d71c6940..bdaec70021b 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -799,6 +799,9 @@ set @@query_prealloc_size = @test; select @@query_prealloc_size = @test; @@query_prealloc_size = @test 1 +set global sql_mode=repeat('a',80); +ERROR 42000: Variable 'sql_mode' can't be set to the value of 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' +End of 4.1 tests create table t1 (a int); select a into @x from t1; Warnings: diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 8a926d44f5f..60c3ff68e67 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -625,7 +625,7 @@ drop table t1; create table t1 (a int, b int); create view v1 as select a, sum(b) from t1 group by a; select b from v1 use index (some_index) where b=1; -ERROR 42000: Key 'some_index' doesn't exist in table 'v1' +ERROR HY000: Incorrect usage of index hints and VIEW drop view v1; drop table t1; create table t1 (col1 char(5),col2 char(5)); @@ -2701,27 +2701,26 @@ CREATE TABLE t1( fName varchar(25) NOT NULL, lName varchar(25) NOT NULL, DOB date NOT NULL, +test_date date NOT NULL, uID int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY); -INSERT INTO t1(fName, lName, DOB) VALUES -('Hank', 'Hill', '1964-09-29'), -('Tom', 'Adams', '1908-02-14'), -('Homer', 'Simpson', '1968-03-05'); +INSERT INTO t1(fName, lName, DOB, test_date) VALUES +('Hank', 'Hill', '1964-09-29', '2007-01-01'), +('Tom', 'Adams', '1908-02-14', '2007-01-01'), +('Homer', 'Simpson', '1968-03-05', '2007-01-01'); CREATE VIEW v1 AS -SELECT (year(now())-year(DOB)) AS Age +SELECT (year(test_date)-year(DOB)) AS Age FROM t1 HAVING Age < 75; SHOW CREATE VIEW v1; View Create View character_set_client collation_connection -v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (year(now()) - year(`t1`.`DOB`)) AS `Age` from `t1` having (`Age` < 75) latin1 latin1_swedish_ci -set timestamp=1136066400; -SELECT (year(now())-year(DOB)) AS Age FROM t1 HAVING Age < 75; +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select (year(`t1`.`test_date`) - year(`t1`.`DOB`)) AS `Age` from `t1` having (`Age` < 75) latin1 latin1_swedish_ci +SELECT (year(test_date)-year(DOB)) AS Age FROM t1 HAVING Age < 75; Age -42 -38 -set timestamp=1136066400; +43 +39 SELECT * FROM v1; Age -42 -38 +43 +39 DROP VIEW v1; DROP TABLE t1; CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a char(6) DEFAULT 'xxx'); @@ -3559,6 +3558,45 @@ table_name is_updatable v1 NO drop view v1; drop table t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE VIEW v1 AS SELECT * FROM t1; +SELECT * FROM v1 USE KEY(non_existant); +ERROR HY000: Incorrect usage of index hints and VIEW +SELECT * FROM v1 FORCE KEY(non_existant); +ERROR HY000: Incorrect usage of index hints and VIEW +SELECT * FROM v1 IGNORE KEY(non_existant); +ERROR HY000: Incorrect usage of index hints and VIEW +DROP VIEW v1; +DROP TABLE t1; +CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL DEFAULT 0, +PRIMARY KEY(a), KEY (b)); +INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(); +CREATE VIEW v1 AS SELECT * FROM t1 FORCE KEY (PRIMARY,b) ORDER BY a; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` FORCE INDEX (PRIMARY) FORCE INDEX (`b`) order by `t1`.`a` latin1 latin1_swedish_ci +EXPLAIN SELECT * FROM v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 15 +CREATE VIEW v2 AS SELECT * FROM t1 USE KEY () ORDER BY a; +SHOW CREATE VIEW v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` USE INDEX () order by `t1`.`a` latin1 latin1_swedish_ci +EXPLAIN SELECT * FROM v2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 15 Using filesort +CREATE VIEW v3 AS SELECT * FROM t1 IGNORE KEY (b) ORDER BY a; +SHOW CREATE VIEW v3; +View Create View character_set_client collation_connection +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` IGNORE INDEX (`b`) order by `t1`.`a` latin1 latin1_swedish_ci +EXPLAIN SELECT * FROM v3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 15 Using filesort +DROP VIEW v1; +DROP VIEW v2; +DROP VIEW v3; +DROP TABLE t1; End of 5.0 tests. DROP DATABASE IF EXISTS `d-1`; CREATE DATABASE `d-1`; diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index 49c98d8e03f..c301953f508 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -778,15 +778,60 @@ GRANT CREATE VIEW ON db26813.v2 TO u26813@localhost; GRANT DROP, CREATE VIEW ON db26813.v3 TO u26813@localhost; GRANT SELECT ON db26813.t1 TO u26813@localhost; ALTER VIEW v1 AS SELECT f2 FROM t1; -ERROR 42000: CREATE VIEW command denied to user 'u26813'@'localhost' for table 'v1' +ERROR 42000: Access denied; you need the SUPER privilege for this operation ALTER VIEW v2 AS SELECT f2 FROM t1; -ERROR 42000: DROP command denied to user 'u26813'@'localhost' for table 'v2' +ERROR 42000: Access denied; you need the SUPER privilege for this operation ALTER VIEW v3 AS SELECT f2 FROM t1; +ERROR 42000: Access denied; you need the SUPER privilege for this operation SHOW CREATE VIEW v3; View Create View character_set_client collation_connection -v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`f2` AS `f2` from `t1` latin1 latin1_swedish_ci +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci DROP USER u26813@localhost; DROP DATABASE db26813; +# +# Bug#29908: A user can gain additional access through the ALTER VIEW. +# +CREATE DATABASE mysqltest_29908; +USE mysqltest_29908; +CREATE TABLE t1(f1 INT, f2 INT); +CREATE USER u29908_1@localhost; +CREATE DEFINER = u29908_1@localhost VIEW v1 AS SELECT f1 FROM t1; +CREATE DEFINER = u29908_1@localhost SQL SECURITY INVOKER VIEW v2 AS +SELECT f1 FROM t1; +GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v1 TO u29908_1@localhost; +GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_1@localhost; +GRANT SELECT ON mysqltest_29908.t1 TO u29908_1@localhost; +CREATE USER u29908_2@localhost; +GRANT DROP, CREATE VIEW ON mysqltest_29908.v1 TO u29908_2@localhost; +GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_2@localhost; +GRANT SELECT ON mysqltest_29908.t1 TO u29908_2@localhost; +ALTER VIEW v1 AS SELECT f2 FROM t1; +ERROR 42000: Access denied; you need the SUPER privilege for this operation +ALTER VIEW v2 AS SELECT f2 FROM t1; +ERROR 42000: Access denied; you need the SUPER privilege for this operation +SHOW CREATE VIEW v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci +ALTER VIEW v1 AS SELECT f2 FROM t1; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f2` AS `f2` from `t1` latin1 latin1_swedish_ci +ALTER VIEW v2 AS SELECT f2 FROM t1; +SHOW CREATE VIEW v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f2` AS `f2` from `t1` latin1 latin1_swedish_ci +ALTER VIEW v1 AS SELECT f1 FROM t1; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci +ALTER VIEW v2 AS SELECT f1 FROM t1; +SHOW CREATE VIEW v2; +View Create View character_set_client collation_connection +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`u29908_1`@`localhost` SQL SECURITY INVOKER VIEW `v2` AS select `t1`.`f1` AS `f1` from `t1` latin1 latin1_swedish_ci +DROP USER u29908_1@localhost; +DROP USER u29908_2@localhost; +DROP DATABASE mysqltest_29908; +####################################################################### DROP DATABASE IF EXISTS mysqltest1; DROP DATABASE IF EXISTS mysqltest2; CREATE DATABASE mysqltest1; @@ -893,6 +938,8 @@ v1 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such`@`user_1` SQL SECURITY DEFINER VI Warnings: Warning 1356 View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them ALTER ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1; +Warnings: +Note 1449 There is no 'no_such'@'user_1' registered SHOW CREATE VIEW v1; View Create View character_set_client collation_connection v1 CREATE ALGORITHM=MERGE DEFINER=`no_such`@`user_1` SQL SECURITY DEFINER VIEW `v1` AS select `test`.`t1`.`i` AS `i` from `t1` latin1 latin1_swedish_ci diff --git a/mysql-test/suite/ndb/r/ps_7ndb.result b/mysql-test/suite/ndb/r/ps_7ndb.result index 82d2d14b075..e6bcb7f035e 100644 --- a/mysql-test/suite/ndb/r/ps_7ndb.result +++ b/mysql-test/suite/ndb/r/ps_7ndb.result @@ -2956,11 +2956,13 @@ Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: +Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c17' at row 1 Warnings: Note 1265 Data truncated for column 'c13' at row 1 @@ -2994,7 +2996,6 @@ Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value for column 'c16' at row 1 Warning 1264 Out of range value for column 'c17' at row 1 Warnings: -Note 1265 Data truncated for column 'c13' at row 1 Warning 1265 Data truncated for column 'c15' at row 1 Warning 1264 Out of range value for column 'c16' at row 1 Warning 1264 Out of range value for column 'c17' at row 1 diff --git a/mysql-test/suite/rpl/r/rpl_flushlog_loop.result b/mysql-test/suite/rpl/r/rpl_flushlog_loop.result index b47c262eb66..ca06a5a328c 100644 --- a/mysql-test/suite/rpl/r/rpl_flushlog_loop.result +++ b/mysql-test/suite/rpl/r/rpl_flushlog_loop.result @@ -4,6 +4,13 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; +show variables like 'relay_log%'; +Variable_name Value +relay_log MYSQLTEST_VARDIR/master-data/relay-log +relay_log_index +relay_log_info_file relay-log.info +relay_log_purge ON +relay_log_space_limit 0 stop slave; change master to master_host='127.0.0.1',master_user='root', master_password='',master_port=MASTER_PORT; diff --git a/mysql-test/suite/rpl/t/rpl_flushlog_loop.test b/mysql-test/suite/rpl/t/rpl_flushlog_loop.test index 7d92ba9c2f4..750b49f19a9 100644 --- a/mysql-test/suite/rpl/t/rpl_flushlog_loop.test +++ b/mysql-test/suite/rpl/t/rpl_flushlog_loop.test @@ -2,12 +2,9 @@ # in case of bi-directional replication -- source include/master-slave.inc -# -# Start replication master -> slave -# -# We have to sync with master, to ensure slave had time to start properly -# before we stop it. If not, we get errors about UNIX_TIMESTAMP() in the log. -sync_slave_with_master; +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +show variables like 'relay_log%'; + connection slave; --disable_warnings stop slave; diff --git a/mysql-test/t/csv.test b/mysql-test/t/csv.test index 5c877557dfc..6c83fbfdc9c 100644 --- a/mysql-test/t/csv.test +++ b/mysql-test/t/csv.test @@ -1322,7 +1322,7 @@ drop table if exists t1,t2,t3,t4; DROP TABLE IF EXISTS bug13894; --enable_warnings -CREATE TABLE bug13894 ( val integer ) ENGINE = CSV; +CREATE TABLE bug13894 ( val integer not null ) ENGINE = CSV; INSERT INTO bug13894 VALUES (5); INSERT INTO bug13894 VALUES (10); INSERT INTO bug13894 VALUES (11); @@ -1340,7 +1340,7 @@ DROP TABLE bug13894; DROP TABLE IF EXISTS bug14672; --enable_warnings -CREATE TABLE bug14672 (c1 integer) engine = CSV; +CREATE TABLE bug14672 (c1 integer not null) engine = CSV; INSERT INTO bug14672 VALUES (1), (2), (3); SELECT * FROM bug14672; DELETE FROM bug14672 WHERE c1 = 2; @@ -1357,7 +1357,7 @@ DROP TABLE bug14672; # Test CONCURRENT INSERT (5.1) # -CREATE TABLE test_concurrent_insert ( val integer ) ENGINE = CSV; +CREATE TABLE test_concurrent_insert ( val integer not null ) ENGINE = CSV; connect (con1,localhost,root,,); connect (con2,localhost,root,,); @@ -1393,7 +1393,7 @@ DROP TABLE test_concurrent_insert; # Check that repair on the newly created table works fine -CREATE TABLE test_repair_table ( val integer ) ENGINE = CSV; +CREATE TABLE test_repair_table ( val integer not null ) ENGINE = CSV; CHECK TABLE test_repair_table; REPAIR TABLE test_repair_table; @@ -1405,7 +1405,7 @@ DROP TABLE test_repair_table; # restore the meta-file # -CREATE TABLE test_repair_table2 ( val integer ) ENGINE = CSV; +CREATE TABLE test_repair_table2 ( val integer not null ) ENGINE = CSV; --remove_file $MYSQLTEST_VARDIR/master-data/test/test_repair_table2.CSM # Should give a warning and perform autorepair. We also disable ps-protocol @@ -1423,7 +1423,7 @@ DROP TABLE test_repair_table2; # Corrupt csv file and see if we can repair it -CREATE TABLE test_repair_table3 ( val integer ) ENGINE = CSV; +CREATE TABLE test_repair_table3 ( val integer not null ) ENGINE = CSV; --remove_file $MYSQLTEST_VARDIR/master-data/test/test_repair_table3.CSV --write_file $MYSQLTEST_VARDIR/master-data/test/test_repair_table3.CSV "1" @@ -1517,7 +1517,7 @@ DROP TABLE test_repair_table5; # BUG#13406 - incorrect amount of "records deleted" # -create table t1 (a int) engine=csv; +create table t1 (a int not null) engine=csv; insert t1 values (1); --enable_info delete from t1; # delete_row @@ -1549,7 +1549,7 @@ drop table t1; # whole alter table code is being tested all around the test suite already. # -create table t1 (v varchar(32)); +create table t1 (v varchar(32) not null); insert into t1 values ('def'),('abc'),('hij'),('3r4f'); select * from t1; # Fast alter, no copy performed @@ -1583,8 +1583,8 @@ drop table t1; # resulted in scanning through deleted memory and we were geting a crash. # that's why we need two tables in the bugtest -create table bug15205 (val int(11) default null) engine=csv; -create table bug15205_2 (val int(11) default null) engine=csv; +create table bug15205 (val int(11) not null) engine=csv; +create table bug15205_2 (val int(11) not null) engine=csv; --remove_file $MYSQLTEST_VARDIR/master-data/test/bug15205.CSV # system error (can't open the datafile) --replace_result $MYSQLTEST_VARDIR . master-data/ '' @@ -1604,8 +1604,8 @@ drop table bug15205_2; # set names latin1; create table t1 ( - c varchar(1), - name varchar(64) + c varchar(1) not null, + name varchar(64) not null ) character set latin1 engine=csv; insert into t1 values (0xC0,'LATIN CAPITAL LETTER A WITH GRAVE'); insert into t1 values (0xE0,'LATIN SMALL LETTER A WITH GRAVE'); @@ -1623,9 +1623,9 @@ drop table t1; # Bug#22080 "CHECK fails to identify some corruption" # -create table bug22080_1 (id int,string varchar(64)) Engine=CSV; -create table bug22080_2 (id int,string varchar(64)) Engine=CSV; -create table bug22080_3 (id int,string varchar(64)) Engine=CSV; +create table bug22080_1 (id int not null,string varchar(64) not null) Engine=CSV; +create table bug22080_2 (id int not null,string varchar(64) not null) Engine=CSV; +create table bug22080_3 (id int not null,string varchar(64) not null) Engine=CSV; insert into bug22080_1 values(1,'string'); insert into bug22080_1 values(2,'string'); insert into bug22080_1 values(3,'string'); @@ -1655,7 +1655,7 @@ drop tables bug22080_1,bug22080_2,bug22080_3; # # Testing float type # -create table float_test (id float,string varchar(64)) Engine=CSV; +create table float_test (id float not null,string varchar(64) not null) Engine=CSV; insert into float_test values(1.0,'string'); insert into float_test values(2.23,'serg.g'); insert into float_test values(0.03,'string'); @@ -1670,12 +1670,12 @@ drop table float_test; # CREATE TABLE `bug21328` ( - `col1` int(11) DEFAULT NULL, - `col2` int(11) DEFAULT NULL, - `col3` int(11) DEFAULT NULL + `col1` int(11) NOT NULL, + `col2` int(11) NOT NULL, + `col3` int(11) NOT NULL ) ENGINE=CSV; -insert into bug21328 values (1,NULL,NULL); +insert into bug21328 values (1,0,0); alter table bug21328 engine=myisam; drop table bug21328; @@ -1683,7 +1683,7 @@ drop table bug21328; # BUG#28971 - ALTER TABLE followed by UPDATE for a CSV table make server # crash # -create table t1(a blob, b int) engine=csv; +create table t1(a blob not null, b int not null) engine=csv; insert into t1 values('a', 1); flush tables; update t1 set b=2; @@ -1693,13 +1693,13 @@ drop table t1; # # Bug #29353: negative values # -create table t1(a int) engine=csv; +create table t1(a int not null) engine=csv; insert into t1 values(-1), (-123.34), (2), (-23); select * from t1; check table t1; drop table t1; -create table t1(a int, b int) engine=csv; +create table t1(a int not null, b int not null) engine=csv; --remove_file $MYSQLTEST_VARDIR/master-data/test/t1.CSV --write_file $MYSQLTEST_VARDIR/master-data/test/t1.CSV 1, 1E-2 @@ -1717,7 +1717,7 @@ drop table t1; # # Bug #29411: deleting from a csv table leads to the table corruption # -create table t1(a int) engine=csv; +create table t1(a int not null) engine=csv; insert into t1 values (0), (1), (2); delete from t1 limit 2; check table t1; @@ -1727,4 +1727,43 @@ check table t1; select * from t1; drop table t1; +# +# Bug #31473: does not work with NULL value in datetime field +# +create table t1(a datetime not null) engine=csv; +insert into t1 values(); +select * from t1; +drop table t1; +create table t1(a set('foo','bar') not null) engine=csv; +insert into t1 values(); +select * from t1; +drop table t1; +create table t1(a varchar(32) not null) engine=csv; +insert into t1 values(); +select * from t1; +drop table t1; +create table t1(a int not null) engine=csv; +insert into t1 values(); +select * from t1; +drop table t1; +create table t1(a blob not null) engine=csv; +insert into t1 values(); +select * from t1; +drop table t1; +create table t1(a bit(1) not null) engine=csv; +insert into t1 values(); +select BIN(a) from t1; +drop table t1; +# We prevent creation of table with nullable ENUM +--error ER_CANT_CREATE_TABLE +create table t1(a enum('foo','bar') default null) engine=csv; +--error ER_CANT_CREATE_TABLE +create table t1(a enum('foo','bar') default 'foo') engine=csv; +# Enum columns must be specified as NOT NULL +create table t1(a enum('foo','bar') default 'foo' not null) engine=csv; +insert into t1 values(); +select * from t1; +drop table t1; + + --echo End of 5.1 tests diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test index bca3a9c3a96..5525a5beb6f 100644 --- a/mysql-test/t/ctype_ucs.test +++ b/mysql-test/t/ctype_ucs.test @@ -651,4 +651,9 @@ select * from t1 where a=if(b<10,_ucs2 0x00C0,_ucs2 0x0062); select * from t1 where a=if(b<10,_ucs2 0x0062,_ucs2 0x00C0); drop table t1; +# +# Bug#30981 CHAR(0x41 USING ucs2) doesn't add leading zero +# +select hex(char(0x41 using ucs2)); + --echo End of 5.0 tests diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index b61bb1d53bf..5c35bf82343 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -1404,3 +1404,30 @@ SELECT b FROM t2 UNION SELECT c FROM t1; SELECT i FROM t2 UNION SELECT c FROM t1; DROP TABLE t1, t2; + +# +# Bug#30982: CHAR(..USING..) can return a not-well-formed string +# Bug #30986: Character set introducer followed by a HEX string can return bad result +# +set sql_mode=traditional; +select hex(char(0xFF using utf8)); +select hex(convert(0xFF using utf8)); +--error ER_INVALID_CHARACTER_STRING +select hex(_utf8 0x616263FF); +--error ER_INVALID_CHARACTER_STRING +select hex(_utf8 X'616263FF'); +--error ER_INVALID_CHARACTER_STRING +select hex(_utf8 B'001111111111'); +--error ER_INVALID_CHARACTER_STRING +select (_utf8 X'616263FF'); +set sql_mode=default; +select hex(char(0xFF using utf8)); +select hex(convert(0xFF using utf8)); +--error ER_INVALID_CHARACTER_STRING +select hex(_utf8 0x616263FF); +--error ER_INVALID_CHARACTER_STRING +select hex(_utf8 X'616263FF'); +--error ER_INVALID_CHARACTER_STRING +select hex(_utf8 B'001111111111'); +--error ER_INVALID_CHARACTER_STRING +select (_utf8 X'616263FF'); diff --git a/mysql-test/t/delete.test b/mysql-test/t/delete.test index 8a03cb6c715..602e30687c8 100644 --- a/mysql-test/t/delete.test +++ b/mysql-test/t/delete.test @@ -277,3 +277,18 @@ SELECT * FROM t1; DROP TABLE t1, t2; DROP DATABASE db1; DROP DATABASE db2; + +# +# Bug 31742: delete from ... order by function call that causes an error, +# asserts server +# + +CREATE FUNCTION f1() RETURNS INT RETURN 1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (0); +--error 1318 +DELETE FROM t1 ORDER BY (f1(10)) LIMIT 1; +DROP TABLE t1; +DROP FUNCTION f1; + +--echo End of 5.0 tests diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 4d8a8e3c3af..4e79fac584f 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -211,7 +211,8 @@ drop table t2; # select list counter # CREATE TABLE `t1` ( `itemid` int(11) NOT NULL default '0', `grpid` varchar(15) NOT NULL default '', `vendor` int(11) NOT NULL default '0', `date_` date NOT NULL default '0000-00-00', `price` decimal(12,2) NOT NULL default '0.00', PRIMARY KEY (`itemid`,`grpid`,`vendor`,`date_`), KEY `itemid` (`itemid`,`vendor`), KEY `itemid_2` (`itemid`,`date_`)); -insert into t1 values (128, 'rozn', 2, now(), 10),(128, 'rozn', 1, now(), 10); +insert into t1 values (128, 'rozn', 2, curdate(), 10), + (128, 'rozn', 1, curdate(), 10); SELECT MIN(price) min, MAX(price) max, AVG(price) avg FROM (SELECT SUBSTRING( MAX(concat(date_,";",price)), 12) price FROM t1 WHERE itemid=128 AND grpid='rozn' GROUP BY itemid, grpid, vendor) lastprices; DROP TABLE t1; diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index d1b4919c83e..e1ec6906cd6 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -867,5 +867,18 @@ SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1; DROP TABLE t1, t2, t3, t4, t5; +# +# Bug #31156: mysqld: item_sum.cc:918: +# virtual bool Item_sum_distinct::setup(THD*): Assertion +# + +CREATE TABLE t1 (a INT); +INSERT INTO t1 values (),(),(); +SELECT (SELECT SLEEP(0) FROM t1 ORDER BY AVG(DISTINCT a) ) as x FROM t1 + GROUP BY x; +SELECT 1 FROM t1 GROUP BY (SELECT SLEEP(0) FROM t1 ORDER BY AVG(DISTINCT a) ); + +DROP TABLE t1; + ### --echo End of 5.0 tests diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test index 2c5ed6a22d3..d8b0c89532e 100644 --- a/mysql-test/t/func_in.test +++ b/mysql-test/t/func_in.test @@ -408,5 +408,13 @@ select f2 from t2 where f2 in (1,'b'); explain select f2 from t2 where f2 in (1,'b'); drop table t1, t2; +# +# Bug #31075: crash in get_func_mm_tree +# + +create table t1 (a time, key(a)); +insert into t1 values (),(),(),(),(),(),(),(),(),(); +select a from t1 where a not in (a,a,a) group by a; +drop table t1; --echo End of 5.1 tests diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index 668528b2e9b..274a953a314 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -224,4 +224,29 @@ select mod(cast(-2 as unsigned), 3), mod(18446744073709551614, 3), mod(-2, 3); select mod(5, cast(-2 as unsigned)), mod(5, 18446744073709551614), mod(5, -2); select pow(cast(-2 as unsigned), 5), pow(18446744073709551614, 5), pow(-2, 5); +# +# Bug #30587: mysql crashes when trying to group by TIME div NUMBER +# + +CREATE TABLE t1 (a timestamp, b varchar(20), c bit(1)); +INSERT INTO t1 VALUES('1998-09-23', 'str1', 1), ('2003-03-25', 'str2', 0); +SELECT a DIV 900 y FROM t1 GROUP BY y; +SELECT DISTINCT a DIV 900 y FROM t1; +SELECT b DIV 900 y FROM t1 GROUP BY y; +SELECT c DIV 900 y FROM t1 GROUP BY y; +DROP TABLE t1; + +CREATE TABLE t1(a LONGBLOB); +INSERT INTO t1 VALUES('1'),('2'),('3'); +SELECT DISTINCT (a DIV 254576881) FROM t1; +SELECT (a DIV 254576881) FROM t1 UNION ALL + SELECT (a DIV 254576881) FROM t1; +DROP TABLE t1; + +CREATE TABLE t1(a SET('a','b','c')); +INSERT INTO t1 VALUES ('a'); +SELECT a DIV 2 FROM t1 UNION SELECT a DIV 2 FROM t1; +DROP TABLE t1; + + --echo End of 5.0 tests diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index 01eff55d1f6..4b7685c3633 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -198,6 +198,21 @@ drop table table_26093; drop function func_26093_a; drop function func_26093_b; +# +# Bug #30832: Assertion + crash with select name_const('test',now()); +# +--error ER_WRONG_ARGUMENTS +SELECT NAME_CONST('test', NOW()); +--error ER_WRONG_ARGUMENTS +SELECT NAME_CONST('test', UPPER('test')); + +SELECT NAME_CONST('test', NULL); +SELECT NAME_CONST('test', 1); +SELECT NAME_CONST('test', -1); +SELECT NAME_CONST('test', 1.0); +SELECT NAME_CONST('test', -1.0); +SELECT NAME_CONST('test', 'test'); + --echo End of 5.0 tests # diff --git a/mysql-test/t/func_regexp.test b/mysql-test/t/func_regexp.test index 23070c71fe9..5eff404bc0f 100644 --- a/mysql-test/t/func_regexp.test +++ b/mysql-test/t/func_regexp.test @@ -74,4 +74,13 @@ execute stmt1 using @a; deallocate prepare stmt1; drop table t1; -# End of 4.1 tests +--echo End of 4.1 tests + + +# +# Bug #31440: 'select 1 regex null' asserts debug server +# + +SELECT 1 REGEXP NULL; + +--echo End of 5.0 tests diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 945f5a050f4..0ba0ba30cd2 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -1231,4 +1231,16 @@ SELECT SUBSTR(a,1,len) FROM t1; DROP TABLE t1; +# +# Bug #28850: Potential bugs related to the return type of the CHAR function +# + +CREATE TABLE t1 AS SELECT CHAR(0x414243) as c1; +SELECT HEX(c1) from t1; +DROP TABLE t1; + +CREATE VIEW v1 AS SELECT CHAR(0x414243) as c1; +SELECT HEX(c1) from v1; +DROP VIEW v1; + --echo End of 5.0 tests diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index d3346bbb880..5c1a5c2200b 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -548,6 +548,16 @@ DROP TABLE testBug8868; SET NAMES DEFAULT; # +# Bug #31160: MAKETIME() crashes server when returning NULL in ORDER BY using +# filesort +# +CREATE TABLE t1 ( + a TIMESTAMP +); +INSERT INTO t1 VALUES (now()), (now()); +SELECT 1 FROM t1 ORDER BY MAKETIME(1, 1, a); +DROP TABLE t1; +# # Bug #19844 time_format in Union truncates values # diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test index 88f31143d93..dad22f42571 100644 --- a/mysql-test/t/gis-rtree.test +++ b/mysql-test/t/gis-rtree.test @@ -797,6 +797,42 @@ UPDATE t1 set spatial_point=GeomFromText('POINT(41 46)') where c1 like 'f%'; CHECK TABLE t1 EXTENDED; DROP TABLE t1; +# +# Bug #30286 spatial index cause corruption and server crash! +# + +create table t1 (a geometry not null, spatial index(a)); +insert into t1 values (PointFromWKB(POINT(1.1517219314031e+164, 131072))); +insert into t1 values (PointFromWKB(POINT(9.1248812352444e+192, 2.9740338169556e+284))); +insert into t1 values (PointFromWKB(POINT(4.7783097267365e-299, -0))); +insert into t1 values (PointFromWKB(POINT(1.49166814624e-154, 2.0880974297595e-53))); +insert into t1 values (PointFromWKB(POINT(4.0917382598702e+149, 1.2024538023802e+111))); +insert into t1 values (PointFromWKB(POINT(2.0349165139404e+236, 2.9993936277913e-241))); +insert into t1 values (PointFromWKB(POINT(2.5243548967072e-29, 1.2024538023802e+111))); +insert into t1 values (PointFromWKB(POINT(0, 6.9835074892995e-251))); +insert into t1 values (PointFromWKB(POINT(2.0880974297595e-53, 3.1050361846014e+231))); +insert into t1 values (PointFromWKB(POINT(2.8728483499323e-188, 2.4600631144627e+260))); +insert into t1 values (PointFromWKB(POINT(3.0517578125e-05, 2.0349165139404e+236))); +insert into t1 values (PointFromWKB(POINT(1.1517219314031e+164, 1.1818212630766e-125))); +insert into t1 values (PointFromWKB(POINT(2.481040258324e-265, 5.7766220027675e-275))); +insert into t1 values (PointFromWKB(POINT(2.0880974297595e-53, 2.5243548967072e-29))); +insert into t1 values (PointFromWKB(POINT(5.7766220027675e-275, 9.9464647281957e+86))); +insert into t1 values (PointFromWKB(POINT(2.2181357552967e+130, 3.7857669957337e-270))); +insert into t1 values (PointFromWKB(POINT(4.5767114681874e-246, 3.6893488147419e+19))); +insert into t1 values (PointFromWKB(POINT(4.5767114681874e-246, 3.7537584144024e+255))); +insert into t1 values (PointFromWKB(POINT(3.7857669957337e-270, 1.8033161362863e-130))); +insert into t1 values (PointFromWKB(POINT(0, 5.8774717541114e-39))); +insert into t1 values (PointFromWKB(POINT(1.1517219314031e+164, 2.2761049594727e-159))); +insert into t1 values (PointFromWKB(POINT(6.243497100632e+144, 3.7857669957337e-270))); +insert into t1 values (PointFromWKB(POINT(3.7857669957337e-270, 2.6355494858076e-82))); +insert into t1 values (PointFromWKB(POINT(2.0349165139404e+236, 3.8518598887745e-34))); +insert into t1 values (PointFromWKB(POINT(4.6566128730774e-10, 2.0880974297595e-53))); +insert into t1 values (PointFromWKB(POINT(2.0880974297595e-53, 1.8827498946116e-183))); +insert into t1 values (PointFromWKB(POINT(1.8033161362863e-130, 9.1248812352444e+192))); +insert into t1 values (PointFromWKB(POINT(4.7783097267365e-299, 2.2761049594727e-159))); +insert into t1 values (PointFromWKB(POINT(1.94906280228e+289, 1.2338789709327e-178))); +drop table t1; + # End of 4.1 tests # diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index d7182e36e3a..b4c515d2e8c 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -432,6 +432,14 @@ INSERT INTO t1 VALUES (NULL); SELECT * FROM t1; DROP TABLE t1; +# +# Bug #30955 geomfromtext() crasher +# +CREATE TABLE `t1` ( `col9` set('a'), `col89` date); +INSERT INTO `t1` VALUES ('','0000-00-00'); +select geomfromtext(col9,col89) as a from t1; +DROP TABLE t1; + --echo End of 4.1 tests # @@ -591,6 +599,8 @@ SELECT AsText(GeometryFromText(CONCAT( --enable_query_log SELECT 1; +-- source include/gis_keys.inc + --echo End of 5.0 tests diff --git a/mysql-test/t/grant2.test b/mysql-test/t/grant2.test index f6075ba2ee4..0f0c92e82eb 100644 --- a/mysql-test/t/grant2.test +++ b/mysql-test/t/grant2.test @@ -585,5 +585,37 @@ drop user mysqltest_1@localhost; drop user mysqltest_2@localhost; +# +# Bug #30468: column level privileges not respected when joining tables +# +CREATE DATABASE db1; + +USE db1; +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1,1),(2,2); + +CREATE TABLE t2 (b INT, c INT); +INSERT INTO t2 VALUES (1,100),(2,200); + +GRANT SELECT ON t1 TO mysqltest1@localhost; +GRANT SELECT (b) ON t2 TO mysqltest1@localhost; + +connect (conn1,localhost,mysqltest1,,); +connection conn1; +USE db1; +--error ER_COLUMNACCESS_DENIED_ERROR +SELECT c FROM t2; +--error ER_COLUMNACCESS_DENIED_ERROR +SELECT * FROM t2; +--error ER_COLUMNACCESS_DENIED_ERROR +SELECT * FROM t1 JOIN t2 USING (b); + +connection default; +disconnect conn1; +DROP TABLE db1.t1, db1.t2; +DROP USER mysqltest1@localhost; +DROP DATABASE db1; + + --echo End of 5.0 tests diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index ae616df0dfd..2ea7aed6bd2 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -811,6 +811,7 @@ EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2); EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2); EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a; EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a; +SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a; EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY) IGNORE INDEX FOR GROUP BY (i2) GROUP BY a; EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2); diff --git a/mysql-test/t/heap_btree.test b/mysql-test/t/heap_btree.test index 204b820970c..b51eeb27331 100644 --- a/mysql-test/t/heap_btree.test +++ b/mysql-test/t/heap_btree.test @@ -213,6 +213,15 @@ CREATE TABLE t1 ( INSERT INTO t1 VALUES('1'), ('2'); DROP TABLE t1; +# +# BUG#30590 - delete from memory table with composite btree primary key +# +CREATE TABLE t1 (a INT, KEY USING BTREE(a)) ENGINE=MEMORY; +INSERT INTO t1 VALUES(1),(2),(2); +DELETE FROM t1 WHERE a=2; +SELECT * FROM t1; +DROP TABLE t1; + --echo End of 4.1 tests # diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 9ad658645bd..1987d9d5773 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -1090,6 +1090,14 @@ show columns from t1; drop table t1; --echo End of 5.0 tests. + +# +# Bug#30079 A check for "hidden" I_S tables is flawed +# +--error 1109 +show fields from information_schema.table_names; +--error 1109 +show keys from information_schema.table_names; # # Show engines # @@ -1178,4 +1186,50 @@ select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysql' AND TA select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysql' AND TABLE_NAME=''; select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='' AND TABLE_NAME=''; select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='' AND TABLE_NAME='nonexisting'; + +# +# Bug#30689 Wrong content in I_S.VIEWS.VIEW_DEFINITION if VIEW is based on I_S +# +CREATE VIEW v1 +AS SELECT * +FROM INFORMATION_SCHEMA.TABLES; +SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'v1'; +DROP VIEW v1; + +# +# Bug#30795 Query on INFORMATION_SCHEMA.SCHEMATA, wrong result +# +SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA +WHERE SCHEMA_NAME ='information_schema'; + +# +# Bug#31381 Error in retrieving Data from INFORMATION_SCHEMA +# +SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES +WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db'; + +# +# Bug#31633 Information schema = NULL queries crash the server +# +select * from information_schema.columns where table_schema = NULL; +select * from `information_schema`.`COLUMNS` where `TABLE_NAME` = NULL; +select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL; +select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL; +select * from `information_schema`.`PARTITIONS` where `TABLE_SCHEMA` = NULL; +select * from `information_schema`.`PARTITIONS` where `TABLE_NAME` = NULL; +select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL; +select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL; +select * from information_schema.schemata where schema_name = NULL; +select * from `information_schema`.`STATISTICS` where `TABLE_SCHEMA` = NULL; +select * from `information_schema`.`STATISTICS` where `TABLE_NAME` = NULL; +select * from information_schema.tables where table_schema = NULL; +select * from information_schema.tables where table_catalog = NULL; +select * from information_schema.tables where table_name = NULL; +select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL; +select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL; +select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_SCHEMA` = NULL; +select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_TABLE` = NULL; +select * from `information_schema`.`VIEWS` where `TABLE_SCHEMA` = NULL; +select * from `information_schema`.`VIEWS` where `TABLE_NAME` = NULL; + --echo End of 5.1 tests. diff --git a/mysql-test/t/innodb_gis.test b/mysql-test/t/innodb_gis.test index 9675b6b69dc..1adb14ea482 100644 --- a/mysql-test/t/innodb_gis.test +++ b/mysql-test/t/innodb_gis.test @@ -1,6 +1,7 @@ --source include/have_innodb.inc SET storage_engine=innodb; --source include/gis_generic.inc +--source include/gis_keys.inc # # Bug #15680 (SPATIAL key in innodb) diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index ace51ef61c7..499db086877 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -372,3 +372,15 @@ INSERT INTO t1 (prev_id) SELECT id SELECT * FROM t1; DROP TABLE t1,t2; + +--echo # +--echo # Bug#30384: Having SQL_BUFFER_RESULT option in the +--echo # CREATE .. KEY(..) .. SELECT led to creating corrupted index. +--echo # +create table t1(f1 int); +insert into t1 values(1),(2),(3); +create table t2 (key(f1)) engine=myisam select sql_buffer_result f1 from t1; +check table t2 extended; +drop table t1,t2; +--echo ################################################################## + diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test index 14c98431970..ed1b84bb5ec 100644 --- a/mysql-test/t/join.test +++ b/mysql-test/t/join.test @@ -698,4 +698,33 @@ select '^^: The above should be ~= 20 + cost(select * from t1). Value less than drop table t1, t2; +# +# Bug #31094: Forcing index-based sort doesn't work anymore if joins are +# done +# + +CREATE TABLE t1 (a INT PRIMARY KEY, b INT); +CREATE TABLE t2 (c INT PRIMARY KEY, d INT); + +INSERT INTO t1 VALUES(1,NULL),(2,NULL),(3,NULL),(4,NULL); +INSERT INTO t1 SELECT a + 4, b FROM t1; +INSERT INTO t1 SELECT a + 8, b FROM t1; +INSERT INTO t1 SELECT a + 16, b FROM t1; +INSERT INTO t1 SELECT a + 32, b FROM t1; +INSERT INTO t1 SELECT a + 64, b FROM t1; +INSERT INTO t2 SELECT a, b FROM t1; + +#expect indexed ORDER BY +EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2; +EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2; +SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2; +SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2; + +#expect filesort +EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a; +EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a; +SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a; +SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a; + +DROP TABLE IF EXISTS t1,t2; --echo End of 5.0 tests. diff --git a/mysql-test/t/log_tables.test b/mysql-test/t/log_tables.test index c4bce2fbf3d..12098b4543b 100644 --- a/mysql-test/t/log_tables.test +++ b/mysql-test/t/log_tables.test @@ -253,11 +253,11 @@ use mysql; CREATE TABLE `general_log` ( `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - `user_host` mediumtext, - `thread_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, - `command_type` varchar(64) DEFAULT NULL, - `argument` mediumtext + `user_host` mediumtext NOT NULL, + `thread_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, + `command_type` varchar(64) NOT NULL, + `argument` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'; CREATE TABLE `slow_log` ( @@ -268,10 +268,10 @@ CREATE TABLE `slow_log` ( `lock_time` time NOT NULL, `rows_sent` int(11) NOT NULL, `rows_examined` int(11) NOT NULL, - `db` varchar(512) DEFAULT NULL, - `last_insert_id` int(11) DEFAULT NULL, - `insert_id` int(11) DEFAULT NULL, - `server_id` int(11) DEFAULT NULL, + `db` varchar(512) NOT NULL, + `last_insert_id` int(11) NOT NULL, + `insert_id` int(11) NOT NULL, + `server_id` int(11) NOT NULL, `sql_text` mediumtext NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'; diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index fd479276b3b..a50588b1e78 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -511,4 +511,18 @@ SELECT * FROM tm1; CHECK TABLE tm1; DROP TABLE tm1, t1, t2; +# +# Bug#15522 - create ... select and with merge tables +# +# This was fixed together with Bug#20662 (Infinite loop in CREATE TABLE +# IF NOT EXISTS ... SELECT with locked tables). +# The new behavior for MERGE tables is consistent with the +# CREATE TABLE SELECT behavior for ordinary tables. +# +CREATE TABLE t1(c1 INT); +CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST; +--error ER_UPDATE_TABLE_USED +CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2; +DROP TABLE t1, t2; + --echo End of 5.0 tests diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 0440b0fb63a..158e8a769bd 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -1577,6 +1577,23 @@ SELECT * FROM t2; DROP TABLE t1,t2; --echo # +--echo # Bug#29815: new option for suppressing last line of mysqldump: +--echo # "Dump completed on" +--echo # + +--echo # --skip-dump-date: +--replace_regex /-- [^D][^u][^m][^p].*// /\/\*!.*// +--exec $MYSQL_DUMP --skip-dump-date test + +--echo # --dump-date: +--replace_regex /-- [^D][^u][^m][^p].*// /\/\*!.*// / on [0-9 :-]+/ on DATE/ +--exec $MYSQL_DUMP --dump-date test + +--echo # --dump-date (default): +--replace_regex /-- [^D][^u][^m][^p].*// /\/\*!.*// / on [0-9 :-]+/ on DATE/ +--exec $MYSQL_DUMP test + +--echo # --echo # End of 5.0 tests --echo # diff --git a/mysql-test/t/mysqlslap.test b/mysql-test/t/mysqlslap.test index dffa226d101..28042f62fe6 100644 --- a/mysql-test/t/mysqlslap.test +++ b/mysql-test/t/mysqlslap.test @@ -40,3 +40,16 @@ --exec $MYSQL_SLAP --only-print --delimiter=";" --query="select * from t1;select * from t2" --create="CREATE TABLE t1 (id int, name varchar(64)); create table t2(foo1 varchar(32), foo2 varchar(32)); INSERT INTO t1 VALUES (1, 'This is a test'); insert into t2 values ('test', 'test2')" --engine="heap,myisam" --post-query="SHOW TABLES" --pre-query="SHOW TABLES" --number-of-queries=6 --commit=1; --exec $MYSQL_SLAP --silent --concurrency=5 --iterations=1 --number-int-cols=2 --number-char-cols=3 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write --detach=2 + +--echo # +--echo # Bug #29985: mysqlslap -- improper handling of resultsets in SPROCs +--echo # + +--disable_warnings +DROP PROCEDURE IF EXISTS p1; +--enable_warnings +CREATE PROCEDURE p1() SELECT 1; + +--exec $MYSQL_SLAP --create-schema=test --delimiter=";" --query="CALL p1; SELECT 1;" --silent 2>&1 + +DROP PROCEDURE p1; diff --git a/mysql-test/t/null.test b/mysql-test/t/null.test index 65e09b006ec..2878b54c357 100644 --- a/mysql-test/t/null.test +++ b/mysql-test/t/null.test @@ -1,6 +1,6 @@ # Initialise --disable_warnings -drop table if exists t1; +drop table if exists t1, t2; --enable_warnings # @@ -231,4 +231,27 @@ drop table bug19145a; drop table bug19145b; drop table bug19145c; -# End of 4.1 tests +--echo # End of 4.1 tests + +--echo # +--echo # Bug #31471: decimal_bin_size: Assertion `scale >= 0 && +--echo # precision > 0 && scale <= precision' +--echo # + +CREATE TABLE t1 (a DECIMAL (1, 0) ZEROFILL, b DECIMAL (1, 0) ZEROFILL); +INSERT INTO t1 (a, b) VALUES (0, 0); + +CREATE TABLE t2 SELECT IFNULL(a, b) FROM t1; +DESCRIBE t2; +DROP TABLE t2; + +CREATE TABLE t2 SELECT IFNULL(a, NULL) FROM t1; +DESCRIBE t2; +DROP TABLE t2; + +CREATE TABLE t2 SELECT IFNULL(NULL, b) FROM t1; +DESCRIBE t2; + +DROP TABLE t1, t2; + +--echo # End of 5.0 tests diff --git a/mysql-test/t/olap.test b/mysql-test/t/olap.test index 05934bff492..1ac99d9c39f 100644 --- a/mysql-test/t/olap.test +++ b/mysql-test/t/olap.test @@ -358,3 +358,12 @@ SELECT * FROM (SELECT a, SUM(a) FROM t1 GROUP BY a WITH ROLLUP) as t; DROP TABLE t1; +--echo # +--echo # Bug#31095: Unexpected NULL constant caused server crash. +--echo # +create table t1(a int); +insert into t1 values (1),(2),(3); +select count(a) from t1 group by null with rollup; +drop table t1; +--echo ############################################################## + diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 37398616299..71238504d36 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -779,3 +779,60 @@ EXPLAIN SELECT id,c3 FROM t2 WHERE c2 BETWEEN 20 AND 30 ORDER BY c3 LIMIT 4000; SELECT id,c3 FROM t2 WHERE c2=11 ORDER BY c3 LIMIT 20; DROP TABLE t1,t2; + +# +# Bug #30665: Inconsistent optimization of IGNORE INDEX FOR {ORDER BY|GROUP BY} +# +CREATE TABLE t1 ( + a INT, + b INT, + PRIMARY KEY (a), + KEY ab(a, b) +); +INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4); +INSERT INTO t1 SELECT a + 4, b + 4 FROM t1; +INSERT INTO t1 SELECT a + 8, b + 8 FROM t1; +INSERT INTO t1 SELECT a +16, b +16 FROM t1; +INSERT INTO t1 SELECT a +32, b +32 FROM t1; +INSERT INTO t1 SELECT a +64, b +64 FROM t1; + +EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a; + +--disable_query_log +--let $q = `show status like 'Created_tmp_tables';` +eval set @tmp_tables_before = + CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED); +--enable_query_log + +SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (a, ab) GROUP BY a; + +# this query creates one temporary table in itself, which we are not +# interested in. + +--disable_query_log +--let $q = `show status like 'Created_tmp_tables';` +eval set @tmp_tables_after = + CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED); +--enable_query_log + +SELECT @tmp_tables_after = @tmp_tables_before ; + +EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a; + +--disable_query_log +--let $q = `show status like 'Created_tmp_tables';` +eval set @tmp_tables_before = + CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED); +--enable_query_log + +SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (a, ab) ORDER BY a; + +--disable_query_log +--let $q = `show status like 'Created_tmp_tables';` +eval set @tmp_tables_after = + CAST(REPLACE('$q', 'Created_tmp_tables', '') AS UNSIGNED); +--enable_query_log + +SELECT @tmp_tables_after = @tmp_tables_before; + +DROP TABLE t1; diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index 2be2ab83c88..2906b4640cd 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -1481,6 +1481,15 @@ ALTER TABLE t1 DROP PARTITION p1; DROP TABLE t1; # +# Bug #30484: Partitions: crash with self-referencing trigger +# + +create table t (s1 int) engine=myisam partition by key (s1); +create trigger t_ad after delete on t for each row insert into t values (old.s1); +insert into t values (1); +drop table t; + +# # Bug #27816: Log tables ran with partitions crashes the server when logging # is enabled. # diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index f4320c5c56a..4a50332b3df 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -134,3 +134,11 @@ SELECT * FROM t1 WHERE first_name='Andy' OR last_name='Jake'; drop table t1; +# +# BUG#30583 - Partition on DOUBLE key + INNODB + count(*) == crash +# +CREATE TABLE t1 (a DOUBLE NOT NULL, KEY(a)) ENGINE=InnoDB +PARTITION BY KEY(a) PARTITIONS 10; +INSERT INTO t1 VALUES(1),(2); +SELECT COUNT(*) FROM t1; +DROP TABLE t1; diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index 7571e60859f..9e250372d51 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -1250,10 +1250,9 @@ disconnect user1; disconnect user2; disconnect user3; ---echo End of 5.0 tests - # # Bug #28211 RENAME DATABASE and query cache don't play nicely together +# # TODO: enable these tests when RENAME DATABASE is implemented. # --disable_warnings # drop database if exists db1; @@ -1299,3 +1298,24 @@ set GLOBAL query_cache_limit=default; set GLOBAL query_cache_min_res_unit=default; set GLOBAL query_cache_size=default; +--echo End of 5.0 tests + +# +# Bug #31157: Crash when select+order by the avg of some field within the +# group by +# + +CREATE TABLE t1 (a ENUM('rainbow')); +INSERT INTO t1 VALUES (),(),(),(),(); +SELECT 1 FROM t1 GROUP BY (SELECT 1 FROM t1 ORDER BY AVG(LAST_INSERT_ID())); +DROP TABLE t1; +CREATE TABLE t1 (a LONGBLOB); +INSERT INTO t1 SET a = 'aaaa'; +INSERT INTO t1 SET a = 'aaaa'; +SELECT 1 FROM t1 GROUP BY + (SELECT LAST_INSERT_ID() FROM t1 ORDER BY MIN(a) ASC LIMIT 1); +DROP TABLE t1; + + +--echo End of 5.1 tests + diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test index dcee4fd2ffc..b21f21d2f3d 100644 --- a/mysql-test/t/type_date.test +++ b/mysql-test/t/type_date.test @@ -139,3 +139,58 @@ insert into t1 values (9912101,9912101,9912101); insert into t1 values (11111,11111,11111); select * from t1; drop table t1; + +# +# Bug #30942: select str_to_date from derived table returns varying results +# +CREATE TABLE t1 ( + a INT +); + +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (NULL); + +SELECT str_to_date( '', a ) FROM t1; +DROP TABLE t1; + + +# +# Bug #31221: Optimizer incorrectly identifies impossible WHERE clause +# + +CREATE TABLE t1 (a DATE, b int, PRIMARY KEY (a,b)); +INSERT INTO t1 VALUES (DATE(NOW()), 1); +SELECT COUNT(*) FROM t1 WHERE a = NOW(); +EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); +INSERT INTO t1 VALUES (DATE(NOW()), 2); +SELECT COUNT(*) FROM t1 WHERE a = NOW(); +EXPLAIN SELECT COUNT(*) FROM t1 WHERE a = NOW(); +SELECT COUNT(*) FROM t1 WHERE a = NOW() AND b = 1; +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(); + +DROP TABLE t1; + +# +# Bug #28687: Search fails on '0000-00-00' date after sql_mode change +# + +CREATE TABLE t1 (a DATE); +CREATE TABLE t2 (a DATE); +CREATE INDEX i ON t1 (a); +INSERT INTO t1 VALUES ('0000-00-00'),('0000-00-00'); +INSERT INTO t2 VALUES ('0000-00-00'),('0000-00-00'); +SELECT * FROM t1 WHERE a = '0000-00-00'; +SELECT * FROM t2 WHERE a = '0000-00-00'; +SET SQL_MODE=TRADITIONAL; +EXPLAIN SELECT * FROM t1 WHERE a = '0000-00-00'; +SELECT * FROM t1 WHERE a = '0000-00-00'; +SELECT * FROM t2 WHERE a = '0000-00-00'; +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES ('0000-00-00'); +SET SQL_MODE=DEFAULT; +DROP TABLE t1,t2; + +--echo End of 5.0 tests diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index 6aa6edd128e..fada7983c2c 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -283,6 +283,43 @@ select * from t1 where f1 between 2002010 and 20070101000000; select * from t1 where f1 between 20020101 and 2007010100000; drop table t1; +--echo # +--echo # Bug#27216: functions with parameters of different date types may +--echo # return wrong type of the result. +--echo # +create table t1 (f1 date, f2 datetime, f3 varchar(20)); +create table t2 as select coalesce(f1,f1) as f4 from t1; +desc t2; +create table t3 as select coalesce(f1,f2) as f4 from t1; +desc t3; +create table t4 as select coalesce(f2,f2) as f4 from t1; +desc t4; +create table t5 as select coalesce(f1,f3) as f4 from t1; +desc t5; +create table t6 as select coalesce(f2,f3) as f4 from t1; +desc t6; +create table t7 as select coalesce(makedate(1997,1),f2) as f4 from t1; +desc t7; +create table t8 as select coalesce(cast('01-01-01' as datetime),f2) as f4 + from t1; +desc t8; +create table t9 as select case when 1 then cast('01-01-01' as date) + when 0 then cast('01-01-01' as date) end as f4 from t1; +desc t9; +create table t10 as select case when 1 then cast('01-01-01' as datetime) + when 0 then cast('01-01-01' as datetime) end as f4 from t1; +desc t10; +create table t11 as select if(1, cast('01-01-01' as datetime), + cast('01-01-01' as date)) as f4 from t1; +desc t11; +create table t12 as select least(cast('01-01-01' as datetime), + cast('01-01-01' as date)) as f4 from t1; +desc t12; +create table t13 as select ifnull(cast('01-01-01' as datetime), + cast('01-01-01' as date)) as f4 from t1; +desc t13; +drop tables t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13; +--echo ################################################################### # # Bug #31253: crash comparing datetime to double # Should return 1st row only. Crashes if NULL propagation fails. @@ -302,7 +339,6 @@ select sum(a) from t1 group by convert(a, datetime); drop table t1; --echo End of 5.0 tests - # # Test of storing datetime into date fields # diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test index 458583fca81..4d61350a613 100644 --- a/mysql-test/t/type_decimal.test +++ b/mysql-test/t/type_decimal.test @@ -410,6 +410,13 @@ SELECT ROUND(qty,3), dps, ROUND(qty,dps) FROM t1; DROP TABLE t1; # +# Bug#31019: MOD() function and operator crashes MySQL when +# divisor is very long and < 1 +# + +SELECT 1 % .123456789123456789123456789123456789123456789123456789123456789123456789123456789 AS '%'; +SELECT MOD(1, .123456789123456789123456789123456789123456789123456789123456789123456789123456789) AS 'MOD()'; + # Bug #31227: memory overrun with decimal (6,6) and zerofill and group_concat # valgrind will complain about this (the group_concat(f2)) on unpatched mysqld. # @@ -419,3 +426,4 @@ select group_concat(f1),group_concat(f2) from t1; drop table t1; --echo End of 5.0 tests + diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index f474d166fae..4a161ea3725 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -575,7 +575,14 @@ set @test = @@query_prealloc_size; set @@query_prealloc_size = @test; select @@query_prealloc_size = @test; -# End of 4.1 tests +# +# Bug#31588 buffer overrun when setting variables +# +# Buffer-size Off By One. Should throw valgrind-warning without fix #31588. +--error 1231 +set global sql_mode=repeat('a',80); + +--echo End of 4.1 tests # # Bug#6282 Packet error with SELECT INTO diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 67354c14cff..e388aa61803 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -510,7 +510,7 @@ drop table t1; # create table t1 (a int, b int); create view v1 as select a, sum(b) from t1 group by a; --- error 1176 +--error ER_WRONG_USAGE select b from v1 use index (some_index) where b=1; drop view v1; drop table t1; @@ -2549,21 +2549,20 @@ CREATE TABLE t1( fName varchar(25) NOT NULL, lName varchar(25) NOT NULL, DOB date NOT NULL, + test_date date NOT NULL, uID int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY); -INSERT INTO t1(fName, lName, DOB) VALUES - ('Hank', 'Hill', '1964-09-29'), - ('Tom', 'Adams', '1908-02-14'), - ('Homer', 'Simpson', '1968-03-05'); +INSERT INTO t1(fName, lName, DOB, test_date) VALUES + ('Hank', 'Hill', '1964-09-29', '2007-01-01'), + ('Tom', 'Adams', '1908-02-14', '2007-01-01'), + ('Homer', 'Simpson', '1968-03-05', '2007-01-01'); CREATE VIEW v1 AS - SELECT (year(now())-year(DOB)) AS Age + SELECT (year(test_date)-year(DOB)) AS Age FROM t1 HAVING Age < 75; SHOW CREATE VIEW v1; -set timestamp=1136066400; -SELECT (year(now())-year(DOB)) AS Age FROM t1 HAVING Age < 75; -set timestamp=1136066400; +SELECT (year(test_date)-year(DOB)) AS Age FROM t1 HAVING Age < 75; SELECT * FROM v1; DROP VIEW v1; @@ -3415,6 +3414,46 @@ select table_name, is_updatable from information_schema.views drop view v1; drop table t1; +# +# Bug #28701: SELECTs from VIEWs completely ignore USE/FORCE KEY, allowing +# invalid statements +# + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE VIEW v1 AS SELECT * FROM t1; +--error ER_WRONG_USAGE +SELECT * FROM v1 USE KEY(non_existant); +--error ER_WRONG_USAGE +SELECT * FROM v1 FORCE KEY(non_existant); +--error ER_WRONG_USAGE +SELECT * FROM v1 IGNORE KEY(non_existant); + +DROP VIEW v1; +DROP TABLE t1; + +# +# Bug #28702: VIEWs defined with USE/FORCE KEY ignore that request +# +CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL DEFAULT 0, + PRIMARY KEY(a), KEY (b)); +INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(); +CREATE VIEW v1 AS SELECT * FROM t1 FORCE KEY (PRIMARY,b) ORDER BY a; +SHOW CREATE VIEW v1; +EXPLAIN SELECT * FROM v1; +CREATE VIEW v2 AS SELECT * FROM t1 USE KEY () ORDER BY a; +SHOW CREATE VIEW v2; +EXPLAIN SELECT * FROM v2; +CREATE VIEW v3 AS SELECT * FROM t1 IGNORE KEY (b) ORDER BY a; +SHOW CREATE VIEW v3; +EXPLAIN SELECT * FROM v3; + +DROP VIEW v1; +DROP VIEW v2; +DROP VIEW v3; +DROP TABLE t1; + + --echo End of 5.0 tests. # diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index b3bfd1cf544..3be0148f765 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -1040,10 +1040,11 @@ GRANT SELECT ON db26813.t1 TO u26813@localhost; connect (u1,localhost,u26813,,db26813); connection u1; ---error 1142 +--error ER_SPECIFIC_ACCESS_DENIED_ERROR ALTER VIEW v1 AS SELECT f2 FROM t1; ---error 1142 +--error ER_SPECIFIC_ACCESS_DENIED_ERROR ALTER VIEW v2 AS SELECT f2 FROM t1; +--error ER_SPECIFIC_ACCESS_DENIED_ERROR ALTER VIEW v3 AS SELECT f2 FROM t1; connection root; @@ -1053,6 +1054,51 @@ DROP USER u26813@localhost; DROP DATABASE db26813; disconnect u1; +--echo # +--echo # Bug#29908: A user can gain additional access through the ALTER VIEW. +--echo # +connection root; +CREATE DATABASE mysqltest_29908; +USE mysqltest_29908; +CREATE TABLE t1(f1 INT, f2 INT); +CREATE USER u29908_1@localhost; +CREATE DEFINER = u29908_1@localhost VIEW v1 AS SELECT f1 FROM t1; +CREATE DEFINER = u29908_1@localhost SQL SECURITY INVOKER VIEW v2 AS + SELECT f1 FROM t1; +GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v1 TO u29908_1@localhost; +GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_1@localhost; +GRANT SELECT ON mysqltest_29908.t1 TO u29908_1@localhost; +CREATE USER u29908_2@localhost; +GRANT DROP, CREATE VIEW ON mysqltest_29908.v1 TO u29908_2@localhost; +GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_2@localhost; +GRANT SELECT ON mysqltest_29908.t1 TO u29908_2@localhost; + +connect (u2,localhost,u29908_2,,mysqltest_29908); +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +ALTER VIEW v1 AS SELECT f2 FROM t1; +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +ALTER VIEW v2 AS SELECT f2 FROM t1; +SHOW CREATE VIEW v2; + +connect (u1,localhost,u29908_1,,mysqltest_29908); +ALTER VIEW v1 AS SELECT f2 FROM t1; +SHOW CREATE VIEW v1; +ALTER VIEW v2 AS SELECT f2 FROM t1; +SHOW CREATE VIEW v2; + +connection root; +ALTER VIEW v1 AS SELECT f1 FROM t1; +SHOW CREATE VIEW v1; +ALTER VIEW v2 AS SELECT f1 FROM t1; +SHOW CREATE VIEW v2; + +DROP USER u29908_1@localhost; +DROP USER u29908_2@localhost; +DROP DATABASE mysqltest_29908; +disconnect u1; +disconnect u2; +--echo ####################################################################### + # # BUG#24040: Create View don't succed with "all privileges" on a database. # diff --git a/mysys/my_getopt.c b/mysys/my_getopt.c index 5b5c0881314..3aad6152dfd 100644 --- a/mysys/my_getopt.c +++ b/mysys/my_getopt.c @@ -21,6 +21,9 @@ #include <my_getopt.h> #include <errno.h> +typedef void (*init_func_p)(const struct my_option *option, uchar* *variable, + longlong value); + static void default_reporter(enum loglevel level, const char *format, ...); my_error_reporter my_getopt_error_reporter= &default_reporter; @@ -34,7 +37,12 @@ static longlong getopt_ll(char *arg, const struct my_option *optp, int *err); static ulonglong getopt_ull(char *arg, const struct my_option *optp, int *err); static double getopt_double(char *arg, const struct my_option *optp, int *err); -static void init_variables(const struct my_option *options); +static void init_variables(const struct my_option *options, + init_func_p init_one_value); +static void init_one_value(const struct my_option *option, uchar* *variable, + longlong value); +static void fini_one_value(const struct my_option *option, uchar* *variable, + longlong value); static int setval(const struct my_option *opts, uchar* *value, char *argument, my_bool set_maximum_value); static char *check_struct_option(char *cur_arg, char *key_name); @@ -118,7 +126,7 @@ int handle_options(int *argc, char ***argv, DBUG_ASSERT(argv && *argv); (*argc)--; /* Skip the program name */ (*argv)++; /* --- || ---- */ - init_variables(longopts); + init_variables(longopts, init_one_value); for (pos= *argv, pos_end=pos+ *argc; pos != pos_end ; pos++) { @@ -922,6 +930,37 @@ static void init_one_value(const struct my_option *option, uchar* *variable, } +/* + Init one value to it's default values + + SYNOPSIS + init_one_value() + option Option to initialize + value Pointer to variable +*/ + +static void fini_one_value(const struct my_option *option, uchar* *variable, + longlong value __attribute__ ((unused))) +{ + DBUG_ENTER("fini_one_value"); + switch ((option->var_type & GET_TYPE_MASK)) { + case GET_STR_ALLOC: + my_free((*(char**) variable), MYF(MY_ALLOW_ZERO_PTR)); + *((char**) variable)= NULL; + break; + default: /* dummy default to avoid compiler warnings */ + break; + } + DBUG_VOID_RETURN; +} + + +void my_cleanup_options(const struct my_option *options) +{ + init_variables(options, fini_one_value); +} + + /* initialize all variables to their default values @@ -935,7 +974,8 @@ static void init_one_value(const struct my_option *option, uchar* *variable, for a value and initialize. */ -static void init_variables(const struct my_option *options) +static void init_variables(const struct my_option *options, + init_func_p init_one_value) { DBUG_ENTER("init_variables"); for (; options->name; options++) diff --git a/scripts/mysql_system_tables.sql b/scripts/mysql_system_tables.sql index 927cbe81ff9..7ca35ae0752 100644 --- a/scripts/mysql_system_tables.sql +++ b/scripts/mysql_system_tables.sql @@ -66,7 +66,7 @@ CREATE TABLE IF NOT EXISTS procs_priv ( Host char(60) binary DEFAULT '' NOT NULL -- Create general_log if CSV is enabled. -SET @str = IF (@@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS general_log (event_time TIMESTAMP NOT NULL, user_host MEDIUMTEXT, thread_id INTEGER, server_id INTEGER, command_type VARCHAR(64), argument MEDIUMTEXT) engine=CSV CHARACTER SET utf8 comment="General log"', 'SET @dummy = 0'); +SET @str = IF (@@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS general_log (event_time TIMESTAMP NOT NULL, user_host MEDIUMTEXT NOT NULL, thread_id INTEGER NOT NULL, server_id INTEGER NOT NULL, command_type VARCHAR(64) NOT NULL, argument MEDIUMTEXT NOT NULL) engine=CSV CHARACTER SET utf8 comment="General log"', 'SET @dummy = 0'); PREPARE stmt FROM @str; EXECUTE stmt; @@ -74,7 +74,7 @@ DROP PREPARE stmt; -- Create slow_log if CSV is enabled. -SET @str = IF (@@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS slow_log (start_time TIMESTAMP NOT NULL, user_host MEDIUMTEXT NOT NULL, query_time TIME NOT NULL, lock_time TIME NOT NULL, rows_sent INTEGER NOT NULL, rows_examined INTEGER NOT NULL, db VARCHAR(512), last_insert_id INTEGER, insert_id INTEGER, server_id INTEGER, sql_text MEDIUMTEXT NOT NULL) engine=CSV CHARACTER SET utf8 comment="Slow log"', 'SET @dummy = 0'); +SET @str = IF (@@have_csv = 'YES', 'CREATE TABLE IF NOT EXISTS slow_log (start_time TIMESTAMP NOT NULL, user_host MEDIUMTEXT NOT NULL, query_time TIME NOT NULL, lock_time TIME NOT NULL, rows_sent INTEGER NOT NULL, rows_examined INTEGER NOT NULL, db VARCHAR(512) NOT NULL, last_insert_id INTEGER NOT NULL, insert_id INTEGER NOT NULL, server_id INTEGER NOT NULL, sql_text MEDIUMTEXT NOT NULL) engine=CSV CHARACTER SET utf8 comment="Slow log"', 'SET @dummy = 0'); PREPARE stmt FROM @str; EXECUTE stmt; diff --git a/sql/field.cc b/sql/field.cc index fa93454c757..e04162d5e14 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -1457,15 +1457,24 @@ void Field_num::add_zerofill_and_unsigned(String &res) const void Field::make_field(Send_field *field) { - if (orig_table->s->db.str && *orig_table->s->db.str) + if (orig_table && orig_table->s->db.str && *orig_table->s->db.str) { field->db_name= orig_table->s->db.str; field->org_table_name= orig_table->s->table_name.str; } else field->org_table_name= field->db_name= ""; - field->table_name= orig_table->alias; - field->col_name= field->org_col_name= field_name; + if (orig_table) + { + field->table_name= orig_table->alias; + field->org_col_name= field_name; + } + else + { + field->table_name= ""; + field->org_col_name= ""; + } + field->col_name= field_name; field->charsetnr= charset()->number; field->length=field_length; field->type=type(); @@ -5600,7 +5609,7 @@ int Field_newdate::store(const char *from,uint len,CHARSET_INFO *cs) { tmp= l_time.day + l_time.month*32 + l_time.year*16*32; if (!error && (ret != MYSQL_TIMESTAMP_DATE) && - thd->count_cuted_fields != CHECK_FIELD_IGNORE) + (l_time.hour || l_time.minute || l_time.second || l_time.second_part)) error= 3; // Datetime was cut (note) } @@ -5648,10 +5657,16 @@ int Field_newdate::store(longlong nr, bool unsigned_val) else tmp= l_time.day + l_time.month*32 + l_time.year*16*32; + if (!error && l_time.time_type != MYSQL_TIMESTAMP_DATE && + (l_time.hour || l_time.minute || l_time.second || l_time.second_part)) + error= 3; + if (error) - set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, - error == 2 ? ER_WARN_DATA_OUT_OF_RANGE : - WARN_DATA_TRUNCATED,nr,MYSQL_TIMESTAMP_DATE, 1); + set_datetime_warning(error == 3 ? MYSQL_ERROR::WARN_LEVEL_NOTE : + MYSQL_ERROR::WARN_LEVEL_WARN, + error == 2 ? + ER_WARN_DATA_OUT_OF_RANGE : WARN_DATA_TRUNCATED, + nr,MYSQL_TIMESTAMP_DATE, 1); int3store(ptr,tmp); return error; @@ -5679,6 +5694,17 @@ int Field_newdate::store_time(MYSQL_TIME *ltime,timestamp_type time_type) set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_WARN, WARN_DATA_TRUNCATED, str.ptr(), str.length(), MYSQL_TIMESTAMP_DATE, 1); } + if (!error && ltime->time_type != MYSQL_TIMESTAMP_DATE && + (ltime->hour || ltime->minute || ltime->second || ltime->second_part)) + { + char buff[MAX_DATE_STRING_REP_LENGTH]; + String str(buff, sizeof(buff), &my_charset_latin1); + make_datetime((DATE_TIME_FORMAT *) 0, ltime, &str); + set_datetime_warning(MYSQL_ERROR::WARN_LEVEL_NOTE, + WARN_DATA_TRUNCATED, + str.ptr(), str.length(), MYSQL_TIMESTAMP_DATE, 1); + error= 3; + } } else { @@ -8005,36 +8031,6 @@ uint Field_blob::is_equal(Create_field *new_field) #ifdef HAVE_SPATIAL -uint Field_geom::get_key_image(uchar *buff, uint length, imagetype type) -{ - uchar *blob; - const char *dummy; - MBR mbr; - ulong blob_length= get_length(ptr); - Geometry_buffer buffer; - Geometry *gobj; - const uint image_length= SIZEOF_STORED_DOUBLE*4; - - if (blob_length < SRID_SIZE) - { - bzero(buff, image_length); - return image_length; - } - get_ptr(&blob); - gobj= Geometry::construct(&buffer, (char*) blob, blob_length); - if (!gobj || gobj->get_mbr(&mbr, &dummy)) - bzero(buff, image_length); - else - { - float8store(buff, mbr.xmin); - float8store(buff + 8, mbr.xmax); - float8store(buff + 16, mbr.ymin); - float8store(buff + 24, mbr.ymax); - } - return image_length; -} - - void Field_geom::sql_type(String &res) const { CHARSET_INFO *cs= &my_charset_latin1; diff --git a/sql/field.h b/sql/field.h index 8aad6783291..d0d867d0b32 100644 --- a/sql/field.h +++ b/sql/field.h @@ -1522,7 +1522,6 @@ public: int store(double nr); int store(longlong nr, bool unsigned_val); int store_decimal(const my_decimal *); - uint get_key_image(uchar *buff,uint length,imagetype type); uint size_of() const { return sizeof(*this); } int reset(void) { return !maybe_null() || Field_blob::reset(); } geometry_type get_geometry_type() { return geom_type; }; diff --git a/sql/filesort.cc b/sql/filesort.cc index b6a5d844eac..c074f90e780 100644 --- a/sql/filesort.cc +++ b/sql/filesort.cc @@ -556,7 +556,7 @@ static ha_rows find_all_keys(SORTPARAM *param, SQL_SELECT *select, file->unlock_row(); /* It does not make sense to read more keys in case of a fatal error */ if (thd->net.report_error) - DBUG_RETURN(HA_POS_ERROR); + break; } if (quick_select) { @@ -573,6 +573,9 @@ static ha_rows find_all_keys(SORTPARAM *param, SQL_SELECT *select, file->ha_rnd_end(); } + if (thd->net.report_error) + DBUG_RETURN(HA_POS_ERROR); + /* Signal we should use orignal column read and write maps */ sort_form->column_bitmaps_set(save_read_set, save_write_set); diff --git a/sql/gstream.cc b/sql/gstream.cc index 46e12b6ef3b..0c8011549f3 100644 --- a/sql/gstream.cc +++ b/sql/gstream.cc @@ -44,7 +44,7 @@ bool Gis_read_stream::get_next_word(LEX_STRING *res) skip_space(); res->str= (char*) m_cur; /* The following will also test for \0 */ - if (!my_isvar_start(&my_charset_bin, *m_cur)) + if ((m_cur >= m_limit) || !my_isvar_start(&my_charset_bin, *m_cur)) return 1; /* diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index 200e8a97c67..8afaab71160 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -2924,12 +2924,7 @@ void ha_partition::start_bulk_insert(ha_rows rows) handler **file; DBUG_ENTER("ha_partition::start_bulk_insert"); - if (!rows) - { - /* Avoid allocation big caches in all underlaying handlers */ - DBUG_VOID_RETURN; - } - rows= rows/m_tot_parts + 1; + rows= rows ? rows/m_tot_parts + 1 : 0; file= m_file; do { @@ -3391,6 +3386,22 @@ int ha_partition::index_init(uint inx, bool sorted) */ if (m_lock_type == F_WRLCK) bitmap_union(table->read_set, &m_part_info->full_part_field_set); + else if (sorted && m_table_flags & HA_PARTIAL_COLUMN_READ) + { + /* + An ordered scan is requested and necessary fields aren't in read_set. + This may happen e.g. with SELECT COUNT(*) FROM t1. We must ensure + that all fields of current key are included into read_set, as + partitioning requires them for sorting + (see ha_partition::handle_ordered_index_scan). + + TODO: handle COUNT(*) queries via unordered scan. + */ + uint i; + for (i= 0; i < m_curr_key_info->key_parts; i++) + bitmap_set_bit(table->read_set, + m_curr_key_info->key_part[i].field->field_index); + } file= m_file; do { @@ -4540,6 +4551,8 @@ void ha_partition::get_dynamic_partition_info(PARTITION_INFO *stat_info, 4) Parameters only used by temporary tables for query processing 5) Parameters only used by MyISAM internally 6) Parameters not used at all + 7) Parameters only used by federated tables for query processing + 8) Parameters only used by NDB The partition handler need to handle category 1), 2) and 3). @@ -4806,6 +4819,15 @@ void ha_partition::get_dynamic_partition_info(PARTITION_INFO *stat_info, HA_EXTRA_INSERT_WITH_UPDATE: Inform handler that an "INSERT...ON DUPLICATE KEY UPDATE" will be executed. This condition is unset by HA_EXTRA_NO_IGNORE_DUP_KEY. + + 8) Parameters only used by NDB + ------------------------------ + HA_EXTRA_DELETE_CANNOT_BATCH: + HA_EXTRA_UPDATE_CANNOT_BATCH: + Inform handler that delete_row()/update_row() cannot batch deletes/updates + and should perform them immediately. This may be needed when table has + AFTER DELETE/UPDATE triggers which access to subject table. + These flags are reset by the handler::extra(HA_EXTRA_RESET) call. */ int ha_partition::extra(enum ha_extra_function operation) @@ -4890,6 +4912,13 @@ int ha_partition::extra(enum ha_extra_function operation) /* Category 7), used by federated handlers */ case HA_EXTRA_INSERT_WITH_UPDATE: DBUG_RETURN(loop_extra(operation)); + /* Category 8) Parameters only used by NDB */ + case HA_EXTRA_DELETE_CANNOT_BATCH: + case HA_EXTRA_UPDATE_CANNOT_BATCH: + { + /* Currently only NDB use the *_CANNOT_BATCH */ + break; + } default: { /* Temporary crash to discover what is wrong */ diff --git a/sql/handler.cc b/sql/handler.cc index 706da76c000..3ba52ca1c71 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -3145,6 +3145,8 @@ int handler::read_multi_range_next(KEY_MULTI_RANGE **found_range_p) } else { + if (was_semi_consistent_read()) + goto scan_it_again; /* We need to set this for the last range only, but checking this condition is more expensive than just setting the result code. @@ -3152,10 +3154,10 @@ int handler::read_multi_range_next(KEY_MULTI_RANGE **found_range_p) result= HA_ERR_END_OF_FILE; } + multi_range_curr++; +scan_it_again: /* Try the next range(s) until one matches a record. */ - for (multi_range_curr++; - multi_range_curr < multi_range_end; - multi_range_curr++) + for (; multi_range_curr < multi_range_end; multi_range_curr++) { result= read_range_first(multi_range_curr->start_key.keypart_map ? &multi_range_curr->start_key : 0, diff --git a/sql/item.cc b/sql/item.cc index 18b48280754..95c8cd582cd 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -967,10 +967,13 @@ int Item::save_in_field_no_warnings(Field *field, bool no_conversions) THD *thd= table->in_use; enum_check_fields tmp= thd->count_cuted_fields; my_bitmap_map *old_map= dbug_tmp_use_all_columns(table, table->write_set); + ulong sql_mode= thd->variables.sql_mode; + thd->variables.sql_mode&= ~(MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE); thd->count_cuted_fields= CHECK_FIELD_IGNORE; res= save_in_field(field, no_conversions); thd->count_cuted_fields= tmp; dbug_tmp_restore_column_map(table->write_set, old_map); + thd->variables.sql_mode= sql_mode; return res; } @@ -4316,6 +4319,47 @@ bool Item::is_datetime() } +String *Item::check_well_formed_result(String *str, bool send_error) +{ + /* Check whether we got a well-formed string */ + CHARSET_INFO *cs= str->charset(); + int well_formed_error; + uint wlen= cs->cset->well_formed_len(cs, + str->ptr(), str->ptr() + str->length(), + str->length(), &well_formed_error); + if (wlen < str->length()) + { + THD *thd= current_thd; + char hexbuf[7]; + enum MYSQL_ERROR::enum_warning_level level; + uint diff= str->length() - wlen; + set_if_smaller(diff, 3); + octet2hex(hexbuf, str->ptr() + wlen, diff); + if (send_error) + { + my_error(ER_INVALID_CHARACTER_STRING, MYF(0), + cs->csname, hexbuf); + return 0; + } + if ((thd->variables.sql_mode & + (MODE_STRICT_TRANS_TABLES | MODE_STRICT_ALL_TABLES))) + { + level= MYSQL_ERROR::WARN_LEVEL_ERROR; + null_value= 1; + str= 0; + } + else + { + level= MYSQL_ERROR::WARN_LEVEL_WARN; + str->length(wlen); + } + push_warning_printf(thd, level, ER_INVALID_CHARACTER_STRING, + ER(ER_INVALID_CHARACTER_STRING), cs->csname, hexbuf); + } + return str; +} + + /* Create a field to hold a string value from an item @@ -4461,11 +4505,8 @@ Field *Item::tmp_table_field_from_field_type(TABLE *table, bool fixed_length) break; // Blob handled outside of case #ifdef HAVE_SPATIAL case MYSQL_TYPE_GEOMETRY: - field= new Field_geom(max_length, maybe_null, name, table->s, - (Field::geometry_type) - ((type() == Item::TYPE_HOLDER) ? - ((Item_type_holder *)this)->get_geometry_type() : - ((Item_geometry_func *)this)->get_geometry_type())); + field= new Field_geom(max_length, maybe_null, + name, table->s, get_geometry_type()); #endif /* HAVE_SPATIAL */ } if (field) @@ -4870,6 +4911,19 @@ warn: } +void Item_hex_string::print(String *str) +{ + char *end= (char*) str_value.ptr() + str_value.length(), + *ptr= end - min(str_value.length(), sizeof(longlong)); + str->append("0x"); + for (; ptr != end ; ptr++) + { + str->append(_dig_vec_lower[((uchar) *ptr) >> 4]); + str->append(_dig_vec_lower[((uchar) *ptr) & 0x0F]); + } +} + + bool Item_hex_string::eq(const Item *arg, bool binary_cmp) const { if (arg->basic_const_item() && arg->type() == type()) @@ -6592,9 +6646,7 @@ Item_type_holder::Item_type_holder(THD *thd, Item *item) prev_decimal_int_part= item->decimal_int_part(); #ifdef HAVE_SPATIAL if (item->field_type() == MYSQL_TYPE_GEOMETRY) - geometry_type= (item->type() == Item::FIELD_ITEM) ? - ((Item_field *)item)->get_geometry_type() : - (Field::geometry_type)((Item_geometry_func *)item)->get_geometry_type(); + geometry_type= item->get_geometry_type(); #endif /* HAVE_SPATIAL */ } diff --git a/sql/item.h b/sql/item.h index 655159ab752..c8294eabb6a 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1001,6 +1001,9 @@ public: */ virtual bool result_as_longlong() { return FALSE; } bool is_datetime(); + virtual Field::geometry_type get_geometry_type() const + { return Field::GEOM_GEOMETRY; }; + String *check_well_formed_result(String *str, bool send_error= 0); }; @@ -1243,6 +1246,8 @@ public: Item_name_const(Item *name_arg, Item *val): value_item(val), name_item(name_arg) { + if(!value_item->basic_const_item()) + my_error(ER_WRONG_ARGUMENTS, MYF(0), "NAME_CONST"); Item::maybe_null= TRUE; } @@ -1467,7 +1472,7 @@ public: int fix_outer_field(THD *thd, Field **field, Item **reference); virtual Item *update_value_transformer(uchar *select_arg); void print(String *str); - Field::geometry_type get_geometry_type() + Field::geometry_type get_geometry_type() const { DBUG_ASSERT(field_type() == MYSQL_TYPE_GEOMETRY); return field->get_geometry_type(); @@ -2019,6 +2024,7 @@ public: enum_field_types field_type() const { return MYSQL_TYPE_VARCHAR; } // to prevent drop fixed flag (no need parent cleanup call) void cleanup() {} + void print(String *str); bool eq(const Item *item, bool binary_cmp) const; virtual Item *safe_charset_converter(CHARSET_INFO *tocs); bool check_partition_func_processor(uchar *int_arg) {return FALSE;} @@ -2805,7 +2811,7 @@ public: Field *make_field_by_type(TABLE *table); static uint32 display_length(Item *item); static enum_field_types get_real_type(Item *); - Field::geometry_type get_geometry_type() { return geometry_type; }; + Field::geometry_type get_geometry_type() const { return geometry_type; }; }; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 8f5ff050dd6..04e0011f1a7 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -146,6 +146,35 @@ static int agg_cmp_type(Item_result *type, Item **items, uint nitems) } +/** + @brief Aggregates field types from the array of items. + + @param[in] items array of items to aggregate the type from + @paran[in] nitems number of items in the array + + @details This function aggregates field types from the array of items. + Found type is supposed to be used later as the result field type + of a multi-argument function. + Aggregation itself is performed by the Field::field_type_merge() + function. + + @note The term "aggregation" is used here in the sense of inferring the + result type of a function from its argument types. + + @return aggregated field type. +*/ + +enum_field_types agg_field_type(Item **items, uint nitems) +{ + uint i; + if (!nitems || items[0]->result_type() == ROW_RESULT ) + return (enum_field_types)-1; + enum_field_types res= items[0]->field_type(); + for (i= 1 ; i < nitems ; i++) + res= Field::field_type_merge(res, items[i]->field_type()); + return res; +} + /* Collects different types for comparison of first item with each other items @@ -2046,10 +2075,20 @@ Item_func_ifnull::fix_length_and_dec() agg_result_type(&hybrid_type, args, 2); maybe_null=args[1]->maybe_null; decimals= max(args[0]->decimals, args[1]->decimals); - max_length= (hybrid_type == DECIMAL_RESULT || hybrid_type == INT_RESULT) ? - (max(args[0]->max_length - args[0]->decimals, - args[1]->max_length - args[1]->decimals) + decimals) : - max(args[0]->max_length, args[1]->max_length); + unsigned_flag= args[0]->unsigned_flag && args[1]->unsigned_flag; + + if (hybrid_type == DECIMAL_RESULT || hybrid_type == INT_RESULT) + { + int len0= args[0]->max_length - args[0]->decimals + - (args[0]->unsigned_flag ? 0 : 1); + + int len1= args[1]->max_length - args[1]->decimals + - (args[1]->unsigned_flag ? 0 : 1); + + max_length= max(len0, len1) + decimals + (unsigned_flag ? 0 : 1); + } + else + max_length= max(args[0]->max_length, args[1]->max_length); switch (hybrid_type) { case STRING_RESULT: @@ -2065,9 +2104,7 @@ Item_func_ifnull::fix_length_and_dec() default: DBUG_ASSERT(0); } - cached_field_type= args[0]->field_type(); - if (cached_field_type != args[1]->field_type()) - cached_field_type= Item_func::field_type(); + cached_field_type= agg_field_type(args, 2); } @@ -2215,11 +2252,13 @@ Item_func_if::fix_length_and_dec() { cached_result_type= arg2_type; collation.set(args[2]->collation.collation); + cached_field_type= args[2]->field_type(); } else if (null2) { cached_result_type= arg1_type; collation.set(args[1]->collation.collation); + cached_field_type= args[1]->field_type(); } else { @@ -2233,6 +2272,7 @@ Item_func_if::fix_length_and_dec() { collation.set(&my_charset_bin); // Number } + cached_field_type= agg_field_type(args + 1, 2); } if ((cached_result_type == DECIMAL_RESULT ) @@ -2582,7 +2622,7 @@ void Item_func_case::fix_length_and_dec() agg_arg_charsets(collation, agg, nagg, MY_COLL_ALLOW_CONV, 1)) return; - + cached_field_type= agg_field_type(agg, nagg); /* Aggregate first expression and all THEN expression types and collations when string comparison @@ -2751,6 +2791,7 @@ my_decimal *Item_func_coalesce::decimal_op(my_decimal *decimal_value) void Item_func_coalesce::fix_length_and_dec() { + cached_field_type= agg_field_type(args, arg_count); agg_result_type(&hybrid_type, args, arg_count); switch (hybrid_type) { case STRING_RESULT: @@ -4367,6 +4408,7 @@ Item_func_regex::fix_fields(THD *thd, Item **ref) if (args[1]->null_value) { // Will always return NULL maybe_null=1; + fixed= 1; return FALSE; } int error; diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 40d0c2dfe91..e9aeef7fc3e 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -640,6 +640,7 @@ public: class Item_func_coalesce :public Item_func_numhybrid { protected: + enum_field_types cached_field_type; Item_func_coalesce(Item *a, Item *b) :Item_func_numhybrid(a, b) {} public: Item_func_coalesce(List<Item> &list) :Item_func_numhybrid(list) {} @@ -652,13 +653,13 @@ public: enum Item_result result_type () const { return hybrid_type; } const char *func_name() const { return "coalesce"; } table_map not_null_tables() const { return 0; } + enum_field_types field_type() const { return cached_field_type; } }; class Item_func_ifnull :public Item_func_coalesce { protected: - enum_field_types cached_field_type; bool field_type_defined; public: Item_func_ifnull(Item *a, Item *b) :Item_func_coalesce(a,b) {} @@ -677,6 +678,7 @@ public: class Item_func_if :public Item_func { enum Item_result cached_result_type; + enum_field_types cached_field_type; public: Item_func_if(Item *a,Item *b,Item *c) :Item_func(a,b,c), cached_result_type(INT_RESULT) @@ -686,6 +688,7 @@ public: String *val_str(String *str); my_decimal *val_decimal(my_decimal *); enum Item_result result_type () const { return cached_result_type; } + enum_field_types field_type() const { return cached_field_type; } bool fix_fields(THD *, Item **); void fix_length_and_dec(); uint decimal_precision() const; @@ -713,6 +716,7 @@ public: bool is_null(); }; + /* Functions to handle the optimized IN */ @@ -1104,6 +1108,7 @@ class Item_func_case :public Item_func uint ncases; Item_result cmp_type; DTCollation cmp_collation; + enum_field_types cached_field_type; cmp_item *cmp_items[5]; /* For all result types */ cmp_item *case_item; public: @@ -1134,6 +1139,7 @@ public: uint decimal_precision() const; table_map not_null_tables() const { return 0; } enum Item_result result_type () const { return cached_result_type; } + enum_field_types field_type() const { return cached_field_type; } const char *func_name() const { return "case"; } void print(String *str); Item *find_item(String *str); @@ -1442,6 +1448,7 @@ public: Item *transform(Item_transformer transformer, uchar *arg); void traverse_cond(Cond_traverser, void *arg, traverse_order order); void neg_arguments(THD *thd); + enum_field_types field_type() const { return MYSQL_TYPE_LONGLONG; } bool subst_argument_checker(uchar **arg) { return TRUE; } Item *compile(Item_analyzer analyzer, uchar **arg_p, Item_transformer transformer, uchar *arg_t); diff --git a/sql/item_func.cc b/sql/item_func.cc index fded5fa1391..64574a9ad74 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -1377,7 +1377,11 @@ longlong Item_func_int_div::val_int() void Item_func_int_div::fix_length_and_dec() { - max_length=args[0]->max_length - args[0]->decimals; + Item_result argtype= args[0]->result_type(); + /* use precision ony for the data type it is applicable for and valid */ + max_length=args[0]->max_length - + (argtype == DECIMAL_RESULT || argtype == INT_RESULT ? + args[0]->decimals : 0); maybe_null=1; unsigned_flag=args[0]->unsigned_flag | args[1]->unsigned_flag; } @@ -2238,6 +2242,7 @@ void Item_func_min_max::fix_length_and_dec() else if ((cmp_type == DECIMAL_RESULT) || (cmp_type == INT_RESULT)) max_length= my_decimal_precision_to_length(max_int_part+decimals, decimals, unsigned_flag); + cached_field_type= agg_field_type(args, arg_count); } @@ -3619,10 +3624,17 @@ longlong Item_func_last_insert_id::val_int() thd->first_successful_insert_id_in_prev_stmt= value; return value; } - thd->lex->uncacheable(UNCACHEABLE_SIDEEFFECT); return thd->read_first_successful_insert_id_in_prev_stmt(); } + +bool Item_func_last_insert_id::fix_fields(THD *thd, Item **ref) +{ + thd->lex->uncacheable(UNCACHEABLE_SIDEEFFECT); + return Item_int_func::fix_fields(thd, ref); +} + + /* This function is just used to test speed of different functions */ longlong Item_func_benchmark::val_int() diff --git a/sql/item_func.h b/sql/item_func.h index 66a417f31fa..b2ba57af817 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -436,6 +436,7 @@ public: longlong int_op(); my_decimal *decimal_op(my_decimal *); const char *func_name() const { return "-"; } + virtual bool basic_const_item() const { return args[0]->basic_const_item(); } void fix_length_and_dec(); void fix_num_length_and_dec(); uint decimal_precision() const { return args[0]->decimal_precision(); } @@ -699,7 +700,8 @@ class Item_func_min_max :public Item_func /* An item used for issuing warnings while string to DATETIME conversion. */ Item *datetime_item; THD *thd; - +protected: + enum_field_types cached_field_type; public: Item_func_min_max(List<Item> &list,int cmp_sign_arg) :Item_func(list), cmp_type(INT_RESULT), cmp_sign(cmp_sign_arg), compare_as_dates(FALSE), @@ -712,6 +714,7 @@ public: enum Item_result result_type () const { return cmp_type; } bool result_as_longlong() { return compare_as_dates; }; uint cmp_datetimes(ulonglong *value); + enum_field_types field_type() const { return cached_field_type; } }; class Item_func_min :public Item_func_min_max @@ -754,6 +757,8 @@ public: collation= args[0]->collation; max_length= args[0]->max_length; decimals=args[0]->decimals; + /* The item could be a NULL constant. */ + null_value= args[0]->null_value; } }; @@ -930,6 +935,7 @@ public: if (arg_count) max_length= args[0]->max_length; } + bool fix_fields(THD *thd, Item **ref); }; diff --git a/sql/item_geofunc.cc b/sql/item_geofunc.cc index 78741483c0b..332e88c950d 100644 --- a/sql/item_geofunc.cc +++ b/sql/item_geofunc.cc @@ -28,7 +28,7 @@ Field *Item_geometry_func::tmp_table_field(TABLE *t_arg) { Field *result; if ((result= new Field_geom(max_length, maybe_null, name, t_arg->s, - (Field::geometry_type) get_geometry_type()))) + get_geometry_type()))) result->init(t_arg); return result; } @@ -41,10 +41,6 @@ void Item_geometry_func::fix_length_and_dec() maybe_null= 1; } -int Item_geometry_func::get_geometry_type() const -{ - return (int)Field::GEOM_GEOMETRY; -} String *Item_func_geometry_from_text::val_str(String *str) { @@ -163,9 +159,9 @@ String *Item_func_geometry_type::val_str(String *str) } -int Item_func_envelope::get_geometry_type() const +Field::geometry_type Item_func_envelope::get_geometry_type() const { - return (int) Field::GEOM_POLYGON; + return Field::GEOM_POLYGON; } @@ -193,9 +189,9 @@ String *Item_func_envelope::val_str(String *str) } -int Item_func_centroid::get_geometry_type() const +Field::geometry_type Item_func_centroid::get_geometry_type() const { - return (int) Field::GEOM_POINT; + return Field::GEOM_POINT; } @@ -333,9 +329,9 @@ err: */ -int Item_func_point::get_geometry_type() const +Field::geometry_type Item_func_point::get_geometry_type() const { - return (int) Field::GEOM_POINT; + return Field::GEOM_POINT; } diff --git a/sql/item_geofunc.h b/sql/item_geofunc.h index 068caa447ef..62be78eee9e 100644 --- a/sql/item_geofunc.h +++ b/sql/item_geofunc.h @@ -33,7 +33,6 @@ public: void fix_length_and_dec(); enum_field_types field_type() const { return MYSQL_TYPE_GEOMETRY; } Field *tmp_table_field(TABLE *t_arg); - virtual int get_geometry_type() const; bool is_null() { (void) val_int(); return null_value; } }; @@ -92,7 +91,7 @@ public: Item_func_centroid(Item *a): Item_geometry_func(a) {} const char *func_name() const { return "centroid"; } String *val_str(String *); - int get_geometry_type() const; + Field::geometry_type get_geometry_type() const; }; class Item_func_envelope: public Item_geometry_func @@ -101,7 +100,7 @@ public: Item_func_envelope(Item *a): Item_geometry_func(a) {} const char *func_name() const { return "envelope"; } String *val_str(String *); - int get_geometry_type() const; + Field::geometry_type get_geometry_type() const; }; class Item_func_point: public Item_geometry_func @@ -111,7 +110,7 @@ public: Item_func_point(Item *a, Item *b, Item *srid): Item_geometry_func(a, b, srid) {} const char *func_name() const { return "point"; } String *val_str(String *); - int get_geometry_type() const; + Field::geometry_type get_geometry_type() const; }; class Item_func_spatial_decomp: public Item_geometry_func diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc index d1c8e7a37e8..03c65c9d654 100644 --- a/sql/item_strfunc.cc +++ b/sql/item_strfunc.cc @@ -36,36 +36,6 @@ C_MODE_END String my_empty_string("",default_charset_info); -String *Item_str_func::check_well_formed_result(String *str) -{ - /* Check whether we got a well-formed string */ - CHARSET_INFO *cs= str->charset(); - int well_formed_error; - uint wlen= cs->cset->well_formed_len(cs, - str->ptr(), str->ptr() + str->length(), - str->length(), &well_formed_error); - if (wlen < str->length()) - { - THD *thd= current_thd; - char hexbuf[7]; - enum MYSQL_ERROR::enum_warning_level level; - uint diff= str->length() - wlen; - set_if_smaller(diff, 3); - octet2hex(hexbuf, str->ptr() + wlen, diff); - if (thd->variables.sql_mode & - (MODE_STRICT_TRANS_TABLES | MODE_STRICT_ALL_TABLES)) - { - level= MYSQL_ERROR::WARN_LEVEL_ERROR; - null_value= 1; - str= 0; - } - else - level= MYSQL_ERROR::WARN_LEVEL_WARN; - push_warning_printf(thd, level, ER_INVALID_CHARACTER_STRING, - ER(ER_INVALID_CHARACTER_STRING), cs->csname, hexbuf); - } - return str; -} bool Item_str_func::fix_fields(THD *thd, Item **ref) @@ -2276,11 +2246,13 @@ String *Item_func_char::val_str(String *str) { DBUG_ASSERT(fixed == 1); str->length(0); + str->set_charset(collation.collation); for (uint i=0 ; i < arg_count ; i++) { int32 num=(int32) args[i]->val_int(); if (!args[i]->null_value) { + char char_num= (char) num; if (num&0xFF000000L) { str->append((char)(num>>24)); goto b2; @@ -2290,10 +2262,9 @@ String *Item_func_char::val_str(String *str) } else if (num&0xFF00L) { b1: str->append((char)(num>>8)); } - str->append((char) num); + str->append(&char_num, 1); } } - str->set_charset(collation.collation); str->realloc(str->length()); // Add end 0 (for Purify) return check_well_formed_result(str); } diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h index 155f8457156..1a264fa2c01 100644 --- a/sql/item_strfunc.h +++ b/sql/item_strfunc.h @@ -35,7 +35,6 @@ public: my_decimal *val_decimal(my_decimal *); enum Item_result result_type () const { return STRING_RESULT; } void left_right_max_length(); - String *check_well_formed_result(String *str); bool fix_fields(THD *thd, Item **ref); }; @@ -509,7 +508,7 @@ public: String *val_str(String *); void fix_length_and_dec() { - max_length= arg_count * collation.collation->mbmaxlen; + max_length= arg_count * 4; } const char *func_name() const { return "char"; } }; diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 571e387cf7b..0e1daadd8e7 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -915,7 +915,9 @@ bool Item_sum_distinct::setup(THD *thd) List<Create_field> field_list; Create_field field_def; /* field definition */ DBUG_ENTER("Item_sum_distinct::setup"); - DBUG_ASSERT(tree == 0); + /* It's legal to call setup() more than once when in a subquery */ + if (tree) + DBUG_RETURN(FALSE); /* Virtual table and the tree are created anew on each re-execution of @@ -923,7 +925,7 @@ bool Item_sum_distinct::setup(THD *thd) mem_root. */ if (field_list.push_back(&field_def)) - return TRUE; + DBUG_RETURN(TRUE); null_value= maybe_null= 1; quick_group= 0; @@ -935,7 +937,7 @@ bool Item_sum_distinct::setup(THD *thd) args[0]->unsigned_flag); if (! (table= create_virtual_tmp_table(thd, field_list))) - return TRUE; + DBUG_RETURN(TRUE); /* XXX: check that the case of CHAR(0) works OK */ tree_key_length= table->s->reclength - table->s->null_bytes; @@ -2462,6 +2464,7 @@ bool Item_sum_count_distinct::setup(THD *thd) /* Setup can be called twice for ROLLUP items. This is a bug. Please add DBUG_ASSERT(tree == 0) here when it's fixed. + It's legal to call setup() more than once when in a subquery */ if (tree || table || tmp_table_param) return FALSE; diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index 300d0788f55..8240b7178c7 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -1651,8 +1651,7 @@ bool Item_func_now::get_date(MYSQL_TIME *res, int Item_func_now::save_in_field(Field *to, bool no_conversions) { to->set_notnull(); - to->store_time(<ime, MYSQL_TIMESTAMP_DATETIME); - return 0; + return to->store_time(<ime, MYSQL_TIMESTAMP_DATETIME); } @@ -3239,7 +3238,7 @@ void Item_func_str_to_date::fix_length_and_dec() String format_str(format_buff, sizeof(format_buff), &my_charset_bin), *format; maybe_null= 1; decimals=0; - cached_field_type= MYSQL_TYPE_STRING; + cached_field_type= MYSQL_TYPE_DATETIME; max_length= MAX_DATETIME_FULL_WIDTH*MY_CHARSET_BIN_MB_MAXLEN; cached_timestamp_type= MYSQL_TIMESTAMP_NONE; format= args[1]->val_str(&format_str); diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h index 5503a172825..844c3d84313 100644 --- a/sql/item_timefunc.h +++ b/sql/item_timefunc.h @@ -940,7 +940,10 @@ class Item_func_maketime :public Item_str_timefunc { public: Item_func_maketime(Item *a, Item *b, Item *c) - :Item_str_timefunc(a, b ,c) {} + :Item_str_timefunc(a, b, c) + { + maybe_null= TRUE; + } String *val_str(String *str); const char *func_name() const { return "maketime"; } }; @@ -1009,7 +1012,7 @@ class Item_func_str_to_date :public Item_str_func bool const_item; public: Item_func_str_to_date(Item *a, Item *b) - :Item_str_func(a, b) + :Item_str_func(a, b), const_item(false) {} String *val_str(String *str); bool get_date(MYSQL_TIME *ltime, uint fuzzy_date); diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 49eb2ba0e36..3b88fe0fca8 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -1680,6 +1680,7 @@ void flush_thread_cache(); /* item_func.cc */ extern bool check_reserved_words(LEX_STRING *name); +extern enum_field_types agg_field_type(Item **items, uint nitems); /* strfunc.cc */ ulonglong find_set(TYPELIB *lib, const char *x, uint length, CHARSET_INFO *cs, diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 5a772501e48..99c28be36b0 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -5053,7 +5053,7 @@ static SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param, Item_func *cond_func, if (inv) { - if (func->array->result_type() != ROW_RESULT) + if (func->array && func->array->result_type() != ROW_RESULT) { /* We get here for conditions in form "t.key NOT IN (c1, c2, ...)", diff --git a/sql/protocol.cc b/sql/protocol.cc index 31d23ec94dd..51d408de9de 100644 --- a/sql/protocol.cc +++ b/sql/protocol.cc @@ -827,6 +827,7 @@ bool Protocol_text::store(const char *from, size_t length, field_types[field_pos] == MYSQL_TYPE_DECIMAL || field_types[field_pos] == MYSQL_TYPE_BIT || field_types[field_pos] == MYSQL_TYPE_NEWDECIMAL || + field_types[field_pos] == MYSQL_TYPE_NEWDATE || (field_types[field_pos] >= MYSQL_TYPE_ENUM && field_types[field_pos] <= MYSQL_TYPE_GEOMETRY)); field_pos++; diff --git a/sql/set_var.cc b/sql/set_var.cc index ec82b56d793..138c71fa559 100644 --- a/sql/set_var.cc +++ b/sql/set_var.cc @@ -1420,7 +1420,7 @@ bool sys_var::check_set(THD *thd, set_var *var, TYPELIB *enum_names) ¬_used)); if (error_len) { - strmake(buff, error, min(sizeof(buff), error_len)); + strmake(buff, error, min(sizeof(buff) - 1, error_len)); goto err; } } diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index 36ecf49acb5..0c8c4b8c2d8 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -3991,47 +3991,78 @@ bool check_column_grant_in_table_ref(THD *thd, TABLE_LIST * table_ref, } -bool check_grant_all_columns(THD *thd, ulong want_access, GRANT_INFO *grant, - const char* db_name, const char *table_name, - Field_iterator *fields) +/** + @brief check if a query can access a set of columns + + @param thd the current thread + @param want_access_arg the privileges requested + @param fields an iterator over the fields of a table reference. + @return Operation status + @retval 0 Success + @retval 1 Falure + @details This function walks over the columns of a table reference + The columns may originate from different tables, depending on the kind of + table reference, e.g. join. + For each table it will retrieve the grant information and will use it + to check the required access privileges for the fields requested from it. +*/ +bool check_grant_all_columns(THD *thd, ulong want_access_arg, + Field_iterator_table_ref *fields) { Security_context *sctx= thd->security_ctx; - GRANT_TABLE *grant_table; - GRANT_COLUMN *grant_column; + ulong want_access= want_access_arg; + const char *table_name= NULL; - want_access &= ~grant->privilege; - if (!want_access) - return 0; // Already checked + const char* db_name; + GRANT_INFO *grant; + /* Initialized only to make gcc happy */ + GRANT_TABLE *grant_table= NULL; rw_rdlock(&LOCK_grant); - /* reload table if someone has modified any grants */ - - if (grant->version != grant_version) - { - grant->grant_table= - table_hash_search(sctx->host, sctx->ip, db_name, - sctx->priv_user, - table_name, 0); /* purecov: inspected */ - grant->version= grant_version; /* purecov: inspected */ - } - /* The following should always be true */ - if (!(grant_table= grant->grant_table)) - goto err; /* purecov: inspected */ - for (; !fields->end_of_fields(); fields->next()) { const char *field_name= fields->name(); - grant_column= column_hash_search(grant_table, field_name, - (uint) strlen(field_name)); - if (!grant_column || (~grant_column->rights & want_access)) - goto err; + + if (table_name != fields->table_name()) + { + table_name= fields->table_name(); + db_name= fields->db_name(); + grant= fields->grant(); + /* get a fresh one for each table */ + want_access= want_access_arg & ~grant->privilege; + if (want_access) + { + /* reload table if someone has modified any grants */ + if (grant->version != grant_version) + { + grant->grant_table= + table_hash_search(sctx->host, sctx->ip, db_name, + sctx->priv_user, + table_name, 0); /* purecov: inspected */ + grant->version= grant_version; /* purecov: inspected */ + } + + grant_table= grant->grant_table; + DBUG_ASSERT (grant_table); + } + } + + if (want_access) + { + GRANT_COLUMN *grant_column= + column_hash_search(grant_table, field_name, + (uint) strlen(field_name)); + if (!grant_column || (~grant_column->rights & want_access)) + goto err; + } } rw_unlock(&LOCK_grant); return 0; err: rw_unlock(&LOCK_grant); + char command[128]; get_privilege_desc(command, sizeof(command), want_access); my_error(ER_COLUMNACCESS_DENIED_ERROR, MYF(0), diff --git a/sql/sql_acl.h b/sql/sql_acl.h index cba283ec65b..a279c26c2e4 100644 --- a/sql/sql_acl.h +++ b/sql/sql_acl.h @@ -244,9 +244,8 @@ bool check_grant_column (THD *thd, GRANT_INFO *grant, const char *name, uint length, Security_context *sctx); bool check_column_grant_in_table_ref(THD *thd, TABLE_LIST * table_ref, const char *name, uint length); -bool check_grant_all_columns(THD *thd, ulong want_access, GRANT_INFO *grant, - const char* db_name, const char *table_name, - Field_iterator *fields); +bool check_grant_all_columns(THD *thd, ulong want_access, + Field_iterator_table_ref *fields); bool check_grant_routine(THD *thd, ulong want_access, TABLE_LIST *procs, bool is_proc, bool no_error); bool check_grant_db(THD *thd,const char *db); diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 6307564c14f..10e6716cadf 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -6563,10 +6563,7 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, !any_privileges) { field_iterator.set(tables); - if (check_grant_all_columns(thd, SELECT_ACL, field_iterator.grant(), - field_iterator.db_name(), - field_iterator.table_name(), - &field_iterator)) + if (check_grant_all_columns(thd, SELECT_ACL, &field_iterator)) DBUG_RETURN(TRUE); } #endif @@ -7758,7 +7755,17 @@ open_performance_schema_table(THD *thd, TABLE_LIST *one_table, table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET; } else + { + /* + If error in mysql_lock_tables(), open_ltable doesn't close the + table. Thread kill during mysql_lock_tables() is such error. But + open tables cannot be accepted when restoring the open tables + state. + */ + if (thd->killed) + close_thread_tables(thd); thd->restore_backup_open_tables_state(backup); + } thd->utime_after_lock= save_utime_after_lock; DBUG_RETURN(table); diff --git a/sql/sql_class.h b/sql/sql_class.h index 5aec68e1c61..a7f196cd825 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -2114,7 +2114,7 @@ class select_insert :public select_result_interceptor { ulonglong autoinc_value_of_last_inserted_row; // autogenerated or not COPY_INFO info; bool insert_into_view; - + bool is_bulk_insert_mode; select_insert(TABLE_LIST *table_list_par, TABLE *table_par, List<Item> *fields_par, List<Item> *update_fields, List<Item> *update_values, diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index b4f2d8c65f2..e868afede51 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -189,11 +189,9 @@ static int check_insert_fields(THD *thd, TABLE_LIST *table_list, return -1; } #ifndef NO_EMBEDDED_ACCESS_CHECKS - Field_iterator_table field_it; - field_it.set_table(table); - if (check_grant_all_columns(thd, INSERT_ACL, &table->grant, - table->s->db.str, table->s->table_name.str, - &field_it)) + Field_iterator_table_ref field_it; + field_it.set(table_list); + if (check_grant_all_columns(thd, INSERT_ACL, &field_it)) return -1; #endif clear_timestamp_auto_bits(table->timestamp_field_type, @@ -2786,7 +2784,8 @@ select_insert::select_insert(TABLE_LIST *table_list_par, TABLE *table_par, bool ignore_check_option_errors) :table_list(table_list_par), table(table_par), fields(fields_par), autoinc_value_of_last_inserted_row(0), - insert_into_view(table_list_par && table_list_par->view != 0) + insert_into_view(table_list_par && table_list_par->view != 0), + is_bulk_insert_mode(FALSE) { bzero((char*) &info,sizeof(info)); info.handle_duplicates= duplic; @@ -2972,8 +2971,11 @@ int select_insert::prepare2(void) { DBUG_ENTER("select_insert::prepare2"); if (thd->lex->current_select->options & OPTION_BUFFER_RESULT && - !thd->prelocked_mode) + !thd->prelocked_mode && !is_bulk_insert_mode) + { table->file->ha_start_bulk_insert((ha_rows) 0); + is_bulk_insert_mode= TRUE; + } DBUG_RETURN(0); } @@ -3092,6 +3094,7 @@ bool select_insert::send_eof() trans_table, table->file->table_type())); error= (!thd->prelocked_mode) ? table->file->ha_end_bulk_insert():0; + is_bulk_insert_mode= FALSE; table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY); table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE); @@ -3315,7 +3318,10 @@ static TABLE *create_table_from_items(THD *thd, HA_CREATE_INFO *create_info, Create_field *cr_field; Field *field, *def_field; if (item->type() == Item::FUNC_ITEM) - field= item->tmp_table_field(&tmp_table); + if (item->result_type() != STRING_RESULT) + field= item->tmp_table_field(&tmp_table); + else + field= item->tmp_table_field_from_field_type(&tmp_table, 0); else field= create_tmp_field(thd, &tmp_table, item, item->type(), (Item ***) 0, &tmp_field, &def_field, 0, 0, 0, 0, @@ -3539,7 +3545,10 @@ select_create::prepare(List<Item> &values, SELECT_LEX_UNIT *u) if (info.handle_duplicates == DUP_UPDATE) table->file->extra(HA_EXTRA_INSERT_WITH_UPDATE); if (!thd->prelocked_mode) + { table->file->ha_start_bulk_insert((ha_rows) 0); + is_bulk_insert_mode= TRUE; + } thd->abort_on_warning= (!info.ignore && (thd->variables.sql_mode & (MODE_STRICT_TRANS_TABLES | diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 4b3228390c7..a26c4fd1ca0 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -259,6 +259,8 @@ public: key_name.str= str; key_name.length= length; } + + void print(THD *thd, String *str); }; /* diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 4d8beb6f67d..e6d953bcbe1 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -5791,7 +5791,12 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd, ST_SCHEMA_TABLE *schema_table= find_schema_table(thd, ptr->table_name); if (!schema_table || (schema_table->hidden && - (sql_command_flags[lex->sql_command] & CF_STATUS_COMMAND) == 0)) + ((sql_command_flags[lex->sql_command] & CF_STATUS_COMMAND) == 0 || + /* + this check is used for show columns|keys from I_S hidden table + */ + lex->sql_command == SQLCOM_SHOW_FIELDS || + lex->sql_command == SQLCOM_SHOW_KEYS))) { my_error(ER_UNKNOWN_TABLE, MYF(0), ptr->table_name, INFORMATION_SCHEMA_NAME.str); diff --git a/sql/sql_plugin.cc b/sql/sql_plugin.cc index 2d33da29b77..2af528f6699 100644 --- a/sql/sql_plugin.cc +++ b/sql/sql_plugin.cc @@ -3053,7 +3053,7 @@ static int test_plugin_options(MEM_ROOT *tmp_root, struct st_plugin_int *tmp, MEM_ROOT *mem_root= alloc_root_inited(&tmp->mem_root) ? &tmp->mem_root : &plugin_mem_root; st_mysql_sys_var **opt; - my_option *opts; + my_option *opts= NULL; char *p, *varname; int error; st_mysql_sys_var *o; @@ -3092,7 +3092,7 @@ static int test_plugin_options(MEM_ROOT *tmp_root, struct st_plugin_int *tmp, { sql_print_error("Parsing options for plugin '%s' failed.", tmp->name.str); - DBUG_RETURN(error); + goto err; } } @@ -3102,6 +3102,8 @@ static int test_plugin_options(MEM_ROOT *tmp_root, struct st_plugin_int *tmp, *enabled= TRUE; } + error= 1; + if (*enabled) { for (opt= tmp->plugin->system_vars; opt && *opt; opt++) @@ -3140,7 +3142,7 @@ static int test_plugin_options(MEM_ROOT *tmp_root, struct st_plugin_int *tmp, { sql_print_error("Plugin '%s' has conflicting system variables", tmp->name.str); - DBUG_RETURN(1); + goto err; } tmp->system_vars= chain.first; } @@ -3150,7 +3152,10 @@ static int test_plugin_options(MEM_ROOT *tmp_root, struct st_plugin_int *tmp, if (enabled_saved && global_system_variables.log_warnings) sql_print_information("Plugin '%s' disabled by command line option", tmp->name.str); - DBUG_RETURN(1); +err: + if (opts) + my_cleanup_options(opts); + DBUG_RETURN(error); } diff --git a/sql/sql_repl.cc b/sql/sql_repl.cc index a6e52c05219..0249af147b0 100644 --- a/sql/sql_repl.cc +++ b/sql/sql_repl.cc @@ -1651,6 +1651,9 @@ static int show_slave_skip_errors(THD *thd, SHOW_VAR *var, char *buff); static SHOW_VAR fixed_vars[]= { {"log_slave_updates", (char*) &opt_log_slave_updates, SHOW_MY_BOOL}, + {"relay_log" , (char*) &opt_relay_logname, SHOW_CHAR_PTR}, + {"relay_log_index", (char*) &opt_relaylog_index_name, SHOW_CHAR_PTR}, + {"relay_log_info_file", (char*) &relay_log_info_file, SHOW_CHAR_PTR}, {"relay_log_space_limit", (char*) &relay_log_space_limit, SHOW_LONGLONG}, {"slave_load_tmpdir", (char*) &slave_load_tmpdir, SHOW_CHAR_PTR}, {"slave_skip_errors", (char*) &show_slave_skip_errors, SHOW_FUNC}, diff --git a/sql/sql_select.cc b/sql/sql_select.cc index d041b2edcfa..fd0ce630a01 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1695,7 +1695,7 @@ JOIN::exec() test_if_skip_sort_order(&join_tab[const_tables], order, select_limit, 0, &join_tab[const_tables].table-> - keys_in_use_for_order_by)))) + keys_in_use_for_query)))) order=0; having= tmp_having; select_describe(this, need_tmp, @@ -12651,6 +12651,8 @@ find_field_in_item_list (Field *field, void *data) If we can use an index, the JOIN_TAB / tab->select struct is changed to use the index. + The index must cover all fields in <order>, or it will not be considered. + Return: 0 We have to use filesort to do the sorting 1 We can use an index. @@ -12810,12 +12812,6 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, LINT_INIT(best_key_direction); LINT_INIT(best_records); - /* - filesort() and join cache are usually faster than reading in - index order and not using join cache - */ - if (tab->type == JT_ALL && tab->join->tables > tab->join->const_tables + 1) - DBUG_RETURN(0); /* If not used with LIMIT, only use keys if the whole query can be resolved with a key; This is because filesort() is usually faster than @@ -12823,6 +12819,12 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit, */ if (select_limit >= table_records) { + /* + filesort() and join cache are usually faster than reading in + index order and not using join cache + */ + if (tab->type == JT_ALL && tab->join->tables > tab->join->const_tables + 1) + DBUG_RETURN(0); keys= *table->file->keys_to_use_for_scanning(); keys.merge(table->covering_keys); @@ -14853,6 +14855,9 @@ change_to_use_tmp_fields(THD *thd, Item **ref_pointer_array, item_field= (Item*) new Item_field(field); if (!item_field) DBUG_RETURN(TRUE); // Fatal error + + if (item->real_item()->type() != Item::FIELD_ITEM) + field->orig_table= 0; item_field->name= item->name; if (item->type() == Item::REF_ITEM) { @@ -16098,6 +16103,43 @@ static void print_join(THD *thd, String *str, List<TABLE_LIST> *tables) } +/** + @brief Print an index hint + + @details Prints out the USE|FORCE|IGNORE index hint. + + @param thd the current thread + @param[out] str appends the index hint here + @param hint what the hint is (as string : "USE INDEX"| + "FORCE INDEX"|"IGNORE INDEX") + @param hint_length the length of the string in 'hint' + @param indexes a list of index names for the hint +*/ + +void +Index_hint::print(THD *thd, String *str) +{ + switch (type) + { + case INDEX_HINT_IGNORE: str->append(STRING_WITH_LEN("IGNORE INDEX")); break; + case INDEX_HINT_USE: str->append(STRING_WITH_LEN("USE INDEX")); break; + case INDEX_HINT_FORCE: str->append(STRING_WITH_LEN("FORCE INDEX")); break; + } + str->append (STRING_WITH_LEN(" (")); + if (key_name.length) + { + if (thd && !my_strnncoll(system_charset_info, + (const uchar *)key_name.str, key_name.length, + (const uchar *)primary_key_name, + strlen(primary_key_name))) + str->append(primary_key_name); + else + append_identifier(thd, str, key_name.str, key_name.length); + } + str->append(')'); +} + + /* Print table as it should be in join list @@ -16165,6 +16207,18 @@ void TABLE_LIST::print(THD *thd, String *str) str->append(' '); append_identifier(thd, str, alias, strlen(alias)); } + + if (index_hints) + { + List_iterator<Index_hint> it(*index_hints); + Index_hint *hint; + + while ((hint= it++)) + { + str->append (STRING_WITH_LEN(" ")); + hint->print (thd, str); + } + } } } diff --git a/sql/sql_select.h b/sql/sql_select.h index efa92432e2b..256d57cc10a 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -566,9 +566,13 @@ public: store_key(THD *thd, Field *field_arg, uchar *ptr, uchar *null, uint length) :null_key(0), null_ptr(null), err(0) { - if (field_arg->type() == MYSQL_TYPE_BLOB) + if (field_arg->type() == MYSQL_TYPE_BLOB + || field_arg->type() == MYSQL_TYPE_GEOMETRY) { - /* Key segments are always packed with a 2 byte length prefix */ + /* + Key segments are always packed with a 2 byte length prefix. + See mi_rkey for details. + */ to_field= new Field_varstring(ptr, length, 2, null, 1, Field::NONE, field_arg->field_name, field_arg->table->s, field_arg->charset()); diff --git a/sql/sql_show.cc b/sql/sql_show.cc index f7813b73089..049c050c288 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -2269,10 +2269,12 @@ int make_table_list(THD *thd, SELECT_LEX *sel, @param[in] table I_S table @param[in, out] lookup_field_vals Struct which holds lookup values - @return void + @return + 0 success + 1 error, there can be no matching records for the condition */ -void get_lookup_value(THD *thd, Item_func *item_func, +bool get_lookup_value(THD *thd, Item_func *item_func, TABLE_LIST *table, LOOKUP_FIELD_VALUES *lookup_field_vals) { @@ -2305,13 +2307,17 @@ void get_lookup_value(THD *thd, Item_func *item_func, idx_val= 0; } else - return; + return 0; item_field= (Item_field*) item_func->arguments()[idx_field]; if (table->table != item_field->field->table) - return; + return 0; tmp_str= item_func->arguments()[idx_val]->val_str(&str_buff); + /* impossible value */ + if (!tmp_str) + return 1; + /* Lookup value is database name */ if (!cs->coll->strnncollsp(cs, (uchar *) field_name1, strlen(field_name1), (uchar *) item_field->field_name, @@ -2330,7 +2336,7 @@ void get_lookup_value(THD *thd, Item_func *item_func, tmp_str->length(), FALSE); } } - return; + return 0; } @@ -2346,14 +2352,16 @@ void get_lookup_value(THD *thd, Item_func *item_func, @param[in] table I_S table @param[in, out] lookup_field_vals Struct which holds lookup values - @return void + @return + 0 success + 1 error, there can be no matching records for the condition */ -void calc_lookup_values_from_cond(THD *thd, COND *cond, TABLE_LIST *table, +bool calc_lookup_values_from_cond(THD *thd, COND *cond, TABLE_LIST *table, LOOKUP_FIELD_VALUES *lookup_field_vals) { if (!cond) - return; + return 0; if (cond->type() == Item::COND_ITEM) { @@ -2364,16 +2372,23 @@ void calc_lookup_values_from_cond(THD *thd, COND *cond, TABLE_LIST *table, while ((item= li++)) { if (item->type() == Item::FUNC_ITEM) - get_lookup_value(thd, (Item_func*)item, table, lookup_field_vals); + { + if (get_lookup_value(thd, (Item_func*)item, table, lookup_field_vals)) + return 1; + } else - calc_lookup_values_from_cond(thd, item, table, lookup_field_vals); + { + if (calc_lookup_values_from_cond(thd, item, table, lookup_field_vals)) + return 1; + } } } - return; + return 0; } - else if (cond->type() == Item::FUNC_ITEM) - get_lookup_value(thd, (Item_func*) cond, table, lookup_field_vals); - return; + else if (cond->type() == Item::FUNC_ITEM && + get_lookup_value(thd, (Item_func*) cond, table, lookup_field_vals)) + return 1; + return 0; } @@ -2486,10 +2501,12 @@ static COND * make_cond_for_info_schema(COND *cond, TABLE_LIST *table) @param[in] tables I_S table @param[in, out] lookup_field_values Struct which holds lookup values - @return void + @return + 0 success + 1 error, there can be no matching records for the condition */ -void get_lookup_field_values(THD *thd, COND *cond, TABLE_LIST *tables, +bool get_lookup_field_values(THD *thd, COND *cond, TABLE_LIST *tables, LOOKUP_FIELD_VALUES *lookup_field_values) { LEX *lex= thd->lex; @@ -2503,7 +2520,7 @@ void get_lookup_field_values(THD *thd, COND *cond, TABLE_LIST *tables, lookup_field_values->db_value.length= strlen(wild); lookup_field_values->wild_db_value= 1; } - break; + return 0; case SQLCOM_SHOW_TABLES: case SQLCOM_SHOW_TABLE_STATUS: case SQLCOM_SHOW_TRIGGERS: @@ -2516,14 +2533,13 @@ void get_lookup_field_values(THD *thd, COND *cond, TABLE_LIST *tables, lookup_field_values->table_value.length= strlen(wild); lookup_field_values->wild_table_value= 1; } - break; + return 0; default: /* The "default" is for queries over I_S. All previous cases handle SHOW commands. */ - calc_lookup_values_from_cond(thd, cond, tables, lookup_field_values); - break; + return calc_lookup_values_from_cond(thd, cond, tables, lookup_field_values); } } @@ -3113,7 +3129,11 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond) } schema_table_idx= get_schema_table_idx(schema_table); - get_lookup_field_values(thd, cond, tables, &lookup_field_vals); + if (get_lookup_field_values(thd, cond, tables, &lookup_field_vals)) + { + error= 0; + goto err; + } DBUG_PRINT("INDEX VALUES",("db_name='%s', table_name='%s'", lookup_field_vals.db_value.str, lookup_field_vals.table_value.str)); @@ -3328,7 +3348,8 @@ int fill_schema_schemata(THD *thd, TABLE_LIST *tables, COND *cond) #endif DBUG_ENTER("fill_schema_shemata"); - get_lookup_field_values(thd, cond, tables, &lookup_field_vals); + if (get_lookup_field_values(thd, cond, tables, &lookup_field_vals)) + DBUG_RETURN(0); DBUG_PRINT("INDEX VALUES",("db_name='%s', table_name='%s'", lookup_field_vals.db_value.str, lookup_field_vals.table_value.str)); @@ -3339,7 +3360,8 @@ int fill_schema_schemata(THD *thd, TABLE_LIST *tables, COND *cond) /* If we have lookup db value we should check that the database exists */ - if(lookup_field_vals.db_value.str && !lookup_field_vals.wild_db_value) + if(lookup_field_vals.db_value.str && !lookup_field_vals.wild_db_value && + !with_i_schema) { char path[FN_REFLEN+16]; uint path_len; @@ -3486,6 +3508,10 @@ static int get_schema_tables_record(THD *thd, TABLE_LIST *tables, (ptr == option_buff ? 0 : (uint) (ptr-option_buff)-1), cs); + tmp_buff= (share->table_charset ? + share->table_charset->name : "default"); + table->field[17]->store(tmp_buff, strlen(tmp_buff), cs); + if (share->comment.str) table->field[20]->store(share->comment.str, share->comment.length, cs); @@ -3563,9 +3589,6 @@ static int get_schema_tables_record(THD *thd, TABLE_LIST *tables, table->field[16]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); table->field[16]->set_notnull(); } - tmp_buff= (share->table_charset ? - share->table_charset->name : "default"); - table->field[17]->store(tmp_buff, strlen(tmp_buff), cs); if (file->ha_table_flags() & (ulong) HA_HAS_CHECKSUM) { table->field[18]->store((longlong) file->checksum(), TRUE); diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 2bd89d4a0ae..6edb8494b03 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -2740,7 +2740,7 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info, } if (f_is_geom(sql_field->pack_flag) && sql_field->geom_type == Field::GEOM_POINT) - column->length= 21; + column->length= 25; if (!column->length) { my_error(ER_BLOB_KEY_WITHOUT_LENGTH, MYF(0), column->field_name); diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 56d50761d95..6e27af63e8a 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -223,9 +223,6 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views, { LEX *lex= thd->lex; bool link_to_local; -#ifndef NO_EMBEDDED_ACCESS_CHECKS - bool definer_check_is_needed= mode != VIEW_ALTER || lex->definer; -#endif /* first table in list is target VIEW name => cut off it */ TABLE_LIST *view= lex->unlink_first_table(&link_to_local); TABLE_LIST *tables= lex->query_tables; @@ -280,7 +277,7 @@ bool mysql_create_view(THD *thd, TABLE_LIST *views, - same as current user - current user has SUPER_ACL */ - if (definer_check_is_needed && + if (lex->definer && (strcmp(lex->definer->user.str, thd->security_ctx->priv_user) != 0 || my_strcasecmp(system_charset_info, lex->definer->host.str, @@ -672,7 +669,7 @@ static File_option view_parameters[]= FILE_OPTIONS_STRING}, {{(char*) STRING_WITH_LEN("view_body_utf8")}, my_offsetof(TABLE_LIST, view_body_utf8), - FILE_OPTIONS_STRING}, + FILE_OPTIONS_ESTRING}, {{NullS, 0}, 0, FILE_OPTIONS_STRING} }; @@ -953,6 +950,12 @@ bool mysql_make_view(THD *thd, File_parser *parser, TABLE_LIST *table, DBUG_RETURN(0); } + if (table->index_hints && table->index_hints->elements) + { + my_error(ER_WRONG_USAGE, MYF(0), "index hints", "VIEW"); + DBUG_RETURN(TRUE); + } + /* check loop via view definition */ for (TABLE_LIST *precedent= table->referencing_view; precedent; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 30e62c5d7b5..109e8f5434f 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -4704,7 +4704,7 @@ spatial_type: | GEOMETRYCOLLECTION { $$= Field::GEOM_GEOMETRYCOLLECTION; } | POINT_SYM { - Lex->length= (char*)"21"; + Lex->length= (char*)"25"; $$= Field::GEOM_POINT; } | MULTIPOINT { $$= Field::GEOM_MULTIPOINT; } @@ -9755,11 +9755,15 @@ literal: String *str= tmp ? tmp->quick_fix_field(), tmp->val_str((String*) 0) : (String*) 0; - $$= new Item_string(str ? str->ptr() : "", + $$= new Item_string(NULL, /* name will be set in select_item */ + str ? str->ptr() : "", str ? str->length() : 0, $1); - if ($$) - ((Item_string *) $$)->set_repertoire_from_value(); + if (!$$ || !$$->check_well_formed_result(&$$->str_value, TRUE)) + { + MYSQL_YYABORT; + } + ((Item_string *) $$)->set_repertoire_from_value(); } | UNDERSCORE_CHARSET BIN_NUM { @@ -9771,14 +9775,18 @@ literal: String *str= tmp ? tmp->quick_fix_field(), tmp->val_str((String*) 0) : (String*) 0; - $$= new Item_string(str ? str->ptr() : "", + $$= new Item_string(NULL, /* name will be set in select_item */ + str ? str->ptr() : "", str ? str->length() : 0, - Lex->charset); + $1); + if (!$$ || !$$->check_well_formed_result(&$$->str_value, TRUE)) + { + MYSQL_YYABORT; + } } - | DATE_SYM text_literal { $$ = $2; } - | TIME_SYM text_literal { $$ = $2; } - | TIMESTAMP text_literal { $$ = $2; } - ; + | DATE_SYM text_literal { $$ = $2; } + | TIME_SYM text_literal { $$ = $2; } + | TIMESTAMP text_literal { $$ = $2; }; NUM_literal: NUM diff --git a/sql/table.cc b/sql/table.cc index c1d8e3abe94..a113975c5c5 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -969,6 +969,7 @@ static int open_binary_frm(THD *thd, TABLE_SHARE *share, uchar *head, } parser_name.str= (char*) next_chunk; parser_name.length= strlen((char*) next_chunk); + next_chunk+= parser_name.length + 1; keyinfo->parser= my_plugin_lock_by_name(NULL, &parser_name, MYSQL_FTPARSER_PLUGIN); if (! keyinfo->parser) @@ -1351,9 +1352,11 @@ static int open_binary_frm(THD *thd, TABLE_SHARE *share, uchar *head, keyinfo->key_length+= HA_KEY_NULL_LENGTH; } if (field->type() == MYSQL_TYPE_BLOB || - field->real_type() == MYSQL_TYPE_VARCHAR) + field->real_type() == MYSQL_TYPE_VARCHAR || + field->type() == MYSQL_TYPE_GEOMETRY) { - if (field->type() == MYSQL_TYPE_BLOB) + if (field->type() == MYSQL_TYPE_BLOB || + field->type() == MYSQL_TYPE_GEOMETRY) key_part->key_part_flag|= HA_BLOB_PART; else key_part->key_part_flag|= HA_VAR_LENGTH_PART; diff --git a/storage/csv/ha_tina.cc b/storage/csv/ha_tina.cc index 9a7781e017d..6a87b8ecc72 100644 --- a/storage/csv/ha_tina.cc +++ b/storage/csv/ha_tina.cc @@ -471,22 +471,30 @@ int ha_tina::encode_quote(uchar *buf) { const char *ptr; const char *end_ptr; + const bool was_null= (*field)->is_null(); /* - CSV does not support nulls. Write quoted 0 to the buffer. In fact, - (*field)->val_str(&attribute,&attribute) would usually return 0 - in this case but we write it explicitly here. - Basically this is a safety check, as no one ensures that the - field content is cleaned up every time we use Field::set_null() - in the code. + CSV does not support nulls. ::create() prevents creation of a table + with nullable columns so if we encounter them here, there is a bug. + This may only occur if the frm was created by an older version of + mysqld which permitted table creation with nullable columns. */ - if ((*field)->is_null()) + DBUG_ASSERT(!(*field)->maybe_null()); + + /* + assistance for backwards compatibility in production builds. + note: this will not work for ENUM columns. + */ + if (was_null) { - buffer.append(STRING_WITH_LEN("\"0\",")); - continue; + (*field)->set_default(); + (*field)->set_notnull(); } (*field)->val_str(&attribute,&attribute); + + if (was_null) + (*field)->set_null(); if ((*field)->str_needs_quotes()) { @@ -1480,6 +1488,16 @@ int ha_tina::create(const char *name, TABLE *table_arg, File create_file; DBUG_ENTER("ha_tina::create"); + /* + check columns + */ + for (Field **field= table_arg->s->field; *field; field++) + { + if ((*field)->real_maybe_null()) + DBUG_RETURN(-1); + } + + if ((create_file= my_create(fn_format(name_buff, name, "", CSM_EXT, MY_REPLACE_EXT|MY_UNPACK_FILENAME), 0, O_RDWR | O_TRUNC,MYF(MY_WME))) < 0) diff --git a/storage/federated/ha_federated.cc b/storage/federated/ha_federated.cc index 3fc17e18e76..8673847fcac 100644 --- a/storage/federated/ha_federated.cc +++ b/storage/federated/ha_federated.cc @@ -2769,7 +2769,12 @@ int ha_federated::info(uint flag) status_query_string.length(0); result= mysql_store_result(mysql); - if (!result) + + /* + We're going to use fields num. 4, 12 and 13 of the resultset, + so make sure we have these fields. + */ + if (!result || (mysql_num_fields(result) < 14)) goto error; if (!mysql_num_rows(result)) diff --git a/storage/heap/hp_delete.c b/storage/heap/hp_delete.c index 1dd79a42e0b..9e9e28da335 100644 --- a/storage/heap/hp_delete.c +++ b/storage/heap/hp_delete.c @@ -72,10 +72,7 @@ int hp_rb_delete_key(HP_INFO *info, register HP_KEYDEF *keyinfo, int res; if (flag) - { info->last_pos= NULL; /* For heap_rnext/heap_rprev */ - info->lastkey_len= 0; - } custom_arg.keyseg= keyinfo->seg; custom_arg.key_length= hp_rb_make_key(keyinfo, info->recbuf, record, recpos); diff --git a/storage/heap/hp_rfirst.c b/storage/heap/hp_rfirst.c index 48c1e625bd8..d0d2ec9b506 100644 --- a/storage/heap/hp_rfirst.c +++ b/storage/heap/hp_rfirst.c @@ -35,6 +35,17 @@ int heap_rfirst(HP_INFO *info, uchar *record, int inx) sizeof(uchar*)); info->current_ptr = pos; memcpy(record, pos, (size_t)share->reclength); + /* + If we're performing index_first on a table that was taken from + table cache, info->lastkey_len is initialized to previous query. + Thus we set info->lastkey_len to proper value for subsequent + heap_rnext() calls. + This is needed for DELETE queries only, otherwise this variable is + not used. + Note that the same workaround may be needed for heap_rlast(), but + for now heap_rlast() is never used for DELETE queries. + */ + info->lastkey_len= 0; info->update = HA_STATE_AKTIV; } else diff --git a/storage/heap/hp_rnext.c b/storage/heap/hp_rnext.c index 262754e9e64..3d715f4e6d3 100644 --- a/storage/heap/hp_rnext.c +++ b/storage/heap/hp_rnext.c @@ -33,11 +33,40 @@ int heap_rnext(HP_INFO *info, uchar *record) heap_rb_param custom_arg; if (info->last_pos) + { + /* + We enter this branch for non-DELETE queries after heap_rkey() + or heap_rfirst(). As last key position (info->last_pos) is available, + we only need to climb the tree using tree_search_next(). + */ pos = tree_search_next(&keyinfo->rb_tree, &info->last_pos, offsetof(TREE_ELEMENT, left), offsetof(TREE_ELEMENT, right)); + } + else if (!info->lastkey_len) + { + /* + We enter this branch only for DELETE queries after heap_rfirst(). E.g. + DELETE FROM t1 WHERE a<10. As last key position is not available + (last key is removed by heap_delete()), we must restart search as it + is done in heap_rfirst(). + + It should be safe to handle this situation without this branch. That is + branch below should find smallest element in a tree as lastkey_len is + zero. tree_search_edge() is a kind of optimisation here as it should be + faster than tree_search_key(). + */ + pos= tree_search_edge(&keyinfo->rb_tree, info->parents, + &info->last_pos, offsetof(TREE_ELEMENT, left)); + } else { + /* + We enter this branch only for DELETE queries after heap_rkey(). E.g. + DELETE FROM t1 WHERE a=10. As last key position is not available + (last key is removed by heap_delete()), we must restart search as it + is done in heap_rkey(). + */ custom_arg.keyseg = keyinfo->seg; custom_arg.key_length = info->lastkey_len; custom_arg.search_flag = SEARCH_SAME | SEARCH_FIND; diff --git a/storage/myisam/rt_index.c b/storage/myisam/rt_index.c index 63ed60586d6..25f9d7c19e4 100644 --- a/storage/myisam/rt_index.c +++ b/storage/myisam/rt_index.c @@ -483,15 +483,16 @@ static uchar *rtree_pick_key(MI_INFO *info, MI_KEYDEF *keyinfo, uchar *key, uint key_length, uchar *page_buf, uint nod_flag) { double increase; - double best_incr = DBL_MAX; + double best_incr; double area; double best_area; - uchar *best_key; + uchar *best_key= NULL; uchar *k = rt_PAGE_FIRST_KEY(page_buf, nod_flag); uchar *last = rt_PAGE_END(page_buf); LINT_INIT(best_area); LINT_INIT(best_key); + LINT_INIT(best_incr); for (; k < last; k = rt_PAGE_NEXT_KEY(k, key_length, nod_flag)) { @@ -500,22 +501,13 @@ static uchar *rtree_pick_key(MI_INFO *info, MI_KEYDEF *keyinfo, uchar *key, &area)) == -1.0) return NULL; /* The following should be safe, even if we compare doubles */ - if (increase < best_incr) + if (!best_key || increase < best_incr || + ((increase == best_incr) && (area < best_area))) { best_key = k; best_area = area; best_incr = increase; } - else - { - /* The following should be safe, even if we compare doubles */ - if ((increase == best_incr) && (area < best_area)) - { - best_key = k; - best_area = area; - best_incr = increase; - } - } } return best_key; } diff --git a/storage/myisam/rt_mbr.c b/storage/myisam/rt_mbr.c index 1855e24feb0..deca23bbec7 100644 --- a/storage/myisam/rt_mbr.c +++ b/storage/myisam/rt_mbr.c @@ -523,7 +523,10 @@ double rtree_overlapping_area(HA_KEYSEG *keyseg, uchar* a, uchar* b, } /* -Calculates MBR_AREA(a+b) - MBR_AREA(a) + Calculates MBR_AREA(a+b) - MBR_AREA(a) + Note: when 'a' and 'b' objects are far from each other, + the area increase can be really big, so this function + can return 'inf' as a result. */ double rtree_area_increase(HA_KEYSEG *keyseg, uchar* a, uchar* b, uint key_length, double *ab_area) diff --git a/strings/decimal.c b/strings/decimal.c index f1753c619d1..f457014b2b1 100644 --- a/strings/decimal.c +++ b/strings/decimal.c @@ -2329,11 +2329,12 @@ static int do_div_mod(decimal_t *from1, decimal_t *from2, } if (unlikely(intg0+frac0 > to->len)) { - stop1-=to->len-frac0-intg0; + stop1-=frac0+intg0-to->len; frac0=to->len-intg0; to->frac=frac0*DIG_PER_DEC1; error=E_DEC_TRUNCATED; } + DBUG_ASSERT(buf0 + (stop1 - start1) <= to->buf + to->len); while (start1 < stop1) *buf0++=*start1++; } diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index 409917d4219..d3ba0660198 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -16001,7 +16001,7 @@ static void test_bug21635() char *query_end; MYSQL_RES *result; MYSQL_FIELD *field; - unsigned int field_count, i; + unsigned int field_count, i, j; int rc; DBUG_ENTER("test_bug21635"); @@ -16017,30 +16017,39 @@ static void test_bug21635() myquery(rc); rc= mysql_query(mysql, "CREATE TABLE t1 (i INT)"); myquery(rc); - rc= mysql_query(mysql, "INSERT INTO t1 VALUES (1)"); - myquery(rc); + /* + We need this loop to ensure correct behavior with both constant and + non-constant tables. + */ + for (j= 0; j < 2 ; j++) + { + rc= mysql_query(mysql, "INSERT INTO t1 VALUES (1)"); + myquery(rc); - rc= mysql_real_query(mysql, query, query_end - query); - myquery(rc); + rc= mysql_real_query(mysql, query, query_end - query); + myquery(rc); - result= mysql_use_result(mysql); - DIE_UNLESS(result); + result= mysql_use_result(mysql); + DIE_UNLESS(result); field_count= mysql_field_count(mysql); for (i= 0; i < field_count; ++i) { field= mysql_fetch_field_direct(result, i); if (!opt_silent) - printf("%s -> %s ... ", expr[i * 2], field->name); + if (!opt_silent) + printf("%s -> %s ... ", expr[i * 2], field->name); fflush(stdout); DIE_UNLESS(field->db[0] == 0 && field->org_table[0] == 0 && field->table[0] == 0 && field->org_name[0] == 0); DIE_UNLESS(strcmp(field->name, expr[i * 2 + 1]) == 0); if (!opt_silent) - puts("OK"); + if (!opt_silent) + puts("OK"); } - mysql_free_result(result); + mysql_free_result(result); + } rc= mysql_query(mysql, "DROP TABLE t1"); myquery(rc); |