Re: Mysql check

On Tue, 25 Sep 2001 02:38, Michael George wrote: > 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 This is a wild guess (like Richard Lynch does ;->) but your primary key is set to autoincrement, but with a default of NULL; this may be a bad thing and possibly should be set to 0 (zero). -- David Robley Techno-JoaT, Web Maintainer, Mail List Admin, etc CENTRE FOR INJURY STUDIES Flinders University, SOUTH AUSTRALIA Just what part of "NO" didn't you understand...?