Re: DATE_FORMAT not working with connector/j 3.1.7

[R Fields]

Mark Matthews wrote:
> R Fields wrote:
> > > Richard, without seeing the query and
> the DDL
> > of
> > > the table(s) involved, this code isn't
> much
> > help.
> > >
> > > This is really unfortunately a server
> bug
> > that the
> > > driver has to work-around, so not all
> cases
> > are
> > > currently caught.
> > >
> > > -Mark
> >
> >
> > Hi Mark,
> >
> > I understand, and appreciate your
> assistance.
> >
> > The query is:
> >
> > select DATE_FORMAT(account_creation_date,
> > '%Y-%m-%d') as date, count(*) as count from
> > customer where customer_deleted_flag = ?
> and
> > affiliate_company_id = ? and
> > account_creation_date >= ? and
> > account_creation_date <= ? group by date
> >
> > This parameters to the prepared statement
> are:
> > params: [0, FRL, 2005-01-16 00:00:00.428,
> > 2005-03-16 23:59:59.428]
> >
> >
> > The customer table is defined as (though I
> pulled
> > out the columns not associated with the
> query):
> >
> > CREATE TABLE `customer` (
> > `CUSTOMER_ID` int(18) NOT NULL
> auto_increment,
> > `ACCOUNT_CREATION_DATE` datetime NOT NULL
> > default '0000-00-00 00:00:00',
> > `AFFILIATE_COMPANY_ID` varchar(50) default
> > NULL,
> > `CUSTOMER_DELETED_FLAG` int(18) NOT NULL
> default
> > '0',
> > PRIMARY KEY (`CUSTOMER_ID`)
> >
> > ) ENGINE=InnoDB
>
> Richard,
>
> I can't duplicate this on my end (i.e. I get a
> String back from rs.getObject(1)).
>
> What does an 'explain' on your query in the mysql
> command-line client report? My guess it will say
> something about using temp tables, which is where
> this bug comes from.
>
> -Mark
>


Hi Mark,

You're absolutely right.

mysql> explain select DATE_FORMAT(account_creation_date, '%Y-%m-%d') as date, count(*) as count from customer where customer_deleted_flag = 0 and affiliate_company_id = 'FRM' and account_creation_date >= '2005-03-02 00:00:00' and account_creation_date <= now() group by date;
+----+-------------+----------+-------+-------------------+-------------------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+-------------------+-------------------+---------+------+------+----------------------------------------------+
| 1 | SIMPLE | customer | range | idx_acdate_cdflag | idx_acdate_cdflag | 12 | NULL | 1 | Using where; Using temporary; Using filesort |
+----+-------------+----------+-------+-------------------+-------------------+---------+------+------+----------------------------------------------+
1 row in set (0.00 sec)


and the index:
| customer | 1 | idx_acdate_cdflag | 1 | ACCOUNT_CREATION_DATE | A | 16 | NULL | NULL | | BTREE | |
| customer | 1 | idx_acdate_cdflag | 2 | CUSTOMER_DELETED_FLAG | A | 16 | NULL | NULL | | BTREE | |


Thanks,
-Richard