Bug#11751794 MYSQL GIVES THE WRONG RESULT WITH SOME SPECIAL USAGE
Bug#11751794 MYSQL GIVES THE WRONG RESULT WITH SOME SPECIAL USAGE

Consider the following query:

SELECT f_1,..,f_m, AGGREGATE_FN(C)
FROM t1
WHERE ...
GROUP BY ...

Loose index scan ("Using index for group-by") can be used for
this query if there is an index 'i' covering all fields in the
select list, and the GROUP BY clause makes up a prefix f1,...,fn
of 'i'. Furthermore, according to rule NGA2 of
get_best_group_min_max(), the WHERE clause must contain a
conjunction of equality predicates for all fields fn+1,...,fm.

The problem in this bug was that a query with WHERE clause that
broke NGA2(NGA: Non Group Attribuite) was not detected and therefore 
used loose index scan.
This lead to wrong result. The query had an index
covering (c1,c2) and had:
  "WHERE (c1 = 1 AND c2 = 'a') OR (c1 = 2 AND c2 = 'b')
   GROUP BY c1"
or 
  "WHERE (c1 = 1 ) OR (c1 = 2 AND c2 = 'b')
   GROUP BY c1"


This WHERE clause cannot be transformed to a conjunction of
equality predicates.

The solution is to introduce another rule, NGA3, that complements
NGA2. NGA3 says that if a gap field (field between those
listed in GROUP BY and C in the index) has a predicate, then
there can only be one range in the query. This requirement is
more strict than it has to be in theory. BUG 15947433 will deal
with that.

flynn1973/mariadb-aix,natsys/mariadb_10.2,davidl-zend/zenddbi,flynn1973/mariadb-aix,natsys/mariadb_10.2,ollie314/server,ollie314/server,natsys/mariadb_10.2,davidl-zend/zenddbi,ollie314/server,flynn1973/mariadb-aix,flynn1973/mariadb-aix,flynn1973/mariadb-aix,davidl-zend/zenddbi,ollie314/server,natsys/mariadb_10.2,ollie314/server,natsys/mariadb_10.2,flynn1973/mariadb-aix,flynn1973/mariadb-aix,flynn1973/mariadb-aix,natsys/mariadb_10.2,natsys/mariadb_10.2,ollie314/server,davidl-zend/zenddbi,davidl-zend/zenddbi,natsys/mariadb_10.2,natsys/mariadb_10.2,ollie314/server,davidl-zend/zenddbi,davidl-zend/zenddbi,flynn1973/mariadb-aix,davidl-zend/zenddbi,slanterns/server,ollie314/server,ollie314/server,natsys/mariadb_10.2,ollie314/server,davidl-zend/zenddbi,davidl-zend/zenddbi,ollie314/server,flynn1973/mariadb-aix,natsys/mariadb_10.2,davidl-zend/zenddbi,flynn1973/mariadb-aix