orExp resulting in *fewer* results

From: Øyvind Harboe (oyvind.harbo..ylin.com)
Date: Tue May 30 2006 - 04:25:10 EDT

  • Next message: Tore Halset: "Re: orExp resulting in *fewer* results"

    If I manually modify the generated query from the orExp() case to use
    a LEFT OUTER JOIN instead of INNER JOIN, I get the expected
    result.

            // This Java code will result in the SQL query immediately below...
            SelectQuery query1=new SelectQuery(ElcRole.class);
            query1.setQualifier(ExpressionFactory.matchExp(ElcRole.ELC_ROLEMEMBER_ARRAY_PROPERTY + "." + ElcRolemember.TO_SYS_USER_PROPERTY, getSysUser()).
                            orExp(ExpressionFactory.matchExp(ElcRole.TO_SYS_USER_PROPERTY, getSysUser())));
            List l=context.performQuery(query1);

    Generates =>
     
    SELECT DISTINCT t0.ELCROLE_NAME, t0.ELCROLE_ID, t0.SYSUSER_ID
    FROM ELC_ROLE t0 LEFT OUTER JOIN
                          ELC_ROLEMEMBER t1 ON t0.ELCROLE_ID = t1.ELCROLE_ID
    WHERE (t1.SYSUSER_ID = 'BD93F348-8C02-4742-BA97-2456E5CD3881') OR
                          (t0.SYSUSER_ID = 'BD93F348-8C02-4742-BA97-2456E5CD3881')

    But should(?) have generated =>

    SELECT DISTINCT t0.ELCROLE_NAME, t0.ELCROLE_ID, t0.SYSUSER_ID
    FROM ELC_ROLE t0 LEFT OUTER JOIN
                          ELC_ROLEMEMBER t1 ON t0.ELCROLE_ID = t1.ELCROLE_ID
    WHERE (t1.SYSUSER_ID = 'BD93F348-8C02-4742-BA97-2456E5CD3881') OR
                          (t0.SYSUSER_ID = 'BD93F348-8C02-4742-BA97-2456E5CD3881')
                          
    =>

    Prosesseier E7BE040B-F0E3-4ecf-88A6-4B81BF7AE865
    Saksbehandler Drift/Vedlikehold 04190624-7503-11d4-9078-00508BD89D95 BD93F348-8C02-4742-BA97-2456E5CD3881
    Saksbehandler Forespørsel A5F7E0D8-E204-478f-A7A4-DCC07C712673 BD93F348-8C02-4742-BA97-2456E5CD3881
    Saksbehandler HMS 0636F7A4-006B-4926-873F-B5CF888F239C
    Saksbehandler Innkjøp E5DED96B-5F93-4575-85A4-1C14D94B9744
    Saksbehandler Lager 48461B6C-791B-11d4-8792-00508BCE14E0 FF3C97BB-8899-444c-8C6E-91E64D087E86
    Saksbehandler Produksjon 04190625-7503-11d4-9078-00508BD89D95 BD93F348-8C02-4742-BA97-2456E5CD3881
    Saksbehandler Transport E17775E2-897A-41d3-8CC0-D96E24E15DE7 BD93F348-8C02-4742-BA97-2456E5CD3881
    Testrolle 1 AEBEDF2E-2656-4733-99A7-2EAC231DE379 BD93F348-8C02-4742-BA97-2456E5CD3881
    Testrolle 2 A5E8553F-BB4D-4158-BA53-D8F206817938 BD93F348-8C02-4742-BA97-2456E5CD3881
    Testrolle 3 B90B4CCB-43EA-4187-B139-4138041A2F8D BD93F348-8C02-4742-BA97-2456E5CD3881
    Testrolle 4 07AF23BD-7DCA-41cc-9268-A4A08CF0B2DF BD93F348-8C02-4742-BA97-2456E5CD3881
                           

    -- 
Øyvind Harboe
http://www.zylin.com

    This archive was generated by hypermail 2.0.0 : Tue May 30 2006 - 04:56:22 EDT