bk commit into 5.0 tree (igor:1.2385) BUG#25580

[igor]

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-01-17 20:13:45-08:00, igor@stripped +6 -0
  Fixed bug #25580: incorrect stored representations of views in cases
  when they contain the '!' operator.
  Added an implementation for the method Item_func_not::print. 
  The method encloses any NOT expression into extra parentheses to avoid
  incorrect stored representations of views that use the '!' operators.
  Without this change when a view was created that contained
  the expression !0*5  its stored representation contained not this
  expression but rather the expression not(0)*5 . 
  The operator '!' is of a higher precedence than '*', while NOT is 
  of a lower precedence than '*'. That's why the expression !0*5 
  is interpreted as not(0)*5, while the expression not(0)*5 is interpreted
  as not((0)*5) unless sql_mode is set to HIGH_NOT_PRECEDENCE.
  Now we translate !0*5 into (not(0))*5. 

  mysql-test/r/sp-code.result@stripped, 2007-01-17 20:13:43-08:00, igor@stripped +1 -1
    Adjusted results after the fix of bug 25580.

  mysql-test/r/subselect.result@stripped, 2007-01-17 20:13:43-08:00, igor@stripped +5
-5
    Adjusted results after the fix of bug 25580.

  mysql-test/r/view.result@stripped, 2007-01-17 20:13:43-08:00, igor@stripped +11 -0
    Added a test case for bug #25580.

  mysql-test/t/view.test@stripped, 2007-01-17 20:13:43-08:00, igor@stripped +11 -0
    Added a test case for bug #25580.

  sql/item_cmpfunc.cc@stripped, 2007-01-17 20:13:43-08:00, igor@stripped +16 -0
    Fixed bug #25580: incorrect stored representations of views in cases
    when they contain the '!' operator.
    Added an implementation for the method Item_func_not::print. 
    The method encloses the NOT expression into extra parenthesis to avoid
    incorrect stored representations of views that use the '!' operators.

  sql/item_cmpfunc.h@stripped, 2007-01-17 20:13:43-08:00, igor@stripped +1 -0
    Fixed bug #25580: incorrect stored representations of views in cases
    when they contain the '!' operator.
    Added an implementation for the method Item_func_not::print. 
    The method encloses the NOT expression into extra parenthesis to avoid
    incorrect stored representations of views that use the '!' operators.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	igor
# Host:	olga.mysql.com
# Root:	/home/igor/dev-opt/mysql-5.0-opt-bug25580

--- 1.228/sql/item_cmpfunc.cc	2007-01-17 20:13:51 -08:00
+++ 1.229/sql/item_cmpfunc.cc	2007-01-17 20:13:51 -08:00
@@ -147,6 +147,22 @@
 }
 
 /*
+  We put any NOT expression into parenthesis to avoid
+  possible problems with internal view representations where
+  any '!' is converted to NOT. It may cause a problem if
+  '!' is used in an expression together with other operators
+  whose precedence is lower than the precedence of '!' yet
+  higher than the precedence of NOT.
+*/
+
+void Item_func_not::print(String *str)
+{
+  str->append('(');
+  Item_func::print(str);
+  str->append(')');
+}
+
+/*
   special NOT for ALL subquery
 */
 

--- 1.137/sql/item_cmpfunc.h	2007-01-17 20:13:51 -08:00
+++ 1.138/sql/item_cmpfunc.h	2007-01-17 20:13:51 -08:00
@@ -269,6 +269,7 @@
   enum Functype functype() const { return NOT_FUNC; }
   const char *func_name() const { return "not"; }
   Item *neg_transformer(THD *thd);
+  void print(String *str);
 };
 
 class Item_maxmin_subselect;

--- 1.167/mysql-test/r/subselect.result	2007-01-17 20:13:51 -08:00
+++ 1.168/mysql-test/r/subselect.result	2007-01-17 20:13:51 -08:00
@@ -1464,7 +1464,7 @@
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
 2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index
 Warnings:
-Note	1003	select `test`.`t1`.`s1` AS
`s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`)
in t2 on s1 checking NULL)))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
+Note	1003	select `test`.`t1`.`s1` AS
`s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`)
in t2 on s1 checking NULL))))) AS `s1 NOT IN (SELECT s1 FROM t2)` from `test`.`t1`
 explain extended select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
@@ -1476,13 +1476,13 @@
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
 2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index
 Warnings:
-Note	1003	select `test`.`t1`.`s1` AS
`s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`)
in t2 on s1 checking NULL)))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
+Note	1003	select `test`.`t1`.`s1` AS
`s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`)
in t2 on s1 checking NULL))))) AS `s1 <> ALL (SELECT s1 FROM t2)` from `test`.`t1`
 explain extended select s1, s1 NOT IN (SELECT s1 FROM t2 WHERE s1 < 'a2') from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	index	NULL	s1	6	NULL	3	Using index
 2	DEPENDENT SUBQUERY	t2	index_subquery	s1	s1	6	func	2	Using index; Using where
 Warnings:
-Note	1003	select `test`.`t1`.`s1` AS
`s1`,not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`)
in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2'))))) AS `s1 NOT IN
(SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
+Note	1003	select `test`.`t1`.`s1` AS
`s1`,(not(<in_optimizer>(`test`.`t1`.`s1`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`s1`)
in t2 on s1 checking NULL where (`test`.`t2`.`s1` < _latin1'a2')))))) AS `s1 NOT IN
(SELECT s1 FROM t2 WHERE s1 < 'a2')` from `test`.`t1`
 drop table t1,t2;
 create table t2 (a int, b int);
 create table t3 (a int);
@@ -1737,14 +1737,14 @@
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
 2	DEPENDENT SUBQUERY	t1	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index; Using where
 Warnings:
-Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1`
where
not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`)
in t1 on PRIMARY where (`test`.`t1`.`id` < 8)))))
+Note	1003	select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `test`.`t1`
where
(not(<in_optimizer>(`test`.`t1`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`id`)
in t1 on PRIMARY where (`test`.`t1`.`id` < 8))))))
 explain extended select * from t1 as tt where not exists (select id from t1 where id <
8 and (id = tt.id or id is null) having id is not null);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	tt	ALL	NULL	NULL	NULL	NULL	12	Using where
 2	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	test.tt.id	1	Using where; Using index
 Warnings:
 Note	1276	Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1
-Note	1003	select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1`
`tt` where not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where
((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having
(`test`.`t1`.`id` is not null)))
+Note	1003	select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1`
`tt` where (not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where
((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having
(`test`.`t1`.`id` is not null))))
 insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
 create table t2 (id int not null, text varchar(20) not null default '', primary key
(id));
 insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'),
(5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'),
(11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'),
(17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'),
(23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'),
(29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'),
(35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'),
(41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'),
(47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10');

--- 1.185/mysql-test/r/view.result	2007-01-17 20:13:51 -08:00
+++ 1.186/mysql-test/r/view.result	2007-01-17 20:13:51 -08:00
@@ -3014,4 +3014,15 @@
 6	3
 DROP VIEW v1, v2;
 DROP TABLE t1;
+CREATE VIEW v AS SELECT !0 * 5 AS x FROM DUAL;
+SHOW CREATE VIEW v;
+View	Create View
+v	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS
select ((not(0)) * 5) AS `x`
+SELECT !0 * 5 AS x FROM DUAL;
+x
+5
+SELECT * FROM v;
+x
+5
+DROP VIEW v;
 End of 5.0 tests.

--- 1.170/mysql-test/t/view.test	2007-01-17 20:13:51 -08:00
+++ 1.171/mysql-test/t/view.test	2007-01-17 20:13:51 -08:00
@@ -2959,5 +2959,16 @@
 DROP VIEW v1, v2;
 DROP TABLE t1;
 
+#
+# Bug #25580: !0 as an operand in a select expression of a view
+#
+
+CREATE VIEW v AS SELECT !0 * 5 AS x FROM DUAL;
+SHOW CREATE VIEW v;
+
+SELECT !0 * 5 AS x FROM DUAL;
+SELECT * FROM v;
+
+DROP VIEW v;
 
 --echo End of 5.0 tests.

--- 1.7/mysql-test/r/sp-code.result	2007-01-17 20:13:51 -08:00
+++ 1.8/mysql-test/r/sp-code.result	2007-01-17 20:13:51 -08:00
@@ -187,7 +187,7 @@
 32	set v_dig@4 (v_dig@4 + 1)
 33	stmt 4 "update sudoku_work set dig = v_dig wh..."
 34	set v_tcounter@6 (v_tcounter@6 + 1)
-35	jump_if_not 37(37) not(`test`.`sudoku_digit_ok`(v_row@7,v_col@8,v_dig@4))
+35	jump_if_not 37(37) (not(`test`.`sudoku_digit_ok`(v_row@7,v_col@8,v_dig@4)))
 36	jump 15
 37	set v_i@3 (v_i@3 + 1)
 38	jump 15