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
Triage: Triaged: D4 (Minor)

[7 Feb 2008 9:31] Corin Langosch
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
[7 Feb 2008 9:31] Corin Langosch
forgot server version, it's "5.0.54a-enterprise-gpl" running on debian amd64.
[7 Feb 2008 10:09] Sveta Smirnova
Thank you for the report.

Please provide output of SHOW CREATE TABLE events_events
[7 Feb 2008 14:39] Corin Langosch
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
[7 Feb 2008 20:17] Sveta Smirnova
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.
[8 Mar 2008 0:00] Bugs System
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".
[8 Apr 2008 17:49] Sveta Smirnova
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'
[8 Apr 2008 18:23] Sveta Smirnova
I am sorry: please ignore previous comment as it contains logic error. We still need feedback to be able to repeat the error.
[8 Apr 2008 18:32] Shane Bester
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;
[8 Apr 2008 19:28] Sveta Smirnova
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)
[28 Nov 2008 22:27] Sergei Golubchik
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()
[28 Nov 2008 23:35] Sergei Golubchik
queued in 6.0-maria
[15 Dec 2008 10:08] Bugs System
Pushed into 6.0.9-alpha  (revid:serg@mysql.com-20081128232713-3be8u6yi1hum4is6) (version source revid:guilhem@mysql.com-20081213204800-0nubni3t4ihn4hv9) (pib:5)
[15 Dec 2008 17:32] Paul Dubois
Not sure how to write the changelog entry. Can you suggest a sentence? Thanks.
[23 Dec 2008 8:02] Sergei Golubchik
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"
[7 Jan 2009 1:03] Paul Dubois
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.
[19 Oct 2009 13:14] Bugs System
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
[31 Oct 2009 8:20] Bugs System
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)
[31 Oct 2009 17:37] Paul Dubois
Already fixed in 6.0.9.

Setting report to NDI pending push to 5.5.x.
[12 Nov 2009 8:22] Bugs System
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)
[12 Nov 2009 19:02] Paul Dubois
Noted in 5.5.0 changelog.