2

I have a between case, where the expression is not a calculation nor a field. Somehow this is valid SQL and the results with different permutations for this kind of system, gives different results.

I have tried to figure out what MySQL does, but I couldnt make out any pattern nor any sense.

Here is the example Query:

SELECT 
*
FROM
<table>
WHERE
status = 'test'
BETWEEN
2 AND
'test';

Feel free to replace the values and data types.

I would appreciate a hint to what could happen here exactly.

Panade
  • 309
  • 3
  • 12
  • Half of the answer (or more) is well-hidden in the MySQL documentation page [Type Conversion in Expression Evaluation](https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html) – axiac May 30 '17 at 15:31

2 Answers2

1

status = 'test' results in true or false which equals 1 or 0 in MySQL.

BETWEEN 2 AND 'test' translates to BETWEEN 2 AND 0 in MySQL.

x BETWEEN a AND b translates to x >= a AND x <= b.

So the condition is never met:

1 >= 2 AND 1 <= 0 => false
2 >= 2 AND 2 <= 0 => false

The query should never return any row.

CORRECTION: It shows that MySQL doesn't Interpret

WHERE status = 'test' BETWEEN 2 AND 'test'

as

WHERE (status = 'test') BETWEEN 2 AND 'test'

but as

WHERE status = ('test' BETWEEN 2 AND 'test')

which I consider a flaw. (EDIT: I looked it up; it is a flaw. The comparision operator = should have precedence over BETWEEN: https://dev.mysql.com/doc/refman/5.7/en/operator-precedence.html.)

Anyway:

  • ('test' BETWEEN 2 AND 'test') is always false, which is 0 in MySQL.
  • If status is a string column, then status converted to integer results in 0 for most strings. Only if the string starts with an integer that integer is converted to number. E.g. '123ABC' results in 123.
  • If status is a numeric column it is not converted.
  • If status is a boolean column it gets converted to 0 for false and 1 for true.
  • So some rows get returned, others not.
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • But it actualy returns results in my case, which is weird. Tested: status has a true result on its own: returns all data Tested: status has a false result on its own: returns all data – Panade May 30 '17 at 15:12
  • Oops, yes, I had to try it. Somehow `BETWEEN` has precedence over `=`, which I didn't expect. I've corrected my answer. – Thorsten Kettner May 30 '17 at 15:21
  • id = 5 BETWEEN 1 AND 10 // note: i have a record with id = 5 // result is false – Panade May 30 '17 at 15:26
  • You are saying the status is a numeric column and for one row the status is 5? That would result in `WHERE 5 = 0` for that row which is false, so the row is not returned by the query. – Thorsten Kettner May 30 '17 at 15:29
  • carefull, should have mentioned i tryed a different field id which is a number. I did this test to simplify the grouping and comparsion with strings. EDIT: how do you come to 5=0? – Panade May 30 '17 at 15:33
  • I don't understand what you tested. Does my explanation match your original query results? (And what datatype is `status` actually?) – Thorsten Kettner May 30 '17 at 15:35
  • In the query from my original question we consider the field status is a varchar. I just threw my query in and tested the result: i got all columns of the table, when status = 'test' is not a valid column. I also tested with 'test' as a existing result, it returns also all rows. – Panade May 30 '17 at 15:49
  • In my test it works with varchar just as I have explained. The query returns all rows that don't start with an integer > 0. Please look here: http://rextester.com/TXI4218 – Thorsten Kettner May 30 '17 at 15:52
  • i did this example: http://rextester.com/IAZF14448 // Your explaination is good for the exact query I put in my question. But I also wrote it acts weird with all kinds of combinations of types. This example is very simple, as it uses numeric values. Somehow the result is not what i expected (bare with me if i used the rextester wrong) – Panade May 30 '17 at 16:25
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/145477/discussion-between-panade-and-thorsten-kettner). – Panade May 30 '17 at 16:25
0

From MySQL documentation:

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described in Section 12.2, “Type Conversion in Expression Evaluation”, but applied to all the three arguments.

So BETWEEN is operating in the same way for integers and for character strings. MySQL is doing type conversion to be able to calculate this operation. This is part of the ANSI standard.

For example:

// returns true
select ('C' BETWEEN 'A' AND 'D');

Or more interesting:

//also returns true
select ('test' BETWEEN 'test' AND 'testa');

For more information check here and here.

lloiacono
  • 4,714
  • 2
  • 30
  • 46
  • status = 'teste' BETWEEN 'test' AND 'testd' // this seems to be always true. if i understand it right, it should conflict with your explaination since e is after d. Is'nt it? – Panade May 30 '17 at 15:15
  • select ('teste' BETWEEN 'test' AND 'testd'); returns false for me; Could you write the complete query so I can check? – lloiacono May 30 '17 at 15:16
  • basicly just add the select and from, out of the question example. I should add that i have entrys that have "test" in colum status. The other 2 (teste and testd) are made up and do not exist as real states. – Panade May 30 '17 at 15:20
  • I managed to reproduce your query like this: select 'test' = 'teste' BETWEEN 2 AND 'test'; but no matter what I change, I always get true. For example select 'test' = 'teste' BETWEEN 4 AND ''; also returns true. Will think about it – lloiacono May 30 '17 at 15:21