MySQL Bugs: #1509: Incorrect behaviour of SQL engine when operating with logical functions

Bug #1509	Incorrect behaviour of SQL engine when operating with logical functions
Submitted:	9 Oct 2003 7:30	Modified:	14 Oct 2003 12:11
Reporter:	Vadim S	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.1	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Current version of 4.1.1 (of 9 Oct. 2003)
Look at the example below.
Something with SQL language engine?
(logical functions are not working or syntax parser errors)

--------------
mysql> select * from msg where Author=6213 and (Saved='s' or Saved='n')
order by ID;
Empty set (0.00 sec)
---------------
mysql> select COUNT(*) from msg where Author=6213 and (Saved='s' or
Saved='n') order by ID;
+----------+
| COUNT(*) |
+----------+
|      191 |
+----------+
1 row in set (0.01 sec)
----------------
mysql> select ID from msg where Author=6213  order by ID;
......
| 18540 |
+-------+
220 rows in set (0.01 sec)
 
 

How to repeat:
Look at the description above.

This bug is the new version of the engine. For my experience - it is active for last 2 weeks.

Bug is actual for GROUP BY statements.
The main problem is with ().

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Please provide repeatable test case. Queries you sent are not enough.

By the way none of queries contain GROUP BY clause so it is not clear how it is related.

I have just created table using your CREATE and entered some rows there. I will wait till tomorrow as you describe in Bug report #1549 to see if I will be able to repeat the bug.

Meanwhile can you please check if table is not corrupted?

Sometimes strange results like this are caused by Index corruption.

By the way in your table key Index_0 is redundant as Index_7 contains Author as a prefix.

 KEY `index_0` (`Author`),
 KEY `index_7` (`Author`,`Saved`)

Indexes are checked, tables are repaired.
Nothing helps.
More, now it behaves constantly wrong.
4.0.15a is OK, 4.1.1 hangs.