MySQL Bugs: #4655: SELECT clause ignoring one WHERE condition

Bug #4655	SELECT clause ignoring one WHERE condition
Submitted:	20 Jul 2004 20:26	Modified:	17 Sep 2004 18:35
Reporter:	EDUARDO DIAZ COMELLAS	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.20	OS:	Linux (Debian)
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
Hi all,

I am facing a rather curious problem with a SELECT clause. The table I'm querying has this definition:

CREATE TABLE `rx_tbl` (
`rx_id` int(11) NOT NULL auto_increment,
`fecha` datetime default NULL,
`ip` varchar(15) NOT NULL default '0',
`puerto` int(11) NOT NULL default '0',
`nserie` int(11) NOT NULL default '0',
`tt` tinyint(3) unsigned NOT NULL default '255',
`procesado` tinyint(1) unsigned NOT NULL default '0',
`datos` text,
PRIMARY KEY (`rx_id`),
KEY `procesado_ASC` (`procesado`),
) 

Several apps Visual Basic applications are accessing the table (with ODBC). One inserts records (leaving the default value 0 for 'procesado') with several values for the 'tt' field. The other apps are just making SELECTs for different values of 'tt', processing the data and updating the table making 'procesado'=1.

The two weird clauses are:

SELECT * FROM rx_tbl WHERE procesado = 0 AND tt <> 26 AND tt <> 27 AND tt <> 9

SELECT * FROM rx_tbl WHERE procesado = 0 AND (tt = 26 OR tt = 27)

Everything works like a charm until, at random intervals, the SELECT clauses return an abnormal number of rows (arround 500000 for the first SELECT, and 30000 for the second)... this number of rows is the same that would be returned if the condition "procesado=0" was not used. I've checked that most of the records returned have procesado=1.

The SELECT and UPDATE sentences are hard coded, so no mistake can be made depending on the value of a variable. The MySQL server is v4.0.20, the one that comes with debian unstable. I've made the select clauses work from a perl application running in the server (linux) and I find the same errors.

How to repeat:
I've tried to reproduce accessing only from perl::DBI in a test environment with no luck. The error appears when using ODBC for the inserts, selects and updates (this is... our production environment, sigh). 

It may be related to the use of the ODBC connector for the insert, select and update clauses. If this is the case, please reassign this bug to the appropiated category.

Can you submit a test case (either the VB or the Perl case would be fine) that demonstrates this behavior?

There are 2 apps accessing this table. The relevant code follows:

APP #1:

    sql = "INSERT INTO pruebas_rx_tbl (fecha, ip, puerto, nserie, tt, datos)"
    sql = sql & " VALUES ('" & Format(fecha, "yyyy-mm-dd Hh:Nn:Ss") & "', '" & ip & "'" & ", " & puerto _
            & ", " & nserie & ", " & ", " & tt & ", '" & datos & "')"
    dbGps.Execute sql, , adExecuteNoRecords

APP #2:

    sql = "SELECT * FROM pruebas_rx_tbl"
    sql = sql & " WHERE procesado = 0 AND tt = 26 OR procesado = 0 AND tt = 27"
    sql = sql & " ORDER BY rx_id ASC LIMIT 10000"
    rsPruebasRx.Open sql, dbGps, adOpenStatic, adLockReadOnly
    
    If rsPruebasRx.RecordCount >= 10000 Then
        Call imprime(vbCrLf & Now & ": ¡ERROR con MYSQL porque PRUEBAS_RX_TBL devuelve 10.000 registros (" _
                        & rsPruebasRx.RecordCount & ") de tipo 26 o 27 sin procesar!" & vbCrLf, True)
    Else
        Do While Not rsPruebasRx.EOF
            'ponemos a procesado
            seg = Second(Now)
            If seg <> 10 And seg <> 20 And seg <> 30 And seg <> 40 And seg <> 50 Then
                'dejamos algunos segundos sin procesar...
                dbGps.Execute "UPDATE pruebas_rx_tbl SET procesado = 1 WHERE rx_id = " & rsPruebasRx("rx_id"), , adExecuteNoRecords
            End If
            rsPruebasRx.MoveNext
        Loop
    End If
    rsPruebasRx.Close
    
    DoEvents
    
    
    sql = "SELECT * FROM `pruebas_rx_tbl`"
    sql = sql & " WHERE `procesado` = 0 AND `tt` != 26 AND `tt` != 27 AND `tt` != 9"
    sql = sql & " ORDER BY  tt ASC, rx_id DESC LIMIT 10000"
    rsPruebasRx.Open sql, dbGps, adOpenStatic, adLockReadOnly
    
    If rsPruebasRx.RecordCount >= 10000 Then
        Call imprime(vbCrLf & Now & ": ¡ERROR con MYSQL porque PRUEBAS_RX_TBL devuelve 10.000 registros (" _
                        & rsPruebasRx.RecordCount & ") de tipo <>9, <>26 y <>27 sin procesar!" & vbCrLf, True)
    Else
        Do While Not rsPruebasRx.EOF
            'ponemos a procesado
            seg = Second(Now)
            If seg <> 10 And seg <> 20 And seg <> 30 And seg <> 40 And seg <> 50 Then
                'dejamos algunos segundos sin procesar...
                dbGps.Execute "UPDATE pruebas_rx_tbl SET procesado = 1 WHERE rx_id = " & rsPruebasRx("rx_id"), , adExecuteNoRecords
            End If
            rsPruebasRx.MoveNext
        Loop
    End If
    rsPruebasRx.Close
    
    DoEvents
    
    
    sql = "SELECT * FROM `pruebas_rx_tbl`"
    sql = sql & " WHERE `procesado` = 0 AND `datos` IS NOT NULL AND `tt` = 9"
    sql = sql & " ORDER BY  rx_id DESC LIMIT 10000"
    rsPruebasRx.Open sql, dbGps, adOpenStatic, adLockReadOnly
    
    If rsPruebasRx.RecordCount >= 10000 Then
        Call imprime(vbCrLf & Now & ": ¡ERROR con MYSQL porque PRUEBAS_RX_TBL devuelve 10.000 registros (" _
                        & rsPruebasRx.RecordCount & ") de tipo 9 sin procesar!" & vbCrLf, True)
    Else
        Do While Not rsPruebasRx.EOF
            'ponemos a procesado
            seg = Second(Now)
            If seg <> 10 And seg <> 20 And seg <> 30 And seg <> 40 And seg <> 50 Then
                'dejamos algunos segundos sin procesar...
                dbGps.Execute "UPDATE pruebas_rx_tbl SET procesado = 1 WHERE rx_id = " & rsPruebasRx("rx_id"), , adExecuteNoRecords
            End If
            rsPruebasRx.MoveNext
        Loop
    End If
    rsPruebasRx.Close
    
    DoEvents

This perl app is just a monitor that prints out the number of rows returned. I wrote it becouse a couldn't believe that this was a MySQL error. The typical output counts from 0 to 30 rows to each query. The bad one counts arround 500.000 for the first and 30.000 for the second. 

May you need any further information, don't hesitate to contact me. If you want the logs or even access to the machine, please contact me at ediaz at ultreia dot es

#!/usr/bin/perl
#

use DBI;
$|=1;
($retardo)=@ARGV;
die "Sintaxis: $0 milisegundos\n" if($#ARGV != 0) ;

$db = DBI->connect ("dbi:mysql:dbname=mydb:host=192.168.1.10","blabla","blablabla")|| die "Fallo al conectar a la base de datos\n";

while(1) {
    ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
    $dat1=$db->selectall_arrayref("SELECT COUNT(*) FROM `rx_tbl` WHERE 1 AND `procesado` = 0 AND `tt` <> 26 AND `tt` <> 27 AND `tt` <>
 9");
    $err1=$db->err.":".$db->errstr;
    $dat2=$db->selectall_arrayref("SELECT COUNT(*) FROM `rx_tbl` WHERE `procesado` = 0 AND `tt` = 26 OR `procesado` = 0 AND `tt` = 27"
);
    $err2=$db->err.":".$db->errstr;
    print $year."/".$mon."/".$mday." ".$hour.":".$min.":".$sec.",".${$$dat1[0]}[0].",".${$$dat2[0]}[0].",".$err1.",".$err2."\n";
    select(undef, undef, undef, $retardo/1000);
}
$db->disconnect;

After extensive testing, I am unable to repeat this.  This may be a problem with the build you are using; try our official, current 4.0 release binary and our current MyODBC driver instead.