WHERE clause regression in 10.1B - Forum - OpenEdge Development - Progress Community
 Forum

WHERE clause regression in 10.1B

  • We would just like to know why this was changed (on purpose or a mistake that will eventually be reversed) so that we know how to proceed in the future with how we handle these where clauses.

    The ?OR? becomes a problem when we have a leading-where in the metadata with and OR condition and it is coupled with AND conditions that are tacked on as part of the search criteria.

    company=} OR company="*" AND committee = ?Board of directors?

    In earlier versions of Open Edge we would get back all committees where the committee = ?Board of directors? with the company value as either ?? or the current company. (company=} OR company="") AND committee = ?Board of directors?

    In Open Edge 10.1b the results returned all committees with the company equal to the current committee, plus the committee ?Board of directors? with a company = ??. company=} OR (company="" AND committee = ?Board of directors?) We had to manually add the parentheses around the value from the leading-where before adding on the additional search criteria in order to obtain query results as before.

  • Standard boolean hierarchy has OR lower priority than AND, so

    x OR y AND b

    is the same as

    x OR (y and b)

    not

    (x or y) and b

    so I'm a bit surprised your prior construct worked the way you say it did.

  • this is the biggest bug in the 4gl, as far as i can remember. it is that serious.

    it is a one of the most fundamental issues that effects almost all queries and basically all programs for thousands and thousands of users.

    it's just like the bi/undo-redo log not working properly, if not more.

    like Tim mentioned in boolean algebra consecutive AND predicates should be treated as being in a parenthesis.

    think of the where clause as a TREE of parenthesis. starting with the main parenthesis, if you will, that may also contain other sub parenthesis and so on.

    if a parenthesis has both AND and OR operations, consecutive AND operations should be treated as being in their own parenthesis.

    (of course, parenthesis should be resolved first).

    for example:

    WHERE salesorder.item = "bike" OR salesorder.duedate > 1/1/08 AND salesorder.cust = "prgs"

    EQUALS

    WHERE salesorder.item = "bike" OR ( salesorder.duedate > 1/1/08 AND salesorder.cust = "prgs" )

    i have lots of experience in this field especially refactoring queries and where clauses.

    i would love to continue this conversation and land a hand where ever i can.

    could this by any chance have to do with query optimization features that you're working on ?

  • The problem doesn't seem to be in 10.1C.

    Do we know from what version, and maybe what service pack the problem started ??

  • And, is it actually a bug or problem. I do understand that changes in behavior fro version ot version can be a hassle and that PSC has been uncommonly friendly in this regard, but there is a part of me that also thinks that anyone who has relied on operator precedence, instead of adding a couple of () to make it clear to everyone what the intention was, almost deserves a shot in the foot eventually.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • I must be misreading something as it seemed to be working as it should.

    I'd also be surprised that it didn't work in the past as well, but I usually add brackets. Apart from clarity I don't rely on my understanding of the rules an ensure it's what I want by using brackets.

  • Hello Thomas,

    I could not disagree more.

    Another way to say it is BTW we mixed up the basic math order of execution.

    So that, 5 * 5 + 10 = 35 now equals 75.

    Obviously, it's ridiculous.

    And honestly I'm really amazed about your remark regarding code compatibility between different versions.

    To put it differently it makes millions and millions of lines of code act in a whole different way.

    It's a critical bug in the most fundamental, core part of all applications.

    It's not a bug it's the mother of all bugs.

    I am really amazed how could such a bug not be caught in QA.

    And with all due respect why Simon is not sure if it's a bug or not ?

  • Is this already confirmed as a bug by Progress? Or an intended change in behaviour (then it would have been documented in the Release notes or similar)?

    Like Miles, I have also not seen any change in behaviour and I've done a lot of query optimization work in many different Progress and OpenEdge (including 10.1B) releases and I'm pretty sure that I would have noticed a difference if there was one.

    And back to Simons first post:

    company=} OR company="*" AND committee = ?Board of directors?

    Why don't you use a dynamic query here? Obviously you want to give the user the chance to select all companies by entering "*". If you don't filter on company, don't use it in the query.

  • Isn't anyone else surprised that

    A. They don't realize it's even a bug. Let alone how big of a bug it is.

    B. And from the mom's and pop's shop like attitude considering it's a software with millions of users.

    I'd really be interested if Salvador can jump into the conversation.

  • Why is it so hard to believe that others did not run into this before and are interested if it's already confirmed a bug?

    If it's such a big bug as you said, I would not loose any time and log it with tech support! The community is a great thing - but nobody here has the hands on OpenEdge source code and is able to fix the bug.

  • Mike,

    My remarks were referred to Progress not you.

    I also didn't imagine you had a copy of the ABL runtime source code.

    And like I said earlier I have not seen them in 10.1C. But I do have several client running 10.1B.

    And like you I would also like to know at what version, maybe what service pack this huge! bug got in.

  • And honestly I'm really amazed about your remark regarding code compatibility between different versions.

    To put it differently it makes millions and millions of lines of code act in a whole different way.

    My point is that in any other language than ABL which has anything like the length of history and the degree of new features, people regularly have to rewrite their applications ... it is just part of the expectation. That we don't is extraordinary.

    It's a critical bug in the most fundamental, core part of all applications.

    It's not a bug it's the mother of all bugs.

    I know you like to get passionate about things, Alon, but I'm a little less excited. Yes, I think you should report it to Tech Support and follow through with them. Greater consistency is always better. But, it is also the case that large numbers of ABL coders have thought for years that it was best practice to always use explicit parentheses to make order of execution clear. Anyone who has followed that best practice has no issue.

    So, bug, apparently, but one that is easily dealt with and which best practice makes irrelevant.

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • You're right

    But in this case its called for.

    It doesn't get more fundamental then that, queries and WHERE clauses.

    And just like you shouldn't parenthesize 5th grade algebra that has both division and additions there's no reason for parenthesis in this case.

    You'll find lots of WHERE clauses that aren't needlessly parenthesized written by gurus and world class experts.

    And lots more in the docs, kb, PEG, progresstalk, and all other DBMS books ... and endless amount more in the real world.

    Frankly I'd expect more then "Hey Tim do you think this will be a problem ?" especially from the guys writing the software.

    There's alot more we have ahead of us then this. It doesn't get anymore basic then this ?

    Progress is talking about a query optimizer somewhere in the future.

    The amount of WHERE clause refactoring that goes into something like that is 100 times more complicated. We can't even get the basics down.

    BTW has anyone actually ran into such a case ?

  • While I think parentheses should be used whenever there is a potential for ambiguity, I am not so religious about them in mathematical constructs because I think that operator precedence rules there are pretty broadly understood ... at least if one sticks to fairly simple combinations of either X and / with + and -. I don't think that there is broad understanding about logical operator precedence and therefore parentheses should always be used. Not because it is necessary, but because it makes it clear and unambiguous to the next person.

    There should be a Prolint rule for it.

    BTW, has anyone cleared up where the problem is and isn't. We seem to have a report of the issue being there in some version of 10.1B, but not in 10.1C. Is is fixed in some 10.1B service pack? I.e., is all this fuss and fury over something that has already been found and fixed?

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • You're right, I agree.

    You should not have to know about it

    but the guys writing database servers should, and alot more.