存储过程中的LEFT JOIN问题

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).

我从查询浏览器调用它,它返回3行。我从存储过程中调用它,它给出了2行(LEFT JOIN变得无效)。

    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?

我很难过!有什么建议?

4 个解决方案

#1

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

在执行UPDATE / DELETE / INSERT之后,您是否忘记了COMMIT / ROLLBACK其中一个会话?

#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.

我没有很多MySQL的经验,但有没有任何特定于连接的设置会导致NULL比较在每种情况下都采取不同的行为?例如,如果versionNo可能为NULL,则一个连接可能会认为NULL = NULL,但另一个连接可能会将其评估为false。

#3

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

我在SQL Server中尝试过,稍作修改后它对我有用:

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.

#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.

在sproc中执行此操作会为我提供在查询浏览器中执行时获得的所有行。

#1