I'm not sure if this is a bug or if this is the way MERGE TABLES works
in MySQL.
It seems that if I have an INDEX in a field (id for instance), and I try
to
SELECT using a WHERE id=NUMBER, it will fail, even though that NUMBER is
a
valid id. ID is an int unsigned field with a key on it. However, when I
drop
the key, everything works as expected..
Is this a bug?
(also if I tried to ORDER BY id, while indexed, that didn't work either)
mysql> desc all_records;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | | | 0 | |
| field1 | varchar(48) | YES | | NULL | |
| field2 | varchar(6) | YES | | NULL | |
| field3 | varchar(25) | YES | | NULL | |
| field4 | varchar(10) | YES | | NULL | |
| field5 | int(10) unsigned | | | 0 | |
| field6 | int(10) unsigned | | | 0 | |
| pages | int(10) unsigned | | | 0 | |
| file_name | varchar(10) | YES | | NULL | |
| catalog | int(10) unsigned | | | 0 | |
| keywords | varchar(255) | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
Here is some output from mysql client (before dropping keys):
mysql> select id from all_records where id=1;
+----+
| id |
+----+
| 13 |
+----+
1 row in set (0.00 sec)
mysql> select * from all_records where id=12;
Empty set (0.01 sec)
however...
mysql> select id from all_records;
+-----+
| id |
+-----+
| .. |
| 12 |
| .. |
---------
336 rows in set (0.01 sec)
ID 12 does exist in all_records..
---------------------
mysql> alter table all_records drop key id_key;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
After dropping keys:
mysql> select * from all_records where id=240;
+-----+
| id |
+-----+
| 240 |
+-----+
1 row in set (0.00 sec)
mysql> select * from all_records where id=158;
+-----+
| id |
+-----+
| 158 |
+-----+
1 row in set (0.00 sec)
everything works as expected...
System Information:
(mysql installed from RPM)
mysql Ver 11.15 Distrib 3.23.39, for pc-linux-gnu (i686)
RedHat 7.0
Linux xxxxx.xxxxxx.xxx 2.4.8 #3 SMP Tue Aug 14 09:47:47 CDT 2001 i686
unknown
---------------------
Johnny Withers
[EMAIL PROTECTED]
p. 601.853.0211
c. 601.209.4985
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
