Two queries that should be equivalent are returning different results

I have two queries. The first returns some results and the second one returns nothing. Here they are.

This one returns some results:

select md5(concat(ad.line1, ad.line2, ad.city, s.name, ad.zip, group_concat(distinct c.name))) id,
       group_concat(distinct c.name) customer_names,
       count(distinct c.name) number_of_customers,
       ad.line1,
       ad.line2,
       ad.city,
       s.name state_name,
       ad.zip,
       a.import_id
  from address ad
  join account_address aa on aa.address_id = ad.id
  join account a on aa.account_id = a.id
  join import i on a.import_id = i.id
  join customer c on a.customer_id = c.id
  join state s on ad.state_id = s.id
 where a.import_id = 188
group by s.name, city, zip, line1, line2

This returns nothing:

select * from
(select md5(concat(ad.line1, ad.line2, ad.city, s.name, ad.zip, group_concat(distinct c.name))) id,
       group_concat(distinct c.name) customer_names,
       count(distinct c.name) number_of_customers,
       ad.line1,
       ad.line2,
       ad.city,
       s.name state_name,
       ad.zip,
       a.import_id
  from address ad
  join account_address aa on aa.address_id = ad.id
  join account a on aa.account_id = a.id
  join import i on a.import_id = i.id
  join customer c on a.customer_id = c.id
  join state s on ad.state_id = s.id
group by s.name, city, zip, line1, line2) v
where v.import_id = 188

I'm completely bamboozled. Any ideas?

My DBMS is MySQL.

Answers


The second query is abusing MySQL extension to GROUP BY which allows to select unaggregated columns.

import_id is selected from a random record per group in the second query, and it's not guaranteed it will be 188. But the query checks for it after the GROUP BY.

Sample data:

grouper   value
1         1
1         1
1         2
1         3
2         1
2         2
2         3

First query:

SELECT  grouper, value
FROM    mytable
WHERE   value = 1

grouper   value
1         1
1         1
2         1

Since WHERE is executed before GROUP BY, this query will only consider records holding value = 1 (which were returned on the previous stage):

SELECT   grouper, COUNT(*)
FROM     mytable
WHERE    value = 1
GROUP BY
         grouper

grouper   COUNT(*)
1         2
2         1

Second query:

SELECT   grouper, COUNT(*), value
FROM     mytable
GROUP BY
         grouper

grouper   COUNT(*)  value
1         4         2
2         3         3

Since value is not grouped and not aggregated, it can be taken from any record within the group! In this case that was taken from the last records or appropriate groups (but could be taken from any other records as well).

SELECT   *
FROM     (
         SELECT   grouper, COUNT(*), value
         FROM     mytable
         GROUP BY
                  grouper
         ) q
WHERE    value = 1

-- no rows

Since there were no record with value = 1 on the previous stage (it happened so that the values were taken from the other records), no record satisfies the WHERE condition.


Need Your Help

What is the safest way to use PDO (in & outgoing data) in PHP 5.3

php sql database pdo

So recently I heard PDO actually isn't safe unless you know exactly what you're doing, or in my case, copy/pasting. Not only that, I didn't know you can't trust your own database either from an att...

Trying to use FindView with a path

asp.net-mvc viewengine

I'm trying to check if a couple of views exist by using paths. But the views cannot be found even if they do exist.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.