In my application there are some predefined expression templates that can be used to filter data. One of them is "between x and y". A QA engineer claims that there is a defect in its definition, because "between 100 and 200" gives different results than "between 200 and 100". The expression is internally translated to "value >= x and value <= y", so obviously there are no results when the second boundary is lower than the first one. I have checked that the same behavior is in SQL - "between x and y" assumes that y >= x or there are no results. It means that the operator is not commutative, at least in SQL.

So, is the QA right that "between x and y" should be commutative?

  • 11
    No, but perhaps your ui should turn red when someone fills it in wrong.
    – Ewan
    Commented Feb 27, 2017 at 9:24
  • 3
    also, it one of those >= <= should be exclusive so that you can chain 100->200 200->300 etc without getting overlaps
    – Ewan
    Commented Feb 27, 2017 at 9:25
  • 23
    It is not clear if between should include or exclude the lower and higher values. The QA person may be pedantic but as long as there is uncertainty someone needs to clarify the user stories/requirements. It might turn out that they way it is done is how it is supposed to be, but a decision has to be made.
    – Bent
    Commented Feb 27, 2017 at 9:34
  • 11
    It's not a case of right or wrong. It's a case of ... how does the business want the application to behave? The answer is what functionality is expected. Technical debates don't drive required functionality. Required functionality drives technical debates and hopefully inspires the best solution for the business. Commented Feb 27, 2017 at 14:43
  • 2
    This question is more about what the user expects than about what a programmer expects. As such, it would be more appropriate on User Experience.
    – Makyen
    Commented Feb 27, 2017 at 19:30

8 Answers 8


If your current spec leaves this undefined, the behaviour is completely arbitrary, there is no "right" or "wrong" definition. So if your QA engineer cannot point you to the exact paragraph in the spec where this behaviour is defined, you can probably deny his request (though it does not seem to be a requirement which needs much effort to implement it). If you both cannot find a consensus, one person in your team has to make a decision what is more important in context of the application:

  • following the SQL standard as closely as possible
  • not following it because of ergonomics, specific use cases, or other requirements

Whatever decision your team makes, it might be a good idea to document the behaviour and the reason why the decision was made.

  • 57
    you can probably deny his request => I would actually says that first and foremost, the behavior should be defined. Then you can thank the QA guy for pointing out the issue and say that now it's specified to work in <specific way> (and make sure the code matches the spec). Commented Feb 27, 2017 at 10:56
  • 1
    @MatthieuM. I think that's a separate answer that should have 33 upvotes of its own. ;)
    – jpmc26
    Commented Feb 27, 2017 at 21:34
  • 1
    Convert bug to improvement and leave it in the backlog for perpetuity. Thank QA for their diligence. Commented Feb 28, 2017 at 2:26
  • 1
    @MatthieuM. yes, in ideal world there would be a clear requirement for every detail. In such a case I would not need to ask questions on Stack Exchange :)
    – pkalinow
    Commented Mar 21, 2017 at 15:53
  • @pkalinow: I think you misunderstood my comment. My point was that before closing the bug, you should (1) thank the QA guy for pointing out an underspecified piece of code and (2) sit together with whoever is interested to actually specify the behavior. This may mean assigning the QA report to whomever is in charge of writing the specifications, to the product owner if you have such things etc... then, once the behavior it should have has been agreed, you can evaluate whether the software needs change or not. Maybe it'll mean changing the software, maybe it'll mean closing the report... Commented Mar 21, 2017 at 17:50

This is a usability or user experience question. How SQL or any other system behaves is irrelevant, the question is what makes most sense from a users perspective.

The current behavior does not make sense from a user perspective. Either x and y should be interchangeable or it should not be allowed to select an x larger than y. Allowing x larger than y but returning an empty set introduces an unnecessary possibility of errors without providing any benefit.

So the QA engineer is correct there is a defect, but the proposed solution is not necessarily the best. You have to perform usability tests do decide this, or at least ask some representative users what seems most natural to them.

Alternatively, you can ask the question over at https://ux.stackexchange.com/. The people over there actually knows a thing or two about user experience.


There are a couple of sensible choices, and which to choose is dependant on the rest of the system, and the expectations of your users.

You can, as the QA engineer points out, just make the expression commutative, and then the translation would be

between x and y => value >= min(x, y) and value <= max(x, y)

You can restrict valid usage to x <= y, which rather requires that your UI can display "that's not a valid expression" as early as possible.

As a variation of the above, the restriction x < y if you have an expression equals x and prefer that to evaluating value >= x and value <= x

  • Note: Please don't make the statement itself value >= min(x, y) and value <= max(x, y). Precalculate what you can to save your database server the work, especially if it's redundant like that (you can do the relevant operations once and set both results accordingly). It might not matter, depending on the database server and the specific values you're feeding in, but a poorly-written SQL server might well perform the min and max for every record if you put them in the where, and if you can strip out that effort, there's no reason not to.
    – anon
    Commented Feb 28, 2017 at 3:18
  • 6
    Please don't listen to QPaysTaxes - optimizing things without ever measuring the necessity is exactly what Knuth called "premature optimizing beeing the root of all evil". Chances are high in most real world code you won't notice a difference in speed if Min and Max are calculated for every record, but precalculating values (and so introducing extra code and extra redundancy) will make the program much less maintainable.
    – Doc Brown
    Commented Feb 28, 2017 at 6:14
  • @DocBrown I agree that we should not make changes for potential performance gains without measuring, but contrary to your claim I would find pre-calculated bounds more readable than a one-liner, and thus more maintainable. Commented Mar 3, 2017 at 11:57
  • 1
    @JacobRaihle: this may be opinionated, but for my taste value >= min(x, y) and value <= max(x, y) is as readable as value >= minXY and value <= maxXY, where minXY and maxXY are the precalculated bounds. However, for the latter one will have to write some code to add these new two variables to the system, fill them beforehand, don't forget to update these values when x and y changes, and so on. Redundant data always introduces a certain risk of errors.
    – Doc Brown
    Commented Mar 3, 2017 at 12:45

In a non-interactive setting, where your boundaries are created by an script, it usually makes sense to require them to be in order. This creates one less validation check to do, makes more sense semantically, and is trivial to manage.

In an interactive setting, you want to help the user out. If possible, make a GUI that doesn't allow swapped ranges to be entered, or at least makes it as easy as possible to enter ranges in order. If you're entering the ranges by text, take a page from vim, that paragon of usability, and prompt the user to automatically swap inverted ranges:

Backwards range given, OK to swap (y/n)?

If your QA engineer had nothing in the way of UX to show him an inverted range would be undesirable, then he made a reasonable assumption.


Frankly? Don't use "between". At all.

First, the term is incredibly ambiguous, especially in English. Is it commutative? Are the terms exclusive? Inclusive?

Second, if you're doing an interface divorced from the backend, don't worry about the backend behavior; and don't let your users assume inherited behavior, either. Sure, SQL defines its BETWEEN as inclusive, but this is almost never the desired behavior (for example - If you do something like rows BETWEEN :start and :start + :stride you're going to get stride + 1 rows).

Instead, you should be explicitly listing the comparisons for the endpoints. "Greater than or equal to x". "Before today". This removes the ambiguity. It also helps writing cleaner code, and avoid some insidious errors. The rows example from earlier is essentially Djikstra's post about indexing. And allowing SQL to use an inclusive upper bound on some types can result in the wrong data being selected.

  • Well, it's worth thinking of. And thanks for the links. The Dijkstra' post is probably not very relevant, but interesting:)
    – pkalinow
    Commented Mar 1, 2017 at 12:59
  • This does not really answer the OP question, instead, it adds to the confusion. Commented Mar 6, 2017 at 11:31

It non-productive to argue with your QA about who is "right" and who is "wrong". You interpreted the spec differently than they did. That means that the spec is sufficiently ambiguous that it requires clarification.

If the user interface is the spec, and it's not the behaviour the QA expects, it's not going to be the behaviour at least some users expect. That indicates a usability problem (even if you want to argue PEBKAC). Work with your QA to find a satisfactory solution to that.

As a general point, be careful with words like "between" that seem clear, but are not. Apart from your disagreement about whether is should commute, their are problems with inclusiveness on either end, and may intuitively mean different things on different domains (for example, "between Friday and Monday" will mean something different to most people than "between Monday and Friday")


I'll fork a UNIX principle which talks about simple interfaces.

Wherever there is an interface you are offering to the outside world, keep the thing as least surprising as possible!

Now that I have reduced the problem statement to a more pragmatic one, I think it will take you just a few moments to realize that when specifying number ranges, it's but obvious to ***keep the smaller one as the former***. If it's still a conundrum, think like this- How many times have you used the reverse way of representing two numbers while telling kids how to compare those?

If your QA engineer calls it a bug, tell him politely that you're expecting some real bugs, and not ways to beam costly energy on trivial things.


Make your debug code throw an error condition or log a warning whenever it is passed the values in wrong order. This way the calling code can check and exchange parameters, if necessary. This way users of this 'feature' will become aware and do the right thing (which you don't know beforehand).

Not the answer you're looking for? Browse other questions tagged or ask your own question.