Bug #30538 Parser uses different datatype for integer literals
Submitted: 21 Aug 2007 18:45 Modified: 27 Mar 2014 21:39
Reporter: Mark Callaghan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.37 OS:Any
Assigned to: Paul DuBois CPU Architecture:Any
Tags: BIGINT, INTEGER, literal, numeric, regression

[21 Aug 2007 18:45] Mark Callaghan
Description:
In MySQL 4.0, this creates a table with a bigint column.
In MySQL 5.0, this creates a table with an int column.

create table foo as select 0

Both MySQL 4 and 5 use bigint for integer literals that are too large for integer.

In MySQL5, the type of an integer literal depends on the value of the integer literal. I wish that were not so.

How to repeat:
Run create table statement above.

Suggested fix:
Stop breaking behavior across database versions. Too many things were broken from 4 to 5. Some of this was to provide SQL compliant behavior. Is this yet another case of that?

Document your SQL grammar. Is 'expr' or 'select_expr' documented?

Support CAST(value as Bigint)
[16 Feb 2008 17:53] Konstantin Osipov
Mark,
what's the real issue? I don't get why this change is a problem to you.
[16 Feb 2008 17:59] Mark Callaghan
Some users that I support do: 'create table as select ..., 0, ... from ...'. Some of these uses required a bigint column. With MySQL5, they no longer get a bigint for the column corresponding to '0' and their code stopped working.
[16 Feb 2008 18:50] Paul DuBois
The CREATE TABLE part of CREATE TABLE ... SELECT can specify data types for the columns, to cause the resulting type to be invariant across versions.
[16 Feb 2008 18:57] Konstantin Osipov
Marc,
I made an investigation and my theory is that this was introduced by a fix for Bug#19714 (I inspected the code, and also when I revert the patch, the server
works as before).

Here's a patch that reverts the fix:
===== sql_select.cc 1.583 vs edited =====
--- 1.583/sql/sql_select.cc	2008-02-01 13:50:18 +03:00
+++ edited/sql/sql_select.cc	2008-02-16 21:53:42 +03:00
@@ -9251,18 +9251,8 @@
                                 item->name, item->decimals, TRUE);
     break;
   case INT_RESULT:
-    /* 
-      Select an integer type with the minimal fit precision.
-      MY_INT32_NUM_DECIMAL_DIGITS is sign inclusive, don't consider the sign.
-      Values with MY_INT32_NUM_DECIMAL_DIGITS digits may or may not fit into 
-      Field_long : make them Field_longlong.  
-    */
-    if (item->max_length >= (MY_INT32_NUM_DECIMAL_DIGITS - 1))
-      new_field=new Field_longlong(item->max_length, maybe_null,
-                                   item->name, item->unsigned_flag);
-    else
-      new_field=new Field_long(item->max_length, maybe_null,
-                               item->name, item->unsigned_flag);
+    new_field=new Field_longlong(item->max_length, maybe_null,
+                                 item->name, item->unsigned_flag);
     break;
   case STRING_RESULT:
     DBUG_ASSERT(item->collation.collation);

(the change is in create_tmp_field_from_item.

But generally, I see nothing wrong in the change, since we choose a more optimal type. We have never documented the old behavior either.

Note, that you can workaround the current behavior by doing:

create table t1 select 000000000000000000000;

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `000000000000000000000` bigint(21) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[16 Feb 2008 18:58] Konstantin Osipov
Suggest to close as "Won't fix"
[16 Feb 2008 19:25] Mark Callaghan
Whether or not you change the code, you should document the existing rules for the datatype used for numeric literals. One section of the docs that might describe this has no details -- http://dev.mysql.com/doc/refman/5.0/en/number-syntax.html

For example, when are the following used for numeric literals?
* Double
* Decimal
* Int
* Bigint
[15 Oct 2010 14:40] Paul DuBois
"Document your SQL grammar. Is 'expr' or 'select_expr' documented?"

It is now. See:

http://dev.mysql.com/doc/refman/5.1/en/expressions.html

Regarding the data type used for numeric literals, most of this is answered here:

http://dev.mysql.com/doc/refman/5.5/en/precision-math-numbers.html

But that doesn't indicate anything about whether INT vs BIGINT will be used. Let me investigate further.
[19 Oct 2010 15:26] Paul DuBois
More info. There are two issues.

For integer expression _evaluation_ (-, +, *), BIGINT arithmetic is used.

For integer expression _assignment_ to a column, e.g., for CREATE TABLE ... SELECT integer_expr, the column might become INT or BIGINT, depending on the max_length of the expression type. If the maximum length of the type does not fit in an INT, a BIGINT will be used instead.

max_length is as described here: http://dev.mysql.com/doc/refman/5.1/en/c-api-data-structures.html
[27 Mar 2014 21:39] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

Added this to the end of http://dev.mysql.com/doc/refman/5.6/en/type-conversion.html:

For integer expressions, the preceding remarks about expression
evaluation apply somewhat differently for expression assignment; for
example, in a statement such as this:

CREATE TABLE t SELECT integer_expr;

In this case, the table in the column resulting from the expression
has type INT or BIGINT depending on the length of the integer
expression. If the maximum length of the expression does no fit in an
INT, BIGINT is used instead. The length is taken from the max_length
value of the SELECT result set metadata. This means that you
can force a BIGINT rather than INT by use of a sufficiently long
expression:

CREATE TABLE t SELECT 000000000000000000000;