I think I've discovered a problem with MySQL, but I wanted to check here to
see if perhaps there's something I'm doing wrong.
I have the following table:
----------------------------
mysql> show columns from IncomingInvoice;
+------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| supplier | int(10) unsigned | | | 0 | |
| sent | date | YES | | NULL | |
| invoiceNumber | int(10) unsigned | | | 0 | |
| paymentReference | char(20) | YES | | NULL | |
| shipping | float | YES | | NULL | |
| taxRate | float | YES | | NULL | |
+------------------+------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
----------------------------
and the following data in that table:
----------------------------
mysql> select * from IncomingInvoice;
+----+----------+------------+-------------+----------------+--------+---------+
| id | supplier | sent |invoiceNumber|paymentReference|shipping| taxRate |
+----+----------+------------+-------------+----------------+--------+---------+
| 1 | 1 | 2001-09-11 | 156 | | 6.9 | 6 |
| 5 | 1 | 2001-09-17 | 0 | | 5 | 6 |
| 6 | 7 | 2001-09-19 | 0 | | 0 | 0 |
| 9 | 1 | 2001-09-20 | 0 | | 8 | 6 |
| 8 | 25 | 2001-09-20 | 0 | | 0 | 0 |
| 11 | 1 | 2001-09-20 | 0 | | 0 | 0 |
+----+----------+------------+-------------+----------------+--------+---------+
6 rows in set (0.00 sec)
----------------------------
But when I try to do a select on record 11, I get:
----------------------------
mysql> select * from IncomingInvoice where id=11;
Empty set (0.01 sec)
----------------------------
and explain tell me:
----------------------------
mysql> explain select * from IncomingInvoice where id=11;
+-----------------------------------------------------+
| Comment |
+-----------------------------------------------------+
| Impossible WHERE noticed after reading const tables |
+-----------------------------------------------------+
1 row in set (0.00 sec)
----------------------------
What are the "const tables" that are indicating that the WHERE is impossible?
If I try to use a more flexible query:
----------------------------
mysql> explain select * from IncomingInvoice where id>10;
+---------------+-----+-------------+-------+-------+------+------+------------+
| table |type |possible_keys|key |key_len| ref | rows | Extra |
+---------------+-----+-------------+-------+-------+------+------+------------+
|IncomingInvoice|range|PRIMARY |PRIMARY| 4| NULL | 1 | where used |
+---------------+-----+-------------+-------+-------+------+------+------------+
1 row in set (0.00 sec)
mysql> select * from IncomingInvoice where id>10;
Empty set (0.00 sec)
----------------------------
Could there be something I've done to put the table into an inconsistent
state? I *am* doing PHP development, so it's possible...
Here's something else I tried:
----------------------------
mysql> select id, supplier, sent, invoiceNumber, shipping, taxRate
-> from IncomingInvoice where id > 8;
+----+----------+------------+---------------+----------+---------+
| id | supplier | sent | invoiceNumber | shipping | taxRate |
+----+----------+------------+---------------+----------+---------+
| 9 | 1 | 2001-09-20 | 0 | 8 | 6 |
+----+----------+------------+---------------+----------+---------+
1 row in set (0.00 sec)
mysql> select id, supplier, sent, invoiceNumber, shipping, taxRate
-> from IncomingInvoice where (id+0) > 8;
+----+----------+------------+---------------+----------+---------+
| id | supplier | sent | invoiceNumber | shipping | taxRate |
+----+----------+------------+---------------+----------+---------+
| 9 | 1 | 2001-09-20 | 0 | 8 | 6 |
| 11 | 1 | 2001-09-20 | 0 | 0 | 0 |
+----+----------+------------+---------------+----------+---------+
2 rows in set (0.00 sec)
mysql> select id, supplier, sent, invoiceNumber, shipping, taxRate
-> from IncomingInvoice where (id+0)=11;
+----+----------+------------+---------------+----------+---------+
| id | supplier | sent | invoiceNumber | shipping | taxRate |
+----+----------+------------+---------------+----------+---------+
| 11 | 1 | 2001-09-20 | 0 | 0 | 0 |
+----+----------+------------+---------------+----------+---------+
1 row in set (0.00 sec)
----------------------------
It therefore appears that MySQL/the table is shorting out and killing the
query before it's really executed.
I'm not on any mailing lists for MySQL, but I'll try checking their web site
for an explanation, too.
Thanks!
-Michael
--
No, my friend, the way to have good and safe government, is not to trust it
all to one, but to divide it among the many, distributing to every one exactly
the functions he is competent to. It is by dividing and subdividing these
republics from the national one down through all its subordinations, until it
ends in the administration of every man's farm by himself; by placing under
every one what his own eye may superintend, that all will be done for the
best.
-- Thomas Jefferson, to Joseph Cabell, 1816
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
