From 9a27672396b660c8c89c30bafcfbb00849a06723 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 17 Jun 2005 08:56:04 -0700 Subject: Fix spurious permissions problem when CONVERT_TZ() is used in a multi-table update query. (Bug #9979) mysql-test/r/timezone_grant.result: Add new results mysql-test/t/timezone_grant.test: Add new test sql/sql_parse.cc: Make sure to check lex->time_zone_tables_used to determine if we are using additional tables. --- mysql-test/r/timezone_grant.result | 7 +++++++ mysql-test/t/timezone_grant.test | 12 ++++++++++++ sql/sql_parse.cc | 5 +++-- 3 files changed, 22 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/timezone_grant.result b/mysql-test/r/timezone_grant.result index 685f8007ac7..471cacde300 100644 --- a/mysql-test/r/timezone_grant.result +++ b/mysql-test/r/timezone_grant.result @@ -45,6 +45,13 @@ select * from mysql.time_zone_name; ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'time_zone_name' select Name, convert_tz('2004-11-30 12:00:00', Name, 'UTC') from mysql.time_zone_name; ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'time_zone_name' +drop table t1, t2; +create table t1 (a int, b datetime); +create table t2 (a int, b varchar(40)); +update t1 set b = '2005-01-01 10:00'; +update t1 set b = convert_tz(b, 'UTC', 'UTC'); +update t1 join t2 on (t1.a = t2.a) set t1.b = '2005-01-01 10:00' where t2.b = 'foo'; +update t1 join t2 on (t1.a = t2.a) set t1.b = convert_tz('2005-01-01 10:00','UTC','UTC') where t2.b = 'foo'; delete from mysql.user where user like 'mysqltest\_%'; delete from mysql.db where user like 'mysqltest\_%'; delete from mysql.tables_priv where user like 'mysqltest\_%'; diff --git a/mysql-test/t/timezone_grant.test b/mysql-test/t/timezone_grant.test index 501315668f5..8627866a0bf 100644 --- a/mysql-test/t/timezone_grant.test +++ b/mysql-test/t/timezone_grant.test @@ -61,6 +61,18 @@ select * from mysql.time_zone_name; --error 1142 select Name, convert_tz('2004-11-30 12:00:00', Name, 'UTC') from mysql.time_zone_name; +# +# Bug #9979: Use of CONVERT_TZ in multiple-table UPDATE causes bogus +# privilege error +# +drop table t1, t2; +create table t1 (a int, b datetime); +create table t2 (a int, b varchar(40)); +update t1 set b = '2005-01-01 10:00'; +update t1 set b = convert_tz(b, 'UTC', 'UTC'); +update t1 join t2 on (t1.a = t2.a) set t1.b = '2005-01-01 10:00' where t2.b = 'foo'; +update t1 join t2 on (t1.a = t2.a) set t1.b = convert_tz('2005-01-01 10:00','UTC','UTC') where t2.b = 'foo'; + # Clean-up connection default; delete from mysql.user where user like 'mysqltest\_%'; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index f0ea0a762bd..a515441ee1b 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -1938,7 +1938,8 @@ mysql_execute_command(THD *thd) that is not a SHOW command or a select that only access local variables, but for now this is probably good enough. */ - if (tables || &lex->select_lex != lex->all_selects_list) + if (tables || &lex->select_lex != lex->all_selects_list || + lex->time_zone_tables_used) mysql_reset_errors(thd); #ifdef HAVE_REPLICATION @@ -5354,7 +5355,7 @@ int multi_update_precheck(THD *thd, TABLE_LIST *tables) /* Is there tables of subqueries? */ - if (&lex->select_lex != lex->all_selects_list) + if (&lex->select_lex != lex->all_selects_list || lex->time_zone_tables_used) { DBUG_PRINT("info",("Checking sub query list")); for (table= tables; table; table= table->next) -- cgit v1.2.1 From 607030266f83a3b81cddf40ca05245381600fdf4 Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 1 Jul 2005 07:40:22 -0700 Subject: olap.result, olap.test: Added a test case for bug #11543. sql_select.cc: Fixed bug #11543. A ROLLUP query could return a wrong result set when its GROUP BY clause contained references to the same column. sql/sql_select.cc: Fixed bug #11543. A ROLLUP query could return a wrong result set when its GROUP BY clause contained references to the same column. mysql-test/t/olap.test: Added a test case for bug #11543. mysql-test/r/olap.result: Added a test case for bug #11543. --- mysql-test/r/olap.result | 12 ++++++++++++ mysql-test/t/olap.test | 11 +++++++++++ sql/sql_select.cc | 4 ++-- 3 files changed, 25 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result index 0c6c4684853..7178895cf80 100644 --- a/mysql-test/r/olap.result +++ b/mysql-test/r/olap.result @@ -504,3 +504,15 @@ IFNULL(a, 'TEST') COALESCE(b, 'TEST') 4 TEST TEST TEST DROP TABLE t1,t2; +CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) NOT NULL); +INSERT INTO t1 VALUES (1, 1); +INSERT INTO t1 VALUES (1, 2); +SELECT a, b, a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP; +a b c count +1 1 1 1 +1 1 NULL 1 +1 2 1 1 +1 2 NULL 1 +1 NULL NULL 2 +NULL NULL NULL 2 +DROP TABLE t1; diff --git a/mysql-test/t/olap.test b/mysql-test/t/olap.test index b9387d15320..9e6adffffcf 100644 --- a/mysql-test/t/olap.test +++ b/mysql-test/t/olap.test @@ -238,3 +238,14 @@ SELECT IFNULL(a, 'TEST'), COALESCE(b, 'TEST') FROM t2 DROP TABLE t1,t2; +# +# Test for bug #11543: ROLLUP query with a repeated column in GROUP BY +# + +CREATE TABLE t1 (a INT(10) NOT NULL, b INT(10) NOT NULL); +INSERT INTO t1 VALUES (1, 1); +INSERT INTO t1 VALUES (1, 2); + +SELECT a, b, a AS c, COUNT(*) AS count FROM t1 GROUP BY a, b, c WITH ROLLUP; + +DROP TABLE t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 044dc60e4b6..5866585bb60 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -9322,7 +9322,7 @@ bool JOIN::rollup_init() ORDER *group_tmp; for (group_tmp= group_list; group_tmp; group_tmp= group_tmp->next) { - if (item->eq(*group_tmp->item,0)) + if (*group_tmp->item == item) item->maybe_null= 1; } if (item->type() == Item::FUNC_ITEM) @@ -9444,7 +9444,7 @@ bool JOIN::rollup_make_fields(List &fields_arg, List &sel_fields, for (group_tmp= start_group, i= pos ; group_tmp ; group_tmp= group_tmp->next, i++) { - if (item->eq(*group_tmp->item,0)) + if (*group_tmp->item == item) { /* This is an element that is used by the GROUP BY and should be -- cgit v1.2.1 From 085653b92d30aa889ba0c01e0ff21f61cf219bab Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 15 Jul 2005 12:31:57 -0700 Subject: Fix error in formatting metadata in mysqltest. client/mysqltest.c: Fix reporting of length and max_length when displaying metadata -- they are unsigned int. --- client/mysqltest.c | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/client/mysqltest.c b/client/mysqltest.c index b7f4ceb9c20..b23b77e9bca 100644 --- a/client/mysqltest.c +++ b/client/mysqltest.c @@ -3141,10 +3141,10 @@ static void run_query_display_metadata(MYSQL_FIELD *field, uint num_fields, int10_to_str((int) field->type, buff, 10); dynstr_append(ds, buff); dynstr_append_mem(ds, "\t", 1); - int10_to_str((int) field->length, buff, 10); + longlong10_to_str((unsigned int) field->length, buff, 10); dynstr_append(ds, buff); dynstr_append_mem(ds, "\t", 1); - int10_to_str((int) field->max_length, buff, 10); + longlong10_to_str((unsigned int) field->max_length, buff, 10); dynstr_append(ds, buff); dynstr_append_mem(ds, "\t", 1); dynstr_append_mem(ds, (char*) (IS_NOT_NULL(field->flags) ? -- cgit v1.2.1 From 7adfe96d9ebb3428550aeab79f08f5395d0ac44c Mon Sep 17 00:00:00 2001 From: unknown Date: Sat, 16 Jul 2005 10:30:25 +0300 Subject: Added test for Bug #11521 "Negative integer keys incorrectly substituted for 0 during range analysis." The problem is that the range optimizer incorrectly replaces any negative constant with '0' for all types except BIGINT because the method save_in_field() casts negative integers to non-negative. This causes incorrect query results where (0 = any_negative_number). The problem caused by this bug is fixed by the patch for BUG#11185. That patch constitutes an optimization due to which the problem code is never called with negative constants. This patch adds a test so we are sure that the problem does not reappear. mysql-test/r/select.result: Test for BUG#11521. mysql-test/t/select.test: Test for BUG#11521. --- mysql-test/r/select.result | 12 ++++++++++++ mysql-test/t/select.test | 11 +++++++++++ 2 files changed, 23 insertions(+) diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 5c0616f9e54..6c9ec9d8297 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2570,3 +2570,15 @@ f2 1 NULL drop table t1,t2; +create table t2 (a tinyint unsigned); +create index t2i on t2(a); +insert into t2 values (0), (254), (255); +explain select * from t2 where a > -1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index t2i t2i 2 NULL 3 Using where; Using index +select * from t2 where a > -1; +a +0 +254 +255 +drop table t2; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index 2e261d0611f..caa9e332e57 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -2127,3 +2127,14 @@ insert into t2 values (1,2,3),(2,4,6); select A.f2 from t1 left join t2 A on A.f2 = f1 where A.f3=(select min(f3) from t2 C where A.f4 = C.f4) or A.f3 IS NULL; drop table t1,t2; + +# +# Bug #11521 Negative integer keys incorrectly substituted for 0 during +# range analysis. + +create table t2 (a tinyint unsigned); +create index t2i on t2(a); +insert into t2 values (0), (254), (255); +explain select * from t2 where a > -1; +select * from t2 where a > -1; +drop table t2; -- cgit v1.2.1 From 6a88fa48ae2568aadf8513060a88c88b65c3f7cc Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 17 Jul 2005 09:46:14 -0700 Subject: select.result, select.test: Added a test case for bug #11745. sql_select.cc: Fixed bug # 11745. Added support of where clause for queries with FROM DUAL. sql_yacc.yy: Fixed bug # 11745. Added optional where clause for queries with FROM DUAL. sql/sql_yacc.yy: Fixed bug # 11745. Added optional where clause for queries with FROM DUAL. sql/sql_select.cc: Fixed bug # 11745. Added support of where clause for queries with FROM DUAL. mysql-test/t/select.test: Added a test case for bug #11745. mysql-test/r/select.result: Added a test case for bug #11745. --- mysql-test/r/select.result | 16 ++++++++++++++++ mysql-test/t/select.test | 18 ++++++++++++++++++ sql/sql_select.cc | 2 +- sql/sql_yacc.yy | 2 +- 4 files changed, 36 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index 6c9ec9d8297..2df60944999 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -2582,3 +2582,19 @@ a 254 255 drop table t2; +CREATE TABLE t1 (a int, b int, c int); +INSERT INTO t1 +SELECT 50, 3, 3 FROM DUAL +WHERE NOT EXISTS +(SELECT * FROM t1 WHERE a = 50 AND b = 3); +SELECT * FROM t1; +a b c +50 3 3 +INSERT INTO t1 +SELECT 50, 3, 3 FROM DUAL +WHERE NOT EXISTS +(SELECT * FROM t1 WHERE a = 50 AND b = 3); +SELECT * FROM t1; +a b c +50 3 3 +DROP TABLE t1; diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index caa9e332e57..893b9ba9267 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -2138,3 +2138,21 @@ insert into t2 values (0), (254), (255); explain select * from t2 where a > -1; select * from t2 where a > -1; drop table t2; + +# +# Bug #11745: SELECT ... FROM DUAL with WHERE condition +# + +CREATE TABLE t1 (a int, b int, c int); +INSERT INTO t1 + SELECT 50, 3, 3 FROM DUAL + WHERE NOT EXISTS + (SELECT * FROM t1 WHERE a = 50 AND b = 3); +SELECT * FROM t1; +INSERT INTO t1 + SELECT 50, 3, 3 FROM DUAL + WHERE NOT EXISTS + (SELECT * FROM t1 WHERE a = 50 AND b = 3); +SELECT * FROM t1; + +DROP TABLE t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index a70fa1c8d5e..5b4eab36dd2 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1072,7 +1072,7 @@ JOIN::exec() else { result->send_fields(fields_list,1); - if (!having || having->val_int()) + if (cond_value != Item::COND_FALSE && (!having || having->val_int())) { if (do_send_rows && (procedure ? (procedure->send_row(fields_list) || procedure->end_of_records()) diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 2587ce7e1a5..be8ead8e157 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -2459,7 +2459,7 @@ select_into: select_from: FROM join_table_list where_clause group_clause having_clause opt_order_clause opt_limit_clause procedure_clause - | FROM DUAL_SYM opt_limit_clause + | FROM DUAL_SYM where_clause opt_limit_clause /* oracle compatibility: oracle always requires FROM clause, and DUAL is system table without fields. Is "SELECT 1 FROM DUAL" any better than "SELECT 1" ? -- cgit v1.2.1 From c4a3500d5e66b366309f4e5e8748c63e34984c6f Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 18 Jul 2005 13:52:18 +0200 Subject: QNX does not know "uint", so the cast is rewritten as "unsigned int". --- cmd-line-utils/libedit/read.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/cmd-line-utils/libedit/read.c b/cmd-line-utils/libedit/read.c index 40093d6647f..051f3e8e42e 100644 --- a/cmd-line-utils/libedit/read.c +++ b/cmd-line-utils/libedit/read.c @@ -478,7 +478,7 @@ el_gets(EditLine *el, int *nread) #endif /* DEBUG_READ */ break; } - if ((uint)cmdnum >= el->el_map.nfunc) { /* BUG CHECK command */ + if ((unsigned int)cmdnum >= el->el_map.nfunc) { /* BUG CHECK command */ #ifdef DEBUG_EDIT (void) fprintf(el->el_errfile, "ERROR: illegal command from key 0%o\r\n", ch); -- cgit v1.2.1 From 750eedd9abdb22b64559b8a43b58679884d84e7c Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 18 Jul 2005 14:46:53 +0200 Subject: Fix for BUG#12003 "assertion failure in testsuite (double lock of LOCK_thread_count)" and for BUG#12004 "SHOW BINARY LOGS reports 0 for the size of all binlogs but the current one". There are a lot of 4.1->5.0 unmerged changes (hardest are in the optimizer), can't merge; still pushing in 4.1 because my changes are very small. Feel free to ask me if you have problems merging them. mysql-test/r/rpl_log.result: correcting binlog sizes mysql-test/r/rpl_rotate_logs.result: correcting binlog sizes sql/mysqld.cc: don't lock LOCK_thread_count when you already have it sql/sql_repl.cc: my_open() needs the complete name, not only the base name --- mysql-test/r/rpl_log.result | 4 ++-- mysql-test/r/rpl_rotate_logs.result | 10 +++++----- sql/mysqld.cc | 2 +- sql/sql_repl.cc | 2 +- 4 files changed, 9 insertions(+), 9 deletions(-) diff --git a/mysql-test/r/rpl_log.result b/mysql-test/r/rpl_log.result index 7813d4d779d..9fcab2a7cbe 100644 --- a/mysql-test/r/rpl_log.result +++ b/mysql-test/r/rpl_log.result @@ -68,12 +68,12 @@ master-bin.000002 168 Query 1 168 use `test`; insert into t1 values (1) master-bin.000002 228 Query 1 228 use `test`; drop table t1 show binary logs; Log_name File_size -master-bin.000001 0 +master-bin.000001 1171 master-bin.000002 276 start slave; show binary logs; Log_name File_size -slave-bin.000001 0 +slave-bin.000001 1285 slave-bin.000002 170 show binlog events in 'slave-bin.000001' from 4; Log_name Pos Event_type Server_id Orig_log_pos Info diff --git a/mysql-test/r/rpl_rotate_logs.result b/mysql-test/r/rpl_rotate_logs.result index 66eef482a63..9f74cdb9502 100644 --- a/mysql-test/r/rpl_rotate_logs.result +++ b/mysql-test/r/rpl_rotate_logs.result @@ -27,8 +27,8 @@ insert into t2 values (34),(67),(123); flush logs; show binary logs; Log_name File_size -master-bin.000001 0 -master-bin.000002 0 +master-bin.000001 461 +master-bin.000002 213 master-bin.000003 4 create table t3 select * from temp_table; select * from t3; @@ -43,12 +43,12 @@ start slave; purge master logs to 'master-bin.000002'; show master logs; Log_name File_size -master-bin.000002 0 +master-bin.000002 213 master-bin.000003 229 purge binary logs to 'master-bin.000002'; show binary logs; Log_name File_size -master-bin.000002 0 +master-bin.000002 213 master-bin.000003 229 purge master logs before now(); show binary logs; @@ -74,7 +74,7 @@ count(*) create table t4 select * from temp_table; show binary logs; Log_name File_size -master-bin.000003 0 +master-bin.000003 4167 master-bin.000004 2886 show master status; File Position Binlog_Do_DB Binlog_Ignore_DB diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 02a775f64d1..3f4b002e369 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -663,7 +663,7 @@ static void close_connections(void) project. This will wake up the socket on Windows and prevent the printing of the error message that we are force closing a connection. */ - close_connection(tmp, 0, 1); + close_connection(tmp, 0, 0); if (tmp->mysys_var) { tmp->mysys_var->abort=1; diff --git a/sql/sql_repl.cc b/sql/sql_repl.cc index fe95ee26748..963c4ccf5a6 100644 --- a/sql/sql_repl.cc +++ b/sql/sql_repl.cc @@ -1384,7 +1384,7 @@ int show_binlogs(THD* thd) else { /* this is an old log, open it and find the size */ - if ((file= my_open(fname+dir_len, O_RDONLY | O_SHARE | O_BINARY, + if ((file= my_open(fname, O_RDONLY | O_SHARE | O_BINARY, MYF(0))) >= 0) { file_length= (ulonglong) my_seek(file, 0L, MY_SEEK_END, MYF(0)); -- cgit v1.2.1 From ddc3586495aa82f310b0e5c2127b77f28c711c27 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 18 Jul 2005 18:03:58 +0400 Subject: Alleviate the autoconf requirement. --- configure.in | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/configure.in b/configure.in index af7ce8b1c3d..b270e5b0013 100644 --- a/configure.in +++ b/configure.in @@ -118,7 +118,7 @@ AC_SUBST(SAVE_LDFLAGS) AC_SUBST(SAVE_CXXLDFLAGS) AC_SUBST(CXXLDFLAGS) -AC_PREREQ(2.58)dnl Minimum Autoconf version required. +AC_PREREQ(2.57)dnl Minimum Autoconf version required. #AC_ARG_PROGRAM # Automaticly invoked by AM_INIT_AUTOMAKE AM_SANITY_CHECK -- cgit v1.2.1