>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
