Bug #25602 Error with /*!HIGH_PRIORITY SQL_CACHE SQL_BIG_RESULT*/ combined with DISTINCT
Submitted: 12 Jan 2007 23:30 Modified: 26 Jun 2007 19:13
Reporter: Harald Reindl Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:5.0.32, 5.0.33, 5.0.37, 5.0.44-BK OS:Linux (Linux (FC 6))
Assigned to: Igor Babaev CPU Architecture:Any
Tags: distinct, regression, SQL_BIG_RESULT

[12 Jan 2007 23:30] Harald Reindl
Description:
In the last two releases (maybe also in 5.0.30?) querys like the following brings strange results, you get back the last distinct-entry as many times as you should get different - hope my english is not too bad...

Sample: 
select /*!HIGH_PRIORITY SQL_CACHE SQL_BIG_RESULT*/ distinct artist from lyrics 

If you change SQL_BIG_RESULT for testing to SQL_SMALL_RESULT or do not use the optimizing-comments all is ok

This is my build-script which is not changed since 5.0.24 and since 5.0.32 the error occurs, 5.0.30 i never used:

---

cd /data/development/src/mysql/
make clean > /dev/null
CFLAGS="-O3 -march=i686" CXX=gcc CXXFLAGS="-O3 -march=i686 -felide-constructors \
  -fno-exceptions -fno-rtti" ./configure \
  --prefix=/usr/local/mysql \
  --enable-assembler \
  --with-mysqld-ldflags=-all-static \
  --with-client-ldflags=-all-static \
  --with-unix-socket-path=/var/lib/mysql/mysql.sock \
  --enable-local-infile \
  --with-charset=latin1 \
  --with-collation=latin1_german1_ci \
  --with-mysqld-user=mysql \
  --without-debug \
  --without-docs \
  --without-man \
  --without-bench \
  --without-innodb \
  --without-readline \
  --without-libedit \
  --with-comment="rhsoft.net build"
make
sudo /scripts/stop-mysql.sh
sudo make install
sudo strip /usr/local/mysql/bin/*
sudo strip /usr/local/mysql/libexec/*

How to repeat:
make a select with /*!HIGH_PRIORITY SQL_CACHE SQL_BIG_RESULT*/ and distinct
[14 Jan 2007 13:55] Valeriy Kravchuk
Thank you for a problem report. Please, send the exact and complete test case (with CREATE TABLE, some data to insert, and SELECT), as well as exact my.cnf file content.
[15 Jan 2007 15:19] Valeriy Kravchuk
I haddownloaded your data, so you can delete them.
[12 Mar 2007 9:03] Valeriy Kravchuk
Please, try to repeat with a newer version, 5.0.27, and inform about the results.
[12 Mar 2007 15:32] Harald Reindl
Problem with 5.0.37 also exists :-(

I´m sure with older 5.0.x-versions this was not so, becaue the scripzt with this query is 2 years old and somewehre with 5.0.30 this strnage results will happen
[14 Jun 2007 12:16] Valeriy Kravchuk
Sorry for a delay with this bug report. Verified just as described on your test data with latest 5.0.44-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.0.44-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select /*!HIGH_PRIORITY SQL_CACHE SQL_BIG_RESULT*/ distinct songinterpret from rh_lyrics order by songinterpret;
+-----------------+
| songinterpret   |
+-----------------+
| Wolfgang Ambros |
| Wolfgang Ambros |
...
| Wolfgang Ambros |
+-----------------+
97 rows in set (0.00 sec)

mysql> explain select /*!HIGH_PRIORITY SQL_CACHE SQL_BIG_RESULT*/ distinct song
interpret from rh_lyrics order by songinterpret;
+----+-------------+-----------+-------+---------------+---------------+--------
-+------+------+------------------------------------------+
| id | select_type | table     | type  | possible_keys | key           | key_len
 | ref  | rows | Extra                                    |
+----+-------------+-----------+-------+---------------+---------------+--------
-+------+------+------------------------------------------+
|  1 | SIMPLE      | rh_lyrics | range | NULL          | songinterpret | 152
 | NULL |  120 | Using index for group-by; Using filesort |
+----+-------------+-----------+-------+---------------+---------------+--------
-+------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> select /*!HIGH_PRIORITY SQL_CACHE*/ distinct songinterpret from rh_lyrics order by songinterpret;
+----------------------------------+
| songinterpret                    |
+----------------------------------+
| A-Ha                             |
| AC/DC                            |
...
| Wolfgang Ambros                  |
+----------------------------------+
97 rows in set (0.00 sec)

mysql> explain select /*!HIGH_PRIORITY SQL_CACHE*/ distinct songinterpret from
rh_lyrics order by songinterpret;
+----+-------------+-----------+-------+---------------+---------------+--------
-+------+------+--------------------------+
| id | select_type | table     | type  | possible_keys | key           | key_len
 | ref  | rows | Extra                    |
+----+-------------+-----------+-------+---------------+---------------+--------
-+------+------+--------------------------+
|  1 | SIMPLE      | rh_lyrics | range | NULL          | songinterpret | 152
 | NULL |  120 | Using index for group-by |
+----+-------------+-----------+-------+---------------+---------------+--------
-+------+------+--------------------------+
1 row in set (0.00 sec)
[24 Jun 2007 6:30] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29458

ChangeSet@1.2504, 2007-06-23 23:33:55-07:00, igor@olga.mysql.com +3 -0
  Fixed bug #25602. A query with DISTINCT in the select list to which
  the loose scan optimization for grouping queries was applied returned 
  a wrong result set when the query was used with the SQL_BIG_RESULT
  option.
  
  The SQL_BIG_RESULT option forces to use sorting algorithm for grouping
  queries instead of employing a suitable index. The current loose scan
  optimization is applied only for one table queries when the suitable
  index is covering. It does not make sense to use sort algorithm in this
  case. However the create_sort_index function does not take into account
  the possible choice of the loose scan to implement the DISTINCT operator
  which makes sorting unnecessary. Moreover the current implementation of
  the loose scan for queries with distinct assumes that sorting will
  never happen. Thus in this case create_sort_index should not call
  the function filesort.
[25 Jun 2007 21:49] Bugs System
Pushed into 5.1.21-beta
[25 Jun 2007 21:51] Bugs System
Pushed into 5.0.46
[26 Jun 2007 19:13] Paul Dubois
Noted in 5.0.46, 5.1.21 changelogs.

A query with DISTINCT in the select list to which the loose-scan
optimization for grouping queries was applied returned an incorrect
result set when the query was used with the SQL_BIG_RESULT option.