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: | |
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
[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;