Bug #42980 Client doesn't set NUM_FLAG for DECIMAL

Description:
DECIMAL is listed as a numeric type but the metadata sent by the server doesn't mark it as such with NUM_FLAG. UNSIGNED_FLAG is however set, depending on the field. All other numeric types have NUM_FLAG set. Be sure to start you command line client with --column-type-info . And be sure it is 5.1, because 5.0's client doesn't support this option.
Can be checked with (that all other numeric types have NUM_FLAG set)

create table manycols(tiny tinyint, tiny_uns tinyint unsigned, small smallint, small_uns smallint unsigned, medium mediumint, medium_uns mediumint unsigned, int_col int, int_col_uns int unsigned, big bigint, big_uns bigint unsigned, decimal_col decimal(10,5), decimal_col_uns decimal(10,5) unsigned, fcol float, fcol_uns float unsigned, dcol double, dcol_uns double unsigned, date_col date, time_col time, timestamp_col timestamp, year_col year, bit_col bit(5),  char_col char(5), varchar_col varchar(10), binary_col binary(10), varbinary_col varbinary(10), tinyblob_col tinyblob, blob_col blob, mediumblob_col mediumblob, longblob_col longblob, text_col text, mediumtext_col mediumtext, longtext_col longtext);
select * from manycols;

How to repeat:
+--------------+
| version()    |
+--------------+
| 5.1.31-debug |
+--------------+
1 row in set (0.00 sec)

mysql> create table t1(dec_f decimal(10,5), dec_f_uns decimal(10,5) unsigned);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
Field   1:  `dec_f`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     12
Max_length: 0
Decimals:   5
Flags:

Field   2:  `dec_f_uns`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   5
Flags:      UNSIGNED

0 rows in set (0.00 sec)

Suggested fix:
Send NUM_FLAG for DECIMAL columns

Thank you for the bug report. Verified as described:

c:\dbs>5.0\bin\mysql --port=3500 -T -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.79-Win x86-64 bzr revno:2741-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t1(dec_f decimal(10,5), dec_f_uns decimal(10,5) unsigned);
Query OK, 0 rows affected (0.06 sec)

mysql> select * from t1;
Field   1:  `dec_f`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     12
Max_length: 0
Decimals:   5
Flags:

Field   2:  `dec_f_uns`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   5
Flags:      UNSIGNED

0 rows in set (0.01 sec)

mysql>

I found out that it's not the Server who sends or has to send this piece of information, it's the client which is expected to find out: the buggy code is in sql-common/client.c.

According to the manul, NUM_FLAG should be set for TIMESTAMP too:
http://dev.mysql.com/doc/refman/5.4/en/c-api-datatypes.html
"NUM_FLAG indicates that a column is numeric. This includes columns with a type of MYSQL_TYPE_DECIMAL, MYSQL_TYPE_TINY, MYSQL_TYPE_SHORT, MYSQL_TYPE_LONG, MYSQL_TYPE_FLOAT, MYSQL_TYPE_DOUBLE, MYSQL_TYPE_NULL, MYSQL_TYPE_TIMESTAMP, MYSQL_TYPE_LONGLONG, MYSQL_TYPE_INT24, and MYSQL_TYPE_YEAR."

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

2876 Guilhem Bichot	2009-08-29
      Fix for BUG#42980 "Client doesn't set NUM_FLAG for DECIMAL and TIMESTAMP":
      DECIMAL and TIMESTAMP used to have NUM_FLAG, but NEWDECIMAL was forgotten.
      It's correct that TIMESTAMP does not have the flag nowadays (manual will be updated, connectors
      developers will be notified).
     @ client/mysqldump.c
        IS_NUM_FIELD(f) removed and replaced by its definition (f>flags & NUM_FLAG).
     @ include/mysql.h
        - IS_NUM_FIELD() is removed because name is too close to IS_NUM() and it is not used a lot
        - INTERNAL_NUM_FIELD() is removed:
          * it forgets to test NEWDECIMAL (when IS_NUM() was updated for NEWDECIMAL we forgot
          to update INTERNAL_NUM_FIELD()), that's why client didn't mark NEWDECIMAL with NUM_FLAG (a bug).
          * it has an obsolete test for length of the TIMESTAMP field: test became accidentally wrong when length
          of TIMESTAMP was changed to always be 19 (when the format was changed from YYYYMMDDhhmmss to
          YYYY-MM-DD hh:mm:ss), never 8 or 14 anymore. That obsolete test caused TIMESTAMP to lose NUM_FLAG,
          which was an accidental but good change (see below).
          * IS_NUM() should be used instead
        - IS_NUM(f) is changed: TIMESTAMP used to be parsable as a number without quotes (when it was formatted as
        "YYYYMMDDhhmmss"); but it is not anymore (now that it is "YYYY-MM-DD hh:mm:ss"), so it should not have NUM_FLAG
        (mysqldump needs to quote TIMESTAMP values), so IS_NUM() should return false for it.
     @ libmysqld/lib_sql.cc
        use IS_NUM() instead of INTERNAL_NUM_FIELD()
     @ mysql-test/r/bigint.result
        result change: NEWDECIMAL fields now have NUM_FLAG (32768)
     @ mysql-test/r/metadata.result
        result change: NEWDECIMAL fields now have NUM_FLAG (32768)
     @ mysql-test/r/mysqldump.result
        DECIMAL columns are not quoted anymore by mysqldump. Which is ok, the parser does not need '' for them
     @ mysql-test/r/ps_2myisam.result
        result change: NEWDECIMAL fields now have NUM_FLAG (32768)
     @ mysql-test/r/ps_3innodb.result
        result change: NEWDECIMAL fields now have NUM_FLAG (32768)
     @ mysql-test/r/ps_4heap.result
        result change: NEWDECIMAL fields now have NUM_FLAG (32768)
     @ mysql-test/r/ps_5merge.result
        result change: NEWDECIMAL fields now have NUM_FLAG (32768)
     @ mysql-test/suite/ndb/r/ps_7ndb.result
        result change: NEWDECIMAL fields now have NUM_FLAG (32768)
     @ mysql-test/t/metadata.test
        test for BUG#42980
     @ sql-common/client.c
        use IS_NUM() instead of INTERNAL_NUM_FIELD()

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

2880 Guilhem Bichot	2009-09-30
      Fix for BUG#42980 "Client doesn't set NUM_FLAG for DECIMAL and TIMESTAMP":
      DECIMAL and TIMESTAMP used to have NUM_FLAG, but NEWDECIMAL was forgotten.
      It's correct that TIMESTAMP does not have the flag nowadays (manual will be updated, connectors
      developers will be notified).
     @ client/mysqldump.c
        IS_NUM_FIELD(f) removed and replaced by its definition (f>flags & NUM_FLAG).
     @ include/mysql.h
        - IS_NUM_FIELD() is removed because name is too close to IS_NUM() and it is not used a lot
        - INTERNAL_NUM_FIELD() is removed:
          * it forgets to test NEWDECIMAL (when IS_NUM() was updated for NEWDECIMAL we forgot
          to update INTERNAL_NUM_FIELD()), that's why client didn't mark NEWDECIMAL with NUM_FLAG (a bug).
          * it has an obsolete test for length of the TIMESTAMP field: test became accidentally wrong when length
          of TIMESTAMP was changed to always be 19 (when the format was changed from YYYYMMDDhhmmss to
          YYYY-MM-DD hh:mm:ss), never 8 or 14 anymore. That obsolete test caused TIMESTAMP to lose NUM_FLAG,
          which was an accidental but good change (see below).
          * IS_NUM() should be used instead
        - IS_NUM(f) is changed: TIMESTAMP used to be parsable as a number without quotes (when it was formatted as
        "YYYYMMDDhhmmss"); but it is not anymore (now that it is "YYYY-MM-DD hh:mm:ss"), so it should not have NUM_FLAG
        (mysqldump needs to quote TIMESTAMP values), so IS_NUM() should return false for it.
     @ libmysqld/lib_sql.cc
        use IS_NUM() instead of INTERNAL_NUM_FIELD()
     @ mysql-test/r/bigint.result
        result change: NEWDECIMAL fields now have NUM_FLAG (32768)
     @ mysql-test/r/metadata.result
        result change: NEWDECIMAL fields now have NUM_FLAG (32768)
     @ mysql-test/r/mysqldump.result
        DECIMAL columns are not quoted anymore by mysqldump. Which is ok, the parser does not need '' for them
     @ mysql-test/r/ps_2myisam.result
        result change: NEWDECIMAL fields now have NUM_FLAG (32768)
     @ mysql-test/r/ps_3innodb.result
        result change: NEWDECIMAL fields now have NUM_FLAG (32768)
     @ mysql-test/r/ps_4heap.result
        result change: NEWDECIMAL fields now have NUM_FLAG (32768)
     @ mysql-test/r/ps_5merge.result
        result change: NEWDECIMAL fields now have NUM_FLAG (32768)
     @ mysql-test/suite/ndb/r/ps_7ndb.result
        result change: NEWDECIMAL fields now have NUM_FLAG (32768)
     @ mysql-test/t/metadata.test
        test for BUG#42980
     @ sql-common/client.c
        use IS_NUM() instead of INTERNAL_NUM_FIELD()

queued to mysql-next-mr-bugfixing.

Doc info:

1) According to the manul, NUM_FLAG should be set for TIMESTAMP:
http://dev.mysql.com/doc/refman/5.4/en/c-api-datatypes.html
"NUM_FLAG indicates that a column is numeric. This includes columns with a type of
MYSQL_TYPE_DECIMAL, MYSQL_TYPE_TINY, MYSQL_TYPE_SHORT, MYSQL_TYPE_LONG, MYSQL_TYPE_FLOAT,
MYSQL_TYPE_DOUBLE, MYSQL_TYPE_NULL, MYSQL_TYPE_TIMESTAMP, MYSQL_TYPE_LONGLONG,
MYSQL_TYPE_INT24, and MYSQL_TYPE_YEAR."
But this is wrong:
1a) in fact TIMESTAMP lost NUM_FLAG when it was changed from "YYYYMMDDhhmmss" to "YYYY-MM-DD hh:mm:ss".
1b) and it's correct that it lost it: this syntax above is not parsable as a number and needs quotes in INSERT (for example mysqldump needs to quote TIMESTAMP values otherwise it creates syntax errors). NUM_FLAG means that the parser can parse the field value as a number (digits, and no need for quotes). 
1c) so this patch changes nothing (TIMESTAMP still doesn't have NUM_FLAG), the manual portion needs to be updated.

2) this patch makes NEWDECIMAL have NUM_FLAG, just like DECIMAL always had.
NEWDECIMAL should thus be added to the manual portion quoted above in 1).

Pushed into 6.0.14-alpha (revid:alik@ibmvm-20091009083208-0o0f0i9w1sq3c1kn) (version source revid:ingo.struewing@sun.com-20091001194257-a6xrw9u0ccx24qgn) (merge vers: 6.0.14-alpha) (pib:12)

Guilhem,
re: 1a), Do you mean the NUM_FLAG was lost for TIMESTAMP when TIMESTAMP changed format in 4.1? If so, then I guess all versions of the manual should be changed to reflect what you note in 1c)?

re: 2), this is now pushed to 6.0.14, will it also go to 5.5.x?

Hello Paul,
1) 4.0 doesn't build on my laptop so I cannot say for 4.0; but for sure, 4.1 client does not report NUM_FLAG for TIMESTAMP fields on 4.1 servers, as I see in mysql-4.1/mysql-test/r/metadata.result: the field "k" created at line 11 has flags 1249 and so does not have 32748 which is NUM_FLAG.
Apart from those facts, yes the logical explanation is the change in syntax of timestamps (added ":" and space).
2) I don't know, it is on a list which I sent to Staale Deraas for consideration of backporting, but he will be the decider

Guilhem,

FYI: This is what I get when I connect to a 4.0.30 server. Looks like the NUM flag is set for the TIMESTAMP column, in contrast to current behavior.

mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t (t timestamp, i int, c char(5));
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t;
Field   1:  `t`
Catalog:    ``
Database:   ``
Table:      `t`
Org_table:  `t`
Type:       TIMESTAMP
Collation:  ? (0)
Length:     14
Max_length: 0
Decimals:   0
Flags:      NOT_NULL UNSIGNED ZEROFILL TIMESTAMP NUM 

Field   2:  `i`
Catalog:    ``
Database:   ``
Table:      `t`
Org_table:  `t`
Type:       LONG
Collation:  ? (0)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NUM 

Field   3:  `c`
Catalog:    ``
Database:   ``
Table:      `t`
Org_table:  `t`
Type:       STRING
Collation:  ? (0)
Length:     5
Max_length: 0
Decimals:   0
Flags:      

I'll ask Staale about 5.5.x. Thanks for the info!

Noted in 6.0.14 changelog.

The NUM_FLAG bit of the MYSQL_FIELD.flags member now is set for
columns of type MYSQL_TYPE_NEWDECIMAL. 

Setting report to NDI pending push into 5.5.x.

Hello Paul. Actually the fix was pushed in next-mr-bugfixing, and is in next-mr now. So, the fix is in 5.5.x now. Not sure you need a changelog entry for 6.0.14.

Paul, the fix is not and will not be in mysql-trunk; it's in mysql-next-mr (I verified).

Setting report to NDI pending push into 5.5.x.

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:alik@ibmvm-20091009130916-0ijstmmz3efzx20g) (merge vers: 5.5.0-beta) (pib:13)

Noted in 5.5.0 changelog.

Bug #55824 is fixed by the fix for this bug.