MySQL Bugs: #25602: Error with /*!HIGH_PRIORITY SQL_CACHE SQL_BIG

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

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

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.

I haddownloaded your data, so you can delete them.

Please, try to repeat with a newer version, 5.0.27, and inform about the results.

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

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)

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.

Pushed into 5.1.21-beta

Pushed into 5.0.46

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.