9

I have a small mysql table (MySQL version 5.6.23) :

+-----------+-----------------+------+-----+---------+----------------+
| Field     | Type            | Null | Key | Default | Extra          |
+-----------+-----------------+------+-----+---------+----------------+
| id        | int(6) unsigned | NO   | PRI | NULL    | auto_increment |
| dividends | float(8,6)      | YES  |     | NULL    |                |
+-----------+-----------------+------+-----+---------+----------------+

My where clause follows the row-subqueries syntax.
If I do:

SELECT id, dividends FROM test  
  where  (id,dividends) >= (660,0.5);

or

SELECT id, dividends FROM test 
  where  (id,dividends) >= (660,CAST(0.5 AS DECIMAL(8,6)));

I get this result:

+-----+-----------+
| id  | dividends |
+-----+-----------+
| 660 |  0.500000 |
| 661 |  0.470000 |
| 662 |  0.470000 |
| 663 |  0.470000 |
| 664 |  2.580000 |
| 665 |  2.581000 |
...

It seems to me that dividends >= 0.5 is not taken into consideration. Why?

Matt
  • 14,906
  • 27
  • 99
  • 149
Bruckwald
  • 797
  • 8
  • 23

3 Answers3

8

You're using row constructors. MySQL treats them exactly like rows of a table. Thus WHERE (id,dividends) >= (660,0.5) effectively does the same as:

  1. ORDER BY id,dividends;

  2. Find the point at which (660,0.5) would sit within that ordering;

  3. Filter for only those records that are equal to or greater than that point in the ordering.

Consequently, it is the same as WHERE (id=660 AND dividends>=0.5) OR id>660.

It appears as though the logic you really wish to express is WHERE id>=660 AND dividends>=0.5.

eggyal
  • 122,705
  • 18
  • 212
  • 237
  • So that it means that my query will return all the rows (without filtering) that are after the point found in 2. ? – Bruckwald Mar 23 '15 at 09:41
  • Weird. This is not what I expected after reading the mysql docs. But many thanks for the explanation! – Bruckwald Mar 23 '15 at 09:43
  • @Bruckwald: I agree that the documentation could be more explicit (I will file a documentation bug report on that point now). However, comparisons always entail some notion of sorting—and when we think about how rows are sorted, this would be the only sensible approach. – eggyal Mar 23 '15 at 09:46
2

Looking at your where clause everything regarding id evaluates to true first and then everything else is evaluated but must not conflict with first id results. Its also possible that you do not have any id greater than 660 which has a dividend greater than 0.5.

    (id,dividends) >= (660,CAST(0.5 AS DECIMAL(8,6)));

SQL query will always start evaluating id first for all fields >= 660 ... Then evaluate dividends... you can try running the query below and check results

    where ((id) >= (660)) AND ((dividends) >= (0.5));
Masilo
  • 53
  • 9
0

Split the WHERE conditions to get it to work

SELECT id, dividends
FROM test  
WHERE id >= 660
AND dividends >= 0.5;

When multi-colum IN is used and index is defined only on first column It is not used:

Similar issue was discussed here and may be of use http://www.percona.com/blog/2008/04/04/multi-column-in-clause-unexpected-mysql-issue/

With the bug tracked here http://bugs.mysql.com/bug.php?id=35819

Matt
  • 14,906
  • 27
  • 99
  • 149
  • 4
    That does not explain why the original query fails – juergen d Mar 23 '15 at 09:16
  • Thanks, but I'm wondering why the above example doesn't work. If I use `=` instead of `>=` I get a correct result – Bruckwald Mar 23 '15 at 09:17
  • I think the OP knows if split the condition in where clause it will work, but question is something else – void Mar 23 '15 at 09:17
  • 1
    I've come across this before, there is a blog post somewhere that has a similar issue using `WHERE (a,b) IN (1,2)` i'll try and dig it out – Matt Mar 23 '15 at 09:28
  • added the link to the blog post and added the link to the specific bug – Matt Mar 23 '15 at 11:34