1

I am reviewing a query which should pull in a list of transactions within a month, either based on its created_at date OR its returned_at date.

SELECT * 
FROM (`transaction`) 
WHERE `created_at` >= '2014-08-01' 
AND `created_at` <= '2014-08-31' 
OR `returned_at` >= '2014-08-01' 
AND `returned_at` <= '2014-08-31'

My initial thought was that this would not work as intended since we do not have parentheses around the grouped WHERE logic. In other words, it would evaluate each condition one after another.

This is how I would have written the statement to ensure order of operations (I added parenthesis):

SELECT * 
FROM (`transaction`) 
WHERE 
  (`created_at` >= '2014-08-01' AND `created_at` <= '2014-08-31')
OR 
  (`returned_at` >= '2014-08-01' AND `returned_at` <= '2014-08-31')

Oddly enough, the initial query appears to be working though I am not sure if its because of my data set or not. Could someone clarify that both sql statements will always return the same results?

Jordan Running
  • 102,619
  • 17
  • 182
  • 182
Roeland
  • 3,698
  • 7
  • 46
  • 62
  • 1
    http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html – Oliver Charlesworth Aug 19 '14 at 20:43
  • 1
    Just like in math, the parenthesis matter. Those two queries are not the same thing. – Andrew Aug 19 '14 at 20:43
  • 1
    @Andrew In this special case those two queries are just the same, but it's always a good idea to use parentheses if one's mixing `AND` and `OR`. – VMai Aug 19 '14 at 20:45
  • I am always using parantheses, as although AND should be executed before OR I would not risk my code be broken if this rule is not respected in the technologies I use. So I put there those parantheses, it does not take so much time and I can sleep without nightmares. – Lajos Arpad Aug 19 '14 at 20:59

3 Answers3

1

Writing it explicitly is always a good idea, but yeah, AND comes before OR

For your pleasure, checked them all explicitly for you:

$ php -r '$d = 0; while($d < pow(2,4)){$bin = sprintf("%04b",$d); echo $bin." => ".@mysql_result(mysql_query("SELECT ".$bin[0]." AND ".$bin[1]." OR ".$bin[2]." AND ".$bin[3]),0,0).PHP_EOL;$d++;}'
0000 => 0
0001 => 0
0010 => 0
0011 => 1
0100 => 0
0101 => 0
0110 => 0
0111 => 1
1000 => 0
1001 => 0
1010 => 0
1011 => 1
1100 => 1
1101 => 1
1110 => 1
1111 => 1

So yeah, operator precedence and all. I would still throw that query back, if only it's hard for the next guy to see at a glance what it does.

Wrikken
  • 69,272
  • 8
  • 97
  • 136
0

Its operator precence. AND is guaranteed to be evaluated before OR

Ahmed Ghonim
  • 4,455
  • 1
  • 17
  • 23
  • Can you show me here is it guaranteed? I know that AND should be evaluated before OR, but where is that guaranteed? Is it difficult to type some parantheses so you are sure instead of believing you have written the right code? – Lajos Arpad Aug 19 '14 at 21:01
  • See http://msdn.microsoft.com/en-us/library/ms190276.aspx and http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html and http://docs.oracle.com/html/A95915_01/sqopr.htm. The three DBMS guarantee it. However, I am not trying to advice you to use it. I was just explaining why it turned out this way even without the parentheses. As wrikken said, it is always a good idea to use parentheses. Just do it for the sake of code clarity/readability even if you are sure it will behavenas you expect without them. – Ahmed Ghonim Aug 19 '14 at 21:32
  • The links in your comment point to documentations of specific versions. Maybe I have been a coder for too long, but I have seen many cases when the documentation did not correspond to the truth and also cases when another/new version of the same project did not behave in the same way. It is naivity to believe a documentation and it is not a responsible behavior to not minimize the number of possible bugs. What would you do, if for instance a new version of MySQL would appear where this rule is not respected for some reason and you have trusted this rule in your codes many times in many project – Lajos Arpad Aug 19 '14 at 21:51
  • development and due to this your client loses too much money and will not have resources to continue paying for your development? Wouldn't you feel responsible? Yes, AND should precede OR and I advise everyone to use parantheses to get a bit of more safety. Documentation is not a guarantee. If that would be the case than our life would be so much easier. Take for example the documentation of Facebook API. In many cases I have seen that the thing does not work according to the documentation. It was so bad that eventually I decided to not read it at all and rather experiment. – Lajos Arpad Aug 19 '14 at 21:54
  • 1
    Forget about the three implememtations. SQL (implementation-independent) defines this precedence. Look at http://www.w3resource.com/sql/sql-syntax.php#PRECEDENC . And if you are going to worry about the implementation changing something as fundamental as OPERATOR PRECEDENCE, you might as well worry about them changing the meaning of SELECT. – Ahmed Ghonim Aug 19 '14 at 22:01
  • So it is difficult to you to write parantheses. You rather have a slightly bigger risk and slightly more unreliable code for the long-term, because the extra comfort of not typing to keys is more important to you. I have no more questions. Great philosophy, congratulations! Down-vote added to your answer due to the fact that it promotes irresponsible behavior. – Lajos Arpad Aug 19 '14 at 22:07
  • LOL! Did you even read my comment??? The part about "I am not advising you to do so" – Ahmed Ghonim Aug 19 '14 at 22:09
  • The important part is: "And if you are going to worry about the implementation changing something as fundamental as OPERATOR PRECEDENCE, you might as well worry about them changing the meaning of SELECT." If you take that back I will take back my down-vote. The fact that you cannot prepare for the case when selects are mis-implemented does not validate the lack of safety where you have the possibility to write more reliable code. You are responsible fot your own code and should not make such invalid comparisons, because some beginners will believe you at the end. – Lajos Arpad Aug 19 '14 at 22:16
  • Still LOOL. No, a bigger LOOL this time. The biggest LOOL I ever had in a while. You sir, can down vote until your fingers bleed. – Ahmed Ghonim Aug 19 '14 at 22:42
  • You are the one with the bleeding fingers who willingly risks the projects of others because you are too lazy to press two buttons. I am sorry for those who will have to use your source-code. You can laugh as much as you want but that does not change the fact that you are irresponsible and promote irresponsability. You continue to do so even your attention was drawn to your mistake. You should be ashamed. I will not waste my time on you from now on. – Lajos Arpad Aug 19 '14 at 22:49
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/59620/discussion-between-ahmed-ghonim-and-lajos-arpad). – Ahmed Ghonim Aug 19 '14 at 23:00
  • I have nothing to say to you. Good bye. – Lajos Arpad Aug 19 '14 at 23:05
0

Depending in your data may be the both queries return the same data. The OR operand will be in conflict with other AND operands because the precedence of each one.

The second query is an approach, but you can use between instead:

SELECT * FROM (`transaction`) 
WHERE (`created_at` BETWEEN '2014-08-01' AND '2014-08-31') 
   OR (`returned_at` BETWEEN '2014-08-01' AND '2014-08-31')
Ivan Cachicatari
  • 4,212
  • 2
  • 21
  • 41