Bug #10095 AND/OR - get diff result
Submitted: 22 Apr 2005 12:38 Modified: 19 May 2005 2:06
Reporter: Hans Baier Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:4.0.24 OS:Linux (linux)
Assigned to: Sergey Petrunya CPU Architecture:Any

[22 Apr 2005 12:38] Hans Baier
Description:
PROBLEM with combination of AND/OR in a where-Statement:
I got with the same logic different results (only the AND-path is moved to the beginning of the where-statement)

mysql> status
--------------
mysql  Ver 12.22 Distrib 4.0.24, for pc-linux-gnu (i686)

Connection id:          36147
Current database:       test
Current user:           xxx
SSL:                    Not in use
Current pager:          less
Using outfile:          ''
Server version:         4.0.24-standard-log
Protocol version:       10
Connection:             mysqlxxxx via TCP/IP
Client characterset:    latin1
Server characterset:    latin1
TCP port:               3306
Uptime:                 1 hours 39 min 22 sec

mysql-table dump (include insert statement)
==============================
--
-- Table structure for table `hb_kunde`
--

CREATE TABLE hb_kunde (
  kunde_intern_id int(10) unsigned NOT NULL auto_increment,
  FK_land_intern_id int(10) unsigned NOT NULL default '0',
  FK_gruppe_id int(10) unsigned NOT NULL default '0',
  FK_firma_id int(10) unsigned NOT NULL default '0',
  FK_benutzer_id int(10) unsigned default NULL,
  FK_mitarbeiter_id int(10) unsigned NOT NULL default '0',
  kunde_id int(10) unsigned NOT NULL default '0',
  aktuell enum('Ja','Nein') NOT NULL default 'Ja',
  geloescht enum('Ja','Nein') NOT NULL default 'Nein',
  geaendert timestamp(14) NOT NULL,
  datum datetime NOT NULL default '0000-00-00 00:00:00',
  kundennummer int(10) unsigned NOT NULL default '1',
  anrede enum('keine Anrede','Herr','Frau','Herr und Frau','Familie') NOT NULL default 'Herr',
  vorname varchar(128) NOT NULL default '',
  nachname varchar(128) NOT NULL default '',
  firma varchar(128) NOT NULL default '',
  ansprechpartner varchar(128) default NULL,
  strasse varchar(128) NOT NULL default '',
  plz varchar(8) NOT NULL default '',
  ort varchar(128) NOT NULL default '',
  telefon varchar(64) NOT NULL default '',
  telefon2 varchar(64) default NULL,
  fax varchar(64) default NULL,
  email varchar(128) NOT NULL default '',
  lieferfirma varchar(128) NOT NULL default '',
  liefernachname varchar(128) NOT NULL default '',
  liefervorname varchar(128) NOT NULL default '',
  lieferstrasse varchar(128) default NULL,
  lieferplz varchar(8) default NULL,
  lieferort varchar(128) default NULL,
  FK_lieferland_intern_id int(10) unsigned NOT NULL default '0',
  liefertelefon varchar(64) default NULL,
  bank varchar(128) NOT NULL default '',
  bankleitzahl varchar(8) NOT NULL default '',
  kontonummer varchar(16) NOT NULL default '',
  zahlart enum('Bar','Scheck','?berweisung','Bankeinzug') NOT NULL default 'Bankeinzug',
  rabatt decimal(3,2) NOT NULL default '0.00',
  zahlungsziel_netto int(10) unsigned NOT NULL default '0',
  skonto decimal(3,2) NOT NULL default '0.00',
  zahlungsziel_skonto int(10) unsigned NOT NULL default '0',
  mwst enum('Ja','Nein') NOT NULL default 'Ja',
  ausgabe enum('Online','Papier','Online und Papier') NOT NULL default 'Online',
  notiz text,
  zustand enum('Angezeigt','Gesperrt') NOT NULL default 'Angezeigt',
  lieferanrede enum('keine Anrede','Herr','Frau','Herr und Frau','Familie') NOT NULL default 'Herr',
  PRIMARY KEY  (kunde_intern_id),
  KEY kunde_index1 (kunde_id),
  KEY kunde_index2 (firma),
  KEY kunde_FKindex2 (FK_benutzer_id),
  KEY kunde_FKindex1 (FK_firma_id),
  KEY kunde_FKIndex3 (FK_gruppe_id),
  KEY kunde_index3 (liefernachname),
  KEY kunde_FKIndex4 (FK_land_intern_id),
  KEY kunde_index4 (aktuell),
  KEY kunde_index5 (zustand),
  KEY kunde_index6 (kundennummer),
  KEY kunde_index7 (geloescht),
  KEY kunde_FKindex5 (FK_mitarbeiter_id)
) TYPE=MyISAM;

--
-- Dumping data for table `hb_kunde`
--

INSERT INTO hb_kunde VALUES (3964,1,2,1,19789,181,3051,'Ja','Nein',20041220094914,'2004-12-20 09:49:14',3252,'Herr','Vorname1','1Nachname','Print Schau XXXX','','Strasse
1. 15','99999','ORT1xxx','0984321 123456','','98765 123456','info@domainname-txxx.dxxx','Print Schau xxxxx','1Nachname','Vorname1','Strasse. 15','99999','ORT1xxx',1,'098
765 123456','Spk irgendwo1-yyyyy','000000','010101010','muster1','0.00',14,'0.00',0,'Ja','Online und Papier',NULL,'Angezeigt','Herr');

INSERT INTO hb_kunde VALUES (3965,1,2,1,11119789,111181111,3051111,'Ja','Nein',20041111220094911114,'2004-11112-20 09:49:11114',3252,'Herr','Vorname1111','1111Nachname',
'Print Schau XXXX','','Strasse1111. 11115','99999','ORT1111xxx','0984321111 111123456','','98765 111123456','info@domainname-txxx.dxxx','Print Schau xxxxx','1111Nachname
','Vorname1111','Strasse. 11115','99999','ORT1111xxx',1111,'098765 111123456','Spk irgendwo1111-yyyyy','000000','011110111101111011110','muster1111','0.00',11114,'0.00',
0,'Ja','Online und Papier',NULL,'Angezeigt','Herr');

How to repeat:
first SQL-Statement and result
--------------------------------
mysql> SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, geloescht FROM hb_kunde
    ->  WHERE
    ->   (
    ->      (
    ->         ( '' != '' AND firma LIKE CONCAT('%', '', '%'))
    ->         OR
    ->         (  vorname LIKE CONCAT('%', 'Vorname1', '%') AND nachname LIKE CONCAT('%', '1Nachname', '%') AND 'Vorname1' != '' AND 'xxxx' != '')
    ->      )
    ->      AND
    ->      (
    ->        aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2
    ->      )
    ->   )
    -> ;
+----------+-------------+---------+-------------+--------------+-----------+
| kunde_id | FK_firma_id | aktuell | vorname     | nachname     | geloescht |
+----------+-------------+---------+-------------+--------------+-----------+
|     3051 |           1 | Ja      | Vorname1    | 1Nachname    | Nein      |
|  3051111 |           1 | Ja      | Vorname1111 | 1111Nachname | Nein      |
+----------+-------------+---------+-------------+--------------+-----------+
2 rows in set (0.00 sec)

second SQL-Statement and result
--------------------------------
mysql> SELECT kunde_id ,FK_firma_id ,aktuell, vorname, nachname, geloescht FROM hb_kunde
    ->  WHERE
    ->   (
    ->     (
    ->       aktuell = 'Ja' AND geloescht = 'Nein' AND FK_firma_id = 2
    ->     )
    ->     AND
    ->     (
    ->         ( '' != '' AND firma LIKE CONCAT('%', '', '%')  )
    ->         OR
    ->         (  vorname LIKE CONCAT('%', 'Vorname1', '%') AND nachname LIKE CONCAT('%', '1Nachname', '%') AND 'Vorname1' != '' AND 'xxxx' != '')
    ->     )
    ->   )
    -> ;
Empty set (0.00 sec)

Problem:
========
DIFF of the queries: only the 'AND statement' is moved from the end of where to the beginning of where
[22 Apr 2005 17:18] Hartmut Holzgraefe
Happens with 4.0 but not with 4.1.  The shortest reproducing example that i could come up with is:

SELECT COUNT(*) FROM hb_kunde WHERE 
( 0 OR (vorname LIKE '%Vorname1%' AND nachname LIKE '%1Nachname%' AND 1)) 
AND FK_firma_id = 2;

Any further changes beyond this point make it return the right result. Table engine type and indexes don't matter.
[7 May 2005 21:11] Sergey Petrunya
Incorrect result with 4.1-bk too. The result seems to be incorrect when 'ref' access method is used.
[10 May 2005 0:58] Bugs System
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/internals/24758
[10 May 2005 11:23] Sergey Petrunya
Please disregard my above erroneous comment about 4.1
[15 May 2005 18:56] Bugs System
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/internals/24914
[15 May 2005 19:20] Sergey Petrunya
Pushed into 4.0.25 tree
[19 May 2005 2:06] Paul DuBois
Noted in 4.0.25 changelog.