MySQL Bugs: #19700: subselect returning BIGINT always returned it as SIGNED

Bug #19700	subselect returning BIGINT always returned it as SIGNED
Submitted:	10 May 2006 22:35	Modified:	25 May 2006 20:30
Reporter:	Michael Del Monte	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.22-BK, 5.0.18	OS:	Linux (Linux, Windows XP)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
Subselects that would return a BIGINT always return it as BIGINT SIGNED.

How to repeat:
This can be easily reproduced by testing the following query against a table_y having a single BIGINT column v with a single BIGINT row value, where that value is greater than the maximum BIGINT SIGNED value:

select * from table_x where val=(select max(v) from table_y)

This query will return null even when table_y contains only val, and even though both tables use BIGINT UNSIGNED.

You can work around the problem by casting the subselect query.  The following will work:

select * from table_x where val=cast((select max(v) from table_y) as unsigned)

Suggested fix:
Ensure that BIGINTs are returned with the proper sign in subqueries.

mysqltest test case

Attachment: bug19700.tar.gz (application/x-gzip, text), 814 bytes.

Also verified with 5.0.22-BK on Linux.

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/6812

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/6858

Pushed in 5.0.23-BK

Noted in 5.0.23 changelog.

Subqueries that produced a <literal>BIGINT UNSIGNED</literal>
value were being treated as returning a signed value.