Valentina woes

[Ruslan Zasukhin]

On 4/26/04 11:19 PM, "Ken Ray" <kray at sonsothunder.com> wrote:

> In order to give you a good time estimate for the rest of the Search stack,
> I've done some more research to find the correct SQL syntax for Valentina to
> produce correct results. Below is an example of a query which works fine
> under MySQL and PostgreSQL, but produces incorrect results in Valentina :
> 
> --------------------------------
> 
> SELECT Projects.Title, Breakdowns.BdDate Ass1.RecID, Ass1.EntityType,
> Ass1.AltEntityName, Ass2.RecID, Ass2.EntityType, Ass2.AltEntityName
> Breakdowns.RecID, Projects.RecID FROM Projects, Breakdowns, Associations
> Ass1, Associations Ass2 WHERE Breakdowns.ProjectLink = Projects.RecID AND
> (Projects.RecID = Ass1.ProjectLink AND Ass1.EntityType = 'Studio') AND
> (Breakdowns.RecID = Ass2.BreakdownLink AND Ass2.EntityType = 'Producer') AND
> (Ass1.AltEntityName LIKE 'ABC' NO_CASE AND Ass2.AltEntityName LIKE 'B'
> NO_CASE)

Hi Ken,
Hi Robert,

Guys, let me repeat again,
Valentina 1.x really have some glitches in WHERE clause.
This is result of my implementation.

Again look on this query in formatted form

SELECT 
    Projects.Title,
    Breakdowns.BdDate Ass1.RecID,
    Ass1.EntityType,
    Ass1.AltEntityName,
    Ass2.RecID, 
    Ass2.EntityType,
    Ass2.AltEntityName
    Breakdowns.RecID,
    Projects.RecID 
FROM 
    Projects, 
    Breakdowns, 
    Associations Ass1,
    Associations Ass2
WHERE 
    Breakdowns.ProjectLink = Projects.RecID
AND
    ( Projects.RecID = Ass1.ProjectLink AND Ass1.EntityType = 'Studio')
    AND
    (Breakdowns.RecID = Ass2.BreakdownLink AND Ass2.EntityType = 'Producer')
    AND
    (Ass1.AltEntityName LIKE 'ABC' NO_CASE AND Ass2.AltEntityName LIKE 'B'
NO_CASE)


------------
Your query have next form:

    (link condition for a Tx and other Tx conditions)

To be safe with Valentina you should use more logical form of query


WHERE 
    (all! link conditions)
and 
    ( 
        (all T1 search conditions)
    and 
        (all T2 search conditions)
        ...    
    )


So your query must be re-written as

WHERE 
        Breakdowns.ProjectLink = Projects.RecID
    AND Projects.RecID = Ass1.ProjectLink
    AND Breakdowns.RecID = Ass2.BreakdownLink
AND 
    (Ass1.EntityType = 'Studio'
     AND Ass1.AltEntityName LIKE 'ABC' NO_CASE )
  AND 
    (Ass2.EntityType = 'Producer'
     AND Ass2.AltEntityName LIKE 'B' NO_CASE )


-------------------------
Now I see why your query did not work.
You have had


    ( Projects.RecID = Ass1.ProjectLink AND Ass1.EntityType = 'Studio')
    AND
    (Breakdowns.RecID = Ass2.BreakdownLink AND Ass2.EntityType = 'Producer')
    AND
    (Ass1.AltEntityName LIKE 'ABC' NO_CASE
        AND Ass2.AltEntityName LIKE 'B' NO_CASE)

Note that SEARCH conditions of Ass1 table are located in different search
groups. (btw, I wonder what sense you have put into such query?!)

Valentina 1.x expect from you that ALL search conditions for one table are
grouped together.


------------------------------
And let me explain, why IMHO way which Valentina expect is Logical.
And I think if you will make habits write queries in such way then you will
get them even more effective in other DBMS also.


--------------
Q:

    why we should at first group link conditions, then search conditions?

A:

    Because if you recall Sql92 standard, then you can note, that it also
    move all link conditions forward, even more they move it into
    FROM clause.

        FROM T1 inner join T2 on key = ptr and ...
        WHERE search conditions

    so why create mess and mix all together?
    let's do things in elegant way.


--------------
Q:

    why we should group search conditions of the same table together.

A:
    and I can add: even use () to show explicitly each group.

    this is only Valentina 1.x limitations. In Valentina 2.0 this is fixed.
    but actually this is again elegant accurate way.
    
    In your query you specify for Ass1 table
        Ass1.EntityType = 'Studio' and
        Ass1.AltEntityName LIKE 'ABC' NO_CASE

    it is logical group this conditions, because DBMS will use this
    2 conditions together for original table to get selection of records,
    and later use ONLY SELECTED records to build join.


-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------