List:General Discussion« Previous MessageNext Message »
From:perry Date:November 27 1999 1:42am
Subject:Index causes SELECT to fail
View as plain text  
>Description:
	Creating an index on a column causes a query which previously
	returned data to return no rows.  The data was originally
	created one row at a time but the problem can be demonstrated by
	performing a query first with no index and then after rebuilding
	the index.

	Version 3.21.33 does not have the problem, version 3.22.25 and
	3.22.27 do.

>How-To-Repeat:
	The following test case
	
		* creates a table
		* populates it with a bunch of data
		* shows a select returning a row
		* creates an index
		* shows the same select not returning any data

	Although there is nothing very strange about the data (the dates 
	range from year 0000 through year 9999) I was not able to recreate 
	the problem by inserting sample data one row at a time.  I can provide
	a dump of the table if needed.

	The year used in the query seems to be significant.  Querying for date
	'1800/12/01' seems to work but date '0800/12/01' does not.

	mysql> create table TestTable ( itemId int not null, start datetime not null, end
datetime not null );                                      
	Query OK, 0 rows affected (0.00 sec)

	mysql> insert into TestTable select eventId, start, end from Event;
	Query OK, 33072 rows affected (1.21 sec)
	Records: 33072  Duplicates: 0  Warnings: 0

	mysql> select itemId, start, end from TestTable where ((start >= '0800/11/01') AND
(start <= '0800/12/01')) ;
	+--------+---------------------+---------------------+
	| itemId | start               | end                 |
	+--------+---------------------+---------------------+
	|  50978 | 0800-11-25 16:00:00 | 0800-11-25 16:00:00 |
	+--------+---------------------+---------------------+
	1 row in set (0.07 sec)

	mysql> create index start on TestTable( start );
	Query OK, 33072 rows affected (0.75 sec)
	Records: 33072  Duplicates: 0  Warnings: 0

	mysql> select itemId, start, end from TestTable where ((start >= '0800/11/01') AND
(start <= '0800/12/01')) ;
	Empty set (0.00 sec)

>Fix:
	Not known.

>Submitter-Id:	<submitter ID>
>Originator:	Perry Clarke
>Organization:
 SmartCalendar Inc.
>MySQL support: none
>Synopsis:	Index causes SELECT to fail
>Severity:	critical
>Priority:	high
>Category:	mysql
>Class:		sw-bug
>Release:	mysql-3.22.27 (Source distribution)
>Server: /usr/local/bin/mysqladmin  Ver 7.11 Distrib 3.22.27, for pc-linux-gnu on i686
TCX Datakonsult AB, by Monty

Server version		3.22.27
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/tmp/mysql.sock
Uptime:			2 days 3 hours 21 min 33 sec

Threads: 3  Questions: 1183  Slow queries: 15  Opens: 165  Flush tables: 1  Open tables:
58
>Environment:
	
System: Linux raptor 2.2.5-15 #2 Sat Aug 21 17:58:29 PDT 1999 i686 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=''  CXX='c++'  CXXFLAGS=''  LDFLAGS=''
Configure command: ./configure 
Perl: This is perl, version 5.005_03 built for i386-linux
Thread
Index causes SELECT to failperry27 Nov
  • Re: Index causes SELECT to failPaul DuBois27 Nov