Bug #15008 where Date error

• Submitted: 17 Nov 2005 8:45
• Modified: 2 Dec 2005 9:19
• Reporter: li gang
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: mysql 5.15
• OS: Windows (windows xp)
• Assigned to: Vasily Kishkin
• CPU Architecture: Any

[17 Nov 2005 8:45] li gang

Description:
date is empty return empty row,but mysql3.23 OK;

How to repeat:
select dateb,endqty,qty,pplanid from pplan_d1 where pplanid='25070016' an
d soid=''   and classb<>'取存貨' and dateb='0000-00-00';
+------------+---------------+---------------+----------+
| dateb         | endqty           | qty                | pplanid    |
+------------+---------------+---------------+----------+
| 0000-00-00 | 1137700.00000 | 1000000.00000 | 25070016 |
+------------+---------------+---------------+----------+
1 row in set (0.00 sec)

select dateb,endqty,qty,pplanid from pplan_d1 where pplanid='25070016' an
d soid=''   and classb<>'取存貨' and dateb='';
Empty set, 1 warning (0.00 sec)

[17 Nov 2005 9:22] Vasily Kishkin

Could you please write here table definition and several rows ?

[17 Nov 2005 9:42] li gang

mysql> desc pplan_d1;
+-----------+---------------+------+-----+------------+-------+
| Field     | Type          | Null | Key | Default    | Extra |
+-----------+---------------+------+-----+------------+-------+
| pplanid   | char(8)       | NO   | MUL |            |       |
| classb    | char(10)      | NO   |     |            |       |
| soid      | char(8)       | NO   |     |            |       |
| csoid     | char(30)      | NO   |     |            |       |
| dateb     | date          | NO   |     |            |       |
| qty       | decimal(15,5) | NO   |     | 0.00000    |       |
| endqty    | decimal(15,5) | NO   |     | 0.00000    |       |
| indexkey  | char(30)      | NO   |     |            |       |
| soqty     | decimal(15,5) | NO   |     | 0.00000    |       |
| customeri | char(6)       | NO   |     |            |       |
| companyna | char(40)      | NO   |     |            |       |
| stockcode | char(24)      | NO   |     |            |       |
| stockname | char(60)      | NO   |     |            |       |
| dated     | date          | NO   |     | 0000-00-00 |       |
+-----------+---------------+------+-----+------------+-------+
14 rows in set (0.00 sec)

[23 Nov 2005 12:42] Vasily Kishkin

Sorry....I ment create definition of table.
Could you please write here result of "show create table pplan_d1".

[24 Nov 2005 3:54] li gang

CREATE TABLE `pplan_d1` (
  `pplanid` char(8) NOT NULL default '',
  `classb` char(10) NOT NULL default '',
  `soid` char(8) NOT NULL default '',
  `csoid` char(30) NOT NULL default '',
  `dateb` date NOT NULL,
  `qty` decimal(15,5) NOT NULL default '0.00000',
  `endqty` decimal(15,5) NOT NULL default '0.00000',
  `indexkey` char(30) NOT NULL default '',
  `soqty` decimal(15,5) NOT NULL default '0.00000',
  `customeri` char(6) NOT NULL default '',
  `companyna` char(40) NOT NULL default '',
  `stockcode` char(24) NOT NULL default '',
  `stockname` char(60) NOT NULL default '',
  `dated` date NOT NULL default '0000-00-00',
  KEY `pplanid` (`pplanid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

[2 Dec 2005 9:18] Vasily Kishkin

Zero value of date and empty space are not same value. If you read warning second query you can read follow message:

mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '' for column 'dateb' at row 1 |
+---------+------+------------------------------------------------------+
1 row in set (0.00 sec)

Please read documantation http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html