MySQL Bugs: #34374: mysql generates incorrect warning

Bug #34374	mysql generates incorrect warning
Submitted:	7 Feb 2008 9:31	Modified:	12 Nov 2009 19:02
Reporter:	Corin Langosch	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.54a, 5.0, 5.1, 6.0 BK	OS:	Linux
Assigned to:	Sergei Golubchik	CPU Architecture:	Any
Tags:	date, double value, incorrect, warning

Description:
running the query given below gives an incorrect warning.

SELECT DISTINCT SQL_CALC_FOUND_ROWS ee.id,ee.name,ee.date,ee.descr,ee.hits,ld.text AS art,ee.thumb,(SELECT el.stadt FROM events_event_loc eel,events_locations el WHERE eel.event_id=ee.id AND eel.location_id=el.id LIMIT 1) AS stadt FROM (events_events AS ee) LEFT JOIN (list_data AS ld) ON (ld.id=ee.art_id) LEFT JOIN (cache_fulltext AS cfe) ON (cfe.section='events_e' AND cfe.ref_id=ee.id) WHERE ee.date>='2008-02-07' AND MATCH(cfe.data) AGAINST ('Para' IN BOOLEAN MODE) AND FIND_IN_SET('freed',ee.flags) ORDER BY ee.date ASC LIMIT 0,15

warning 1/1 code 1292 for query: Truncated incorrect DOUBLE value: '2008-02-07'

How to repeat:
run query and look at warning

forgot server version, it's "5.0.54a-enterprise-gpl" running on debian amd64.

Thank you for the report.

Please provide output of SHOW CREATE TABLE events_events

CREATE TABLE `events_events` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `user_id` int(10) unsigned NOT NULL default '0',
 `name` varchar(100) collate latin1_german1_ci NOT NULL default '',
 `date` date NOT NULL default '0000-00-00',
 `email` varchar(100) collate latin1_german1_ci NOT NULL default '',
 `homepage` varchar(200) collate latin1_german1_ci NOT NULL default '',
 `art_id` int(10) unsigned NOT NULL default '0',
 `music_list` int(10) unsigned NOT NULL default '0',
 `preis` int(11) NOT NULL default '-1',
 `min_alter` tinyint(3) unsigned NOT NULL default '0',
 `descr` text collate latin1_german1_ci NOT NULL,
 `flags` set('freed','special') collate latin1_german1_ci NOT NULL default '',
 `created` int(10) unsigned NOT NULL default '0',
 `last_change` int(10) unsigned NOT NULL default '0',
 `normal` int(10) unsigned NOT NULL default '0',
 `thumb` int(10) unsigned NOT NULL default '0',
 `normal_stamp` int(10) unsigned NOT NULL default '0',
 `hits` int(10) unsigned NOT NULL default '0',
 PRIMARY KEY  (`id`),
 KEY `flags` (`flags`),
 KEY `date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=3565 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci

Thank you for the feedback.

Bug is not repeatable with test query.

Please provide output of SHOW CREATE TABLE for other tables (events_event_loc, events_locations, list_data) as well.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Found simplier test case:

CREATE TABLE `t1` (
`f1` int,
`f2` text
);
insert into t1 values(1, 'a/b');
update t1 set f2='c/d' and f1=2;
Warnings:
Warning 1292    Truncated incorrect DOUBLE value: 'c/d'

I am sorry: please ignore previous comment as it contains logic error. We still need feedback to be able to repeat the error.

drop table if exists t1;
create table t1(a text,b date,fulltext index(a))engine=myisam;
insert into t1 set a='water',b='2008-08-04';
select 1 from t1 where match(a) against ('water' in boolean mode) and b>='2008-08-01';
show warnings;

Shane,

thank you for the test case. Verified as described.

Workaround:

select 1 from t1 where match(a) against ('water' in boolean mode) and b>=cast('2008-08-01' as DATE)

It's an obvious bug, an item is unnecessary evaluated and in the wrong context.
The fix, in add_ft_keys():

-          ((functype == Item_func::GE_FUNC && arg1->val_real() > 0) ||
-           (functype == Item_func::GT_FUNC && arg1->val_real() >=0))  &&
-           arg0->type() == Item::FUNC_ITEM            &&
-           arg0->functype() == Item_func::FT_FUNC)
+           arg0->type() == Item::FUNC_ITEM            &&
+           arg0->functype() == Item_func::FT_FUNC     &&
+          ((functype == Item_func::GE_FUNC && arg1->val_real() > 0) ||
+           (functype == Item_func::GT_FUNC && arg1->val_real() >=0)))

and a similar for arg0->val_real()

queued in 6.0-maria

Pushed into 6.0.9-alpha  (revid:serg@mysql.com-20081128232713-3be8u6yi1hum4is6) (version source revid:guilhem@mysql.com-20081213204800-0nubni3t4ihn4hv9) (pib:5)

Not sure how to write the changelog entry. Can you suggest a sentence? Thanks.

Paul, write about "spurious warnings about 'Truncated incorrect DOUBLE value' in queries with MATCH ... AGAINST and > or < with a constant (which was reported as incorrect DOUBLE value) in the WHERE condition"

Note in 6.0.9 changelog.

There were spurious warnings about "Truncated incorrect DOUBLE value"
in queries with MATCH ... AGAINST and > or < with a constant (which
was reported as an incorrect DOUBLE value) in the WHERE condition.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/87308

2903 Sergei Golubchik	2009-10-19
      Bug#34374 mysql generates incorrect warning
      backport to next-mr

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091031081410-qkxmjsdzjmj840aq) (version source revid:serg@mysql.com-20091019190146-2g2j9i8aqhnd4a1u) (merge vers: 6.0.14-alpha) (pib:13)

Already fixed in 6.0.9.

Setting report to NDI pending push to 5.5.x.

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091102100915-a2nbfxaqprpgptfw) (merge vers: 5.5.0-beta) (pib:13)

Noted in 5.5.0 changelog.