MySQL Query WTF

Question

Can anyone help me understand or post any ideas concerning this where clause?

sql was here

I've changed the table name, but other than that, any idea what the developer was trying to do here?

There is nothing else after that, that's the where clause.

Answer 1

If (table.date_field = (select max(table2.exit_date) from table as table2)) is null the it'll return 1=1, which basically means there's no where clause at all.

Now let's look into that nasty expression. I can only assume that if "a = b" is not true then that's also equivalent to null, otherwise it seems like the first branch would always happen. It looks like it's trying to say "if the latest exit date is equal to the date field, select those, otherwise have no where clause". However, I don't think that this will work at all. It really looks like either way, each row will be selected.

Answer 2

It says basically if table.date_field = max exit date or if max exit_date is null or table.date_field is null return true. Will return false if max exit_date is not null and table.date_field is not null but they do not equal.

Answer 3

The MySQL ifnull function returns the first argument if it is not null, otherwise the second argument. This looks like it tries to compare table.date_field to the max(table2.exit_date), and return true if the comarison was not possible due to nulls.

Answer 4

It looks to me like he is trying to find the row where table.date_field is equal to the maximum of table.exit_data. There is a check for null which I think would happen in any of these cases:

• table is empty
• all rows in table have exit_data set to NULL
• table.date_field is NULL for the row in question

In any of these three cases, the row will be returned. I don't understand why he uses the string '1=1' instead of, to give some examples: 1=1, 1 or true, but it appears to work fine. In the first case I assume that there will be no rows in the result set anyway (depending on the rest of the query) so he was probably trying to handle one of the other two cases - I'd guess the last one.

This is only an explanation of what is happening. To understand why he is doing this, it would help if you gave a little more context.

Answer 5

• MySQL is nonstandard in that true is really equal to the numeric value 1. Any expression that evaluates to true, or any nonzero value, satisfies the condition.

  mysql> CREATE TABLE foo AS SELECT 1=1 AS f;
  mysql> SHOW CREATE TABLE foo;
  CREATE TABLE `foo` (
    `f` INT NOT NULL DEFAULT '0'
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1
  

  So the following WHERE clause is legal in MySQL, but not in most other SQL databases:

  ... WHERE 1;
  

• Some people use 1=1 as a placeholder condition meaning true, but putting it in a string is meaningless because SQL expressions have no equivalent to an eval() function as other languages have. In this case, the leading character 1 in the string is implicitly cast to a numeric value 1, which is interpreted as true in MySQL. So it probably works as intended, but kind of by accident.

• The use of IFNULL() is so that if either date_field or MAX(exit_date) is NULL, it returns the row. If you didn't use this function, then anything = NULL would evaluate as unknown, which means the row would not be returned.