MySQL Bugs: #25580: The Parsing difers from a direct select to a create view

Bug #25580	The Parsing difers from a direct select to a create view
Submitted:	12 Jan 2007 11:53	Modified:	8 Feb 2007 18:16
Reporter:	João Mota	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.34-BK, 5.0.27-community	OS:	Linux (Linux, WIN32)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
I ran into some diferences in results when executing a query and executing the same query as a view using the function not when trying to avoid an IF().

How to repeat:
> select !0 * 5 as X;

  returns 5

> create view test as
   select !0 * 5 as X;
> select * from test;

  returns 1

Suggested fix:
I know I should use extra parentisis but either way the results should be the same . I imagine that the create view parser is a little more thorough than the query parser. I should at least get a warning when creating the view that the results may differ from the direct query.

Thank you for a bug report. Verified just as described with latest 5.0.34-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.34-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select !0*5 as x;
+---+
| x |
+---+
| 5 |
+---+
1 row in set (0.00 sec)

mysql> create table tt as select !0*5 as x;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table tt\G
*************************** 1. row ***************************
       Table: tt
Create Table: CREATE TABLE `tt` (
  `x` int(3) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from tt;
+---+
| x |
+---+
| 5 |
+---+
1 row in set (0.01 sec)

mysql> create view tv as select !0*5 as x;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tv;
+---+
| x |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> show create view tv\G
*************************** 1. row ***************************
       View: tv
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY
DEFINER VIEW `tv` AS select not((0 * 5)) AS `x`
1 row in set (0.00 sec)

So, as we can see, view is created incorrectly.

This problem can be demostrated without using views:

mysql> SELECT (not(0) * 5) AS X FROM DUAL;
+---+
| X |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> EXPLAIN EXTENDED SELECT (not(0) * 5) AS X FROM DUAL;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+----------------------------+
| Level | Code | Message                    |
+-------+------+----------------------------+
| Note  | 1003 | select not((0 * 5)) AS `X` |
+-------+------+----------------------------+
1 row in set (0.00 sec)

The problem is not observed for 4.1 though:

mysql> SELECT VERSION();
+--------------+
| VERSION()    |
+--------------+
| 4.1.23-debug |
+--------------+
1 row in set (0.00 sec)

mysql> EXPLAIN EXTENDED SELECT (not(0) * 5) AS X FROM DUAL;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL |    NULL | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set, 1 warning (0.01 sec)

mysql> SHOW WARNINGS;
+-------+------+----------------------------+
| Level | Code | Message                    |
+-------+------+----------------------------+
| Note  | 1003 | select (not(0) * 5) AS `X` |
+-------+------+----------------------------+
1 row in set (0.00 sec)

The following result can be explained by the fact that 'not' has a lower precedence than '*': 

mysql> SELECT (not(0) * 5) AS X FROM DUAL;
+---+
| X |
+---+
| 1 |
+---+

If we make 'not' to have the highest precedence the result is different:

mysql> SET sql_mode = HIGH_NOT_PRECEDENCE;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT (not(0) * 5) AS X FROM DUAL;
+---+
| X |
+---+
| 5 |
+---+
1 row in set (0.00 sec)

mysql> EXPLAIN EXTENDED SELECT (not(0) * 5) AS X FROM DUAL;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+----------------------------+
| Level | Code | Message                    |
+-------+------+----------------------------+
| Note  | 1003 | select (not(0) * 5) AS `X` |
+-------+------+----------------------------+
1 row in set (0.00 sec)

However '!' has a higher precedence than '*'.

This explains why we have the correct result if we use '!' instead of 'not':

mysql> SELECT (!(0) * 5) AS X FROM DUAL;
+---+
| X |
+---+
| 5 |
+---+
1 row in set (0.00 sec)

The problem with views is that in internal stored representations of views any '!' operator is always replaced by a 'not' operator. 
This problem can be resolved by putting expressions under 'not' into parentheses.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/18306

ChangeSet@1.2385, 2007-01-17 20:13:45-08:00, igor@olga.mysql.com +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.

The fix has been pushed into 5.0.36, 5.1.16-beta main trees.

Noted in 5.0.36, 5.1.16 changelogs.

View definitions that used the ! operator were treated as containing
the NOT operator, which has a different precedence and can produce
different results.