CODEWITHSUNDEEP
mysqldatabaseselect
GabrieleMartini
GabrieleMartini

Reputation: 1721

MySQL date comparison query

I have this table in my database:

table t

id   |  a   | b  | date_x

1    |  81  | 12 | 2018-03-16
2    |  9   | 54 | 2025-04-21
3    |  81  | 67 | 2018-03-16
4    |  763 | 81 | 2018-03-16
5    |  90  | 22 | 2025-12-08

date_x is type DATE

I would like to select the rows where a = 81 or b = 81 and date_x is before 2019-05-28 .

So I perform the following query in MySQL Workbench:

SELECT * FROM t
WHERE a = '81' OR b = '81'
AND date_x > '2019-05-28';

This is what I get:

1    |  81  | 12 | 2018-03-16
3    |  81  | 67 | 2018-03-16

I would expect that 2018-03-16 is not later than 2019-05-28 . Moreover, why only 2 rows returned? There is another with same date in date_x column.

This query return the same:

SELECT * FROM t
WHERE a = '81' OR b = '81'
AND date_x > str_to_date('2019-05-28', '%Y-$m-%d');

I have performed the following query for debug:

SELECT * FROM t
WHERE a = '81' OR b = '81'
AND date_x < '2019-05-28';

and 

SELECT * FROM t
WHERE a = '81' OR b = '81'
AND date_x < str_to_date('2019-05-28', '%Y-$m-%d');

then both return, as expected:

1    |  81  | 12 | 2018-03-16
3    |  81  | 67 | 2018-03-16
4    |  763 | 81 | 2018-03-16

I read the following Q/A but I'm still missing something:

Any hint? Thank you

Upvotes: 1

Views: 79

Answers (3)

Raymond Nijland
Raymond Nijland

Reputation: 11602

A rewite into 

SELECT * FROM t
WHERE a = '81' AND date_x > '2019-05-28'
UNION ALL 
SELECT * FROM t
WHERE b = '81' AND date_x > '2019-05-28'

might make more sense especially when you consider performance, consider this table structure and indexing.. 

CREATE TABLE t (
  `id` INTEGER,
  `a` INTEGER,
  `b` INTEGER,
  `date_x` VARCHAR(10)
  , INDEX(a, date_x)
  , INDEX(b, date_x)
);

As

SELECT * FROM t
WHERE (a = '81' OR b = '81')
AND date_x > '2019-05-28';

can't use indexes see demo and most likely ends up scanning the complete table/index files.

As the MySQL optimizer is costs based i also included this to make sure the FULL scan for the first query was not simply triggerd by a to low number off records..
But it's but clear the second query has the more stabile plan..

Oracle database i believe does to OR to UNION ALL rewite direcly in this optimizer if indexed. As MySQL is owned by Oracle i hope they also add it that optimizion in MySQL.

Upvotes: 1

M. Kanarkowski
M. Kanarkowski

Reputation: 2195

You should wrap your select in brackets. Because of it you get WHERE a = '81' OR (b = '81' AND date_x >'2019-05-28').

SELECT * FROM t
WHERE (a = '81' OR b = '81')
AND date_x > '2019-05-28';

Upvotes: 2

Slawomir Chodnicki
Slawomir Chodnicki

Reputation: 1545

Your query has the form

SELECT * FROM t WHERE condition_a OR condition_b AND condition_c

The AND operator binds more strongly than OR, therefore you end up with

SELECT * FROM t WHERE condition_a OR (condition_b AND condition_c)

This is where I think the confusion comes from. Nothing to do with dates as such.

Upvotes: 2

Related Questions