CODEWITHSUNDEEP
mysqlsqlsubquery
Bruckwald
Bruckwald

Reputation: 797

MySQL row subquery comparison issue

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?

Upvotes: 9

Views: 441

Answers (3)

Matt
Matt

Reputation: 15071

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

Upvotes: 0

eggyal
eggyal

Reputation: 125865

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.

Upvotes: 8

Masilo
Masilo

Reputation: 53

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));

Upvotes: 2

Related Questions