Hi, I'm trying to sort out an inconsistency where adding an index changes the results of a query. Seems like this is incorrect.
I'm running 3.23.36-debug, under NT. I have yet to try this on other versions... The following may be a bug, but it's at least inconsistent. I wonder if this is fixed in a later version? I've found that when a query uses only the INDEX to get the answer to a query, the result of a MIN function may be different. Note that this seems to only happen when MYSQL takes the shortcut of looking only at the INDEX to get the answer. My guess is that the INDEX object contains a node for the record with NULL. And when querying, mysql doesn't bother to check whether the "value" it has looked up is real. I believe this is inconsistent with ANSI standards. Run the following scenario script to see the difference. Comments ? Thanks! Here's a scenario script. create table sometable ( col1 int,col2 int,col3 int,col4 int); insert into sometable values ( 1,2,3,null); insert into sometable values ( 1,2,3,4); insert into sometable values ( 1,2,1,5); explain SELECT min(col4), max(col4) from sometable; SELECT min(col4), max(col4) from sometable; create index sometable_col3_idx on sometable (col3); create index sometable_col4_idx on sometable (col4); explain SELECT min(col4), max(col4) from sometable; SELECT min(col4), max(col4) from sometable; ----------------- Results: mysql> create table sometable ( col1 int,col2 int,col3 int,col4 int); Query OK, 0 rows affected (0.01 sec) mysql> insert into sometable values ( 1,2,3,null); Query OK, 1 row affected (0.00 sec) mysql> insert into sometable values ( 1,2,3,4); Query OK, 1 row affected (0.00 sec) mysql> insert into sometable values ( 1,2,1,5); Query OK, 1 row affected (0.00 sec) mysql> mysql> explain SELECT min(col4), max(col4) from sometable; +-----------+------+---------------+------+---------+------+------+-------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-----------+------+---------------+------+---------+------+------+-------+ | sometable | ALL | NULL | NULL | NULL | NULL | 3 | | +-----------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec) mysql> SELECT min(col4), max(col4) from sometable; +-----------+-----------+ | min(col4) | max(col4) | +-----------+-----------+ | 4 | 5 | +-----------+-----------+ 1 row in set (0.00 sec) mysql> mysql> create index sometable_col3_idx on sometable (col3); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> create index sometable_col4_idx on sometable (col4); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> mysql> explain SELECT min(col4), max(col4) from sometable; +------------------------------+ | Comment | +------------------------------+ | Select tables optimized away | +------------------------------+ 1 row in set (0.00 sec) mysql> SELECT min(col4), max(col4) from sometable; +-----------+-----------+ | min(col4) | max(col4) | +-----------+-----------+ | NULL | 5 | +-----------+-----------+ 1 row in set (0.00 sec) -- Kevin Fries --------------------------------------------------------------------- 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
