LEFT JOIN problem within a Stored Procedure

Question

I've an odd problem:

SELECT a.uid, b.*, c.liveId FROM a
INNER JOIN b ON (a.uid=b.uid AND a.versionNo=b.versionNo)
LEFT JOIN c ON (a.uid=c.uid)
WHERE a.memberId=1;

I call that from a query browser and it returns 3 rows. I call it from within a stored procedure and it gives 2 rows (the LEFT JOIN becomes ineffective).

    DELIMITER //

    DROP PROCEDURE IF EXISTS sp_Test //

    CREATE
      DEFINER = CURRENT_USER
      PROCEDURE sp_Test( IN in_mid INTEGER UNSIGNED )

      READS SQL DATA
      NOT DETERMINISTIC

    BEGIN

     SELECT a.uid, b.*, c.liveId FROM a
     INNER JOIN b ON (a.uid=b.uid AND a.versionNo=b.versionNo)
     LEFT JOIN c ON (a.uid=c.uid)
     WHERE a.memberId=in_mid;

    END //

DELIMITER ;

I'm stumped! Any suggestions?

Answer 1

Have you forgotten to COMMIT / ROLLBACK one of the sessions after doing an UPDATE / DELETE / INSERT?

Answer 2

I don't have a lot of experience with MySQL, but are there any connection specific settings that would cause NULL comparisons to act differently in each case? For example, if the versionNo could be NULL then one connection might consider NULL = NULL, but the other might evaluate that as false.

Answer 3

I tried it in SQL Server and after a little change it does work for me:

CREATE PROCEDURE sp_Test(
@in_mid int)
AS
BEGIN
SELECT a.uid, b.*, c.liveId FROM a
 INNER JOIN b ON (a.uid=b.uid AND a.versionNo=b.versionNo)
 LEFT JOIN c ON (a.uid=c.uid)
 WHERE a.memberId=@in_mid;
END

Try it.

Answer 4

I've solved it with an utterly bizarre and illogical change.

SELECT a.uid, b.*, c.liveId FROM a
     INNER JOIN b ON (a.uid=b.uid AND a.versionNo=b.versionNo)
     LEFT JOIN c ON (a.uid=c.uid)
     WHERE a.memberId=in_mid
UNION
SELECT a.uid, b.*, c.liveId FROM a
     INNER JOIN b ON (a.uid=b.uid AND a.versionNo=b.versionNo)
     LEFT JOIN c ON (a.uid=c.uid)
     WHERE a.memberId=in_mid;

Doing that within the sproc gives me all the rows that I was getting when executed within the query browser.