diff options
114 files changed, 2517 insertions, 363 deletions
diff --git a/.bzrignore b/.bzrignore index c0bac9d05f1..7a8066fdba1 100644 --- a/.bzrignore +++ b/.bzrignore @@ -900,6 +900,7 @@ libmysql_r/.libs/libmysqlclient_r.lai libmysql_r/.libs/libmysqlclient_r.so.15 libmysql_r/.libs/libmysqlclient_r.so.15.0.0 libmysql_r/acconfig.h +libmysql_r/client_settings.h libmysql_r/conf_to_src libmysql_r/my_static.h libmysql_r/mysys_priv.h @@ -3004,4 +3005,3 @@ win/vs71cache.txt win/vs8cache.txt zlib/*.ds? zlib/*.vcproj -libmysql_r/client_settings.h diff --git a/client/mysql.cc b/client/mysql.cc index 1dd33593b83..180bc8df848 100644 --- a/client/mysql.cc +++ b/client/mysql.cc @@ -144,6 +144,7 @@ static my_bool ignore_errors=0,wait_flag=0,quick=0, show_warnings= 0, executing_query= 0, interrupted_query= 0; static my_bool debug_info_flag, debug_check_flag; static my_bool column_types_flag; +static my_bool preserve_comments= 0; static ulong opt_max_allowed_packet, opt_net_buffer_length; static uint verbose=0,opt_silent=0,opt_mysql_port=0, opt_local_infile=0; static uint my_end_arg; @@ -778,6 +779,10 @@ static struct my_option my_long_options[] = {"show-warnings", OPT_SHOW_WARNINGS, "Show warnings after every statement.", (uchar**) &show_warnings, (uchar**) &show_warnings, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, + {"comments", 'c', "Preserve comments. Send comments to the server." + " Comments are discarded by default, enable with --enable-comments", + (uchar**) &preserve_comments, (uchar**) &preserve_comments, + 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, { 0, 0, 0, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0} }; @@ -1154,10 +1159,6 @@ static int read_and_execute(bool interactive) status.exit_status=0; break; } - if (!in_string && (line[0] == '#' || - (line[0] == '-' && line[1] == '-') || - line[0] == 0)) - continue; // Skip comment lines /* Check if line is a mysql command line @@ -1283,15 +1284,21 @@ static bool add_line(String &buffer,char *line,char *in_string, for (pos=out=line ; (inchar= (uchar) *pos) ; pos++) { - if (my_isspace(charset_info,inchar) && out == line && - buffer.is_empty()) - continue; + if (!preserve_comments) + { + // Skip spaces at the beggining of a statement + if (my_isspace(charset_info,inchar) && (out == line) && + buffer.is_empty()) + continue; + } + #ifdef USE_MB + // Accept multi-byte characters as-is int length; if (use_mb(charset_info) && (length= my_ismbchar(charset_info, pos, end_of_line))) { - if (!*ml_comment) + if (!*ml_comment || preserve_comments) { while (length--) *out++ = *pos++; @@ -1317,8 +1324,13 @@ static bool add_line(String &buffer,char *line,char *in_string, } if ((com=find_command(NullS,(char) inchar))) { - const String tmp(line,(uint) (out-line), charset_info); - buffer.append(tmp); + // Flush previously accepted characters + if (out != line) + { + buffer.append(line, (uint) (out-line)); + out= line; + } + if ((*com->func)(&buffer,pos-1) > 0) DBUG_RETURN(1); // Quit if (com->takes_params) @@ -1346,7 +1358,6 @@ static bool add_line(String &buffer,char *line,char *in_string, pos+= delimiter_length - 1; // Point at last delim char } } - out=line; } else { @@ -1359,46 +1370,105 @@ static bool add_line(String &buffer,char *line,char *in_string, } } else if (!*ml_comment && !*in_string && - (*pos == *delimiter && is_prefix(pos + 1, delimiter + 1) || - buffer.length() == 0 && (out - line) >= 9 && - !my_strcasecmp(charset_info, line, "delimiter"))) - { - uint old_delimiter_length= delimiter_length; + strlen(pos) >= 10 && + !my_strnncoll(charset_info, (uchar*) pos, 10, + (const uchar*) "delimiter ", 10)) + { + // Flush previously accepted characters if (out != line) - buffer.append(line, (uint) (out - line)); // Add this line + { + buffer.append(line, (uint32) (out - line)); + out= line; + } + + // Flush possible comments in the buffer + if (!buffer.is_empty()) + { + if (com_go(&buffer, 0) > 0) // < 0 is not fatal + DBUG_RETURN(1); + buffer.length(0); + } + + /* + Delimiter wants the get rest of the given line as argument to + allow one to change ';' to ';;' and back + */ + buffer.append(pos); + if (com_delimiter(&buffer, pos) > 0) + DBUG_RETURN(1); + + buffer.length(0); + break; + } + else if (!*ml_comment && !*in_string && is_prefix(pos, delimiter)) + { + // Found a statement. Continue parsing after the delimiter + pos+= delimiter_length; + + if (preserve_comments) + { + while (my_isspace(charset_info, *pos)) + *out++= *pos++; + } + // Flush previously accepted characters + if (out != line) + { + buffer.append(line, (uint32) (out-line)); + out= line; + } + + if (preserve_comments && ((*pos == '#') || + ((*pos == '-') && + (pos[1] == '-') && + my_isspace(charset_info, pos[2])))) + { + // Add trailing single line comments to this statement + buffer.append(pos); + pos+= strlen(pos); + } + + pos--; + if ((com= find_command(buffer.c_ptr(), 0))) { - if (com->func == com_delimiter) - { - /* - Delimiter wants the get rest of the given line as argument to - allow one to change ';' to ';;' and back - */ - char *end= strend(pos); - buffer.append(pos, (uint) (end - pos)); - /* Ensure pos will point at \0 after the pos+= below */ - pos= end - old_delimiter_length + 1; - } - if ((*com->func)(&buffer, buffer.c_ptr()) > 0) - DBUG_RETURN(1); // Quit + + if ((*com->func)(&buffer, buffer.c_ptr()) > 0) + DBUG_RETURN(1); // Quit } else { - if (com_go(&buffer, 0) > 0) // < 0 is not fatal - DBUG_RETURN(1); + if (com_go(&buffer, 0) > 0) // < 0 is not fatal + DBUG_RETURN(1); } buffer.length(0); - out= line; - pos+= old_delimiter_length - 1; } else if (!*ml_comment && (!*in_string && (inchar == '#' || inchar == '-' && pos[1] == '-' && my_isspace(charset_info,pos[2])))) - break; // comment to end of line + { + // Flush previously accepted characters + if (out != line) + { + buffer.append(line, (uint32) (out - line)); + out= line; + } + + // comment to end of line + if (preserve_comments) + buffer.append(pos); + + break; + } else if (!*in_string && inchar == '/' && *(pos+1) == '*' && *(pos+2) != '!') { - pos++; + if (preserve_comments) + { + *out++= *pos++; // copy '/' + *out++= *pos; // copy '*' + } + else + pos++; *ml_comment= 1; if (out != line) { @@ -1408,8 +1478,21 @@ static bool add_line(String &buffer,char *line,char *in_string, } else if (*ml_comment && !ss_comment && inchar == '*' && *(pos + 1) == '/') { - pos++; + if (preserve_comments) + { + *out++= *pos++; // copy '*' + *out++= *pos; // copy '/' + } + else + pos++; *ml_comment= 0; + if (out != line) + { + buffer.append(line, (uint32) (out - line)); + out= line; + } + // Consumed a 2 chars or more, and will add 1 at most, + // so using the 'line' buffer to edit data in place is ok. need_space= 1; } else @@ -1424,14 +1507,12 @@ static bool add_line(String &buffer,char *line,char *in_string, else if (!*ml_comment && !*in_string && (inchar == '\'' || inchar == '"' || inchar == '`')) *in_string= (char) inchar; - if (!*ml_comment) + if (!*ml_comment || preserve_comments) { if (need_space && !my_isspace(charset_info, (char)inchar)) - { *out++= ' '; - need_space= 0; - } - *out++= (char) inchar; + need_space= 0; + *out++= (char) inchar; } } } @@ -1441,7 +1522,7 @@ static bool add_line(String &buffer,char *line,char *in_string, uint length=(uint) (out-line); if (buffer.length() + length >= buffer.alloced_length()) buffer.realloc(buffer.length()+length+IO_SIZE); - if (!(*ml_comment) && buffer.append(line,length)) + if ((!*ml_comment || preserve_comments) && buffer.append(line, length)) DBUG_RETURN(1); } DBUG_RETURN(0); @@ -2366,6 +2447,7 @@ static char *fieldflags2str(uint f) { ff2s_check_flag(GROUP); ff2s_check_flag(UNIQUE); ff2s_check_flag(BINCMP); + ff2s_check_flag(ON_UPDATE_NOW); #undef ff2s_check_flag if (f) sprintf(s, " unknows=0x%04x", f); diff --git a/client/mysqldump.c b/client/mysqldump.c index d504d177490..f5362b272cd 100644 --- a/client/mysqldump.c +++ b/client/mysqldump.c @@ -1040,8 +1040,10 @@ static int fetch_db_collation(const char *db_name, char query[QUERY_LENGTH]; MYSQL_RES *db_cl_res; MYSQL_ROW db_cl_row; + char quoted_database_buf[NAME_LEN*2+3]; + char *qdatabase= quote_name(db_name, quoted_database_buf, 1); - my_snprintf(query, sizeof (query), "use %s", db_name); + my_snprintf(query, sizeof (query), "use %s", qdatabase); if (mysql_query_with_error_report(mysql, NULL, query)) return 1; diff --git a/include/mysql_com.h b/include/mysql_com.h index f76486b9ec2..4ae7f66060f 100644 --- a/include/mysql_com.h +++ b/include/mysql_com.h @@ -94,6 +94,7 @@ enum enum_server_command #define TIMESTAMP_FLAG 1024 /* Field is a timestamp */ #define SET_FLAG 2048 /* field is a set */ #define NO_DEFAULT_VALUE_FLAG 4096 /* Field doesn't have default value */ +#define ON_UPDATE_NOW_FLAG 8192 /* Field is set to NOW on UPDATE */ #define NUM_FLAG 32768 /* Field is num (for clients) */ #define PART_KEY_FLAG 16384 /* Intern; Part of some key */ #define GROUP_FLAG 32768 /* Intern: Group field */ diff --git a/mysql-test/r/almost_full.result b/mysql-test/r/almost_full.result new file mode 100644 index 00000000000..eb28f12fa51 --- /dev/null +++ b/mysql-test/r/almost_full.result @@ -0,0 +1,29 @@ +drop table if exists t1; +set global myisam_data_pointer_size=2; +CREATE TABLE t1 (a int auto_increment primary key not null, b longtext) ENGINE=MyISAM; +DELETE FROM t1 WHERE a=1 or a=5; +INSERT INTO t1 SET b=repeat('a',600); +ERROR HY000: The table 't1' is full +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check warning Datafile is almost full, 65448 of 65534 used +test.t1 check status OK +UPDATE t1 SET b=repeat('a', 800) where a=10; +ERROR HY000: The table 't1' is full +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check warning Datafile is almost full, 65448 of 65534 used +test.t1 check status OK +INSERT INTO t1 SET b=repeat('a',400); +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check warning Datafile is almost full, 65448 of 65534 used +test.t1 check status OK +DELETE FROM t1 WHERE a=2 or a=6; +UPDATE t1 SET b=repeat('a', 600) where a=11; +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check warning Datafile is almost full, 65448 of 65534 used +test.t1 check status OK +drop table t1; +set global myisam_data_pointer_size=default; diff --git a/mysql-test/r/archive.result b/mysql-test/r/archive.result index 0a473a16771..77d3cba63d5 100644 --- a/mysql-test/r/archive.result +++ b/mysql-test/r/archive.result @@ -12683,3 +12683,7 @@ check table t1 extended; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; +CREATE TABLE t1(a VARCHAR(510)) ENGINE = ARCHIVE; +INSERT INTO t1(a) VALUES (''); +SELECT * FROM t1 ORDER BY a; +DROP TABLE t1; diff --git a/mysql-test/r/binlog_unsafe.result b/mysql-test/r/binlog_unsafe.result index 281bb475944..47284ed8bc3 100644 --- a/mysql-test/r/binlog_unsafe.result +++ b/mysql-test/r/binlog_unsafe.result @@ -5,9 +5,9 @@ CREATE TABLE t3 (b INT AUTO_INCREMENT PRIMARY KEY); CREATE VIEW v1(a,b) AS SELECT a,b FROM t2,t3; INSERT INTO t1 SELECT UUID(); Warnings: -Warning 1591 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. SHOW WARNINGS; Level Warning -Code 1591 +Code 1592 Message Statement is not safe to log in statement format. DROP TABLE t1,t2,t3; diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index afd0a5a990e..c1af92c5f8d 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -414,4 +414,28 @@ NULL NULL 20070719 drop table t1; +CREATE TABLE t1 (f1 DATE); +INSERT INTO t1 VALUES ('2007-07-19'), (NULL); +SELECT HOUR(f1), +MINUTE(f1), +SECOND(f1) FROM t1; +HOUR(f1) MINUTE(f1) SECOND(f1) +0 0 0 +NULL NULL NULL +SELECT HOUR(CAST('2007-07-19' AS DATE)), +MINUTE(CAST('2007-07-19' AS DATE)), +SECOND(CAST('2007-07-19' AS DATE)); +HOUR(CAST('2007-07-19' AS DATE)) MINUTE(CAST('2007-07-19' AS DATE)) SECOND(CAST('2007-07-19' AS DATE)) +0 0 0 +SELECT HOUR(CAST(NULL AS DATE)), +MINUTE(CAST(NULL AS DATE)), +SECOND(CAST(NULL AS DATE)); +HOUR(CAST(NULL AS DATE)) MINUTE(CAST(NULL AS DATE)) SECOND(CAST(NULL AS DATE)) +NULL NULL NULL +SELECT HOUR(NULL), +MINUTE(NULL), +SECOND(NULL); +HOUR(NULL) MINUTE(NULL) SECOND(NULL) +NULL NULL NULL +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result index b6b77101874..83609856393 100644 --- a/mysql-test/r/events_bugs.result +++ b/mysql-test/r/events_bugs.result @@ -31,7 +31,7 @@ create event e_55 on schedule at 10000101000000 do drop table t; ERROR HY000: Incorrect AT value: '10000101000000' create event e_55 on schedule at 20000101000000 do drop table t; Warnings: -Note 1587 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. show events; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation create event e_55 on schedule at 20200101000000 starts 10000101000000 do drop table t; @@ -457,22 +457,22 @@ CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' DO SELECT 1; Warnings: -Note 1587 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' DISABLE DO SELECT 1; Warnings: -Note 1587 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DO SELECT 1; Warnings: -Note 1587 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. CREATE EVENT e4 ON SCHEDULE AT '1999-01-01 00:00:00' DISABLE DO SELECT 1; Warnings: -Note 1587 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +Note 1588 Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. SHOW EVENTS; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation events_test e1 root@localhost +05:00 RECURRING NULL 1 DAY 2006-01-01 00:00:00 NULL ENABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci @@ -482,19 +482,19 @@ The following should succeed giving a warning. ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE; Warnings: -Note 1543 Event execution time is in the past. Event has been disabled +Note 1544 Event execution time is in the past. Event has been disabled CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' ON COMPLETION PRESERVE DO SELECT 1; Warnings: -Note 1543 Event execution time is in the past. Event has been disabled +Note 1544 Event execution time is in the past. Event has been disabled CREATE EVENT e5 ON SCHEDULE AT '1999-01-01 00:00:00' ON COMPLETION PRESERVE DO SELECT 1; Warnings: -Note 1543 Event execution time is in the past. Event has been disabled +Note 1544 Event execution time is in the past. Event has been disabled The following should succeed without warnings. ALTER EVENT e2 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00'; ALTER EVENT e3 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' diff --git a/mysql-test/r/events_trans.result b/mysql-test/r/events_trans.result index 984e22a2c1a..16ec64b4c50 100644 --- a/mysql-test/r/events_trans.result +++ b/mysql-test/r/events_trans.result @@ -63,7 +63,7 @@ begin work; insert into t1 (a) values ("OK: create event if not exists"); create event if not exists e1 on schedule every 2 day do select 2; Warnings: -Note 1536 Event 'e1' already exists +Note 1537 Event 'e1' already exists rollback work; select * from t1; a diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result index 7ae7806baea..d257e526abf 100644 --- a/mysql-test/r/func_gconcat.result +++ b/mysql-test/r/func_gconcat.result @@ -867,4 +867,13 @@ select group_concat(distinct a, c order by a desc, c desc) from t1; group_concat(distinct a, c order by a desc, c desc) 31,11,10,01,00 drop table t1; +create table t1 (f1 char(20)); +insert into t1 values (''),(''); +select group_concat(distinct f1) from t1; +group_concat(distinct f1) + +select group_concat(f1) from t1; +group_concat(f1) +, +drop table t1; End of 5.0 tests diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index ce29d25b736..abf717091af 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1378,4 +1378,24 @@ SELECT 1 FROM t1 GROUP BY (SELECT SLEEP(0) FROM t1 ORDER BY AVG(DISTINCT a) ); 1 1 DROP TABLE t1; +CREATE TABLE t1 (a int, b date NOT NULL, KEY k1 (a,b)); +SELECT MIN(b) FROM t1 WHERE a=1 AND b>'2007-08-01'; +MIN(b) +NULL +DROP TABLE t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3),(4); +SET SQL_MODE=ONLY_FULL_GROUP_BY; +SELECT a FROM t1 HAVING COUNT(*)>2; +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SELECT COUNT(*), a FROM t1; +ERROR 42000: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause +SET SQL_MODE=DEFAULT; +SELECT a FROM t1 HAVING COUNT(*)>2; +a +1 +SELECT COUNT(*), a FROM t1; +COUNT(*) a +4 1 +DROP TABLE t1; End of 5.0 tests diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index 78d86d3f342..6f6edd5112b 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -722,9 +722,9 @@ Warning 1265 Data truncated for column 'format(130,10)' at row 1 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `bin(130)` varchar(64) NOT NULL DEFAULT '', - `oct(130)` varchar(64) NOT NULL DEFAULT '', - `conv(130,16,10)` varchar(64) NOT NULL DEFAULT '', + `bin(130)` varchar(64) DEFAULT NULL, + `oct(130)` varchar(64) DEFAULT NULL, + `conv(130,16,10)` varchar(64) DEFAULT NULL, `hex(130)` varchar(6) NOT NULL DEFAULT '', `char(130)` varbinary(4) NOT NULL DEFAULT '', `format(130,10)` varchar(4) NOT NULL DEFAULT '', @@ -1316,6 +1316,18 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 select decode('',_latin1'zxcv') AS `enc` from `test`.`t1` drop table t1; +create table t1 (a bigint not null)engine=myisam; +insert into t1 set a = 1024*1024*1024*4; +delete from t1 order by (inet_ntoa(a)) desc limit 10; +drop table t1; +create table t1 (a char(36) not null)engine=myisam; +insert ignore into t1 set a = ' '; +insert ignore into t1 set a = ' '; +select * from t1 order by (oct(a)); +a + + +drop table t1; End of 4.1 tests create table t1 (d decimal default null); insert into t1 values (null); diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 4e25ada43a0..4d402d7d0e9 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -529,7 +529,7 @@ Db char(64) NO PRI User char(16) NO PRI Table_name char(64) NO PRI Grantor char(77) NO MUL -Timestamp timestamp NO CURRENT_TIMESTAMP +Timestamp timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') NO Column_priv set('Select','Insert','Update','References') NO use test; diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 7498267c91d..1472fdfdd7a 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -1064,6 +1064,71 @@ select t1.f1,t.* from t1, t1 t group by 1; ERROR 42000: 'test.t.f1' isn't in GROUP BY drop table t1; SET SQL_MODE = ''; +CREATE TABLE t1( +id INT AUTO_INCREMENT PRIMARY KEY, +c1 INT NOT NULL, +c2 INT NOT NULL, +UNIQUE KEY (c2,c1)); +INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3); +SELECT * FROM t1 ORDER BY c1; +id c1 c2 +5 1 3 +4 2 3 +3 3 5 +2 4 1 +1 5 1 +SELECT * FROM t1 GROUP BY id ORDER BY c1; +id c1 c2 +5 1 3 +4 2 3 +3 3 5 +2 4 1 +1 5 1 +SELECT * FROM t1 GROUP BY id ORDER BY id DESC; +id c1 c2 +5 1 3 +4 2 3 +3 3 5 +2 4 1 +1 5 1 +SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1; +id c1 c2 +2 4 1 +1 5 1 +5 1 3 +4 2 3 +3 3 5 +SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1; +id c1 c2 +3 3 5 +5 1 3 +4 2 3 +2 4 1 +1 5 1 +SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC; +id c1 c2 +3 3 5 +4 2 3 +5 1 3 +1 5 1 +2 4 1 +SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1; +id c1 c2 +1 5 1 +4 2 3 +3 3 5 +SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1; +id c1 c2 +3 3 5 +4 2 3 +1 5 1 +SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC; +id c1 c2 +3 3 5 +4 2 3 +1 5 1 +DROP TABLE t1; +End of 5.0 tests CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), KEY i2(a,b)); diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 7693fe628ef..4df3b56da86 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1141,11 +1141,13 @@ DROP FUNCTION func2; select column_type, group_concat(table_schema, '.', table_name), count(*) as num from information_schema.columns where table_schema='information_schema' and -(column_type = 'varchar(7)' or column_type = 'varchar(20)') +(column_type = 'varchar(7)' or column_type = 'varchar(20)' + or column_type = 'varchar(27)') group by column_type order by num; column_type group_concat(table_schema, '.', table_name) num +varchar(27) information_schema.COLUMNS 1 varchar(7) information_schema.ROUTINES,information_schema.VIEWS 2 -varchar(20) information_schema.COLUMNS,information_schema.FILES,information_schema.FILES,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PLUGINS 6 +varchar(20) information_schema.FILES,information_schema.FILES,information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PLUGINS 5 create table t1(f1 char(1) not null, f2 char(9) not null) default character set utf8; select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from @@ -1604,4 +1606,9 @@ 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 +explain extended select 1 from information_schema.tables; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE tables ALL NULL NULL NULL NULL NULL NULL Skip_open_table; Scanned all databases +Warnings: +Note 1003 select 1 AS `1` from `information_schema`.`tables` End of 5.1 tests. diff --git a/mysql-test/r/information_schema_db.result b/mysql-test/r/information_schema_db.result index 51f7694ab68..2da4e305b01 100644 --- a/mysql-test/r/information_schema_db.result +++ b/mysql-test/r/information_schema_db.result @@ -141,7 +141,7 @@ Warnings: Warning 1356 View 'testdb_1.v7' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them show fields from testdb_1.v7; Field Type Null Key Default Extra -f1 null YES NULL +f1 char(4) YES NULL Warnings: Note 1449 There is no 'no_such_user'@'no_such_host' registered create table t3 (f1 char(4), f2 char(4)); @@ -161,7 +161,7 @@ View Create View character_set_client collation_connection v6 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testdb_1`.`v6` AS select `testdb_1`.`t1`.`f1` AS `f1` from `testdb_1`.`t1` latin1 latin1_swedish_ci show fields from testdb_1.v7; Field Type Null Key Default Extra -f1 null YES NULL +f1 char(4) YES NULL Warnings: Note 1449 There is no 'no_such_user'@'no_such_host' registered show create view testdb_1.v7; @@ -189,7 +189,7 @@ show create view v4; ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table show fields from v4; Field Type Null Key Default Extra -f1 null YES NULL +f1 char(4) YES NULL f2 char(4) YES NULL show fields from v2; Field Type Null Key Default Extra diff --git a/mysql-test/r/log_state.result b/mysql-test/r/log_state.result index 3a3ef584ce3..b0ec0d25935 100644 --- a/mysql-test/r/log_state.result +++ b/mysql-test/r/log_state.result @@ -175,3 +175,66 @@ SET GLOBAL slow_query_log = ON; SET GLOBAL READ_ONLY = OFF; SET GLOBAL general_log = @old_general_log_state; SET GLOBAL slow_query_log = @old_slow_log_state; +SET @old_general_log_state = @@global.general_log; +SET @old_slow_log_state = @@global.slow_query_log; +SHOW VARIABLES LIKE 'general_log'; +Variable_name Value +general_log ON +SHOW VARIABLES LIKE 'log'; +Variable_name Value +log ON +SELECT @@general_log, @@log; +@@general_log @@log +1 1 +SET GLOBAL log = 0; +SHOW VARIABLES LIKE 'general_log'; +Variable_name Value +general_log OFF +SHOW VARIABLES LIKE 'log'; +Variable_name Value +log OFF +SELECT @@general_log, @@log; +@@general_log @@log +0 0 +SET GLOBAL general_log = 1; +SHOW VARIABLES LIKE 'general_log'; +Variable_name Value +general_log ON +SHOW VARIABLES LIKE 'log'; +Variable_name Value +log ON +SELECT @@general_log, @@log; +@@general_log @@log +1 1 +SHOW VARIABLES LIKE 'slow_query_log'; +Variable_name Value +slow_query_log OFF +SHOW VARIABLES LIKE 'log_slow_queries'; +Variable_name Value +log_slow_queries OFF +SELECT @@slow_query_log, @@log_slow_queries; +@@slow_query_log @@log_slow_queries +0 0 +SET GLOBAL log_slow_queries = 0; +SHOW VARIABLES LIKE 'slow_query_log'; +Variable_name Value +slow_query_log OFF +SHOW VARIABLES LIKE 'log_slow_queries'; +Variable_name Value +log_slow_queries OFF +SELECT @@slow_query_log, @@log_slow_queries; +@@slow_query_log @@log_slow_queries +0 0 +SET GLOBAL slow_query_log = 1; +SHOW VARIABLES LIKE 'slow_query_log'; +Variable_name Value +slow_query_log ON +SHOW VARIABLES LIKE 'log_slow_queries'; +Variable_name Value +log_slow_queries ON +SELECT @@slow_query_log, @@log_slow_queries; +@@slow_query_log @@log_slow_queries +1 1 +SET GLOBAL general_log = @old_general_log_state; +SET GLOBAL slow_query_log = @old_slow_log_state; +End of 5.1 tests diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result index 261e3292f4d..f3578598195 100644 --- a/mysql-test/r/log_tables.result +++ b/mysql-test/r/log_tables.result @@ -50,7 +50,7 @@ general_log CREATE TABLE `general_log` ( ) 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 +event_time timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP user_host mediumtext NO NULL thread_id int(11) NO NULL server_id int(11) NO NULL @@ -73,7 +73,7 @@ slow_log CREATE TABLE `slow_log` ( ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' show fields from mysql.slow_log; Field Type Null Key Default Extra -start_time timestamp NO CURRENT_TIMESTAMP +start_time timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP user_host mediumtext NO NULL query_time time NO NULL lock_time time NO NULL @@ -820,3 +820,31 @@ Execute select '000 001 002 003 004 005 006 007 008 009010 011 012 013 014 015 0 Query set global general_log = off deallocate prepare long_query; set global general_log = @old_general_log_state; +SET @old_slow_log_state = @@global.slow_query_log; +SET SESSION long_query_time = 0; +SET GLOBAL slow_query_log = ON; +FLUSH LOGS; +TRUNCATE TABLE mysql.slow_log; +CREATE TABLE t1 (f1 SERIAL,f2 INT, f3 INT, PRIMARY KEY(f1), KEY(f2)); +INSERT INTO t1 VALUES (1,1,1); +INSERT INTO t1 VALUES (2,2,2); +INSERT INTO t1 VALUES (3,3,3); +INSERT INTO t1 VALUES (4,4,4); +SELECT SQL_NO_CACHE 'Bug#31700 - SCAN',f1,f2,f3 FROM t1 WHERE f3=4; +Bug#31700 - SCAN f1 f2 f3 +Bug#31700 - SCAN 4 4 4 +SELECT SQL_NO_CACHE 'Bug#31700 - KEY', f1,f2,f3 FROM t1 WHERE f2=3; +Bug#31700 - KEY f1 f2 f3 +Bug#31700 - KEY 3 3 3 +SELECT SQL_NO_CACHE 'Bug#31700 - PK', f1,f2,f3 FROM t1 WHERE f1=2; +Bug#31700 - PK f1 f2 f3 +Bug#31700 - PK 2 2 2 +SELECT start_time, rows_examined, rows_sent, sql_text FROM mysql.slow_log WHERE sql_text LIKE '%Bug#31700%' ORDER BY start_time; +start_time rows_examined rows_sent sql_text +TIMESTAMP 4 1 SELECT SQL_NO_CACHE 'Bug#31700 - SCAN',f1,f2,f3 FROM t1 WHERE f3=4 +TIMESTAMP 1 1 SELECT SQL_NO_CACHE 'Bug#31700 - KEY', f1,f2,f3 FROM t1 WHERE f2=3 +TIMESTAMP 1 1 SELECT SQL_NO_CACHE 'Bug#31700 - PK', f1,f2,f3 FROM t1 WHERE f1=2 +DROP TABLE t1; +TRUNCATE TABLE mysql.slow_log; +SET GLOBAL slow_query_log = @old_slow_log_state; +SET SESSION long_query_time =@old_long_query_time; diff --git a/mysql-test/r/lowercase_view.result b/mysql-test/r/lowercase_view.result index 3653461c6b8..c37dc41c495 100644 --- a/mysql-test/r/lowercase_view.result +++ b/mysql-test/r/lowercase_view.result @@ -119,7 +119,7 @@ create table t1Aa (col1 int); create view v1Aa as select col1 from t1Aa as AaA; show create view v1AA; View Create View character_set_client collation_connection -v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `AaA` latin1 latin1_swedish_ci +v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `aaa` latin1 latin1_swedish_ci drop view v1AA; select Aaa.col1 from t1Aa as AaA; col1 @@ -128,6 +128,23 @@ drop view v1AA; create view v1Aa as select AaA.col1 from t1Aa as AaA; show create view v1AA; View Create View character_set_client collation_connection -v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `AaA` latin1 latin1_swedish_ci +v1aa CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1aa` AS select `aaa`.`col1` AS `col1` from `t1aa` `aaa` latin1 latin1_swedish_ci drop view v1AA; drop table t1Aa; +CREATE TABLE t1 (a int, b int); +select X.a from t1 AS X group by X.b having (X.a = 1); +a +select X.a from t1 AS X group by X.b having (x.a = 1); +a +select X.a from t1 AS X group by X.b having (x.b = 1); +a +CREATE OR REPLACE VIEW v1 AS +select X.a from t1 AS X group by X.b having (X.a = 1); +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 `x`.`a` AS `a` from `t1` `x` group by `x`.`b` having (`x`.`a` = 1) latin1 latin1_swedish_ci +SELECT * FROM v1; +a +DROP VIEW v1; +DROP TABLE t1; +End of 5.0 tests. diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result index 4a776b6a253..76c6ae7d034 100644 --- a/mysql-test/r/metadata.result +++ b/mysql-test/r/metadata.result @@ -21,7 +21,7 @@ def test t1 t1 g g 5 4 0 Y 32768 3 63 def test t1 t1 h h 246 7 0 Y 0 4 63 def test t1 t1 i i 13 4 0 Y 32864 0 63 def test t1 t1 j j 10 10 0 Y 128 0 63 -def test t1 t1 k k 7 19 0 N 1249 0 63 +def test t1 t1 k k 7 19 0 N 9441 0 63 def test t1 t1 l l 12 19 0 Y 128 0 63 def test t1 t1 m m 254 1 0 Y 256 0 8 def test t1 t1 n n 254 3 0 Y 2048 0 8 diff --git a/mysql-test/r/mysql_comments.result b/mysql-test/r/mysql_comments.result new file mode 100644 index 00000000000..7f1c0b50c5e --- /dev/null +++ b/mysql-test/r/mysql_comments.result @@ -0,0 +1,62 @@ +drop table if exists t1; +drop function if exists foofct; +drop procedure if exists empty; +drop procedure if exists foosp; +drop procedure if exists nicesp; +drop trigger if exists t1_empty; +drop trigger if exists t1_bi; +"Pass 1 : --disable-comments" +1 +1 +2 +2 +foofct("call 1") +call 1 +Function sql_mode Create Function character_set_client collation_connection Database Collation +foofct CREATE DEFINER=`root`@`localhost` FUNCTION `foofct`(x char(20)) RETURNS char(20) CHARSET latin1\nreturn\n\n\n\nx latin1 latin1_swedish_ci latin1_swedish_ci +foofct("call 2") +call 2 +Function sql_mode Create Function character_set_client collation_connection Database Collation +foofct CREATE DEFINER=`root`@`localhost` FUNCTION `foofct`(x char(20)) RETURNS char(20) CHARSET latin1\nbegin\n \n \n \n\n \n\n \n return x;\nend latin1 latin1_swedish_ci latin1_swedish_ci +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +empty CREATE DEFINER=`root`@`localhost` PROCEDURE `empty`()\nbegin\nend latin1 latin1_swedish_ci latin1_swedish_ci +id data +foo 42 +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +foosp CREATE DEFINER=`root`@`localhost` PROCEDURE `foosp`()\ninsert into test.t1\n\n\n\n\n \n\n \n values ("foo", 42) latin1 latin1_swedish_ci latin1_swedish_ci +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +nicesp CREATE DEFINER=`root`@`localhost` PROCEDURE `nicesp`(a int)\nbegin\n \n declare b int;\n declare c float;\n\n \n \n\n \nend latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +t1_empty CREATE DEFINER=`root`@`localhost` trigger t1_empty after delete on t1\nfor each row\nbegin\nend latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +t1_bi CREATE DEFINER=`root`@`localhost` trigger t1_bi before insert on t1\nfor each row\nbegin\n\n\n\n \n declare b int;\n declare c float;\n\n \n \n\n \n set NEW.data := 12;\nend latin1 latin1_swedish_ci latin1_swedish_ci +id data +trig 12 +"Pass 2 : --enable-comments" +1 +1 +2 +2 +foofct("call 1") +call 1 +Function sql_mode Create Function character_set_client collation_connection Database Collation +foofct CREATE DEFINER=`root`@`localhost` FUNCTION `foofct`(x char(20)) RETURNS char(20) CHARSET latin1\nreturn\n-- comment 1a\n# comment 1b\n/* comment 1c */\nx # after body, on same line latin1 latin1_swedish_ci latin1_swedish_ci +foofct("call 2") +call 2 +Function sql_mode Create Function character_set_client collation_connection Database Collation +foofct CREATE DEFINER=`root`@`localhost` FUNCTION `foofct`(x char(20)) RETURNS char(20) CHARSET latin1\nbegin\n -- comment 1a\n # comment 1b\n /*\n comment 1c\n */\n\n -- empty line below\n\n -- empty line above\n return x;\nend latin1 latin1_swedish_ci latin1_swedish_ci +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +empty CREATE DEFINER=`root`@`localhost` PROCEDURE `empty`()\nbegin\nend latin1 latin1_swedish_ci latin1_swedish_ci +id data +foo 42 +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +foosp CREATE DEFINER=`root`@`localhost` PROCEDURE `foosp`()\ninsert into test.t1\n## These comments are part of the procedure body, and should be kept.\n# Comment 2a\n-- Comment 2b\n/* Comment 2c */\n -- empty line below\n\n -- empty line above\n values ("foo", 42) # comment 3, still part of the body latin1 latin1_swedish_ci latin1_swedish_ci +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +nicesp CREATE DEFINER=`root`@`localhost` PROCEDURE `nicesp`(a int)\nbegin\n -- declare some variables here\n declare b int;\n declare c float;\n\n -- do more stuff here\n -- commented nicely and so on\n\n -- famous last words ...\nend latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +t1_empty CREATE DEFINER=`root`@`localhost` trigger t1_empty after delete on t1\nfor each row\nbegin\nend latin1 latin1_swedish_ci latin1_swedish_ci +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +t1_bi CREATE DEFINER=`root`@`localhost` trigger t1_bi before insert on t1\nfor each row\nbegin\n# comment 1a\n-- comment 1b\n/*\n comment 1c\n*/\n -- declare some variables here\n declare b int;\n declare c float;\n\n -- do more stuff here\n -- commented nicely and so on\n\n -- famous last words ...\n set NEW.data := 12;\nend latin1 latin1_swedish_ci latin1_swedish_ci +id data +trig 12 +End of 5.0 tests diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index c0856314ba5..d07aed5317a 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -4212,5 +4212,24 @@ TRUNCATE mysql.event; SHOW EVENTS; Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation # +# Bug#31113 mysqldump 5.1 can't handle a dash ("-") in database names +# +create database `test-database`; +use `test-database`; +create table test (a int); +DROP TABLE IF EXISTS `test`; +SET @saved_cs_client = @@character_set_client; +SET character_set_client = utf8; +CREATE TABLE `test` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +SET character_set_client = @saved_cs_client; +LOCK TABLES `test` WRITE; +/*!40000 ALTER TABLE `test` DISABLE KEYS */; +/*!40000 ALTER TABLE `test` ENABLE KEYS */; +UNLOCK TABLES; +drop database `test-database`; +use test; +# # End of 5.1 tests # diff --git a/mysql-test/r/outfile_loaddata.result b/mysql-test/r/outfile_loaddata.result new file mode 100644 index 00000000000..1bcaf308b7c --- /dev/null +++ b/mysql-test/r/outfile_loaddata.result @@ -0,0 +1,85 @@ +DROP TABLE IF EXISTS t1, t2; +# +# Bug#31663 FIELDS TERMINATED BY special character +# +CREATE TABLE t1 (i1 int, i2 int, c1 VARCHAR(256), c2 VARCHAR(256)); +INSERT INTO t1 VALUES (101, 202, '-r-', '=raker='); +# FIELDS TERMINATED BY 'raker', warning: +SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS TERMINATED BY 'raker' FROM t1; +Warnings: +Warning 1475 First character of the FIELDS TERMINATED string is ambiguous; please use non-optional and non-empty FIELDS ENCLOSED BY +SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt'); +LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt') +101raker202raker-r-raker=raker= + +CREATE TABLE t2 SELECT * FROM t1; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS TERMINATED BY 'raker'; +Warnings: +Warning 1262 Row 1 was truncated; it contained more data than there were input columns +SELECT * FROM t2; +i1 i2 c1 c2 +101 202 -r- =raker= +101 202 -r- = +DROP TABLE t2; +# Only numeric fields, FIELDS TERMINATED BY 'r', no warnings: +SELECT i1, i2 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS TERMINATED BY 'r' FROM t1; +SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt'); +LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt') +101r202 + +CREATE TABLE t2 SELECT i1, i2 FROM t1; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS TERMINATED BY 'r'; +SELECT i1, i2 FROM t2; +i1 i2 +101 202 +101 202 +DROP TABLE t2; +# FIELDS TERMINATED BY '0', warning: +SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS TERMINATED BY '0' FROM t1; +Warnings: +Warning 1475 First character of the FIELDS TERMINATED string is ambiguous; please use non-optional and non-empty FIELDS ENCLOSED BY +SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt'); +LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt') +10102020-r-0=raker= + +CREATE TABLE t2 SELECT * FROM t1; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS TERMINATED BY '0'; +Warnings: +Warning 1262 Row 1 was truncated; it contained more data than there were input columns +SELECT * FROM t2; +i1 i2 c1 c2 +101 202 -r- =raker= +1 1 2 2 +DROP TABLE t2; +# FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0', warning: +SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0' FROM t1; +Warnings: +Warning 1475 First character of the FIELDS TERMINATED string is ambiguous; please use non-optional and non-empty FIELDS ENCLOSED BY +SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt'); +LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt') +10102020"-r-"0"=raker=" + +CREATE TABLE t2 SELECT * FROM t1; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0'; +Warnings: +Warning 1262 Row 1 was truncated; it contained more data than there were input columns +SELECT * FROM t2; +i1 i2 c1 c2 +101 202 -r- =raker= +1 1 2 2 +DROP TABLE t2; +# Only string fields, FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0', no warnings: +SELECT c1, c2 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0' FROM t1; +SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt'); +LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt') +"-r-"0"=raker=" + +CREATE TABLE t2 SELECT c1, c2 FROM t1; +LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0'; +SELECT c1, c2 FROM t2; +c1 c2 +-r- =raker= +-r- =raker= +DROP TABLE t2; +DROP TABLE t1; +# End of 5.0 tests. diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index aa6a64ac76e..d646226daf8 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -1285,4 +1285,9 @@ t1 CREATE TABLE `t1` ( `b` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (b) (PARTITION p1 VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (20) ENGINE = MyISAM) */ drop table t1, t2; +create table t1 +(s1 timestamp on update current_timestamp, s2 int) +partition by key(s1) partitions 3; +insert into t1 values (null,null); +drop table t1; End of 5.1 tests diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index 5b755b6bfd5..f80e0001ea8 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -136,3 +136,16 @@ SELECT COUNT(*) FROM t1; COUNT(*) 2 DROP TABLE t1; +create table t1 (int_column int, char_column char(5)) +PARTITION BY RANGE (int_column) subpartition by key (char_column) subpartitions 2 +(PARTITION p1 VALUES LESS THAN (5) ENGINE = InnoDB); +alter table t1 PARTITION BY RANGE (int_column) +subpartition by key (char_column) subpartitions 2 +(PARTITION p1 VALUES LESS THAN (5) ENGINE = myisam); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `int_column` int(11) DEFAULT NULL, + `char_column` char(5) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (int_column) SUBPARTITION BY KEY (char_column) SUBPARTITIONS 2 (PARTITION p1 VALUES LESS THAN (5) ENGINE = MyISAM) */ +drop table t1; diff --git a/mysql-test/r/plugin.result b/mysql-test/r/plugin.result index 8628acecf55..e4af1535775 100644 --- a/mysql-test/r/plugin.result +++ b/mysql-test/r/plugin.result @@ -17,3 +17,13 @@ UNINSTALL PLUGIN EXAMPLE; ERROR 42000: PLUGIN EXAMPLE does not exist UNINSTALL PLUGIN non_exist; ERROR 42000: PLUGIN non_exist does not exist +# +# Bug#32034: check_func_enum() does not check correct values but set it +# to impossible int val +# +INSTALL PLUGIN example SONAME 'ha_example.so'; +SET GLOBAL example_enum_var= e1; +SET GLOBAL example_enum_var= e2; +SET GLOBAL example_enum_var= impossible; +ERROR 42000: Variable 'enum_var' can't be set to the value of 'impossible' +UNINSTALL PLUGIN example; diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result index 6f0bad71f23..eb8ef7a85c3 100644 --- a/mysql-test/r/ps_2myisam.result +++ b/mysql-test/r/ps_2myisam.result @@ -63,7 +63,7 @@ def test t9 t9 c11 c11 246 9 6 Y 0 4 63 def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 -def test t9 t9 c15 c15 7 19 19 N 1249 0 63 +def test t9 t9 c15 c15 7 19 19 N 9441 0 63 def test t9 t9 c16 c16 11 8 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result index 85a109b0630..15650efa41d 100644 --- a/mysql-test/r/ps_3innodb.result +++ b/mysql-test/r/ps_3innodb.result @@ -63,7 +63,7 @@ def test t9 t9 c11 c11 246 9 6 Y 0 4 63 def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 -def test t9 t9 c15 c15 7 19 19 N 1249 0 63 +def test t9 t9 c15 c15 7 19 19 N 9441 0 63 def test t9 t9 c16 c16 11 8 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result index 980b89ad504..486f770220e 100644 --- a/mysql-test/r/ps_4heap.result +++ b/mysql-test/r/ps_4heap.result @@ -64,7 +64,7 @@ def test t9 t9 c11 c11 246 9 6 Y 0 4 63 def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 -def test t9 t9 c15 c15 7 19 19 N 1249 0 63 +def test t9 t9 c15 c15 7 19 19 N 9441 0 63 def test t9 t9 c16 c16 11 8 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result index e95f8766a24..0229b0ece08 100644 --- a/mysql-test/r/ps_5merge.result +++ b/mysql-test/r/ps_5merge.result @@ -106,7 +106,7 @@ def test t9 t9 c11 c11 246 9 6 Y 0 4 63 def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 -def test t9 t9 c15 c15 7 19 19 N 1249 0 63 +def test t9 t9 c15 c15 7 19 19 N 9441 0 63 def test t9 t9 c16 c16 11 8 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 @@ -3128,7 +3128,7 @@ def test t9 t9 c11 c11 246 9 6 Y 0 4 63 def test t9 t9 c12 c12 246 10 6 Y 0 4 63 def test t9 t9 c13 c13 10 10 10 Y 128 0 63 def test t9 t9 c14 c14 12 19 19 Y 128 0 63 -def test t9 t9 c15 c15 7 19 19 N 1249 0 63 +def test t9 t9 c15 c15 7 19 19 N 9441 0 63 def test t9 t9 c16 c16 11 8 8 Y 128 0 63 def test t9 t9 c17 c17 13 4 4 Y 32864 0 63 def test t9 t9 c18 c18 1 4 1 Y 32768 0 63 diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 1acb82e4137..719d0926022 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2827,6 +2827,14 @@ FFFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF 8FFFFFFFFFFFFFFF 7FFFFFFFFFFFFFFF drop table t1; +CREATE TABLE t1 (c0 int); +CREATE TABLE t2 (c0 int); +INSERT INTO t1 VALUES(@@connect_timeout); +INSERT INTO t2 VALUES(@@connect_timeout); +SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@connect_timeout); +c0 c0 +X X +DROP TABLE t1, t2; End of 4.1 tests CREATE TABLE t1 ( K2C4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '', @@ -3233,40 +3241,40 @@ drop table t1, t2 ,t3; create table t1(f1 int, f2 date); insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'), (4,'2005-10-01'),(5,'2005-12-30'); -select * from t1 where f2 >= 0; +select * from t1 where f2 >= 0 order by f2; f1 f2 1 2005-01-01 2 2005-09-01 3 2005-09-30 4 2005-10-01 5 2005-12-30 -select * from t1 where f2 >= '0000-00-00'; +select * from t1 where f2 >= '0000-00-00' order by f2; f1 f2 1 2005-01-01 2 2005-09-01 3 2005-09-30 4 2005-10-01 5 2005-12-30 -select * from t1 where f2 >= '2005-09-31'; +select * from t1 where f2 >= '2005-09-31' order by f2; f1 f2 4 2005-10-01 5 2005-12-30 -select * from t1 where f2 >= '2005-09-3a'; +select * from t1 where f2 >= '2005-09-3a' order by f2; f1 f2 +3 2005-09-30 4 2005-10-01 5 2005-12-30 Warnings: Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1 -select * from t1 where f2 <= '2005-09-31'; +select * from t1 where f2 <= '2005-09-31' order by f2; f1 f2 1 2005-01-01 2 2005-09-01 3 2005-09-30 -select * from t1 where f2 <= '2005-09-3a'; +select * from t1 where f2 <= '2005-09-3a' order by f2; f1 f2 1 2005-01-01 2 2005-09-01 -3 2005-09-30 Warnings: Warning 1292 Incorrect date value: '2005-09-3a' for column 'f2' at row 1 drop table t1; @@ -4062,25 +4070,208 @@ x 1 Warnings: Warning 1466 Leading spaces are removed from name ' x' +CREATE VIEW v1 AS SELECT 1 AS ``; +ERROR 42000: Incorrect column name '' CREATE VIEW v1 AS SELECT 1 AS ` `; +ERROR 42000: Incorrect column name ' ' +CREATE VIEW v1 AS SELECT 1 AS ` `; +ERROR 42000: Incorrect column name ' ' +CREATE VIEW v1 AS SELECT (SELECT 1 AS ` `); +ERROR 42000: Incorrect column name ' ' +CREATE VIEW v1 AS SELECT 1 AS ` x`; Warnings: -Warning 1474 Name ' ' has become '' -SELECT `` FROM v1; - +Warning 1466 Leading spaces are removed from name ' x' +SELECT `x` FROM v1; +x +1 +ALTER VIEW v1 AS SELECT 1 AS ` `; +ERROR 42000: Incorrect column name ' ' +DROP VIEW v1; +select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' + and '2007/10/20 00:00:00 GMT'; +str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' + and '2007/10/20 00:00:00 GMT' 1 -CREATE VIEW v2 AS SELECT 1 AS ` `; Warnings: -Warning 1474 Name ' ' has become '' -SELECT `` FROM v2; - +Warning 1292 Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT' +Warning 1292 Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT' +select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'; +str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6' 1 -CREATE VIEW v3 AS SELECT 1 AS ` x`; Warnings: -Warning 1466 Leading spaces are removed from name ' x' -SELECT `x` FROM v3; -x +Warning 1292 Truncated incorrect date value: '2007/10/01 00:00:00 GMT-6' +select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'; +str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6' +1 +Warnings: +Warning 1292 Truncated incorrect date value: '2007/10/2000:00:00 GMT-6' +select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'; +str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6' +1 +Warnings: +Warning 1292 Truncated incorrect date value: '2007-10-1 00:00:00 GMT-6' +select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'; +str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6' +1 +Warnings: +Warning 1292 Truncated incorrect date value: '2007-10-01 x00:00:00 GMT-6' +select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6'; +str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6' +1 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:00:00 GMT-6' +select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6'; +str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6' +1 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:x00:00 GMT-6' +select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6'; +str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6' +1 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2007-10-01 x12:34:56 GMT-6' +select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; +str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6' +1 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6' +select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; +str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6' +0 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6' +select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56'; +str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56' +1 +select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00'; +str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00' +0 +select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00'; +str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00' +1 +select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00'; +str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00' +1 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34' +select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34'; +str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34' +1 +select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; +str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' +1 +select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' + and '2007/10/20 00:00:00'; +str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' + and '2007/10/20 00:00:00' +1 +set SQL_MODE=TRADITIONAL; +select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; +str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' +0 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34' +select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; +str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' +0 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34' +select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; +str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34' +0 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2007-10-00 12:34:00' +select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' + and '2007/10/20'; +str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' + and '2007/10/20' +0 +Warnings: +Warning 1292 Incorrect datetime value: '2007-10-00' for column '2007/09/01' at row 1 +Warning 1292 Incorrect datetime value: '2007-10-00' for column '2007/10/20' at row 1 +set SQL_MODE=DEFAULT; +select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'; +str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20' +1 +Warnings: +Warning 1292 Truncated incorrect datetime value: '' +select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20'; +str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20' +0 +select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; +str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34' +0 +select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34'; +str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34' +NULL +select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = ''; +str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '' +0 +Warnings: +Warning 1292 Truncated incorrect datetime value: '' +select str_to_date('1','%Y-%m-%d') = '1'; +str_to_date('1','%Y-%m-%d') = '1' +0 +Warnings: +Warning 1292 Truncated incorrect date value: '1' +select str_to_date('1','%Y-%m-%d') = '1'; +str_to_date('1','%Y-%m-%d') = '1' +0 +Warnings: +Warning 1292 Truncated incorrect date value: '1' +select str_to_date('','%Y-%m-%d') = ''; +str_to_date('','%Y-%m-%d') = '' +0 +Warnings: +Warning 1292 Truncated incorrect date value: '' +select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL; +str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL +0 +select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00'; +str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00' +0 +select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL; +str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL +0 +CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL, +c22 INT DEFAULT NULL, +KEY(c21, c22)); +CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0, +c32 INT DEFAULT NULL, +c33 INT NOT NULL, +c34 INT UNSIGNED DEFAULT 0, +KEY (c33, c34, c32)); +INSERT INTO t1 values (),(),(),(),(); +INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b; +INSERT INTO t3 VALUES (1, 1, 1, 0), +(2, 2, 0, 0), +(3, 3, 1, 0), +(4, 4, 0, 0), +(5, 5, 1, 0); +SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND +t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND +t3.c33 = 1 AND t2.c22 in (1, 3) +ORDER BY c32; +c32 +1 +1 +3 +3 +5 +5 +SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND +t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND +t3.c33 = 1 AND t2.c22 in (1, 3) +ORDER BY c32 DESC; +c32 +5 +5 +3 +3 +1 1 -DROP VIEW v1, v2, v3; +DROP TABLE t1, t2, t3; # # Bug#30736: Row Size Too Large Error Creating a Table and diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index 04a5253e6a8..ff32d2a1512 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -979,7 +979,7 @@ def COLUMNS CHARACTER_SET_NAME CHARACTER_SET_NAME 253 192 0 Y 0 0 33 def COLUMNS COLLATION_NAME COLLATION_NAME 253 192 0 Y 0 0 33 def COLUMNS COLUMN_TYPE COLUMN_TYPE 252 589815 7 N 17 0 33 def COLUMNS COLUMN_KEY COLUMN_KEY 253 9 3 N 1 0 33 -def COLUMNS EXTRA EXTRA 253 60 0 N 1 0 33 +def COLUMNS EXTRA EXTRA 253 81 0 N 1 0 33 def COLUMNS PRIVILEGES PRIVILEGES 253 240 31 N 1 0 33 def COLUMNS COLUMN_COMMENT COLUMN_COMMENT 253 765 0 N 1 0 33 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT @@ -998,7 +998,7 @@ def COLUMNS COLUMN_TYPE Type 252 589815 7 N 17 0 33 def COLUMNS IS_NULLABLE Null 253 9 2 N 1 0 33 def COLUMNS COLUMN_KEY Key 253 9 3 N 1 0 33 def COLUMNS COLUMN_DEFAULT Default 252 589815 0 Y 16 0 33 -def COLUMNS EXTRA Extra 253 60 0 N 1 0 33 +def COLUMNS EXTRA Extra 253 81 0 N 1 0 33 Field Type Null Key Default Extra c int(11) NO PRI NULL ---------------------------------------------------------------- diff --git a/mysql-test/r/skip_grants.result b/mysql-test/r/skip_grants.result index 6bb8cbea2f1..cd652c03f96 100644 --- a/mysql-test/r/skip_grants.result +++ b/mysql-test/r/skip_grants.result @@ -72,3 +72,8 @@ count(*) select count(*) from information_schema.USER_PRIVILEGES; count(*) 0 +CREATE FUNCTION a RETURNS STRING SONAME ''; +ERROR HY000: Can't initialize function 'a'; UDFs are unavailable with the --skip-grant-tables option +DROP FUNCTION a; +ERROR 42000: FUNCTION test.a does not exist +End of 5.0 tests diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 412f6b94fa2..68aa278585f 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -5670,7 +5670,7 @@ drop function if exists pi; create function pi() returns varchar(50) return "pie, my favorite desert."; Warnings: -Note 1584 This function 'pi' has the same name as a native function +Note 1585 This function 'pi' has the same name as a native function SET @save_sql_mode=@@sql_mode; SET SQL_MODE='IGNORE_SPACE'; select pi(), pi (); @@ -5719,15 +5719,15 @@ use test; create function `database`() returns varchar(50) return "Stored function database"; Warnings: -Note 1584 This function 'database' has the same name as a native function +Note 1585 This function 'database' has the same name as a native function create function `current_user`() returns varchar(50) return "Stored function current_user"; Warnings: -Note 1584 This function 'current_user' has the same name as a native function +Note 1585 This function 'current_user' has the same name as a native function create function md5(x varchar(50)) returns varchar(50) return "Stored function md5"; Warnings: -Note 1584 This function 'md5' has the same name as a native function +Note 1585 This function 'md5' has the same name as a native function SET SQL_MODE='IGNORE_SPACE'; select database(), database (); database() database () diff --git a/mysql-test/r/sp_gis.result b/mysql-test/r/sp_gis.result index b0960dec647..7a76507754f 100644 --- a/mysql-test/r/sp_gis.result +++ b/mysql-test/r/sp_gis.result @@ -7,11 +7,11 @@ return 1; create function x() returns int return 2; Warnings: -Note 1584 This function 'x' has the same name as a native function +Note 1585 This function 'x' has the same name as a native function create function y() returns int return 3; Warnings: -Note 1584 This function 'y' has the same name as a native function +Note 1585 This function 'y' has the same name as a native function select a(); a() 1 diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index a25183a0e6d..25df7a56aa3 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -4139,6 +4139,66 @@ SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1; (SELECT SUM(t1.a) FROM t2 WHERE a=1) 3 DROP TABLE t1,t2; +CREATE TABLE t1 (a1 INT, a2 INT); +CREATE TABLE t2 (b1 INT, b2 INT); +INSERT INTO t1 VALUES (100, 200); +INSERT INTO t1 VALUES (101, 201); +INSERT INTO t2 VALUES (101, 201); +INSERT INTO t2 VALUES (103, 203); +SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1; +((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL +0 +0 +DROP TABLE t1, t2; +CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5)); +INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43); +SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1); +s1 s2 +SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1); +s1 s2 +CREATE INDEX I1 ON t1 (s1); +CREATE INDEX I2 ON t1 (s2); +SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1); +s1 s2 +SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1); +s1 s2 +TRUNCATE t1; +INSERT INTO t1 VALUES (0x41,0x41); +SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1); +s1 s2 +DROP TABLE t1; +CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1)); +CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2)); +CREATE TABLE t3 (a3 BINARY(2) default '0'); +INSERT INTO t1 VALUES (1),(2),(3),(4); +INSERT INTO t2 VALUES (1),(2),(3); +INSERT INTO t3 VALUES (1),(2),(3); +SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2; +LEFT(t2.a2, 1) +1 +2 +3 +SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1; +a1 t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) +1 0 +2 0 +3 0 +4 0 +DROP TABLE t1,t2,t3; +CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3)); +CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY); +CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY); +INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40); +INSERT INTO t2 VALUES (2), (3), (4), (5); +INSERT INTO t3 VALUES (10), (20), (30); +SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3; +LEFT(t1.a1,1) +1 +2 +3 +SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3); +a2 +DROP TABLE t1, t2, t3; End of 5.0 tests. CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (2,22),(1,11),(2,22); diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result index 189722bfe9b..47ffc90e3cd 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -1978,3 +1978,9 @@ a 1 drop table table_25411_a; drop table table_25411_b; +DROP TRIGGER IF EXISTS trg; +Warnings: +Note 1360 Trigger does not exist +SHOW CREATE TRIGGER trg; +ERROR HY000: Trigger does not exist +End of 5.1 tests. diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result index 42276a004bb..e37a398d22e 100644 --- a/mysql-test/r/type_decimal.result +++ b/mysql-test/r/type_decimal.result @@ -812,4 +812,81 @@ select group_concat(f1),group_concat(f2) from t1; group_concat(f1) group_concat(f2) -0.123456 0.123456 drop table t1; +create table t1 ( +ua_id decimal(22,0) not null, +ua_invited_by_id decimal(22,0) default NULL, +primary key(ua_id) +); +insert into t1 values (123, NULL), (456, NULL); +this must not produce error 1048: +select * from t1 where ua_invited_by_id not in (select ua_id from t1); +ua_id ua_invited_by_id +drop table t1; +DROP TABLE IF EXISTS t3; +DROP TABLE IF EXISTS t4; +CREATE TABLE t1( a NUMERIC, b INT ); +INSERT INTO t1 VALUES (123456, 40), (123456, 40); +SELECT TRUNCATE( a, b ) AS c FROM t1 ORDER BY c; +c +123456 +123456 +SELECT ROUND( a, b ) AS c FROM t1 ORDER BY c; +c +123456 +123456 +SELECT ROUND( a, 100 ) AS c FROM t1 ORDER BY c; +c +123456.000000000000000000000000000000 +123456.000000000000000000000000000000 +CREATE TABLE t2( a NUMERIC, b INT ); +INSERT INTO t2 VALUES (123456, 100); +SELECT TRUNCATE( a, b ) AS c FROM t2 ORDER BY c; +c +123456 +SELECT ROUND( a, b ) AS c FROM t2 ORDER BY c; +c +123456 +CREATE TABLE t3( a DECIMAL, b INT ); +INSERT INTO t3 VALUES (123456, 40), (123456, 40); +SELECT TRUNCATE( a, b ) AS c FROM t3 ORDER BY c; +c +123456 +123456 +SELECT ROUND( a, b ) AS c FROM t3 ORDER BY c; +c +123456 +123456 +SELECT ROUND( a, 100 ) AS c FROM t3 ORDER BY c; +c +123456.000000000000000000000000000000 +123456.000000000000000000000000000000 +CREATE TABLE t4( a DECIMAL, b INT ); +INSERT INTO t4 VALUES (123456, 40), (123456, 40); +SELECT TRUNCATE( a, b ) AS c FROM t4 ORDER BY c; +c +123456 +123456 +SELECT ROUND( a, b ) AS c FROM t4 ORDER BY c; +c +123456 +123456 +SELECT ROUND( a, 100 ) AS c FROM t4 ORDER BY c; +c +123456.000000000000000000000000000000 +123456.000000000000000000000000000000 +delete from t1; +INSERT INTO t1 VALUES (1234567890, 20), (999.99, 5); +Warnings: +Note 1265 Data truncated for column 'a' at row 2 +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` decimal(10,0) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select round(a,b) as c from t1 order by c; +c +1000 +1234567890 +DROP TABLE t1, t2, t3, t4; End of 5.0 tests diff --git a/mysql-test/r/type_ranges.result b/mysql-test/r/type_ranges.result index 149f0d2dca5..6e08067d8a4 100644 --- a/mysql-test/r/type_ranges.result +++ b/mysql-test/r/type_ranges.result @@ -57,7 +57,7 @@ ushort smallint(5) unsigned zerofill NULL NO MUL 00000 # umedium mediumint(8) unsigned NULL NO MUL 0 # ulong int(11) unsigned NULL NO MUL 0 # ulonglong bigint(13) unsigned NULL NO MUL 0 # -time_stamp timestamp NULL NO CURRENT_TIMESTAMP # +time_stamp timestamp NULL NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP # date_field date NULL YES NULL # time_field time NULL YES NULL # date_time datetime NULL YES NULL # @@ -225,7 +225,7 @@ ushort smallint(5) unsigned zerofill NULL NO 00000 # umedium mediumint(8) unsigned NULL NO MUL 0 # ulong int(11) unsigned NULL NO MUL 0 # ulonglong bigint(13) unsigned NULL NO MUL 0 # -time_stamp timestamp NULL NO CURRENT_TIMESTAMP # +time_stamp timestamp NULL NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP # date_field char(10) latin1_swedish_ci YES NULL # time_field time NULL YES NULL # date_time datetime NULL YES NULL # diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index 7caf7e78fe9..596dcbf432c 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -251,7 +251,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show columns from t1; Field Type Null Key Default Extra -t1 timestamp NO 2003-01-01 00:00:00 +t1 timestamp NO 2003-01-01 00:00:00 on update CURRENT_TIMESTAMP t2 datetime YES NULL drop table t1; create table t1 (t1 timestamp default now() on update now(), t2 datetime); @@ -276,7 +276,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show columns from t1; Field Type Null Key Default Extra -t1 timestamp NO CURRENT_TIMESTAMP +t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP t2 datetime YES NULL drop table t1; create table t1 (t1 timestamp, t2 datetime, t3 timestamp); @@ -302,7 +302,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show columns from t1; Field Type Null Key Default Extra -t1 timestamp NO CURRENT_TIMESTAMP +t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP t2 datetime YES NULL t3 timestamp NO 0000-00-00 00:00:00 drop table t1; @@ -328,7 +328,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show columns from t1; Field Type Null Key Default Extra -t1 timestamp NO CURRENT_TIMESTAMP +t1 timestamp NO CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP t2 datetime YES NULL truncate table t1; insert into t1 values ('2004-04-01 00:00:00', '2004-04-01 00:00:00'); diff --git a/mysql-test/r/xml.result b/mysql-test/r/xml.result index 552f4896698..d98173dbe15 100644 --- a/mysql-test/r/xml.result +++ b/mysql-test/r/xml.result @@ -647,32 +647,32 @@ select extractValue('<a>a','/a'); extractValue('<a>a','/a') NULL Warnings: -Warning 1524 Incorrect XML value: 'parse error at line 1 pos 5: unexpected END-OF-INPUT' +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 5: unexpected END-OF-INPUT' select extractValue('<a>a<','/a'); extractValue('<a>a<','/a') NULL Warnings: -Warning 1524 Incorrect XML value: 'parse error at line 1 pos 6: END-OF-INPUT unexpected (ident or '/' wanted)' +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 6: END-OF-INPUT unexpected (ident or '/' wanted)' select extractValue('<a>a</','/a'); extractValue('<a>a</','/a') NULL Warnings: -Warning 1524 Incorrect XML value: 'parse error at line 1 pos 7: END-OF-INPUT unexpected (ident wanted)' +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 7: END-OF-INPUT unexpected (ident wanted)' select extractValue('<a>a</a','/a'); extractValue('<a>a</a','/a') NULL Warnings: -Warning 1524 Incorrect XML value: 'parse error at line 1 pos 8: END-OF-INPUT unexpected ('>' wanted)' +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 8: END-OF-INPUT unexpected ('>' wanted)' select extractValue('<a>a</a></b>','/a'); extractValue('<a>a</a></b>','/a') NULL Warnings: -Warning 1524 Incorrect XML value: 'parse error at line 1 pos 12: '</b>' unexpected (END-OF-INPUT wanted)' +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 12: '</b>' unexpected (END-OF-INPUT wanted)' select extractValue('<a b=>a</a>','/a'); extractValue('<a b=>a</a>','/a') NULL Warnings: -Warning 1524 Incorrect XML value: 'parse error at line 1 pos 7: '>' unexpected (ident or string wanted)' +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 7: '>' unexpected (ident or string wanted)' select extractValue('<e>1</e>','position()'); ERROR HY000: XPATH syntax error: '' select extractValue('<e>1</e>','last()'); @@ -723,17 +723,17 @@ select extractValue('<zot><tim0><01>10:39:15</01><02>140</02></tim0></zot>','//* extractValue('<zot><tim0><01>10:39:15</01><02>140</02></tim0></zot>','//*') NULL Warnings: -Warning 1524 Incorrect XML value: 'parse error at line 1 pos 13: unknown token unexpected (ident or '/' wanted)' +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 13: unknown token unexpected (ident or '/' wanted)' select extractValue('<.>test</.>','//*'); extractValue('<.>test</.>','//*') NULL Warnings: -Warning 1524 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' select extractValue('<->test</->','//*'); extractValue('<->test</->','//*') NULL Warnings: -Warning 1524 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' +Warning 1525 Incorrect XML value: 'parse error at line 1 pos 2: unknown token unexpected (ident or '/' wanted)' select extractValue('<:>test</:>','//*'); extractValue('<:>test</:>','//*') test diff --git a/mysql-test/suite/ndb/r/ndb_dd_basic.result b/mysql-test/suite/ndb/r/ndb_dd_basic.result index 0ff28f889b7..f04905b8d13 100644 --- a/mysql-test/suite/ndb/r/ndb_dd_basic.result +++ b/mysql-test/suite/ndb/r/ndb_dd_basic.result @@ -8,20 +8,20 @@ INITIAL_SIZE 16M UNDO_BUFFER_SIZE = 1M ENGINE=MYISAM; Warnings: -Error 1477 Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' +Error 1478 Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' ALTER LOGFILE GROUP lg1 ADD UNDOFILE 'undofile02.dat' INITIAL_SIZE = 4M ENGINE=XYZ; Warnings: Warning 1286 Unknown table engine 'XYZ' -Error 1477 Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' +Error 1478 Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' CREATE TABLESPACE ts1 ADD DATAFILE 'datafile.dat' USE LOGFILE GROUP lg1 INITIAL_SIZE 12M; Warnings: -Error 1477 Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' +Error 1478 Table storage engine 'MyISAM' does not support the create option 'TABLESPACE or LOGFILE GROUP' set storage_engine=ndb; CREATE LOGFILE GROUP lg1 ADD UNDOFILE 'undofile.dat' diff --git a/mysql-test/suite/ndb/r/ndb_dd_ddl.result b/mysql-test/suite/ndb/r/ndb_dd_ddl.result index 569769cfaef..d8d9e8631d5 100644 --- a/mysql-test/suite/ndb/r/ndb_dd_ddl.result +++ b/mysql-test/suite/ndb/r/ndb_dd_ddl.result @@ -16,7 +16,7 @@ ERROR HY000: Failed to create LOGFILE GROUP SHOW WARNINGS; Level Code Message Error 1296 Got error 1514 'Currently there is a limit of one logfile group' from NDB -Error 1527 Failed to create LOGFILE GROUP +Error 1528 Failed to create LOGFILE GROUP CREATE LOGFILE GROUP lg1 ADD UNDOFILE 'undofile.dat' INITIAL_SIZE 1M diff --git a/mysql-test/suite/ndb/r/ndb_gis.result b/mysql-test/suite/ndb/r/ndb_gis.result index b401dee8054..6c44c6fb822 100644 --- a/mysql-test/suite/ndb/r/ndb_gis.result +++ b/mysql-test/suite/ndb/r/ndb_gis.result @@ -463,7 +463,7 @@ drop table t1; End of 4.1 tests CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY); Warnings: -Error 1477 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK' +Error 1478 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK' INSERT INTO t1 VALUES("center", GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))')); INSERT INTO t1 VALUES("small", GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))')); INSERT INTO t1 VALUES("big", GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))')); @@ -1013,7 +1013,7 @@ drop table t1; End of 4.1 tests CREATE TABLE t1 (name VARCHAR(100), square GEOMETRY); Warnings: -Error 1477 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK' +Error 1478 Table storage engine 'ndbcluster' does not support the create option 'Binlog of table with BLOB attribute and no PK' INSERT INTO t1 VALUES("center", GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))')); INSERT INTO t1 VALUES("small", GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))')); INSERT INTO t1 VALUES("big", GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))')); diff --git a/mysql-test/suite/ndb/r/ndb_row_format.result b/mysql-test/suite/ndb/r/ndb_row_format.result index ea495e7e9c5..eea0692dd92 100644 --- a/mysql-test/suite/ndb/r/ndb_row_format.result +++ b/mysql-test/suite/ndb/r/ndb_row_format.result @@ -8,7 +8,7 @@ ENGINE=NDB; ERROR HY000: Can't create table 'test.t1' (errno: 138) SHOW WARNINGS; Level Code Message -Error 1477 Table storage engine 'ndbcluster' does not support the create option 'Row format FIXED incompatible with variable sized attribute' +Error 1478 Table storage engine 'ndbcluster' does not support the create option 'Row format FIXED incompatible with variable sized attribute' Error 1005 Can't create table 'test.t1' (errno: 138) CREATE TABLE t1 ( a INT KEY, diff --git a/mysql-test/suite/ndb/r/ndb_single_user.result b/mysql-test/suite/ndb/r/ndb_single_user.result index 552629ae532..0a4f7cd0b5f 100644 --- a/mysql-test/suite/ndb/r/ndb_single_user.result +++ b/mysql-test/suite/ndb/r/ndb_single_user.result @@ -11,7 +11,7 @@ ERROR HY000: Failed to create LOGFILE GROUP show warnings; Level Code Message Error 1296 Got error 299 'Operation not allowed or aborted due to single user mode' from NDB -Error 1527 Failed to create LOGFILE GROUP +Error 1528 Failed to create LOGFILE GROUP create table t1 (a int key, b int unique, c int) engine ndb; CREATE LOGFILE GROUP lg1 ADD UNDOFILE 'undofile.dat' @@ -27,14 +27,14 @@ ERROR HY000: Failed to create TABLESPACE show warnings; Level Code Message Error 1296 Got error 299 'Operation not allowed or aborted due to single user mode' from NDB -Error 1527 Failed to create TABLESPACE +Error 1528 Failed to create TABLESPACE DROP LOGFILE GROUP lg1 ENGINE =NDB; ERROR HY000: Failed to drop LOGFILE GROUP show warnings; Level Code Message Error 1296 Got error 299 'Operation not allowed or aborted due to single user mode' from NDB -Error 1528 Failed to drop LOGFILE GROUP +Error 1529 Failed to drop LOGFILE GROUP CREATE TABLESPACE ts1 ADD DATAFILE 'datafile.dat' USE LOGFILE GROUP lg1 @@ -47,7 +47,7 @@ ERROR HY000: Failed to alter: DROP DATAFILE show warnings; Level Code Message Error 1296 Got error 299 'Operation not allowed or aborted due to single user mode' from NDB -Error 1532 Failed to alter: DROP DATAFILE +Error 1533 Failed to alter: DROP DATAFILE ALTER TABLESPACE ts1 DROP DATAFILE 'datafile.dat' ENGINE NDB; @@ -57,7 +57,7 @@ ERROR HY000: Failed to drop TABLESPACE show warnings; Level Code Message Error 1296 Got error 299 'Operation not allowed or aborted due to single user mode' from NDB -Error 1528 Failed to drop TABLESPACE +Error 1529 Failed to drop TABLESPACE DROP TABLESPACE ts1 ENGINE NDB; DROP LOGFILE GROUP lg1 diff --git a/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result b/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result index fe5a5b28682..007cd5a252c 100644 --- a/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result +++ b/mysql-test/suite/rpl/r/rpl_extraCol_innodb.result @@ -72,7 +72,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 size mismatch - master has size 10, test.t2 on slave has size 6. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -90,7 +90,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 size mismatch - master has size 10, test.t2 on slave has size 6. Master's column size should be <= the slave's column size. STOP SLAVE; RESET SLAVE; @@ -139,7 +139,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -157,7 +157,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -201,7 +201,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -219,7 +219,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -263,7 +263,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 Skip_Counter 0 Exec_Master_Log_Pos # @@ -281,7 +281,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -324,7 +324,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -342,7 +342,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=3; *** Drop t6 *** @@ -436,7 +436,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 Skip_Counter 0 Exec_Master_Log_Pos # @@ -454,7 +454,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -497,7 +497,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 Skip_Counter 0 Exec_Master_Log_Pos # @@ -515,7 +515,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -822,7 +822,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 Skip_Counter 0 Exec_Master_Log_Pos # @@ -840,7 +840,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result b/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result index e70a2efaf29..873fc5cddbc 100644 --- a/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result +++ b/mysql-test/suite/rpl/r/rpl_extraCol_myisam.result @@ -72,7 +72,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 size mismatch - master has size 10, test.t2 on slave has size 6. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -90,7 +90,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 size mismatch - master has size 10, test.t2 on slave has size 6. Master's column size should be <= the slave's column size. STOP SLAVE; RESET SLAVE; @@ -139,7 +139,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -157,7 +157,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -201,7 +201,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -219,7 +219,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -263,7 +263,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 Skip_Counter 0 Exec_Master_Log_Pos # @@ -281,7 +281,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -324,7 +324,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -342,7 +342,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=3; *** Drop t6 *** @@ -436,7 +436,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 Skip_Counter 0 Exec_Master_Log_Pos # @@ -454,7 +454,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -497,7 +497,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 Skip_Counter 0 Exec_Master_Log_Pos # @@ -515,7 +515,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -822,7 +822,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 Skip_Counter 0 Exec_Master_Log_Pos # @@ -840,7 +840,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_incident.result b/mysql-test/suite/rpl/r/rpl_incident.result index 568078cc276..c3baabbdbc3 100644 --- a/mysql-test/suite/rpl/r/rpl_incident.result +++ b/mysql-test/suite/rpl/r/rpl_incident.result @@ -44,7 +44,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1589 +Last_Errno 1590 Last_Error The incident LOST_EVENTS occured on the master. Message: <none> Skip_Counter 0 Exec_Master_Log_Pos # @@ -62,7 +62,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1589 +Last_SQL_Errno 1590 Last_SQL_Error The incident LOST_EVENTS occured on the master. Message: <none> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result b/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result index d30110f85e9..a4d67754bd6 100644 --- a/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result +++ b/mysql-test/suite/rpl/r/rpl_loaddata_fatal.result @@ -65,7 +65,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1592 +Last_Errno 1593 Last_Error Fatal error: Not enough memory Skip_Counter 0 Exec_Master_Log_Pos 325 @@ -83,7 +83,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1592 +Last_SQL_Errno 1593 Last_SQL_Error Fatal error: Not enough memory SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_row_colSize.result b/mysql-test/suite/rpl/r/rpl_row_colSize.result index 9358e36cac6..6d002a722f1 100644 --- a/mysql-test/suite/rpl/r/rpl_row_colSize.result +++ b/mysql-test/suite/rpl/r/rpl_row_colSize.result @@ -37,7 +37,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 10, test.t1 on slave has size 3. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -55,7 +55,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 10, test.t1 on slave has size 3. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -91,7 +91,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 12, test.t1 on slave has size 12. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -109,7 +109,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 12, test.t1 on slave has size 12. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -145,7 +145,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 10, test.t1 on slave has size 3. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -163,7 +163,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 10, test.t1 on slave has size 3. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -200,7 +200,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 5, test.t1 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -218,7 +218,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 5, test.t1 has type 4 SELECT COUNT(*) FROM t1; COUNT(*) @@ -255,7 +255,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 8, test.t1 on slave has size 1. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -273,7 +273,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 8, test.t1 on slave has size 1. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -309,7 +309,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2, test.t1 on slave has size 2. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -327,7 +327,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2, test.t1 on slave has size 2. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -364,7 +364,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2, test.t1 on slave has size 1. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -382,7 +382,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2, test.t1 on slave has size 1. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -419,7 +419,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 20, test.t1 on slave has size 11. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -437,7 +437,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 20, test.t1 on slave has size 11. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -505,7 +505,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2, test.t1 on slave has size 1. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -523,7 +523,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2, test.t1 on slave has size 1. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -560,7 +560,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2000, test.t1 on slave has size 100. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -578,7 +578,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2000, test.t1 on slave has size 100. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -614,7 +614,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 200, test.t1 on slave has size 10. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -632,7 +632,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 200, test.t1 on slave has size 10. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -668,7 +668,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2000, test.t1 on slave has size 1000. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -686,7 +686,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 2000, test.t1 on slave has size 1000. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) @@ -723,7 +723,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 4, test.t1 on slave has size 1. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -741,7 +741,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 size mismatch - master has size 4, test.t1 on slave has size 1. Master's column size should be <= the slave's column size. SELECT COUNT(*) FROM t1; COUNT(*) diff --git a/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result b/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result index 566537ab745..06dc90f18aa 100644 --- a/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result +++ b/mysql-test/suite/rpl/r/rpl_row_tabledefs_2myisam.result @@ -214,7 +214,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 3, test.t4 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -232,7 +232,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 3, test.t4 has type 4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -257,7 +257,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 1 type mismatch - received type 3, test.t5 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -275,7 +275,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 1 type mismatch - received type 3, test.t5 has type 4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -300,7 +300,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 3, test.t6 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -318,7 +318,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 3, test.t6 has type 4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result b/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result index 83df75b81e9..3911fe89b7f 100644 --- a/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result +++ b/mysql-test/suite/rpl/r/rpl_row_tabledefs_3innodb.result @@ -214,7 +214,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 3, test.t4 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -232,7 +232,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 3, test.t4 has type 4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -257,7 +257,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 1 type mismatch - received type 3, test.t5 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -275,7 +275,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 1 type mismatch - received type 3, test.t5 has type 4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -300,7 +300,7 @@ Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 3, test.t6 has type 4 Skip_Counter 0 Exec_Master_Log_Pos # @@ -318,7 +318,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno 0 Last_IO_Error -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 3, test.t6 has type 4 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; diff --git a/mysql-test/suite/rpl/r/rpl_udf.result b/mysql-test/suite/rpl/r/rpl_udf.result index fcd2f4743ba..79a82b5fbc7 100644 --- a/mysql-test/suite/rpl/r/rpl_udf.result +++ b/mysql-test/suite/rpl/r/rpl_udf.result @@ -182,19 +182,19 @@ CREATE TABLE t1(sum INT, price FLOAT(24)) ENGINE=MyISAM; affected rows: 0 INSERT INTO t1 VALUES(myfunc_int(100), myfunc_double(50.00)); Warnings: -Warning 1591 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. affected rows: 1 INSERT INTO t1 VALUES(myfunc_int(10), myfunc_double(5.00)); Warnings: -Warning 1591 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. affected rows: 1 INSERT INTO t1 VALUES(myfunc_int(200), myfunc_double(25.00)); Warnings: -Warning 1591 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. affected rows: 1 INSERT INTO t1 VALUES(myfunc_int(1), myfunc_double(500.00)); Warnings: -Warning 1591 Statement is not safe to log in statement format. +Warning 1592 Statement is not safe to log in statement format. affected rows: 1 SELECT * FROM t1 ORDER BY sum; sum price diff --git a/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result b/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result index 41e888827b1..54056ac613b 100644 --- a/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result +++ b/mysql-test/suite/rpl_ndb/r/rpl_ndb_extraCol.result @@ -72,7 +72,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 size mismatch - master has size 10, test.t2 on slave has size 6. Master's column size should be <= the slave's column size. Skip_Counter 0 Exec_Master_Log_Pos # @@ -90,7 +90,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 size mismatch - master has size 10, test.t2 on slave has size 6. Master's column size should be <= the slave's column size. STOP SLAVE; RESET SLAVE; @@ -139,7 +139,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -157,7 +157,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 252, test.t3 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -201,7 +201,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -219,7 +219,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 246, test.t4 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -263,7 +263,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 Skip_Counter 0 Exec_Master_Log_Pos # @@ -281,7 +281,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 5 type mismatch - received type 4, test.t5 has type 246 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -324,7 +324,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 Skip_Counter 0 Exec_Master_Log_Pos # @@ -342,7 +342,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 3 type mismatch - received type 16, test.t6 has type 3 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=3; *** Drop t6 *** @@ -436,7 +436,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 Skip_Counter 0 Exec_Master_Log_Pos # @@ -454,7 +454,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 254, test.t10 has type 5 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -497,7 +497,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 Skip_Counter 0 Exec_Master_Log_Pos # @@ -515,7 +515,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 2 type mismatch - received type 15, test.t11 has type 252 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; @@ -823,7 +823,7 @@ Replicate_Do_Table Replicate_Ignore_Table # Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table -Last_Errno 1534 +Last_Errno 1535 Last_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 Skip_Counter 0 Exec_Master_Log_Pos # @@ -841,7 +841,7 @@ Seconds_Behind_Master # Master_SSL_Verify_Server_Cert No Last_IO_Errno # Last_IO_Error # -Last_SQL_Errno 1534 +Last_SQL_Errno 1535 Last_SQL_Error Table definition on master and slave does not match: Column 0 type mismatch - received type 8, test.t17 has type 2 SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2; START SLAVE; diff --git a/mysql-test/t/almost_full.test b/mysql-test/t/almost_full.test new file mode 100644 index 00000000000..5c67ab3c088 --- /dev/null +++ b/mysql-test/t/almost_full.test @@ -0,0 +1,41 @@ +# +# Some special cases with empty tables +# + +--disable_warnings +drop table if exists t1; +--enable_warnings + +set global myisam_data_pointer_size=2; +CREATE TABLE t1 (a int auto_increment primary key not null, b longtext) ENGINE=MyISAM; + +--disable_query_log +let $1= 303; +while ($1) +{ + INSERT INTO t1 SET b=repeat('a',200); + dec $1; +} +--enable_query_log + +DELETE FROM t1 WHERE a=1 or a=5; + +--error 1114 +INSERT INTO t1 SET b=repeat('a',600); +CHECK TABLE t1 EXTENDED; + +--error 1114 +UPDATE t1 SET b=repeat('a', 800) where a=10; +CHECK TABLE t1 EXTENDED; + +INSERT INTO t1 SET b=repeat('a',400); +CHECK TABLE t1 EXTENDED; + +DELETE FROM t1 WHERE a=2 or a=6; +UPDATE t1 SET b=repeat('a', 600) where a=11; +CHECK TABLE t1 EXTENDED; +drop table t1; + +set global myisam_data_pointer_size=default; + +# End of 4.1 tests diff --git a/mysql-test/t/archive.test b/mysql-test/t/archive.test index e0a2e877af5..f8eff10e30a 100644 --- a/mysql-test/t/archive.test +++ b/mysql-test/t/archive.test @@ -1567,3 +1567,25 @@ insert into t1 set a=''; insert into t1 set a='a'; check table t1 extended; drop table t1; + +# +# BUG#31036 - Using order by with archive table crashes server +# + +CREATE TABLE t1(a VARCHAR(510)) ENGINE = ARCHIVE; + +let $bug31036=41; +--disable_query_log +while($bug31036) +{ + INSERT INTO t1(a) VALUES (REPEAT('a', 510)); + dec $bug31036; +} +--enable_query_log +INSERT INTO t1(a) VALUES (''); + +--disable_result_log +SELECT * FROM t1 ORDER BY a; +--enable_result_log + +DROP TABLE t1; diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test index 5563e260a06..50865215944 100644 --- a/mysql-test/t/cast.test +++ b/mysql-test/t/cast.test @@ -246,4 +246,26 @@ INSERT INTO t1(d1) VALUES ('2007-07-19 08:30:00'), (NULL), SELECT cast(date(d1) as signed) FROM t1; drop table t1; +# +# Bug #31990: MINUTE() and SECOND() return bogus results when used on a DATE +# + +# Show that HH:MM:SS of a DATE are 0, and that it's the same for columns +# and typecasts (NULL in, NULL out). +CREATE TABLE t1 (f1 DATE); +INSERT INTO t1 VALUES ('2007-07-19'), (NULL); +SELECT HOUR(f1), + MINUTE(f1), + SECOND(f1) FROM t1; +SELECT HOUR(CAST('2007-07-19' AS DATE)), + MINUTE(CAST('2007-07-19' AS DATE)), + SECOND(CAST('2007-07-19' AS DATE)); +SELECT HOUR(CAST(NULL AS DATE)), + MINUTE(CAST(NULL AS DATE)), + SECOND(CAST(NULL AS DATE)); +SELECT HOUR(NULL), + MINUTE(NULL), + SECOND(NULL); +DROP TABLE t1; + --echo End of 5.0 tests diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index 4c5dd6467bd..322be2b7b8e 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -590,4 +590,13 @@ select group_concat(distinct a, c order by a desc, c desc) from t1; drop table t1; +# +# Bug#30897 GROUP_CONCAT returns extra comma on empty fields +# +create table t1 (f1 char(20)); +insert into t1 values (''),(''); +select group_concat(distinct f1) from t1; +select group_concat(f1) from t1; +drop table t1; + --echo End of 5.0 tests diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test index e1ec6906cd6..8beed28cfbf 100644 --- a/mysql-test/t/func_group.test +++ b/mysql-test/t/func_group.test @@ -880,5 +880,33 @@ SELECT 1 FROM t1 GROUP BY (SELECT SLEEP(0) FROM t1 ORDER BY AVG(DISTINCT a) ); DROP TABLE t1; +# +# Bug #30715: Assertion failed: item_field->field->real_maybe_null(), file +# .\opt_sum.cc, line +# + +CREATE TABLE t1 (a int, b date NOT NULL, KEY k1 (a,b)); +SELECT MIN(b) FROM t1 WHERE a=1 AND b>'2007-08-01'; +DROP TABLE t1; + +# +# Bug #31794: no syntax error on SELECT id FROM t HAVING count(*)>2; +# + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3),(4); + +SET SQL_MODE=ONLY_FULL_GROUP_BY; +--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS +SELECT a FROM t1 HAVING COUNT(*)>2; +--error ER_MIX_OF_GROUP_FUNC_AND_FIELDS +SELECT COUNT(*), a FROM t1; + +SET SQL_MODE=DEFAULT; +SELECT a FROM t1 HAVING COUNT(*)>2; +SELECT COUNT(*), a FROM t1; + +DROP TABLE t1; + ### --echo End of 5.0 tests diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 0ba0ba30cd2..b6da14211ae 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -782,6 +782,19 @@ explain extended select encode(f1,'zxcv') as 'enc' from t1; explain extended select decode(f1,'zxcv') as 'enc' from t1; drop table t1; +# +# Bug #31758 inet_ntoa, oct, crashes server with null + filesort +# +create table t1 (a bigint not null)engine=myisam; +insert into t1 set a = 1024*1024*1024*4; +delete from t1 order by (inet_ntoa(a)) desc limit 10; +drop table t1; +create table t1 (a char(36) not null)engine=myisam; +insert ignore into t1 set a = ' '; +insert ignore into t1 set a = ' '; +select * from t1 order by (oct(a)); +drop table t1; + --echo End of 4.1 tests # diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 2ea7aed6bd2..94c2b463aaa 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -790,6 +790,41 @@ drop table t1; SET SQL_MODE = ''; # + +# +# Bug #32202: ORDER BY not working with GROUP BY +# + +CREATE TABLE t1( + id INT AUTO_INCREMENT PRIMARY KEY, + c1 INT NOT NULL, + c2 INT NOT NULL, + UNIQUE KEY (c2,c1)); + +INSERT INTO t1(c1,c2) VALUES (5,1), (4,1), (3,5), (2,3), (1,3); + +# Show that the test cases from the bug report pass +SELECT * FROM t1 ORDER BY c1; +SELECT * FROM t1 GROUP BY id ORDER BY c1; + +# Show that DESC is handled correctly +SELECT * FROM t1 GROUP BY id ORDER BY id DESC; + +# Show that results are correctly ordered when ORDER BY fields +# are a subset of GROUP BY ones +SELECT * FROM t1 GROUP BY c2 ,c1, id ORDER BY c2, c1; +SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1; +SELECT * FROM t1 GROUP BY c2, c1, id ORDER BY c2 DESC, c1 DESC; + +# Show that results are correctly ordered when GROUP BY fields +# are a subset of ORDER BY ones +SELECT * FROM t1 GROUP BY c2 ORDER BY c2, c1; +SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1; +SELECT * FROM t1 GROUP BY c2 ORDER BY c2 DESC, c1 DESC; + +DROP TABLE t1; + +--echo End of 5.0 tests # Bug #21174: Index degrades sort performance and # optimizer does not honor IGNORE INDEX. # a.k.a WL3527. diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 1987d9d5773..2dd19a94758 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -807,7 +807,8 @@ DROP FUNCTION func2; select column_type, group_concat(table_schema, '.', table_name), count(*) as num from information_schema.columns where table_schema='information_schema' and -(column_type = 'varchar(7)' or column_type = 'varchar(20)') +(column_type = 'varchar(7)' or column_type = 'varchar(20)' + or column_type = 'varchar(27)') group by column_type order by num; # @@ -1232,4 +1233,9 @@ 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; +# +# Bug#31630 debug assert with explain extended select ... from i_s +# +explain extended select 1 from information_schema.tables; + --echo End of 5.1 tests. diff --git a/mysql-test/t/log_state.test b/mysql-test/t/log_state.test index c67da261ef1..f7795e49b37 100644 --- a/mysql-test/t/log_state.test +++ b/mysql-test/t/log_state.test @@ -179,6 +179,42 @@ SET GLOBAL READ_ONLY = OFF; SET GLOBAL general_log = @old_general_log_state; SET GLOBAL slow_query_log = @old_slow_log_state; +# +# Bug #29131: SHOW VARIABLES reports variable 'log' but SET doesn't recognize it +# + +SET @old_general_log_state = @@global.general_log; +SET @old_slow_log_state = @@global.slow_query_log; + +SHOW VARIABLES LIKE 'general_log'; +SHOW VARIABLES LIKE 'log'; +SELECT @@general_log, @@log; +SET GLOBAL log = 0; +SHOW VARIABLES LIKE 'general_log'; +SHOW VARIABLES LIKE 'log'; +SELECT @@general_log, @@log; +SET GLOBAL general_log = 1; +SHOW VARIABLES LIKE 'general_log'; +SHOW VARIABLES LIKE 'log'; +SELECT @@general_log, @@log; + +SHOW VARIABLES LIKE 'slow_query_log'; +SHOW VARIABLES LIKE 'log_slow_queries'; +SELECT @@slow_query_log, @@log_slow_queries; +SET GLOBAL log_slow_queries = 0; +SHOW VARIABLES LIKE 'slow_query_log'; +SHOW VARIABLES LIKE 'log_slow_queries'; +SELECT @@slow_query_log, @@log_slow_queries; +SET GLOBAL slow_query_log = 1; +SHOW VARIABLES LIKE 'slow_query_log'; +SHOW VARIABLES LIKE 'log_slow_queries'; +SELECT @@slow_query_log, @@log_slow_queries; + +SET GLOBAL general_log = @old_general_log_state; +SET GLOBAL slow_query_log = @old_slow_log_state; + +--echo End of 5.1 tests + --enable_ps_protocol # diff --git a/mysql-test/t/log_tables.test b/mysql-test/t/log_tables.test index 12098b4543b..39bf8029a7c 100644 --- a/mysql-test/t/log_tables.test +++ b/mysql-test/t/log_tables.test @@ -924,3 +924,34 @@ set global general_log = off; select command_type, argument from mysql.general_log; deallocate prepare long_query; set global general_log = @old_general_log_state; + +# +# Bug #31700: thd->examined_row_count not incremented for 'const' type queries +# +SET @old_slow_log_state = @@global.slow_query_log; + +SET SESSION long_query_time = 0; +SET GLOBAL slow_query_log = ON; +FLUSH LOGS; +TRUNCATE TABLE mysql.slow_log; + +# Let there be three columns, unique, non-unique, and non-indexed: +CREATE TABLE t1 (f1 SERIAL,f2 INT, f3 INT, PRIMARY KEY(f1), KEY(f2)); +INSERT INTO t1 VALUES (1,1,1); +INSERT INTO t1 VALUES (2,2,2); +INSERT INTO t1 VALUES (3,3,3); +INSERT INTO t1 VALUES (4,4,4); + +SELECT SQL_NO_CACHE 'Bug#31700 - SCAN',f1,f2,f3 FROM t1 WHERE f3=4; +SELECT SQL_NO_CACHE 'Bug#31700 - KEY', f1,f2,f3 FROM t1 WHERE f2=3; +SELECT SQL_NO_CACHE 'Bug#31700 - PK', f1,f2,f3 FROM t1 WHERE f1=2; + +--replace_column 1 TIMESTAMP +SELECT start_time, rows_examined, rows_sent, sql_text FROM mysql.slow_log WHERE sql_text LIKE '%Bug#31700%' ORDER BY start_time; + +DROP TABLE t1; + +TRUNCATE TABLE mysql.slow_log; + +SET GLOBAL slow_query_log = @old_slow_log_state; +SET SESSION long_query_time =@old_long_query_time; diff --git a/mysql-test/t/lowercase_view.test b/mysql-test/t/lowercase_view.test index e9cc26bec18..d6612b3e6b9 100644 --- a/mysql-test/t/lowercase_view.test +++ b/mysql-test/t/lowercase_view.test @@ -138,3 +138,26 @@ create view v1Aa as select AaA.col1 from t1Aa as AaA; show create view v1AA; drop view v1AA; drop table t1Aa; + + +# +# Bug #31562: HAVING and lower case +# + +CREATE TABLE t1 (a int, b int); + +select X.a from t1 AS X group by X.b having (X.a = 1); +select X.a from t1 AS X group by X.b having (x.a = 1); +select X.a from t1 AS X group by X.b having (x.b = 1); + +CREATE OR REPLACE VIEW v1 AS +select X.a from t1 AS X group by X.b having (X.a = 1); + +SHOW CREATE VIEW v1; + +SELECT * FROM v1; + +DROP VIEW v1; +DROP TABLE t1; + +--echo End of 5.0 tests. diff --git a/mysql-test/t/mysql_comments.sql b/mysql-test/t/mysql_comments.sql new file mode 100644 index 00000000000..2497c35e465 --- /dev/null +++ b/mysql-test/t/mysql_comments.sql @@ -0,0 +1,218 @@ +##============================================================================ +## Notes +##============================================================================ + +# Test case for Bug#11230 + +# The point of this test is to make sure that '#', '-- ' and '/* ... */' +# comments, as well as empty lines, are sent from the client to the server. +# This is to ensure better error reporting, and to keep comments in the code +# for stored procedures / functions / triggers (Bug#11230). +# As a result, be careful when editing comments in this script, they do +# matter. +# +# Also, note that this is a script for **mysql**, not mysqltest. +# This is critical, as the mysqltest client interprets comments differently. + +##============================================================================ +## Setup +##============================================================================ + +## See mysql_comments.test for initial cleanup + +# Test tables +# +# t1 is reused throughout the file, and dropped at the end. +# +drop table if exists t1; +create table t1 ( + id char(16) not null default '', + data int not null +); + +##============================================================================ +## Comments outside statements +##============================================================================ + +# Ignored 1a +-- Ignored 1b +/* + Ignored 1c +*/ + +select 1; + +##============================================================================ +## Comments inside statements +##============================================================================ + +select # comment 1a +# comment 2a +-- comment 2b +/* + comment 2c +*/ +2 +; # not strictly inside, but on same line +# ignored + +##============================================================================ +## Comments inside functions +##============================================================================ + +drop function if exists foofct ; + +create function foofct (x char(20)) +returns char(20) +/* not inside the body yet */ +return +-- comment 1a +# comment 1b +/* comment 1c */ +x; # after body, on same line + +select foofct("call 1"); + +show create function foofct; +drop function foofct; + +delimiter | + +create function foofct(x char(20)) +returns char(20) +begin + -- comment 1a + # comment 1b + /* + comment 1c + */ + + -- empty line below + + -- empty line above + return x; +end| + +delimiter ; + +select foofct("call 2"); + +show create function foofct; +drop function foofct; + +##============================================================================ +## Comments inside stored procedures +##============================================================================ + +# Empty statement +drop procedure if exists empty; +create procedure empty() +begin +end; + +call empty(); +show create procedure empty; +drop procedure empty; + +drop procedure if exists foosp; + +## These comments are before the create, and will be lost +# Comment 1a +-- Comment 1b +/* + Comment 1c + */ +create procedure foosp() +/* Comment not quiet in the body yet */ + insert into test.t1 +## These comments are part of the procedure body, and should be kept. +# Comment 2a +-- Comment 2b +/* Comment 2c */ + -- empty line below + + -- empty line above + values ("foo", 42); # comment 3, still part of the body +## After the ';', therefore not part of the body +# comment 4a +-- Comment 4b +/* + Comment 4c + */ + +call foosp(); +select * from t1; +delete from t1; +show create procedure foosp; +drop procedure foosp; + +drop procedure if exists nicesp; + +delimiter | + +create procedure nicesp(a int) +begin + -- declare some variables here + declare b int; + declare c float; + + -- do more stuff here + -- commented nicely and so on + + -- famous last words ... +end| + +delimiter ; + +show create procedure nicesp; +drop procedure nicesp; + +##============================================================================ +## Comments inside triggers +##============================================================================ + +drop trigger if exists t1_empty; + +create trigger t1_empty after delete on t1 +for each row +begin +end; + +show create trigger t1_empty; + +drop trigger if exists t1_bi; + +delimiter | + +create trigger t1_bi before insert on t1 +for each row +begin +# comment 1a +-- comment 1b +/* + comment 1c +*/ + -- declare some variables here + declare b int; + declare c float; + + -- do more stuff here + -- commented nicely and so on + + -- famous last words ... + set NEW.data := 12; +end| + +delimiter ; + +show create trigger t1_bi; + +# also make sure the trigger still works +insert into t1(id) value ("trig"); +select * from t1; + +##============================================================================ +## Cleanup +##============================================================================ + +drop table t1; diff --git a/mysql-test/t/mysql_comments.test b/mysql-test/t/mysql_comments.test new file mode 100644 index 00000000000..1f997aeb1ab --- /dev/null +++ b/mysql-test/t/mysql_comments.test @@ -0,0 +1,37 @@ +# This test should work in embedded server after we fix mysqltest +-- source include/not_embedded.inc +###################### mysql_comments.test ############################# +# # +# Testing of comments handling by the command line client (mysql) # +# # +# Creation: # +# 2007-10-29 akopytov Implemented this test as a part of fixes for # +# bug #26215 and bug #11230 # +# # +######################################################################## + +# +# Bug #11230: Keeping comments when storing stored procedures +# + +# See the content of mysql_comments.sql +# Set the test database to a known state before running the tests. +--disable_warnings +drop table if exists t1; +drop function if exists foofct; +drop procedure if exists empty; +drop procedure if exists foosp; +drop procedure if exists nicesp; +drop trigger if exists t1_empty; +drop trigger if exists t1_bi; +--enable_warnings + +# Test without comments +--echo "Pass 1 : --disable-comments" +--exec $MYSQL --disable-comments test 2>&1 < "./t/mysql_comments.sql" + +# Test with comments +--echo "Pass 2 : --enable-comments" +--exec $MYSQL --enable-comments test 2>&1 < "./t/mysql_comments.sql" + +--echo End of 5.0 tests diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 158e8a769bd..0e4e9989ffa 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -1788,6 +1788,18 @@ TRUNCATE mysql.event; --exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug29938.sql SHOW EVENTS; + +--echo # +--echo # Bug#31113 mysqldump 5.1 can't handle a dash ("-") in database names +--echo # +create database `test-database`; +use `test-database`; +create table test (a int); +--exec $MYSQL_DUMP --compact --opt --quote-names test-database +drop database `test-database`; +use test; + + --echo # --echo # End of 5.1 tests --echo # diff --git a/mysql-test/t/outfile_loaddata.test b/mysql-test/t/outfile_loaddata.test new file mode 100644 index 00000000000..2f6ac998b3d --- /dev/null +++ b/mysql-test/t/outfile_loaddata.test @@ -0,0 +1,89 @@ +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +--echo # +--echo # Bug#31663 FIELDS TERMINATED BY special character +--echo # + +CREATE TABLE t1 (i1 int, i2 int, c1 VARCHAR(256), c2 VARCHAR(256)); +INSERT INTO t1 VALUES (101, 202, '-r-', '=raker='); + +--let $fields=* +--let $clauses=FIELDS TERMINATED BY 'raker' +--echo # $clauses, warning: + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT $fields INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' $clauses FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT LOAD_FILE('$MYSQLTEST_VARDIR/tmp/bug31663.txt') +--eval CREATE TABLE t2 SELECT $fields FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 $clauses +--eval SELECT $fields FROM t2 +--remove_file $MYSQLTEST_VARDIR/tmp/bug31663.txt +DROP TABLE t2; + +--let $fields=i1, i2 +--let $clauses=FIELDS TERMINATED BY 'r' +--echo # Only numeric fields, $clauses, no warnings: + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT $fields INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' $clauses FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT LOAD_FILE('$MYSQLTEST_VARDIR/tmp/bug31663.txt') +--eval CREATE TABLE t2 SELECT $fields FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 $clauses +--eval SELECT $fields FROM t2 +--remove_file $MYSQLTEST_VARDIR/tmp/bug31663.txt +DROP TABLE t2; + +--let $fields=* +--let $clauses=FIELDS TERMINATED BY '0' +--echo # $clauses, warning: + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT $fields INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' $clauses FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT LOAD_FILE('$MYSQLTEST_VARDIR/tmp/bug31663.txt') +--eval CREATE TABLE t2 SELECT $fields FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 $clauses +--eval SELECT $fields FROM t2 +--remove_file $MYSQLTEST_VARDIR/tmp/bug31663.txt +DROP TABLE t2; + +--let $fields=* +--let $clauses=FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0' +--echo # $clauses, warning: + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT $fields INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' $clauses FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT LOAD_FILE('$MYSQLTEST_VARDIR/tmp/bug31663.txt') +--eval CREATE TABLE t2 SELECT $fields FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 $clauses +--eval SELECT $fields FROM t2 +--remove_file $MYSQLTEST_VARDIR/tmp/bug31663.txt +DROP TABLE t2; + +--let $fields=c1, c2 +--let $clauses=FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0' +--echo # Only string fields, $clauses, no warnings: + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT $fields INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' $clauses FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval SELECT LOAD_FILE('$MYSQLTEST_VARDIR/tmp/bug31663.txt') +--eval CREATE TABLE t2 SELECT $fields FROM t1 +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 $clauses +--eval SELECT $fields FROM t2 +--remove_file $MYSQLTEST_VARDIR/tmp/bug31663.txt +DROP TABLE t2; + +DROP TABLE t1; + +--echo # End of 5.0 tests. diff --git a/mysql-test/t/partition.test b/mysql-test/t/partition.test index f986215ee89..65e78a5e740 100644 --- a/mysql-test/t/partition.test +++ b/mysql-test/t/partition.test @@ -1510,4 +1510,25 @@ PARTITION BY RANGE (b) ( show create table t1; drop table t1, t2; +# +# Bug #32067 Partitions: crash with timestamp column +# this bug occurs randomly on some UPDATE statement +# with the '1032: Can't find record in 't1'' error + +create table t1 + (s1 timestamp on update current_timestamp, s2 int) + partition by key(s1) partitions 3; + +insert into t1 values (null,null); +--disable_query_log +let $cnt= 1000; +while ($cnt) +{ + update t1 set s2 = 1; + update t1 set s2 = 2; + dec $cnt; +} +--enable_query_log + +drop table t1; --echo End of 5.1 tests diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index 4a50332b3df..6b73d0b2c5e 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -142,3 +142,15 @@ PARTITION BY KEY(a) PARTITIONS 10; INSERT INTO t1 VALUES(1),(2); SELECT COUNT(*) FROM t1; DROP TABLE t1; + +# +# Bug #31893 Partitions: crash if subpartitions and engine change +# +create table t1 (int_column int, char_column char(5)) + PARTITION BY RANGE (int_column) subpartition by key (char_column) subpartitions 2 + (PARTITION p1 VALUES LESS THAN (5) ENGINE = InnoDB); +alter table t1 PARTITION BY RANGE (int_column) + subpartition by key (char_column) subpartitions 2 + (PARTITION p1 VALUES LESS THAN (5) ENGINE = myisam); +show create table t1; +drop table t1; diff --git a/mysql-test/t/plugin.test b/mysql-test/t/plugin.test index fb6d5febe45..d8d6d069676 100644 --- a/mysql-test/t/plugin.test +++ b/mysql-test/t/plugin.test @@ -24,3 +24,18 @@ UNINSTALL PLUGIN EXAMPLE; --error 1305 UNINSTALL PLUGIN non_exist; + + +--echo # +--echo # Bug#32034: check_func_enum() does not check correct values but set it +--echo # to impossible int val +--echo # + +INSTALL PLUGIN example SONAME 'ha_example.so'; + +SET GLOBAL example_enum_var= e1; +SET GLOBAL example_enum_var= e2; +--error 1231 +SET GLOBAL example_enum_var= impossible; + +UNINSTALL PLUGIN example; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 71a7caba399..8d273441ce8 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -2361,6 +2361,27 @@ insert into t1 values (0xfffffffffffffffff, 0xfffffffffffffffff), select hex(a), hex(b) from t1; drop table t1; +# +# Bug #32103: optimizer crash when join on int and mediumint with variable in +# where clause +# + +CREATE TABLE t1 (c0 int); +CREATE TABLE t2 (c0 int); + +# We need any variable that: +# 1. has integer type, +# 2. can be used with the "@@name" syntax +# 3. available in every server build +INSERT INTO t1 VALUES(@@connect_timeout); +INSERT INTO t2 VALUES(@@connect_timeout); + +# We only need to ensure 1 row is returned to validate the results +--replace_column 1 X 2 X +SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@connect_timeout); + +DROP TABLE t1, t2; + --echo End of 4.1 tests # @@ -2743,14 +2764,14 @@ create table t1(f1 int, f2 date); insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'), (4,'2005-10-01'),(5,'2005-12-30'); # should return all records -select * from t1 where f2 >= 0; -select * from t1 where f2 >= '0000-00-00'; +select * from t1 where f2 >= 0 order by f2; +select * from t1 where f2 >= '0000-00-00' order by f2; # should return 4,5 -select * from t1 where f2 >= '2005-09-31'; -select * from t1 where f2 >= '2005-09-3a'; +select * from t1 where f2 >= '2005-09-31' order by f2; +select * from t1 where f2 >= '2005-09-3a' order by f2; # should return 1,2,3 -select * from t1 where f2 <= '2005-09-31'; -select * from t1 where f2 <= '2005-09-3a'; +select * from t1 where f2 <= '2005-09-31' order by f2; +select * from t1 where f2 <= '2005-09-3a' order by f2; drop table t1; # @@ -3455,24 +3476,129 @@ DROP TABLE t1; # --disable_ps_protocol - SELECT 1 AS ` `; SELECT 1 AS ` `; SELECT 1 AS ` x`; +--enable_ps_protocol -CREATE VIEW v1 AS SELECT 1 AS ` `; -SELECT `` FROM v1; - -CREATE VIEW v2 AS SELECT 1 AS ` `; -SELECT `` FROM v2; - -CREATE VIEW v3 AS SELECT 1 AS ` x`; -SELECT `x` FROM v3; - -DROP VIEW v1, v2, v3; +--error 1166 +CREATE VIEW v1 AS SELECT 1 AS ``; ---enable_ps_protocol +--error 1166 +CREATE VIEW v1 AS SELECT 1 AS ` `; +--error 1166 +CREATE VIEW v1 AS SELECT 1 AS ` `; + +--error 1166 +CREATE VIEW v1 AS SELECT (SELECT 1 AS ` `); + +--error 1166 +ALTER VIEW v1 AS SELECT 1 AS ` `; + +DROP VIEW v1; + +# +# Bug#31800: Date comparison fails with timezone and slashes for greater +# than comparison +# + +# On DATETIME-like literals with trailing garbage, BETWEEN fudged in a +# DATETIME comparator, while greater/less-than used bin-string comparisons. +# Should correctly be compared as DATE or DATETIME, but throw a warning: + +select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT' + and '2007/10/20 00:00:00 GMT'; +select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'; +select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'; + +# We have all we need -- and trailing garbage: +# (leaving out a leading zero in first example to prove it's a +# value-comparison, not a string-comparison!) +select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'; +select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'; +select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6'; +select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6'; +# no time at all: +select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6'; +# partial time: +select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; +# fail, different second part: +select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'; +# correct syntax, no trailing nonsense -- this one must throw no warning: +select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34:56'; +# no warning, but failure (different hour parts): +select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 12:00:00'; +# succeed: +select str_to_date('2007-10-01 12','%Y-%m-%d %H') = '2007-10-01 12:00:00'; +# succeed, but warn for "trailing garbage" (":34"): +select str_to_date('2007-10-01 12:34','%Y-%m-%d %H') = '2007-10-01 12:00:00'; +# invalid date (Feb 30) succeeds +select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34'; +# 0-day for both, just works in default SQL mode. +select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; +# 0-day, succeed +select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' + and '2007/10/20 00:00:00'; +set SQL_MODE=TRADITIONAL; +# 0-day throws warning in traditional mode, and fails +select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; +select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; +# different code-path: get_datetime_value() with 0-day +select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; +select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' + and '2007/10/20'; +set SQL_MODE=DEFAULT; +select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'; +select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20'; +select str_to_date('','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; +select str_to_date(NULL,'%Y-%m-%d %H:%i') = '2007-10-01 12:34'; +select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = ''; + +select str_to_date('1','%Y-%m-%d') = '1'; +select str_to_date('1','%Y-%m-%d') = '1'; +select str_to_date('','%Y-%m-%d') = ''; + +# these three should work! +select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL; +select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00'; +select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL; + +# +# Bug #30666: Incorrect order when using range conditions on 2 tables or more +# + +CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); +CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL, + c22 INT DEFAULT NULL, + KEY(c21, c22)); +CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0, + c32 INT DEFAULT NULL, + c33 INT NOT NULL, + c34 INT UNSIGNED DEFAULT 0, + KEY (c33, c34, c32)); + +INSERT INTO t1 values (),(),(),(),(); +INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b; +INSERT INTO t3 VALUES (1, 1, 1, 0), + (2, 2, 0, 0), + (3, 3, 1, 0), + (4, 4, 0, 0), + (5, 5, 1, 0); + +# Show that ORDER BY produces the correct results order +SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND + t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND + t3.c33 = 1 AND t2.c22 in (1, 3) + ORDER BY c32; + +# Show that ORDER BY DESC produces the correct results order +SELECT c32 FROM t1, t2, t3 WHERE t1.c11 IN (1, 3, 5) AND + t3.c31 = t1.c11 AND t2.c21 = t1.c11 AND + t3.c33 = 1 AND t2.c22 in (1, 3) + ORDER BY c32 DESC; + +DROP TABLE t1, t2, t3; ########################################################################### --echo diff --git a/mysql-test/t/skip_grants.test b/mysql-test/t/skip_grants.test index 5e0fc65bc34..018366f705f 100644 --- a/mysql-test/t/skip_grants.test +++ b/mysql-test/t/skip_grants.test @@ -122,3 +122,15 @@ select count(*) from information_schema.COLUMN_PRIVILEGES; select count(*) from information_schema.SCHEMA_PRIVILEGES; select count(*) from information_schema.TABLE_PRIVILEGES; select count(*) from information_schema.USER_PRIVILEGES; + +# +# Bug #32020: loading udfs while --skip-grant-tables is enabled causes out of +# memory errors +# + +--error ER_CANT_INITIALIZE_UDF +CREATE FUNCTION a RETURNS STRING SONAME ''; +--error ER_SP_DOES_NOT_EXIST +DROP FUNCTION a; + +--echo End of 5.0 tests diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 95514fd773e..df51239b256 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2987,6 +2987,63 @@ SELECT (SELECT SUM(t1.a) FROM t2 WHERE a!=0) FROM t1; SELECT (SELECT SUM(t1.a) FROM t2 WHERE a=1) FROM t1; DROP TABLE t1,t2; +# +# Bug #31884: Assertion + crash in subquery in the SELECT clause. +# + +CREATE TABLE t1 (a1 INT, a2 INT); +CREATE TABLE t2 (b1 INT, b2 INT); + +INSERT INTO t1 VALUES (100, 200); +INSERT INTO t1 VALUES (101, 201); +INSERT INTO t2 VALUES (101, 201); +INSERT INTO t2 VALUES (103, 203); + +SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1; +DROP TABLE t1, t2; + +# +# Bug #28076: inconsistent binary/varbinary comparison +# + +CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5)); +INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43); + +SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1); +SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1); + +CREATE INDEX I1 ON t1 (s1); +CREATE INDEX I2 ON t1 (s2); + +SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1); +SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1); + +TRUNCATE t1; +INSERT INTO t1 VALUES (0x41,0x41); +SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1); + +DROP TABLE t1; + +CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1)); +CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2)); +CREATE TABLE t3 (a3 BINARY(2) default '0'); +INSERT INTO t1 VALUES (1),(2),(3),(4); +INSERT INTO t2 VALUES (1),(2),(3); +INSERT INTO t3 VALUES (1),(2),(3); +SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2; +SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1; +DROP TABLE t1,t2,t3; + +CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3)); +CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY); +CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY); +INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40); +INSERT INTO t2 VALUES (2), (3), (4), (5); +INSERT INTO t3 VALUES (10), (20), (30); +SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3; +SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3); +DROP TABLE t1, t2, t3; + --echo End of 5.0 tests. # diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 9f4634e1e17..1c98a0f8d29 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -2246,3 +2246,15 @@ select * from table_25411_a; drop table table_25411_a; drop table table_25411_b; +# +# Bug #31866: MySQL Server crashes on SHOW CREATE TRIGGER statement +# + +--disable-warnings +DROP TRIGGER IF EXISTS trg; +--enable-warnings + +--error ER_TRG_DOES_NOT_EXIST +SHOW CREATE TRIGGER trg; + +--echo End of 5.1 tests. diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test index 4d61350a613..807d1e6b01e 100644 --- a/mysql-test/t/type_decimal.test +++ b/mysql-test/t/type_decimal.test @@ -425,5 +425,57 @@ insert into t1 values (-0.123456,0.123456); select group_concat(f1),group_concat(f2) from t1; drop table t1; ---echo End of 5.0 tests +# +# BUG#31450 "Query causes error 1048" +# +create table t1 ( + ua_id decimal(22,0) not null, + ua_invited_by_id decimal(22,0) default NULL, + primary key(ua_id) +); +insert into t1 values (123, NULL), (456, NULL); +--echo this must not produce error 1048: +select * from t1 where ua_invited_by_id not in (select ua_id from t1); + +drop table t1; + +# +# Bug #30889: filesort and order by with float/numeric crashes server +# +--disable_warnings +DROP TABLE IF EXISTS t3; +DROP TABLE IF EXISTS t4; +--enable_warnings +CREATE TABLE t1( a NUMERIC, b INT ); +INSERT INTO t1 VALUES (123456, 40), (123456, 40); +SELECT TRUNCATE( a, b ) AS c FROM t1 ORDER BY c; +SELECT ROUND( a, b ) AS c FROM t1 ORDER BY c; +SELECT ROUND( a, 100 ) AS c FROM t1 ORDER BY c; + +CREATE TABLE t2( a NUMERIC, b INT ); +INSERT INTO t2 VALUES (123456, 100); +SELECT TRUNCATE( a, b ) AS c FROM t2 ORDER BY c; +SELECT ROUND( a, b ) AS c FROM t2 ORDER BY c; + +CREATE TABLE t3( a DECIMAL, b INT ); +INSERT INTO t3 VALUES (123456, 40), (123456, 40); +SELECT TRUNCATE( a, b ) AS c FROM t3 ORDER BY c; +SELECT ROUND( a, b ) AS c FROM t3 ORDER BY c; +SELECT ROUND( a, 100 ) AS c FROM t3 ORDER BY c; + +CREATE TABLE t4( a DECIMAL, b INT ); +INSERT INTO t4 VALUES (123456, 40), (123456, 40); +SELECT TRUNCATE( a, b ) AS c FROM t4 ORDER BY c; +SELECT ROUND( a, b ) AS c FROM t4 ORDER BY c; +SELECT ROUND( a, 100 ) AS c FROM t4 ORDER BY c; + +delete from t1; +INSERT INTO t1 VALUES (1234567890, 20), (999.99, 5); +show create table t1; + +select round(a,b) as c from t1 order by c; + +DROP TABLE t1, t2, t3, t4; + +--echo End of 5.0 tests diff --git a/sql-common/my_time.c b/sql-common/my_time.c index a1a3e0b6c77..48d1b198dfc 100644 --- a/sql-common/my_time.c +++ b/sql-common/my_time.c @@ -54,24 +54,24 @@ uint calc_days_in_year(uint year) 366 : 365); } -/* - Check datetime value for validity according to flags. - - SYNOPSIS - check_date() - ltime Date to check. - not_zero_date ltime is not the zero date - flags flags to check - was_cut set to 2 if value was truncated. - NOTE: This is not touched if value was not truncated - NOTES - Here we assume that year and month is ok ! +/** + @brief Check datetime value for validity according to flags. + + @param[in] ltime Date to check. + @param[in] not_zero_date ltime is not the zero date + @param[in] flags flags to check + (see str_to_datetime() flags in my_time.h) + @param[out] was_cut set to 2 if value was invalid according to flags. + (Feb 29 in non-leap etc.) This remains unchanged + if value is not invalid. + + @details Here we assume that year and month is ok! If month is 0 we allow any date. (This only happens if we allow zero date parts in str_to_datetime()) Disallow dates with zero year and non-zero month and/or day. - RETURN - 0 ok + @return + 0 OK 1 error */ @@ -117,9 +117,9 @@ my_bool check_date(const MYSQL_TIME *ltime, my_bool not_zero_date, TIME_NO_ZERO_IN_DATE Don't allow partial dates TIME_NO_ZERO_DATE Don't allow 0000-00-00 date TIME_INVALID_DATES Allow 2000-02-31 - was_cut 0 Value ok + was_cut 0 Value OK 1 If value was cut during conversion - 2 Date part was within ranges but date was wrong + 2 check_date(date,flags) considers date invalid DESCRIPTION At least the following formats are recogniced (based on number of digits) @@ -1084,7 +1084,7 @@ int my_TIME_to_str(const MYSQL_TIME *l_time, char *to) flags - flags to use in validating date, as in str_to_datetime() was_cut 0 Value ok 1 If value was cut during conversion - 2 Date part was within ranges but date was wrong + 2 check_date(date,flags) considers date invalid DESCRIPTION Convert a datetime value of formats YYMMDD, YYYYMMDD, YYMMDDHHMSS, diff --git a/sql/field.cc b/sql/field.cc index e04162d5e14..36ba6e6f12c 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -4616,6 +4616,8 @@ Field_timestamp::Field_timestamp(uchar *ptr_arg, uint32 len_arg, /* This timestamp has auto-update */ share->timestamp_field= this; flags|= TIMESTAMP_FLAG; + if (unireg_check != TIMESTAMP_DN_FIELD) + flags|= ON_UPDATE_NOW_FLAG; } } @@ -4629,6 +4631,8 @@ Field_timestamp::Field_timestamp(bool maybe_null_arg, { /* For 4.0 MYD and 4.0 InnoDB compatibility */ flags|= ZEROFILL_FLAG | UNSIGNED_FLAG; + if (unireg_check != TIMESTAMP_DN_FIELD) + flags|= ON_UPDATE_NOW_FLAG; } diff --git a/sql/ha_partition.cc b/sql/ha_partition.cc index b53a5e3da97..1400d9da753 100644 --- a/sql/ha_partition.cc +++ b/sql/ha_partition.cc @@ -2794,16 +2794,28 @@ exit: int ha_partition::update_row(const uchar *old_data, uchar *new_data) { uint32 new_part_id, old_part_id; - int error; + int error= 0; longlong func_value; + timestamp_auto_set_type orig_timestamp_type= table->timestamp_field_type; DBUG_ENTER("ha_partition::update_row"); + /* + We need to set timestamp field once before we calculate + the partition. Then we disable timestamp calculations + inside m_file[*]->update_row() methods + */ + if (orig_timestamp_type & TIMESTAMP_AUTO_SET_ON_UPDATE) + { + table->timestamp_field->set_time(); + table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET; + } + if ((error= get_parts_for_update(old_data, new_data, table->record[0], m_part_info, &old_part_id, &new_part_id, &func_value))) { m_part_info->err_value= func_value; - DBUG_RETURN(error); + goto exit; } /* @@ -2815,23 +2827,27 @@ int ha_partition::update_row(const uchar *old_data, uchar *new_data) if (new_part_id == old_part_id) { DBUG_PRINT("info", ("Update in partition %d", new_part_id)); - DBUG_RETURN(m_file[new_part_id]->update_row(old_data, new_data)); + error= m_file[new_part_id]->update_row(old_data, new_data); + goto exit; } else { DBUG_PRINT("info", ("Update from partition %d to partition %d", old_part_id, new_part_id)); if ((error= m_file[new_part_id]->write_row(new_data))) - DBUG_RETURN(error); + goto exit; if ((error= m_file[old_part_id]->delete_row(old_data))) { #ifdef IN_THE_FUTURE (void) m_file[new_part_id]->delete_last_inserted_row(new_data); #endif - DBUG_RETURN(error); + goto exit; } } - DBUG_RETURN(0); + +exit: + table->timestamp_field_type= orig_timestamp_type; + DBUG_RETURN(error); } diff --git a/sql/item.cc b/sql/item.cc index 95c8cd582cd..c8ec2206f02 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -3349,7 +3349,7 @@ static Item** find_field_in_group_list(Item *find_item, ORDER *group_list) if (cur_field->table_name && table_name) { /* If field_name is qualified by a table name. */ - if (strcmp(cur_field->table_name, table_name)) + if (my_strcasecmp(table_alias_charset, cur_field->table_name, table_name)) /* Same field names, different tables. */ return NULL; @@ -4649,7 +4649,7 @@ int Item::save_in_field(Field *field, bool no_conversions) my_decimal decimal_value; my_decimal *value= val_decimal(&decimal_value); if (null_value) - return set_field_to_null(field); + return set_field_to_null_with_conversions(field, no_conversions); field->set_notnull(); error=field->store_decimal(value); } @@ -6358,9 +6358,9 @@ bool field_is_equal_to_item(Field *field,Item *item) return result == field->val_real(); } -Item_cache* Item_cache::get_cache(Item_result type) +Item_cache* Item_cache::get_cache(const Item *item) { - switch (type) { + switch (item->result_type()) { case INT_RESULT: return new Item_cache_int(); case REAL_RESULT: @@ -6368,7 +6368,7 @@ Item_cache* Item_cache::get_cache(Item_result type) case DECIMAL_RESULT: return new Item_cache_decimal(); case STRING_RESULT: - return new Item_cache_str(); + return new Item_cache_str(item); case ROW_RESULT: return new Item_cache_row(); default: @@ -6546,6 +6546,14 @@ my_decimal *Item_cache_str::val_decimal(my_decimal *decimal_val) } +int Item_cache_str::save_in_field(Field *field, bool no_conversions) +{ + int res= Item_cache::save_in_field(field, no_conversions); + return (is_varbinary && field->type() == MYSQL_TYPE_STRING && + value->length() < field->field_length) ? 1 : res; +} + + bool Item_cache_row::allocate(uint num) { item_count= num; @@ -6564,7 +6572,7 @@ bool Item_cache_row::setup(Item * item) { Item *el= item->element_index(i); Item_cache *tmp; - if (!(tmp= values[i]= Item_cache::get_cache(el->result_type()))) + if (!(tmp= values[i]= Item_cache::get_cache(el))) return 1; tmp->setup(el); } diff --git a/sql/item.h b/sql/item.h index c8294eabb6a..6990d9ed021 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1719,7 +1719,7 @@ public: double val_real() { DBUG_ASSERT(fixed == 1); return ulonglong2double((ulonglong)value); } String *val_str(String*); - Item *clone_item() { return new Item_uint(name,max_length); } + Item *clone_item() { return new Item_uint(name, value, max_length); } int save_in_field(Field *field, bool no_conversions); void print(String *str); Item_num *neg (); @@ -2637,7 +2637,7 @@ public: }; virtual void store(Item *)= 0; enum Type type() const { return CACHE_ITEM; } - static Item_cache* get_cache(Item_result type); + static Item_cache* get_cache(const Item *item); table_map used_tables() const { return used_table_map; } virtual void keep_array() {} // to prevent drop fixed flag (no need parent cleanup call) @@ -2699,9 +2699,16 @@ class Item_cache_str: public Item_cache { char buffer[STRING_BUFFER_USUAL_SIZE]; String *value, value_buff; + bool is_varbinary; + public: - Item_cache_str(): Item_cache(), value(0) { } - + Item_cache_str(const Item *item) : + Item_cache(), value(0), + is_varbinary(item->type() == FIELD_ITEM && + ((const Item_field *) item)->field->type() == + MYSQL_TYPE_VARCHAR && + !((const Item_field *) item)->field->has_charset()) + {} void store(Item *item); double val_real(); longlong val_int(); @@ -2709,6 +2716,7 @@ public: my_decimal *val_decimal(my_decimal *); enum Item_result result_type() const { return STRING_RESULT; } CHARSET_INFO *charset() const { return value->charset(); }; + int save_in_field(Field *field, bool no_conversions); }; class Item_cache_row: public Item_cache diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 04e0011f1a7..639788d65bc 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -604,26 +604,26 @@ int Arg_comparator::set_compare_func(Item_bool_func2 *item, Item_result type) } -/* - Convert date provided in a string to the int representation. - - SYNOPSIS - get_date_from_str() - thd Thread handle - str a string to convert - warn_type type of the timestamp for issuing the warning - warn_name field name for issuing the warning - error_arg [out] TRUE if string isn't a DATETIME or clipping occur - - DESCRIPTION - Convert date provided in the string str to the int representation. - if the string contains wrong date or doesn't contain it at all - then the warning is issued and TRUE returned in the error_arg argument. - The warn_type and the warn_name arguments are used as the name and the - type of the field when issuing the warning. - - RETURN - converted value. +/** + @brief Convert date provided in a string to the int representation. + + @param[in] thd thread handle + @param[in] str a string to convert + @param[in] warn_type type of the timestamp for issuing the warning + @param[in] warn_name field name for issuing the warning + @param[out] error_arg could not extract a DATE or DATETIME + + @details Convert date provided in the string str to the int + representation. If the string contains wrong date or doesn't + contain it at all then a warning is issued. The warn_type and + the warn_name arguments are used as the name and the type of the + field when issuing the warning. If any input was discarded + (trailing or non-timestampy characters), was_cut will be non-zero. + was_type will return the type str_to_datetime() could correctly + extract. + + @return + converted value. 0 on error and on zero-dates -- check 'failure' */ static ulonglong @@ -634,26 +634,33 @@ get_date_from_str(THD *thd, String *str, timestamp_type warn_type, int error; MYSQL_TIME l_time; enum_mysql_timestamp_type ret; - *error_arg= TRUE; ret= str_to_datetime(str->ptr(), str->length(), &l_time, (TIME_FUZZY_DATE | MODE_INVALID_DATES | (thd->variables.sql_mode & (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE))), &error); - if ((ret == MYSQL_TIMESTAMP_DATETIME || ret == MYSQL_TIMESTAMP_DATE)) + + if (ret == MYSQL_TIMESTAMP_DATETIME || ret == MYSQL_TIMESTAMP_DATE) { - value= TIME_to_ulonglong_datetime(&l_time); + /* + Do not return yet, we may still want to throw a "trailing garbage" + warning. + */ *error_arg= FALSE; + value= TIME_to_ulonglong_datetime(&l_time); } - - if (error || *error_arg) + else { + *error_arg= TRUE; + error= 1; /* force warning */ + } + + if (error > 0) make_truncated_value_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, str->ptr(), str->length(), warn_type, warn_name); - *error_arg= TRUE; - } + return value; } @@ -954,6 +961,12 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg, timestamp_type t_type= f_type == MYSQL_TYPE_DATE ? MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME; value= get_date_from_str(thd, str, t_type, warn_item->name, &error); + /* + If str did not contain a valid date according to the current + SQL_MODE, get_date_from_str() has already thrown a warning, + and we don't want to throw NULL on invalid date (see 5.2.6 + "SQL modes" in the manual), so we're done here. + */ } /* Do not cache GET_USER_VAR() function as its const_item() may return TRUE @@ -1425,7 +1438,7 @@ longlong Item_func_truth::val_int() bool Item_in_optimizer::fix_left(THD *thd, Item **ref) { if (!args[0]->fixed && args[0]->fix_fields(thd, args) || - !cache && !(cache= Item_cache::get_cache(args[0]->result_type()))) + !cache && !(cache= Item_cache::get_cache(args[0]))) return 1; cache->setup(args[0]); diff --git a/sql/item_func.cc b/sql/item_func.cc index ec0ecc89394..809319d9056 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -2000,6 +2000,7 @@ void Item_func_round::fix_length_and_dec() case DECIMAL_RESULT: { hybrid_type= DECIMAL_RESULT; + decimals_to_set= min(DECIMAL_MAX_SCALE, decimals_to_set); int decimals_delta= args[0]->decimals - decimals_to_set; int precision= args[0]->decimal_precision(); int length_increase= ((decimals_delta <= 0) || truncate) ? 0:1; @@ -2106,7 +2107,7 @@ my_decimal *Item_func_round::decimal_op(my_decimal *decimal_value) longlong dec= args[1]->val_int(); if (dec > 0 || (dec < 0 && args[1]->unsigned_flag)) { - dec= min((ulonglong) dec, DECIMAL_MAX_SCALE); + dec= min((ulonglong) dec, decimals); decimals= (uint8) dec; // to get correct output } else if (dec < INT_MIN) diff --git a/sql/item_func.h b/sql/item_func.h index b2ba57af817..4d7c96a18c1 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -236,9 +236,40 @@ public: my_decimal *val_decimal(my_decimal *); String *val_str(String*str); + /** + @brief Performs the operation that this functions implements when the + result type is INT. + + @return The result of the operation. + */ virtual longlong int_op()= 0; + + /** + @brief Performs the operation that this functions implements when the + result type is REAL. + + @return The result of the operation. + */ virtual double real_op()= 0; + + /** + @brief Performs the operation that this functions implements when the + result type is DECIMAL. + + @param A pointer where the DECIMAL value will be allocated. + @return + - 0 If the result is NULL + - The same pointer it was given, with the area initialized to the + result of the operation. + */ virtual my_decimal *decimal_op(my_decimal *)= 0; + + /** + @brief Performs the operation that this functions implements when the + result type is a string type. + + @return The result of the operation. + */ virtual String *str_op(String *)= 0; bool is_null() { update_null_value(); return null_value; } }; diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h index 1a264fa2c01..3608568bd5e 100644 --- a/sql/item_strfunc.h +++ b/sql/item_strfunc.h @@ -558,7 +558,8 @@ public: void fix_length_and_dec() { collation.set(default_charset()); - max_length= 64; + max_length=64; + maybe_null= 1; } }; @@ -656,7 +657,7 @@ public: } String* val_str(String* str); const char *func_name() const { return "inet_ntoa"; } - void fix_length_and_dec() { decimals = 0; max_length=3*8+7; } + void fix_length_and_dec() { decimals = 0; max_length=3*8+7; maybe_null=1;} }; class Item_func_quote :public Item_str_func diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 8eb7421f854..423f067e54d 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1760,7 +1760,7 @@ void subselect_engine::set_row(List<Item> &item_list, Item_cache **row) item->decimals= sel_item->decimals; item->unsigned_flag= sel_item->unsigned_flag; maybe_null= sel_item->maybe_null; - if (!(row[i]= Item_cache::get_cache(res_type))) + if (!(row[i]= Item_cache::get_cache(sel_item))) return; row[i]->setup(sel_item); } @@ -2222,6 +2222,7 @@ int subselect_indexsubquery_engine::exec() ((Item_in_subselect *) item)->value= 0; empty_result_set= TRUE; null_keypart= 0; + table->status= 0; if (check_null) { @@ -2234,6 +2235,16 @@ int subselect_indexsubquery_engine::exec() if (copy_ref_key()) DBUG_RETURN(1); + if (table->status) + { + /* + We know that there will be no rows even if we scan. + Can be set in copy_ref_key. + */ + ((Item_in_subselect *) item)->value= 0; + DBUG_RETURN(0); + } + if (null_keypart) DBUG_RETURN(scan_table()); diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 51ab7b8ad42..de4b1cbdc06 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -307,6 +307,7 @@ public: double val_real(); String *val_str(String*); my_decimal *val_decimal(my_decimal *); + void update_null_value () { (void) val_bool(); } bool val_bool(); void top_level_item() { abort_on_null=1; } inline bool is_top_level_item() { return abort_on_null; } diff --git a/sql/item_sum.cc b/sql/item_sum.cc index 0e1daadd8e7..a7574cf4d60 100644 --- a/sql/item_sum.cc +++ b/sql/item_sum.cc @@ -3427,7 +3427,7 @@ String* Item_func_group_concat::val_str(String* str) DBUG_ASSERT(fixed == 1); if (null_value) return 0; - if (!result.length() && tree) + if (no_appended && tree) /* Tree is used for sorting as in ORDER BY */ tree_walk(tree, (tree_walk_action)&dump_leaf_key, (void*)this, left_root_right); diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc index 8240b7178c7..e7d513e9d6a 100644 --- a/sql/item_timefunc.cc +++ b/sql/item_timefunc.cc @@ -2586,6 +2586,13 @@ bool Item_date_typecast::get_date(MYSQL_TIME *ltime, uint fuzzy_date) } +bool Item_date_typecast::get_time(MYSQL_TIME *ltime) +{ + bzero((char *)ltime, sizeof(MYSQL_TIME)); + return args[0]->null_value; +} + + String *Item_date_typecast::val_str(String *str) { DBUG_ASSERT(fixed == 1); diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h index 844c3d84313..9be7e97db49 100644 --- a/sql/item_timefunc.h +++ b/sql/item_timefunc.h @@ -778,6 +778,7 @@ public: const char *func_name() const { return "cast_as_date"; } String *val_str(String *str); bool get_date(MYSQL_TIME *ltime, uint fuzzy_date); + bool get_time(MYSQL_TIME *ltime); const char *cast_type() const { return "date"; } enum_field_types field_type() const { return MYSQL_TYPE_DATE; } Field *tmp_table_field(TABLE *table) diff --git a/sql/opt_range.cc b/sql/opt_range.cc index b89e8b3dcbb..c67687b2bde 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -7350,6 +7350,9 @@ check_quick_keys(PARAM *param, uint idx, SEL_ARG *key_tree, tmp_max_flag= max_key_flag | key_tree->max_flag; } + if (unlikely(param->thd->killed != 0)) + return HA_POS_ERROR; + keynr=param->real_keynr[idx]; param->range_count++; if (!tmp_min_flag && ! tmp_max_flag && diff --git a/sql/opt_range.h b/sql/opt_range.h index dd219129167..4f5cce28bf2 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -24,9 +24,14 @@ #endif typedef struct st_key_part { - uint16 key,part, store_length, length; + uint16 key,part; + /* See KEY_PART_INFO for meaning of the next two: */ + uint16 store_length, length; uint8 null_bit; - /* Keypart flags (0 if partition pruning is used) */ + /* + Keypart flags (0 when this structure is used by partition pruning code + for fake partitioning index description) + */ uint8 flag; Field *field; Field::imagetype image_type; diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc index 6836c53db4e..12ad504d738 100644 --- a/sql/opt_sum.cc +++ b/sql/opt_sum.cc @@ -295,14 +295,15 @@ int opt_sum_query(TABLE_LIST *tables, List<Item> &all_fields,COND *conds) Check if case 1 from above holds. If it does, we should read the skipped tuple. */ - if (ref.key_buff[prefix_len] == 1 && - /* + if (item_field->field->real_maybe_null() && + ref.key_buff[prefix_len] == 1 && + /* Last keypart (i.e. the argument to MIN) is set to NULL by find_key_for_maxmin only if all other keyparts are bound to constants in a conjunction of equalities. Hence, we can detect this by checking only if the last keypart is NULL. - */ + */ (error == HA_ERR_KEY_NOT_FOUND || key_cmp_if_same(table, ref.key_buff, ref.key, prefix_len))) { diff --git a/sql/set_var.cc b/sql/set_var.cc index 11874b1020c..d408b2de64e 100644 --- a/sql/set_var.cc +++ b/sql/set_var.cc @@ -641,8 +641,14 @@ static sys_var_const_str sys_license(&vars, "license", STRINGIFY_ARG(LICENSE)); /* Global variables which enable|disable logging */ static sys_var_log_state sys_var_general_log(&vars, "general_log", &opt_log, QUERY_LOG_GENERAL); +/* Synonym of "general_log" for consistency with SHOW VARIABLES output */ +static sys_var_log_state sys_var_log(&vars, "log", &opt_log, + QUERY_LOG_GENERAL); static sys_var_log_state sys_var_slow_query_log(&vars, "slow_query_log", &opt_slow_log, QUERY_LOG_SLOW); +/* Synonym of "slow_query_log" for consistency with SHOW VARIABLES output */ +static sys_var_log_state sys_var_log_slow(&vars, "log_slow_queries", + &opt_slow_log, QUERY_LOG_SLOW); sys_var_str sys_var_general_log_path(&vars, "general_log_file", sys_check_log_path, sys_update_general_log_path, sys_default_general_log_path, @@ -678,10 +684,8 @@ static SHOW_VAR fixed_vars[]= { #ifdef HAVE_MLOCKALL {"locked_in_memory", (char*) &locked_in_memory, SHOW_MY_BOOL}, #endif - {"log", (char*) &opt_log, SHOW_MY_BOOL}, {"log_bin", (char*) &opt_bin_log, SHOW_BOOL}, {"log_error", (char*) log_error_file, SHOW_CHAR}, - {"log_slow_queries", (char*) &opt_slow_log, SHOW_MY_BOOL}, {"lower_case_file_system", (char*) &lower_case_file_system, SHOW_MY_BOOL}, {"lower_case_table_names", (char*) &lower_case_table_names, SHOW_INT}, {"myisam_recover_options", (char*) &myisam_recover_options_str, SHOW_CHAR_PTR}, diff --git a/sql/share/errmsg.txt b/sql/share/errmsg.txt index 257cea7c27b..02e20ad7c5c 100644 --- a/sql/share/errmsg.txt +++ b/sql/share/errmsg.txt @@ -5658,6 +5658,8 @@ ER_TOO_HIGH_LEVEL_OF_NESTING_FOR_SELECT eng "Too high level of nesting for select" ER_NAME_BECOMES_EMPTY eng "Name '%-.64s' has become ''" +ER_AMBIGUOUS_FIELD_TERM + eng "First character of the FIELDS TERMINATED string is ambiguous; please use non-optional and non-empty FIELDS ENCLOSED BY" ER_FOREIGN_SERVER_EXISTS eng "The foreign server, %s, you are trying to create already exists." ER_FOREIGN_SERVER_DOESNT_EXIST diff --git a/sql/sp_rcontext.cc b/sql/sp_rcontext.cc index 60a0c962c28..70a043c4fac 100644 --- a/sql/sp_rcontext.cc +++ b/sql/sp_rcontext.cc @@ -503,14 +503,14 @@ sp_cursor::fetch(THD *thd, List<struct sp_variable> *vars) */ Item_cache * -sp_rcontext::create_case_expr_holder(THD *thd, Item_result result_type) +sp_rcontext::create_case_expr_holder(THD *thd, const Item *item) { Item_cache *holder; Query_arena current_arena; thd->set_n_backup_active_arena(thd->spcont->callers_arena, ¤t_arena); - holder= Item_cache::get_cache(result_type); + holder= Item_cache::get_cache(item); thd->restore_active_arena(thd->spcont->callers_arena, ¤t_arena); @@ -559,7 +559,7 @@ sp_rcontext::set_case_expr(THD *thd, int case_expr_id, Item **case_expr_item_ptr case_expr_item->result_type()) { m_case_expr_holders[case_expr_id]= - create_case_expr_holder(thd, case_expr_item->result_type()); + create_case_expr_holder(thd, case_expr_item); } m_case_expr_holders[case_expr_id]->store(case_expr_item); diff --git a/sql/sp_rcontext.h b/sql/sp_rcontext.h index 0104b71a648..43102cfeeb2 100644 --- a/sql/sp_rcontext.h +++ b/sql/sp_rcontext.h @@ -261,7 +261,7 @@ private: bool init_var_table(THD *thd); bool init_var_items(); - Item_cache *create_case_expr_holder(THD *thd, Item_result result_type); + Item_cache *create_case_expr_holder(THD *thd, const Item *item); int set_variable(THD *thd, Field *field, Item **value); }; // class sp_rcontext : public Sql_alloc diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 66d0cda2155..6896ccd1b8c 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -5163,7 +5163,9 @@ find_field_in_tables(THD *thd, Item_ident *item, { Field *cur_field= find_field_in_table_ref(thd, cur_table, name, length, item->name, db, table_name, ref, - check_privileges, + (thd->lex->sql_command == + SQLCOM_SHOW_FIELDS) + ? false : check_privileges, allow_rowid, &(item->cached_field_index), register_tree_change, @@ -5357,7 +5359,8 @@ find_item_in_list(Item *find, List<Item> &items, uint *counter, if (item_field->field_name && item_field->table_name && !my_strcasecmp(system_charset_info, item_field->field_name, field_name) && - !strcmp(item_field->table_name, table_name) && + !my_strcasecmp(table_alias_charset, item_field->table_name, + table_name) && (!db_name || (item_field->db_name && !strcmp(item_field->db_name, db_name)))) { diff --git a/sql/sql_class.cc b/sql/sql_class.cc index a904023cbff..bf8413e8627 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -1554,6 +1554,7 @@ int select_export::prepare(List<Item> &list, SELECT_LEX_UNIT *u) { bool blob_flag=0; + bool string_results= FALSE, non_string_results= FALSE; unit= u; if ((uint) strlen(exchange->file_name) + NAME_LEN >= FN_REFLEN) strmake(path,exchange->file_name,FN_REFLEN-1); @@ -1571,13 +1572,18 @@ select_export::prepare(List<Item> &list, SELECT_LEX_UNIT *u) blob_flag=1; break; } + if (item->result_type() == STRING_RESULT) + string_results= TRUE; + else + non_string_results= TRUE; } } field_term_length=exchange->field_term->length(); + field_term_char= field_term_length ? (*exchange->field_term)[0] : INT_MAX; if (!exchange->line_term->length()) exchange->line_term=exchange->field_term; // Use this if it exists field_sep_char= (exchange->enclosed->length() ? (*exchange->enclosed)[0] : - field_term_length ? (*exchange->field_term)[0] : INT_MAX); + field_term_char); escape_char= (exchange->escaped->length() ? (*exchange->escaped)[0] : -1); is_ambiguous_field_sep= test(strchr(ESCAPE_CHARS, field_sep_char)); is_unsafe_field_sep= test(strchr(NUMERIC_CHARS, field_sep_char)); @@ -1589,12 +1595,25 @@ select_export::prepare(List<Item> &list, SELECT_LEX_UNIT *u) exchange->opt_enclosed=1; // A little quicker loop fixed_row_size= (!field_term_length && !exchange->enclosed->length() && !blob_flag); + if ((is_ambiguous_field_sep && exchange->enclosed->is_empty() && + (string_results || is_unsafe_field_sep)) || + (exchange->opt_enclosed && non_string_results && + field_term_length && strchr(NUMERIC_CHARS, field_term_char))) + { + push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_AMBIGUOUS_FIELD_TERM, ER(ER_AMBIGUOUS_FIELD_TERM)); + is_ambiguous_field_term= TRUE; + } + else + is_ambiguous_field_term= FALSE; + return 0; } #define NEED_ESCAPING(x) ((int) (uchar) (x) == escape_char || \ - (int) (uchar) (x) == field_sep_char || \ + (enclosed ? (int) (uchar) (x) == field_sep_char \ + : (int) (uchar) (x) == field_term_char) || \ (int) (uchar) (x) == line_sep_char || \ !(x)) @@ -1623,8 +1642,10 @@ bool select_export::send_data(List<Item> &items) while ((item=li++)) { Item_result result_type=item->result_type(); + bool enclosed = (exchange->enclosed->length() && + (!exchange->opt_enclosed || result_type == STRING_RESULT)); res=item->str_result(&tmp); - if (res && (!exchange->opt_enclosed || result_type == STRING_RESULT)) + if (res && enclosed) { if (my_b_write(&cache,(uchar*) exchange->enclosed->ptr(), exchange->enclosed->length())) @@ -1715,11 +1736,16 @@ bool select_export::send_data(List<Item> &items) DBUG_ASSERT before the loop makes that sure. */ - if (NEED_ESCAPING(*pos) || - (check_second_byte && - my_mbcharlen(character_set_client, (uchar) *pos) == 2 && - pos + 1 < end && - NEED_ESCAPING(pos[1]))) + if ((NEED_ESCAPING(*pos) || + (check_second_byte && + my_mbcharlen(character_set_client, (uchar) *pos) == 2 && + pos + 1 < end && + NEED_ESCAPING(pos[1]))) && + /* + Don't escape field_term_char by doubling - doubling is only + valid for ENCLOSED BY characters: + */ + (enclosed || !is_ambiguous_field_term || *pos != field_term_char)) { char tmp_buff[2]; tmp_buff[0]= ((int) *pos == field_sep_char && @@ -1758,7 +1784,7 @@ bool select_export::send_data(List<Item> &items) goto err; } } - if (res && (!exchange->opt_enclosed || result_type == STRING_RESULT)) + if (res && enclosed) { if (my_b_write(&cache, (uchar*) exchange->enclosed->ptr(), exchange->enclosed->length())) @@ -1887,7 +1913,7 @@ bool select_max_min_finder_subselect::send_data(List<Item> &items) { if (!cache) { - cache= Item_cache::get_cache(val_item->result_type()); + cache= Item_cache::get_cache(val_item); switch (val_item->result_type()) { case REAL_RESULT: diff --git a/sql/sql_class.h b/sql/sql_class.h index 632c440266f..9dd3856dfa8 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -1366,9 +1366,20 @@ public: ulonglong limit_found_rows; ulonglong options; /* Bitmap of states */ - longlong row_count_func; /* For the ROW_COUNT() function */ - ha_rows cuted_fields, - sent_row_count, examined_row_count; + longlong row_count_func; /* For the ROW_COUNT() function */ + ha_rows cuted_fields; + + /* + number of rows we actually sent to the client, including "synthetic" + rows in ROLLUP etc. + */ + ha_rows sent_row_count; + + /* + number of rows we read, sent or not, including in create_sort_index() + */ + ha_rows examined_row_count; + /* The set of those tables whose fields are referenced in all subqueries of the query. @@ -1404,7 +1415,11 @@ public: /* Statement id is thread-wide. This counter is used to generate ids */ ulong statement_id_counter; ulong rand_saved_seed1, rand_saved_seed2; - ulong row_count; // Row counter, mainly for errors and warnings + /* + Row counter, mainly for errors and warnings. Not increased in + create_sort_index(); may differ from examined_row_count. + */ + ulong row_count; pthread_t real_id; /* For debugging */ my_thread_id thread_id; uint tmp_table, global_read_lock; @@ -2098,6 +2113,7 @@ public: class select_export :public select_to_file { uint field_term_length; int field_sep_char,escape_char,line_sep_char; + int field_term_char; // first char of FIELDS TERMINATED BY or MAX_INT /* The is_ambiguous_field_sep field is true if a value of the field_sep_char field is one of the 'n', 't', 'r' etc characters @@ -2105,6 +2121,12 @@ class select_export :public select_to_file { */ bool is_ambiguous_field_sep; /* + The is_ambiguous_field_term is true if field_sep_char contains the first + char of the FIELDS TERMINATED BY (ENCLOSED BY is empty), and items can + contain this character. + */ + bool is_ambiguous_field_term; + /* The is_unsafe_field_sep field is true if a value of the field_sep_char field is one of the '0'..'9', '+', '-', '.' and 'e' characters (see the NUMERIC_CHARS constant value). diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc index ad9eec1906a..a06ad0a4612 100644 --- a/sql/sql_partition.cc +++ b/sql/sql_partition.cc @@ -5044,7 +5044,10 @@ the generated partition syntax in a correct manner. *partition_changed= TRUE; } if (create_info->db_type == partition_hton) - part_info->default_engine_type= table->part_info->default_engine_type; + { + if (!part_info->default_engine_type) + part_info->default_engine_type= table->part_info->default_engine_type; + } else part_info->default_engine_type= create_info->db_type; if (check_native_partitioned(create_info, &is_native_partitioned, diff --git a/sql/sql_plugin.cc b/sql/sql_plugin.cc index c8d9116f196..ef4c63381e2 100644 --- a/sql/sql_plugin.cc +++ b/sql/sql_plugin.cc @@ -1945,7 +1945,7 @@ static int check_func_enum(THD *thd, struct st_mysql_sys_var *var, length= sizeof(buff); if (!(str= value->val_str(value, buff, &length))) goto err; - if ((result= find_type(typelib, str, length, 1)-1) < 0) + if ((result= (long)find_type(typelib, str, length, 1)-1) < 0) { strvalue= str; goto err; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 862948e48a4..60f004cda71 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -571,11 +571,12 @@ JOIN::prepare(Item ***rref_pointer_array, /* - Check if one one uses a not constant column with group functions - and no GROUP BY. + Check if there are references to un-aggregated columns when computing + aggregate functions with implicit grouping (there is no GROUP BY). TODO: Add check of calculation of GROUP functions and fields: SELECT COUNT(*)+table.col1 from table1; */ + if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY) { if (!group_list) { @@ -589,6 +590,13 @@ JOIN::prepare(Item ***rref_pointer_array, else if (!(flag & 2) && !item->const_during_execution()) flag|=2; } + if (having) + { + if (having->with_sum_func) + flag |= 1; + else if (!having->const_during_execution()) + flag |= 2; + } if (flag == 3) { my_message(ER_MIX_OF_GROUP_FUNC_AND_FIELDS, @@ -1073,10 +1081,19 @@ JOIN::optimize() We have found that grouping can be removed since groups correspond to only one row anyway, but we still have to guarantee correct result order. The line below effectively rewrites the query from GROUP BY - <fields> to ORDER BY <fields>. One exception is if skip_sort_order is - set (see above), then we can simply skip GROUP BY. - */ - order= skip_sort_order ? 0 : group_list; + <fields> to ORDER BY <fields>. There are two exceptions: + - if skip_sort_order is set (see above), then we can simply skip + GROUP BY; + - we can only rewrite ORDER BY if the ORDER BY fields are 'compatible' + with the GROUP BY ones, i.e. either one is a prefix of another. + We only check if the ORDER BY is a prefix of GROUP BY. In this case + test_if_subpart() copies the ASC/DESC attributes from the original + ORDER BY fields. + If GROUP BY is a prefix of ORDER BY, then it is safe to leave + 'order' as is. + */ + if (!order || test_if_subpart(group_list, order)) + order= skip_sort_order ? 0 : group_list; /* If we have an IGNORE INDEX FOR GROUP BY(fields) clause, this must be rewritten to IGNORE INDEX FOR ORDER BY(fields). @@ -6282,10 +6299,9 @@ make_join_readinfo(JOIN *join, ulonglong options) ordered. If there is a temp table the ordering is done as a last operation and doesn't prevent join cache usage. */ - if (!ordered_set && !join->need_tmp && - ((table == join->sort_by_table && - (!join->order || join->skip_sort_order)) || - (join->sort_by_table == (TABLE *) 1 && i != join->const_tables))) + if (!ordered_set && !join->need_tmp && + (table == join->sort_by_table || + (join->sort_by_table == (TABLE *) 1 && i != join->const_tables))) ordered_set= 1; tab->sorted= sorted; @@ -10648,6 +10664,15 @@ do_select(JOIN *join,List<Item> *fields,TABLE *table,Procedure *procedure) error= (*end_select)(join, 0, 0); if (error == NESTED_LOOP_OK || error == NESTED_LOOP_QUERY_LIMIT) error= (*end_select)(join, 0, 1); + + /* + If we don't go through evaluate_join_record(), do the counting + here. join->send_records is increased on success in end_send(), + so we don't touch it here. + */ + join->examined_rows++; + join->thd->row_count++; + DBUG_ASSERT(join->examined_rows <= 1); } else if (join->send_row_on_empty_set()) { @@ -15818,6 +15843,10 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, /* Add "rows" field to item_list. */ if (table_list->schema_table) { + /* in_rows */ + if (join->thd->lex->describe & DESCRIBE_EXTENDED) + item_list.push_back(item_null); + /* rows */ item_list.push_back(item_null); } else @@ -16196,8 +16225,21 @@ void TABLE_LIST::print(THD *thd, String *str) } if (my_strcasecmp(table_alias_charset, cmp_name, alias)) { + char t_alias_buff[MAX_ALIAS_NAME]; + const char *t_alias= alias; + str->append(' '); - append_identifier(thd, str, alias, strlen(alias)); + if (lower_case_table_names== 1) + { + if (alias && alias[0]) + { + strmov(t_alias_buff, alias); + my_casedn_str(files_charset_info, t_alias_buff); + t_alias= t_alias_buff; + } + } + + append_identifier(thd, str, t_alias, strlen(t_alias)); } if (index_hints) diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 049c050c288..a347482859f 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -3777,8 +3777,11 @@ static int get_schema_column_record(THD *thd, TABLE_LIST *tables, end= tmp; if (field->unireg_check == Field::NEXT_NUMBER) - end=strmov(tmp,"auto_increment"); - table->field[16]->store(tmp, (uint) (end-tmp), cs); + table->field[16]->store(STRING_WITH_LEN("auto_increment"), cs); + if (show_table->timestamp_field == field && + field->unireg_check != Field::TIMESTAMP_DN_FIELD) + table->field[16]->store(STRING_WITH_LEN("on update CURRENT_TIMESTAMP"), + cs); table->field[18]->store(field->comment.str, field->comment.length, cs); if (schema_table_store_record(thd, table)) @@ -5981,7 +5984,7 @@ ST_FIELD_INFO columns_fields_info[]= {"COLLATION_NAME", 64, MYSQL_TYPE_STRING, 0, 1, "Collation", OPEN_FRM_ONLY}, {"COLUMN_TYPE", 65535, MYSQL_TYPE_STRING, 0, 0, "Type", OPEN_FRM_ONLY}, {"COLUMN_KEY", 3, MYSQL_TYPE_STRING, 0, 0, "Key", OPEN_FRM_ONLY}, - {"EXTRA", 20, MYSQL_TYPE_STRING, 0, 0, "Extra", OPEN_FRM_ONLY}, + {"EXTRA", 27, MYSQL_TYPE_STRING, 0, 0, "Extra", OPEN_FRM_ONLY}, {"PRIVILEGES", 80, MYSQL_TYPE_STRING, 0, 0, "Privileges", OPEN_FRM_ONLY}, {"COLUMN_COMMENT", 255, MYSQL_TYPE_STRING, 0, 0, "Comment", OPEN_FRM_ONLY}, {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE} @@ -6862,6 +6865,9 @@ bool show_create_trigger(THD *thd, const sp_name *trg_name) { TABLE_LIST *lst= get_trigger_table(thd, trg_name); + if (!lst) + return TRUE; + /* Open the table by name in order to load Table_triggers_list object. diff --git a/sql/sql_udf.cc b/sql/sql_udf.cc index 18511063117..30e8829d764 100644 --- a/sql/sql_udf.cc +++ b/sql/sql_udf.cc @@ -394,7 +394,12 @@ int mysql_create_function(THD *thd,udf_func *udf) if (!initialized) { - my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0)); + if (opt_noacl) + my_error(ER_CANT_INITIALIZE_UDF, MYF(0), + udf->name.str, + "UDFs are unavailable with the --skip-grant-tables option"); + else + my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0)); DBUG_RETURN(1); } @@ -517,7 +522,10 @@ int mysql_drop_function(THD *thd,const LEX_STRING *udf_name) if (!initialized) { - my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0)); + if (opt_noacl) + my_error(ER_FUNCTION_NOT_DEFINED, MYF(0), udf_name->str); + else + my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0)); DBUG_RETURN(1); } diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 04af253fec9..66316d1ab5c 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -6323,6 +6323,12 @@ select_item: MYSQL_YYABORT; if ($4.str) { + if (Lex->sql_command == SQLCOM_CREATE_VIEW && + check_column_name($4.str)) + { + my_error(ER_WRONG_COLUMN_NAME, MYF(0), $4.str); + MYSQL_YYABORT; + } $2->is_autogenerated_name= FALSE; $2->set_name($4.str, $4.length, system_charset_info); } diff --git a/storage/archive/azio.c b/storage/archive/azio.c index c04749444cb..2cf0fe114d3 100644 --- a/storage/archive/azio.c +++ b/storage/archive/azio.c @@ -681,8 +681,8 @@ my_off_t azseek (s, offset, whence) /* There was a zmemzero here if inbuf was null -Brian */ while (offset > 0) { - uInt size = AZ_BUFSIZE_WRITE; - if (offset < AZ_BUFSIZE_WRITE) size = (uInt)offset; + uInt size = AZ_BUFSIZE_READ; + if (offset < AZ_BUFSIZE_READ) size = (uInt)offset; size = azwrite(s, s->inbuf, size); if (size == 0) return -1L; @@ -725,11 +725,11 @@ my_off_t azseek (s, offset, whence) } while (offset > 0) { int error; - unsigned int size = AZ_BUFSIZE_READ; - if (offset < AZ_BUFSIZE_READ) size = (int)offset; + unsigned int size = AZ_BUFSIZE_WRITE; + if (offset < AZ_BUFSIZE_WRITE) size = (int)offset; size = azread(s, s->outbuf, size, &error); - if (error <= 0) return -1L; + if (error < 0) return -1L; offset -= size; } return s->out; diff --git a/storage/archive/ha_archive.cc b/storage/archive/ha_archive.cc index 6696eac2fbb..84298e785d1 100644 --- a/storage/archive/ha_archive.cc +++ b/storage/archive/ha_archive.cc @@ -1241,8 +1241,8 @@ int ha_archive::rnd_pos(uchar * buf, uchar *pos) DBUG_ENTER("ha_archive::rnd_pos"); ha_statistic_increment(&SSV::ha_read_rnd_next_count); current_position= (my_off_t)my_get_ptr(pos, ref_length); - (void)azseek(&archive, current_position, SEEK_SET); - + if (azseek(&archive, current_position, SEEK_SET) == (my_off_t)(-1L)) + DBUG_RETURN(HA_ERR_CRASHED_ON_USAGE); DBUG_RETURN(get_row(&archive, buf)); } diff --git a/storage/example/ha_example.cc b/storage/example/ha_example.cc index b7186dda676..6d9f4841e06 100644 --- a/storage/example/ha_example.cc +++ b/storage/example/ha_example.cc @@ -848,6 +848,34 @@ int ha_example::create(const char *name, TABLE *table_arg, struct st_mysql_storage_engine example_storage_engine= { MYSQL_HANDLERTON_INTERFACE_VERSION }; +static ulong srv_enum_var= 0; + +const char *enum_var_names[]= +{ + "e1", "e2", NullS +}; + +TYPELIB enum_var_typelib= +{ + array_elements(enum_var_names) - 1, "enum_var_typelib", + enum_var_names, NULL +}; + +static MYSQL_SYSVAR_ENUM( + enum_var, // name + srv_enum_var, // varname + PLUGIN_VAR_RQCMDARG, // opt + "Sample ENUM system variable.", // comment + NULL, // check + NULL, // update + 0, // def + &enum_var_typelib); // typelib + +static struct st_mysql_sys_var* example_system_variables[]= { + MYSQL_SYSVAR(enum_var), + NULL +}; + mysql_declare_plugin(example) { MYSQL_STORAGE_ENGINE_PLUGIN, @@ -860,7 +888,7 @@ mysql_declare_plugin(example) example_done_func, /* Plugin Deinit */ 0x0001 /* 0.1 */, NULL, /* status variables */ - NULL, /* system variables */ + example_system_variables, /* system variables */ NULL /* config options */ } mysql_declare_plugin_end; diff --git a/storage/myisam/mi_dynrec.c b/storage/myisam/mi_dynrec.c index cdd70abe9ad..9940cf62204 100644 --- a/storage/myisam/mi_dynrec.c +++ b/storage/myisam/mi_dynrec.c @@ -326,6 +326,29 @@ static int write_dynamic_record(MI_INFO *info, const uchar *record, DBUG_ENTER("write_dynamic_record"); flag=0; + + /* + Check if we have enough room for the new record. + First we do simplified check to make usual case faster. + Then we do more precise check for the space left. + Though it still is not absolutely precise, as + we always use MI_MAX_DYN_BLOCK_HEADER while it can be + less in the most of the cases. + */ + + if (unlikely(info->s->base.max_data_file_length - + info->state->data_file_length < + reclength + MI_MAX_DYN_BLOCK_HEADER)) + { + if (info->s->base.max_data_file_length - info->state->data_file_length + + info->state->empty - info->state->del * MI_MAX_DYN_BLOCK_HEADER < + reclength + MI_MAX_DYN_BLOCK_HEADER) + { + my_errno=HA_ERR_RECORD_FILE_FULL; + DBUG_RETURN(1); + } + } + do { if (_mi_find_writepos(info,reclength,&filepos,&length)) @@ -762,6 +785,51 @@ static int update_dynamic_record(MI_INFO *info, my_off_t filepos, uchar *record, DBUG_ENTER("update_dynamic_record"); flag=block_info.second_read=0; + /* + Check if we have enough room for the record. + First we do simplified check to make usual case faster. + Then we do more precise check for the space left. + Though it still is not absolutely precise, as + we always use MI_MAX_DYN_BLOCK_HEADER while it can be + less in the most of the cases. + */ + + /* + compare with just the reclength as we're going + to get some space from the old replaced record + */ + if (unlikely(info->s->base.max_data_file_length - + info->state->data_file_length < reclength)) + { + /* + let's read the old record's block to find out the length of the + old record + */ + if ((error=_mi_get_block_info(&block_info,info->dfile,filepos)) + & (BLOCK_DELETED | BLOCK_ERROR | BLOCK_SYNC_ERROR | BLOCK_FATAL_ERROR)) + { + DBUG_PRINT("error",("Got wrong block info")); + if (!(error & BLOCK_FATAL_ERROR)) + my_errno=HA_ERR_WRONG_IN_RECORD; + goto err; + } + + /* + if new record isn't longer, we can go on safely + */ + if (block_info.rec_len < reclength) + { + if (info->s->base.max_data_file_length - info->state->data_file_length + + info->state->empty - info->state->del * MI_MAX_DYN_BLOCK_HEADER < + reclength - block_info.rec_len + MI_MAX_DYN_BLOCK_HEADER) + { + my_errno=HA_ERR_RECORD_FILE_FULL; + goto err; + } + } + block_info.second_read=0; + } + while (reclength > 0) { if (filepos != info->s->state.dellink) diff --git a/tests/mysql_client_test.c b/tests/mysql_client_test.c index 0cb9ff49128..8bfe315fb59 100644 --- a/tests/mysql_client_test.c +++ b/tests/mysql_client_test.c @@ -7787,7 +7787,7 @@ static void test_explain_bug() verify_prepare_field(result, 5, "Extra", "EXTRA", mysql_get_server_version(mysql) <= 50000 ? MYSQL_TYPE_STRING : MYSQL_TYPE_VAR_STRING, - 0, 0, "", 20, 0); + 0, 0, "", 27, 0); mysql_free_result(result); mysql_stmt_close(stmt); |