Bug #2311 Expression with user variables returns NULL

• Submitted: 7 Jan 2004 14:38
• Modified: 8 Jan 2004 12:03
• Reporter: Jedrek Jozefowicz
• Status: Not a Bug
• Category: MySQL Server: MyISAM storage engine
• Severity: S3 (Non-critical)
• Assigned to: Dean Ellis
• CPU Architecture: Any

[7 Jan 2004 14:38] Jedrek Jozefowicz

Description:
Variable @stosunek returns NULL when it's declared at beginning of query or in query executed before this example query after line 6 always returns NULL

1  SELECT (...)
2  IF(LOCATE('matrix',LOWER(ff_posts_text.post_text))>1, 
3  IFNULL(@licz:=@licz+1,@licz:=1),@licz), 
4  @wyrazBz:=ABS(LENGTH(ff_posts_text.post_text) - 
5  LENGTH(REPLACE(ff_posts_text.post_text,SUBSTRING('matrix',2),'matrix'))), 
6  IF(@wyrazBz>0,@stosunek:=IFNULL(@stosunek+(@wyrazBz*0.1),@stosunek),@stosunek) ,
7  IF(LOCATE('matrix',LOWER(ff_posts_text.post_subject))>0, 
8  @stosunek:=@stosunek+0.05,@stosunek), 
9  IF(LOCATE('matrix',LOWER(ff_topics.topic_title))>0, 
10 @stosunek:=@stosunek+2,@stosunek), @stosunek:=@stosunek*100 (...)

I tried without declaring variable and and with this expression  instead of line 6 expresion:
IF(@wyrazBz>0,@stosunek:=@stosunek+(@wyrazBz*0.1),0)

But, when I declare variable @stosunek by this method:
@stosunek:=(IFNULL(@licz,0)/CHAR_LENGTH(ff_posts_text.post_text))
all expressions returns correct values.

How to repeat:
Full query (tables from any phpbb forum)

SELECT DISTINCT ff_topics.topic_id, ff_posts.post_time, IF(LOCATE('matrix',LOWER(ff_posts_text.post_text))>1, IFNULL(@licz:=@licz+1,@licz:=1),@licz), @wyrazBz:=ABS(LENGTH(ff_posts_text.post_text) - LENGTH(REPLACE(ff_posts_text.post_text,SUBSTRING('matrix',2),'matrix'))), IF(@wyrazBz>0,@stosunek:=IFNULL(@stosunek+(@wyrazBz*0.1),@stosunek),@stosunek) ,IF(LOCATE('matrix',LOWER(ff_posts_text.post_subject))>0, @stosunek:=@stosunek+0.05,@stosunek), IF(LOCATE('matrix',LOWER(ff_topics.topic_title))>0, @stosunek:=@stosunek+2,@stosunek), @stosunek:=@stosunek*100, @stosunek AS stosunek, ff_topics.topic_title, ff_users.username AS autor, ff_users.user_id AS autor_id, ff_posts_text.post_id AS id, IF(LOCATE('matrix',LOWER(ff_posts_text.post_subject))>0, @stosunek:=@stosunek+(@stosunek*2),@stosunek), IF(LOCATE('matrix',LOWER(ff_topics.topic_title))>0, @stosunek:=@stosunek+5,@stosunek) FROM ff_users, ff_posts_text LEFT JOIN ff_posts ON ( ff_posts.post_id = ff_posts_text.post_id) LEFT JOIN ff_topics ON ( ff_posts.topic_id = ff_topics.topic_id) WHERE (ff_posts_text.post_text LIKE '%matrix%' OR ff_posts_text.post_subject LIKE '%matrix%' OR ff_topics.topic_title LIKE '%matrix%') AND ff_users.user_id=ff_topics.topic_poster GROUP BY ff_topics.topic_id ORDER BY ff_posts.post_time DESC LIMIT 100

[8 Jan 2004 12:03] Dean Ellis

The manual mentions that using a user variable in the same query where it is assigned a value is not recommended.  It can (does) lead to very unpredictable results, particularly in queries which use GROUP BY.

For more information:

http://www.mysql.com/doc/en/Variables.html