OpenEdge 11.7.5 => 12 Query Not Working

Posted by alex.wiese.pulse on 30-Jan-2020 07:28

This query returns YES NO on v11.7.5 when a matching usr record is found, but a matching security record is not.

On v12.1 it returns NO NO with the exact same data.

I can't find any mention of this breaking change of behaviour in the documentation. Surely this is a bug?

FOR FIRST usr NO-LOCK
      WHERE usr.user_id EQ 'admin',
      EACH security NO-LOCK WHERE security.users = usr.user_id: 
END.
      
DISPLAY AVAIL usr AVAIL security.



Disabling "server side joins" fixes the problem, so it appears to be a bug with the new "server side joins" feature.

Posted by frank.meulblok on 30-Jan-2020 09:28

A key point here is that buffer state outside a FOR FIRST loop is *undefined* (see https://knowledgebase.progress.com/articles/Article/000052043). Which means you really shouldn't relied upon.

With client-side joins, the client reads the left-hand side first, then fail to find a record on right-hand side, and the 1st record lingers around in the buffer due to black magic implementation details even though the inner join fell through.

With server-side joins, the client isn't aware of anything until the server returns. The server gets the query, finds the left-hand side record, fails to find a record on right-hand side, and returns nothing to the client. Because that's what's expected of an inner-join query if one side of the join has no data. 

Make the query an OUTER-JOIN query and chances are it'll also work with server-side joins. Or not, because on the client you're still checking the buffers outside the loop, thus the state of the buffers still is undefined.

All Replies

Posted by Lieven De Foor on 30-Jan-2020 07:38

I would never depend on the availability of buffers outside a FOR statement.

But inside the FOR, since this is an inner-join, I would expect both to be available.

Can you rewrite using a (dynamic) query and get the first result?

Then both should be either available, or both not...

Posted by alex.wiese.pulse on 30-Jan-2020 07:50

[quote user="Lieven De Foor"]

I would never depend on the availability of buffers outside a FOR statement.

But inside the FOR, since this is an inner-join, I would expect both to be available.

Can you rewrite using a (dynamic) query and get the first result?

Then both should be either available, or both not...

[/quote]

Hmm this is a pattern we use extensively in our application (rightly or wrongly); we haven't had an issue with it before and didn't expect it to change in v12.

Inside the FOR will never be executed if there is no matching security record. 

On v11.7.5 and earlier the first buffer is available (even when there is no matching security record), and in v12.1 with "server side joins" disabled it's also available, so it appears to be a bug in "server side joins", or at the least a breaking change.

If it's intended behaviour with the new "server side joins" then we will have to rewrite a lot of code [:|]. 

Posted by frank.meulblok on 30-Jan-2020 09:28

A key point here is that buffer state outside a FOR FIRST loop is *undefined* (see https://knowledgebase.progress.com/articles/Article/000052043). Which means you really shouldn't relied upon.

With client-side joins, the client reads the left-hand side first, then fail to find a record on right-hand side, and the 1st record lingers around in the buffer due to black magic implementation details even though the inner join fell through.

With server-side joins, the client isn't aware of anything until the server returns. The server gets the query, finds the left-hand side record, fails to find a record on right-hand side, and returns nothing to the client. Because that's what's expected of an inner-join query if one side of the join has no data. 

Make the query an OUTER-JOIN query and chances are it'll also work with server-side joins. Or not, because on the client you're still checking the buffers outside the loop, thus the state of the buffers still is undefined.

Posted by alex.wiese.pulse on 30-Jan-2020 10:58

[quote user="frank.meulblok"]

A key point here is that buffer state outside a FOR FIRST loop is *undefined* (see https://knowledgebase.progress.com/articles/Article/000052043). Which means you really shouldn't relied upon.

With client-side joins, the client reads the left-hand side first, then fail to find a record on right-hand side, and the 1st record lingers around in the buffer due to black magic implementation details even though the inner join fell through.

With server-side joins, the client isn't aware of anything until the server returns. The server gets the query, finds the left-hand side record, fails to find a record on right-hand side, and returns nothing to the client. Because that's what's expected of an inner-join query if one side of the join has no data. 

Make the query an OUTER-JOIN query and chances are it'll also work with server-side joins. Or not, because on the client you're still checking the buffers outside the loop, thus the state of the buffers still is undefined.

[/quote]

Yeah that makes sense. I guess we have a lot of code to refactor [:'(]

This thread is closed