Bug #37013 CONDITION NOT MET IN VALID SQL STATEMENT
Submitted: 27 May 2008 18:06 Modified: 8 Feb 2018 23:00
Reporter: Marian Stránecký Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.0.22,5.0.54, 5.0, 5.1, 6.0 BK OS:Linux (CentOS5, Gentoo2008)
Assigned to: CPU Architecture:Any
Tags: doc_type_id, select statement

[27 May 2008 18:06] Marian Stránecký
Description:
WHERE condition NOT EVALUEATED CORRECTLY for this statement:

SELECT * 
FROM ota_messages 
WHERE ((oma_pro = 0) 
and (ota_ptov = 1) 
and (http_stack = 0) 
and (multi_prov = 0) 
and (doc_type_id = 0)) 
ORDER BY msg_block, msg_part

The very last "AND" condition, i.e. 'and (doc_type_id = 0)' has no impact on selecting records THUS THE RESULTSET IS INVALID

WORKAROUND:
- change column name 'doc_type_id' to any different, not reserved keyword, e.g. 'doctype'
OR add a column, e.g.: "ALTER TABLE ota_messages ADD COLUMN cpdoctype INTEGER UNSIGNED DEFAULT NULL AFTER http_stack"

TESTED ON (a least):
- MySQL 5.0.54; Gentoo Linux SMP i686 Intel(R) Core(TM)2 Duo CPU E4500 @ 2.20GHz GenuineIntel GNU/Linux
- MySQL 5.0.22; CentOS Linux 2.6.18-53.el5 #1 SMP Mon Nov 12 02:22:48 EST 2007 i686 i686 i386 GNU/Linux

How to repeat:
1) CREATE TABLE AS FOLLOWS:
--------------------------
CREATE TABLE `ota_messages` (
  `id` int(11) NOT NULL,
  `msg_part` tinyint(4) NOT NULL,
  `msg_block` tinyint(4) NOT NULL,
  `oma_pro` bit(1) NOT NULL,
  `ota_ptov` bit(1) NOT NULL,
  `multi_prov` bit(1) NOT NULL,
  `http_stack` bit(1) NOT NULL,
  `doc_type_id` int(10) unsigned NOT NULL,
  `wap` bit(1) NOT NULL,
  `mms` bit(1) NOT NULL,
  `note` varchar(250) default NULL,
  `document_note` varchar(250) default NULL,
  `UDH` varchar(200) NOT NULL,
  `UD` varchar(200) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

2) THEN FILL IT WITH ANY DATA ACCORDING TO APPROPRIATE COLUMN DATA TYPE

3) USE SELECT AS ABOVE

Suggested fix:
- CHECK 'EVALUATION' MECHANISM AND CORRECT ITS BEHAVIOUR, PROVIDE PATCH
- AND/OR UPDATE DOCUMENTATION
[27 May 2008 20:23] Sveta Smirnova
Thank you for the report.

Verified as described using following test:

CREATE TABLE `ota_messages` (
  `oma_pro` bit(1) NOT NULL,
  `doc_type_id` int(10) unsigned NOT NULL,
  `UD` varchar(200) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert into ota_messages(oma_pro, doc_type_id)
values (0, 0), (0, 1);

SELECT doc_type_id
FROM ota_messages 
WHERE oma_pro=0  
and doc_type_id=0;
[8 Feb 2018 23:00] Roy Lyseng
Posted by developer:
 
Fixed in 5.6.40 and up.