A query that is on a mission to turn my hair gray

[dlite922]

All I want to ask is

What can you think of in your WILDEST imagination that explains a query that returns results 1 out of several times?

The same exact query (from a mysql prompt) I execute (by pressing the UP key) repeatedly sometimes returns results other times it's an empty result set.

The data is not being rebuild and updated in the background. In fact there is a cron job that updates it every half hour, I've taken that out. There is no other process changing the data.

What in the Universe is going on?

The query is simple. SELECT some fields FROM a few different tables WHERE cartesian joins. (tried LEFT JOINS too).

I use a Stored Function that converts an ID between two format to join a couple of those tables (I had a thread about this function a while back if you remember)

Shoot whatever ideas you have at me. At this point what I know of simple logic is going out the window and I'm ready to smash the computer with a rock.

I envy cavemen


Thanks to anyone who can make me sane again,




Dan

[dlite922]

I think I found a MySQL bug.

I'm going to create a sample DB with sample Tables and create a Stored Function.

I think the stored function caches the last value returned and returns it even if given a different input but only when used in a LEFT JOIN. Function works fine if you just do a SELECT with it.

Will keep you updated...



Dan

[dlite922]

Hey Guys,

If anybody has a test MySQL server handy, could you look over this bug report. I'll submit it to MySQL bug if you guys confirm it on your end too.

Thanks alot.

 
/**
 
Desc:             Example Schema, Data and Query that illustrate a possible MySQL bug involving use Stored Function in a LEFT JOIN. 
 
Created:         10/05/2009
Author:         Dan M (dlite922)
Version:         mysql  Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (x86_64) using readline 5.0
Repeatability:     Always 
 
/*
 
/** SCHEMA **/
drop table IF EXISTS image;
drop table IF EXISTS event_image;
drop table IF EXISTS event;
 
Create table event (
    event_id Int NOT NULL AUTO_INCREMENT,
    preconverted_id Varchar(5) NOT NULL,
    name Varchar(20) NOT NULL,
    UNIQUE (event_id),
 Primary Key (event_id)) ENGINE = MyISAM;
 
Create table event_image (
    postconverted_id Int NOT NULL,
    image_id Int,
    UNIQUE (postconverted_id),
 Primary Key (postconverted_id)) ENGINE = MyISAM;
 
Create table image (
    image_id Int NOT NULL AUTO_INCREMENT,
    image_name Varchar(20) NOT NULL,
    image_type Varchar(4) NOT NULL,
    UNIQUE (image_id),
 Primary Key (image_id)) ENGINE = MyISAM;
 
 
 
 
/** STORED FUNCTION TO CONVERT IDs **/ 
DELIMITER $$
DROP FUNCTION IF EXISTS ConvertID$$
CREATE FUNCTION ConvertID(preconverted_id VARCHAR(5)) RETURNS INT
DETERMINISTIC
BEGIN
 DECLARE catText CHAR(1); 
 DECLARE catNum INT; 
 DECLARE series CHAR(4); 
 DECLARE FinalID INT;  
 
 SET catText = SUBSTRING(preconverted_id FROM 1 FOR 1); 
 SET series = SUBSTRING(preconverted_id FROM 2 FOR 5); 
 
 IF(catText = 'A') THEN
  SET catNum = 1;
 ELSEIF(catText = 'B') THEN
  SET catNum = 2;
 ELSEIF(catText = 'C') THEN
  SET catNum = 3;
 END IF;
 
 SET FinalID = (catNum << 26 | series); 
 RETURN FinalID;
END $$
DELIMITER ;
 
 
 
 
/** SAMPLE DATA **/
INSERT INTO event VALUES (1,'A1234','Bar'),(2,'B5678','Foo'),(3,'C9011','Baz'); 
INSERT INTO event_image VALUES (67110098,55),(134223406,97),(201335603,42); 
INSERT INTO image VALUES (55,'Bar_and_friends','jpg'),(97,'FoorAreYou','gif'),(42,'BazFishing','png'); 
 
 
 
 
 
/** QUERY THAT EXPOSES BUG **/
/** PLEASE OBSERVE: covertedColumn. All fields have the converted of the last ID (that of 'Baz'). **/
SELECT 
 e.event_id,
 e.preconverted_id,
 e.name, 
 ConvertID(e.preconverted_id) AS convertedColumn,
 ei.postconverted_id,
 ei.image_id, 
 i.image_id,
 i.image_name,
 i.image_type
FROM 
 event AS e LEFT JOIN event_image AS ei ON ConvertID(e.preconverted_id) = ei.postconverted_id 
 LEFT JOIN image AS i ON ei.image_id = i.image_id; 
 
 
/** If you don't believe my function is working properly checkout these results **/
SELECT e.event_id, e.name, ConvertID(e.preconverted_id) FROM event AS e;
 
// OR SIMPLY: 
SELECT ConvertID('A1234'); 
SELECT ConvertID('B5678'); 
SELECT ConvertID('C9011'); 
 
 
// END OF FILE 
 
 

I've tried with and without the DETERMINISTIC flag on the function.

Please let me know if you see something odd. I often make silly mistakes.

Thanks again,


Dan