Bug #479 ERROR 1105: Unknown error with ORDER BY on query that should return empty set

• Submitted: 22 May 2003 15:06
• Modified: 30 Jun 2003 7:34
• Reporter: Spencer Sun
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 3.23.52
• OS: Linux (RedHat 6.2)
• Assigned to: Bugs System
• CPU Architecture: Any

[22 May 2003 15:06] Spencer Sun

Description:
We have a query (which I have distilled into a test case below) which
seems like it should return an empty set but instead dies with ERROR 1105:
Unknown error.

Bug has been reproduced in 3.23.52, .53, and .55.

I tried to make the data set size in the test case smaller, but it is not
reliably reproducible when the data set size gets too small (as you delete
rows from bar, either when the size of the bar table drops below some unknown
threshold, or the number of distinct ref_id numbers drops below some unknown
threshold, the bug no longer appears).

I am fairly confident that the script below will reproduce the bug (I ran
it from a clean starting point on the 3 different server versions mentioned
above) but in case it doesn't, please let me know, and I'll see what else I
can come up with.

environment details from mysqlbug:

Release:       mysql-3.23.52 (Source distribution)
Server: /usr/local/bin/mysqladmin  Ver 8.23 Distrib 3.23.52, for pc-linux-gnu on i686
System: Linux skylab.zanshin.com 2.2.24-6.2.3 #1 Fri Mar 14 09:00:20 EST 2003 i6
86 unknown
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
Compilation info: CC='gcc'  CFLAGS='-O3 -mpentiumpro'  CXX='gcc'  CXXFLAGS='-O3
-mpentiumpro -felide-constructors'  LDFLAGS=''
LIBC:
lrwxrwxrwx    1 root     root           13 Nov  7  2000 /lib/libc.so.6 -> libc-2
.1.3.so
-rwxr-xr-x    1 root     root      4106668 Mar  5 13:56 /lib/libc-2.1.3.so
-rw-r--r--    1 root     root     20337052 Mar  5 13:56 /usr/lib/libc.a
-rw-r--r--    1 root     root          178 Mar  5 13:56 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/local --localstatedir=/var/mysql --
enable-assembler '--with-mysqld-ldflags=-all-static -rdynamic -L/usr/local/lib -
R/usr/local/lib' --with-raid --enable-local-infile 'CFLAGS=-O3 -mpentiumpro' 'CX
XFLAGS=-O3 -mpentiumpro -felide-constructors' CXX=gcc

How to repeat:
DROP TABLE IF EXISTS foo;

CREATE TABLE foo (
  foo_id int(10) unsigned NOT NULL default '0',
  ref_id int(10) unsigned default NULL,
  PRIMARY KEY  (foo_id)
) TYPE=MyISAM;

INSERT INTO foo VALUES (2400,14618);
INSERT INTO foo VALUES (2401,14590);
INSERT INTO foo VALUES (2425,NULL);
INSERT INTO foo VALUES (2402,14601);
INSERT INTO foo VALUES (2403,14603);
INSERT INTO foo VALUES (2404,14641);
INSERT INTO foo VALUES (2405,14613);
INSERT INTO foo VALUES (2406,14615);
INSERT INTO foo VALUES (2407,14667);
INSERT INTO foo VALUES (2408,14668);
INSERT INTO foo VALUES (2409,14670);
INSERT INTO foo VALUES (2411,14669);
INSERT INTO foo VALUES (2412,7738);
INSERT INTO foo VALUES (2413,14671);
INSERT INTO foo VALUES (2414,14674);
INSERT INTO foo VALUES (2415,NULL);
INSERT INTO foo VALUES (2416,14689);
INSERT INTO foo VALUES (2417,NULL);
INSERT INTO foo VALUES (2419,NULL);
INSERT INTO foo VALUES (2420,NULL);
INSERT INTO foo VALUES (2421,NULL);
INSERT INTO foo VALUES (2422,NULL);
INSERT INTO foo VALUES (2423,NULL);
INSERT INTO foo VALUES (2424,NULL);
INSERT INTO foo VALUES (2426,14795);
INSERT INTO foo VALUES (2427,15024);
INSERT INTO foo VALUES (2428,NULL);
INSERT INTO foo VALUES (2429,NULL);
INSERT INTO foo VALUES (2430,NULL);

DROP TABLE IF EXISTS bar;

CREATE TABLE bar (
  bar_id int(10) unsigned NOT NULL default '0',
  ref_id int(10) unsigned NOT NULL default '0',
  str varchar(20) default NULL,
  PRIMARY KEY  (bar_id),
  KEY ref_id (ref_id)
) TYPE=MyISAM;

INSERT INTO bar VALUES (112633,14590,'bar 112633');
INSERT INTO bar VALUES (112634,14590,'bar 112634');
INSERT INTO bar VALUES (112733,14601,'bar 112733');
INSERT INTO bar VALUES (112734,14601,'bar 112734');
INSERT INTO bar VALUES (112735,14601,'bar 112735');
INSERT INTO bar VALUES (112736,14601,'bar 112736');
INSERT INTO bar VALUES (112751,14603,'bar 112751');
INSERT INTO bar VALUES (112752,14603,'bar 112752');
INSERT INTO bar VALUES (112753,14603,'bar 112753');
INSERT INTO bar VALUES (112754,14603,'bar 112754');
INSERT INTO bar VALUES (112755,14603,'bar 112755');
INSERT INTO bar VALUES (112873,14615,'bar 112873');
INSERT INTO bar VALUES (112874,14615,'bar 112874');
INSERT INTO bar VALUES (112875,14615,'bar 112875');
INSERT INTO bar VALUES (112876,14615,'bar 112876');
INSERT INTO bar VALUES (112877,14615,'bar 112877');
INSERT INTO bar VALUES (112903,14618,'bar 112903');
INSERT INTO bar VALUES (112904,14618,'bar 112904');
INSERT INTO bar VALUES (112905,14618,'bar 112905');
INSERT INTO bar VALUES (112906,14618,'bar 112906');
INSERT INTO bar VALUES (112907,14618,'bar 112907');
INSERT INTO bar VALUES (112908,14618,'bar 112908');
INSERT INTO bar VALUES (112909,14618,'bar 112909');
INSERT INTO bar VALUES (112910,14618,'bar 112910');
INSERT INTO bar VALUES (112911,14618,'bar 112911');
INSERT INTO bar VALUES (112912,14618,'bar 112912');
INSERT INTO bar VALUES (113159,14641,'bar 113159');
INSERT INTO bar VALUES (113160,14641,'bar 113160');
INSERT INTO bar VALUES (113161,14641,'bar 113161');
INSERT INTO bar VALUES (113162,14641,'bar 113162');
INSERT INTO bar VALUES (113163,14641,'bar 113163');
INSERT INTO bar VALUES (113164,14641,'bar 113164');
INSERT INTO bar VALUES (113165,14641,'bar 113165');
INSERT INTO bar VALUES (113166,14641,'bar 113166');
INSERT INTO bar VALUES (113167,14641,'bar 113167');
INSERT INTO bar VALUES (113168,14641,'bar 113168');
INSERT INTO bar VALUES (113169,14641,'bar 113169');
INSERT INTO bar VALUES (113170,14641,'bar 113170');
INSERT INTO bar VALUES (113171,14641,'bar 113171');
INSERT INTO bar VALUES (113172,14641,'bar 113172');
INSERT INTO bar VALUES (113173,14641,'bar 113173');
INSERT INTO bar VALUES (113174,14641,'bar 113174');
INSERT INTO bar VALUES (113175,14641,'bar 113175');
INSERT INTO bar VALUES (113436,14674,'bar 113436');
INSERT INTO bar VALUES (113437,14674,'bar 113437');
INSERT INTO bar VALUES (113486,14689,'bar 113486');
INSERT INTO bar VALUES (113487,14689,'bar 113487');
INSERT INTO bar VALUES (113488,14689,'bar 113488');
INSERT INTO bar VALUES (113489,14689,'bar 113489');
INSERT INTO bar VALUES (113504,14795,'bar 113504');
INSERT INTO bar VALUES (115396,15024,'bar 115396');
INSERT INTO bar VALUES (115397,15024,'bar 115397');

-- these two statements succeed

SELECT bar_id, str FROM foo, bar WHERE foo_id = 2400
AND foo.ref_id = bar.ref_id ORDER BY str, bar_id;

SELECT bar_id, str FROM foo, bar WHERE foo_id = 2401
AND foo.ref_id = bar.ref_id ORDER BY str, bar_id;

-- this statement seems like it ought to just return an empty set
-- but it actually fails with "ERROR 1105: Unknown error"

SELECT bar_id, str FROM foo, bar WHERE foo_id = 2425
AND foo.ref_id = bar.ref_id ORDER BY str, bar_id;

-- if we drop the ORDER BY clause, then it succeeds

SELECT bar_id, str FROM foo, bar WHERE foo_id = 2425
AND foo.ref_id = bar.ref_id;

[23 May 2003 2:35] Alexander Keremidarski

It is repeatable with 3.23.56, but not with 4.0.13 and higher.

Working on simpler test case

[30 Jun 2003 1:12] Victor Vagin

patch:

===== sql/sql_select.cc 1.132 vs edited =====
*** /tmp/sql_select.cc-1.132-5152       Wed Mar 12 16:50:21 2003
--- edited/sql/sql_select.cc    Mon Jun 30 11:34:37 2003
***************
*** 1020,1026 ****
      s->read_time=(ha_rows) s->table->file->scan_time();
  
      /* Set a max range of how many seeks we can expect when using keys */
!     s->worst_seeks= (double) (s->read_time*2);
      if (s->worst_seeks < 2.0)                 // Fix for small tables
        s->worst_seeks=2.0;
  
--- 1020,1026 ----
      s->read_time=(ha_rows) s->table->file->scan_time();
  
      /* Set a max range of how many seeks we can expect when using keys */
!     s->worst_seeks= (double) s->found_records / 5;
      if (s->worst_seeks < 2.0)                 // Fix for small tables
        s->worst_seeks=2.0;

minimal test:

use test;

DROP TABLE IF EXISTS foo;

CREATE TABLE foo (
  foo_id int(10) unsigned NOT NULL default '0',
  ref_id int(10) unsigned default NULL,
  PRIMARY KEY  (foo_id)
) TYPE=MyISAM;

INSERT INTO foo VALUES (2425,NULL);

DROP TABLE IF EXISTS bar;

CREATE TABLE bar (
  bar_id int(10) unsigned NOT NULL default '0',
  ref_id int(10) unsigned NOT NULL default '0',
  str varchar(20) default NULL,
  PRIMARY KEY  (bar_id)
  ,KEY ref_id (ref_id)
) TYPE=MyISAM;

INSERT INTO bar VALUES (112633,14590,'bar 112633');
INSERT INTO bar VALUES (112634,14590,'bar 112634');
INSERT INTO bar VALUES (112733,14601,'bar 112733');
INSERT INTO bar VALUES (112734,14601,'bar 112734');
INSERT INTO bar VALUES (112735,14601,'bar 112735');
INSERT INTO bar VALUES (112876,14615,'bar 112876');
INSERT INTO bar VALUES (112877,14615,'bar 112877');
INSERT INTO bar VALUES (112903,14618,'bar 112903');
INSERT INTO bar VALUES (112904,14618,'bar 112904');
INSERT INTO bar VALUES (112905,14618,'bar 112905');
INSERT INTO bar VALUES (113172,14641,'bar 113172');
INSERT INTO bar VALUES (113436,14674,'bar 113436');
INSERT INTO bar VALUES (113437,14674,'bar 113437');
INSERT INTO bar VALUES (113486,14689,'bar 113486');
INSERT INTO bar VALUES (113487,14689,'bar 113487');
INSERT INTO bar VALUES (113488,14689,'bar 113488');
INSERT INTO bar VALUES (113489,14689,'bar 113489');
INSERT INTO bar VALUES (113504,14795,'bar 113504');
INSERT INTO bar VALUES (115396,15024,'bar 115396');
INSERT INTO bar VALUES (115397,15024,'bar 115397');

SELECT bar_id, str FROM foo, bar WHERE foo_id = 2425
AND foo.ref_id = bar.ref_id ORDER BY str, bar_id;

[30 Jun 2003 7:34] Victor Vagin

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

patch was made from mysql-4.0