odd SQL from a null-or-some-value expression

From: Bryan Lewis (brya..aine.rr.com)
Date: Fri Oct 29 2004 - 18:50:26 EDT

  • Next message: Andrus Adamchik: "Re: odd SQL from a null-or-some-value expression"

    I've run into a puzzle trying to formulate a query. I initially did a
    direct port of a query from our old WebObjects apps, but the SQL it
    generated was a surprise and didn't fetch the right set of records.

    We have a TradeSource entity with a db column TradeStatus_ID which
    relates to a read-only TradeStatus table. In other words, a
    non-mandatory 1:1 relationship TradeSource -> TradeStatus. Let's say I
    want to fetch all TradeSources with either a null tradeStatus OR a
    tradeStatus having one of several primary-key values. (Legacy values
    that are meaningful to our users.)

    I didn't want to expose the TradeStatus_ID foreign key in the
    TradeSource entity, but I figured there's no harm in exposing the
    primary key of the read-only TradeStatus table. That attribute is named
    "oid". My first attempt was:

         Expression statusNullExp = ExpressionFactory.
            matchExp("tradeStatus", null);
         String[] types = { "FX", "CB", "TE", "LM", "TC", "U", "ES" };
         Expression statusValuesExp = ExpressionFactory.
            inExp("tradeStatus.oid", types);

         Expression statusExp = statusNullExp.orExp(statusValuesExp);
         SelectQuery query = new SelectQuery("TradeSource", statusExp);

         List tradeSources = dc.performQuery(query);

    It generated this SQL:

    SELECT * FROM TRADESOURCE t0, LKPTRADESTATUS t1 WHERE
    t0.TRADESTATUS_ID = t1.TRADESTATUS_ID AND
    ((RTRIM(t0.TRADESTATUS_ID) IS NULL)
    OR (RTRIM(t1.TRADESTATUS_ID) IN ('FX', 'CB', 'TE', 'LM', 'TC', 'U', 'ES')))

    That fetches too few records. What I expected was:

    SELECT * FROM TRADESOURCE t0, LKPTRADESTATUS t1 WHERE
    ((RTRIM(t0.TRADESTATUS_ID) IS NULL)
    OR (RTRIM(t0.TRADESTATUS_ID) IN ('FX', 'CB', 'TE', 'LM', 'TC', 'U', 'ES')))

    That is, the same SQL with no reference to the second table.
    The extra qualifier "TRADESTATUS_ID = t1.TRADESTATUS_ID" required a
    related record to exist, defeating the effect of "TRADESTATUS_ID IS NULL".

    Now, I can work around it by exposing the TradeStatus_ID foreign key in
    the TradeSource table, but that feels like a hack. Another way would be
    to use the db: attribute in the IN expression, that is, replacing:

         ExpressionFactory.inExp("tradeStatus.oid", types);

    with:

         Expression.fromString("db:TRADESTATUS_ID in
            ('FX','CB','TE','LM','TC','U','ES')");

    Both ways work, but it seems odd that one should have to think about
    such things. Maybe I'm missing something.

    This archive was generated by hypermail 2.0.0 : Fri Oct 29 2004 - 18:53:15 EDT