Bug #10276 IS_NUM_FIELD () macro returning wrong value
Submitted: 30 Apr 2005 2:52 Modified: 24 May 2005 4:43
Reporter: Ritesh Nadhani Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.7 OS:Windows (Windows)
Assigned to: CPU Architecture:Any

[30 Apr 2005 2:52] Ritesh Nadhani 
Description:
Hello,

In MySQL C API(), macro IS_NUM_FIELD is provided to find whether a field is numeric or not.

I have a table structure like:

CREATE TABLE `mp3_files` (
  `ID` bigint(20) NOT NULL auto_increment,
  `Title` varchar(128) default NULL,
  `Artist` varchar(128) default NULL,
  `Album` varchar(128) default NULL,
  `Genre` varchar(32) default NULL,
  `Rating` tinyint(4) default NULL,
  `TrackTime` int(11) default NULL,
  `Nr` smallint(6) default NULL,
  `Bitrate` int(11) default NULL,
  `NumberOfChannels` smallint(6) default NULL,
  `RecYear` smallint(6) default NULL,
  `ArtistWebPage` varchar(255) default NULL,
  `TrackType` smallint(6) default NULL,
  `TrackFormat` varchar(50) default NULL,
  `File_name` varchar(255) NOT NULL,
  `DateAdded` varchar(40) default NULL,
  `ModTime` varchar(40) default NULL,
  `DateLastPlayed` varchar(40) default NULL,
  `TrackPlayCount` int(11) default NULL,
  `FileSizeInBytes` double default NULL,
  `DiskNumberInSet` smallint(6) default NULL,
  `TotalDisksInSet` smallint(6) default NULL,
  `Composer` varchar(128) default NULL,
  `FeaturedArtists` varchar(128) default NULL,
  `Instrument` varchar(128) default NULL,
  `Lyrics` mediumtext,
  `Notes` mediumtext,
  `ArtistBio` mediumtext,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

If I issue a query like:

select mp3_files.Artist , mp3_files.Album , mp3_files.Genre , mp3_files.File_name
from musik.mp3_files
where mp3_files.Artist is not NULL and mp3_files.Album is not NULL
group by Album, Artist, Genre
order by Genre, Artist, Album;

and use the above macro to determine the type, the macro returns TRUE for all fields irrespective of whether its a numeric field or not. 

The macro is defined to be:

#define IS_NUM_FIELD(f)	 ((f)->flags & NUM_FLAG)

The value of f->flags in the above case is always 32768.

But if I execute a query like:

select * from mp3_files limit 10;

IS_NUM_FIELD() returns correct value as the f->flags is containing correct values.

I am using MySQL 5.0 client library.

Is this a known bug? I searched on IS_NUM_FIELD in the bugs database but couldnt find any report?

How to repeat:
Create the table using the table structure given and execute the above two queries and use IS_NUM_FIELD() to determine the type passing MySQL_FIELD structure.
[1 May 2005 3:05] Jorge del Conde 
I was unable to reproduce this w/4.1.12:

mysql> create table num (a int, b float, c decimal(10,2), d double, e varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
jorge-jorge/mysql_code> ./main
Query: SELECT * FROM num
IS_NUM_FIELD(a): 32768
IS_NUM_FIELD(b): 32768
IS_NUM_FIELD(c): 32768
IS_NUM_FIELD(d): 32768
IS_NUM_FIELD(e): 0

Query: SELECT * FROM num LIMIT 10
IS_NUM_FIELD(a): 32768
IS_NUM_FIELD(b): 32768
IS_NUM_FIELD(c): 32768
IS_NUM_FIELD(d): 32768
IS_NUM_FIELD(e): 0

main.c:

#include <stdio.h>
#include <mysql.h>
#include <errmsg.h>

int do_select(MYSQL *mysql, const char *qry)
{
  MYSQL_RES *res;
  MYSQL_FIELD *field;

  if ((mysql_query(mysql, qry) != 0))
    return (0);

  if ((res = mysql_store_result(mysql)))
    {
      printf("Query: %s\n", qry);
      while((field = mysql_fetch_field(res)))
        printf("IS_NUM_FIELD(%s): %d\n", field->name, IS_NUM_FIELD(field));
      mysql_free_result(res);
      printf("\n");
      return 1;
    }
  return 0;
}

int main ()
{
  MYSQL *mysql;

  mysql= mysql_init(NULL);
  mysql_real_connect(mysql,"localhost","root",NULL,"test",0,NULL,0);

  do_select(mysql, "SELECT * FROM num");
  do_select(mysql, "SELECT * FROM num LIMIT 10");

  mysql_close(mysql);
  return 0;
}
[1 May 2005 9:58] Sergei Golubchik 
Also, I tried with exactly your query, and 5.0 client library (vs. 4.1 server) - worked as expected,
flags were set correctly.
[1 May 2005 14:25] Ritesh Nadhani 
Hello,

This is very strange. I have attached a sql dump of my database. Please restore the table and execute the two queries I provided.

In my case (I am using the Windows client library), for the first simple query i.e. select * from mp3_files limit 10 returns correct value in flags field.

0 for non-numeric and 32768 for numeric and thus the following condition works:

if ( IS_NUM_FIELD ( field ) )
 printf ( "Numeric" );
else
 printf ( "Non-numeric" );

But if I execute the more complex version i.e. containing the group clause, the flags value for all field is 32967 which results the condition to be true in:

if ( IS_NUM_FIELD ( field ) )
[1 May 2005 20:06] Sergei Golubchik 
perhaps it's windows-only problem.
we'll try to repeat it on windows
[1 May 2005 23:15] MySQL Verification Team 
Verified on Windows 4.1.11:

C:\temp>bug10276
Query: select Artist,Album,Genre,File_name from mp3_files where Artist is not NULL 
and Album is not NULL group by Album, Artist, Genre order by Genre, Artist, Album;

IS_NUM_FIELD(Artist): 32768
IS_NUM_FIELD(Album): 32768
IS_NUM_FIELD(Genre): 32768
IS_NUM_FIELD(File_name): 0

Query: select * from mp3_files limit 10;
IS_NUM_FIELD(ID): 32768
IS_NUM_FIELD(Title): 0
IS_NUM_FIELD(Artist): 0
IS_NUM_FIELD(Album): 0
IS_NUM_FIELD(Genre): 0
IS_NUM_FIELD(Rating): 32768
IS_NUM_FIELD(TrackTime): 32768
IS_NUM_FIELD(Nr): 32768
IS_NUM_FIELD(Bitrate): 32768
IS_NUM_FIELD(NumberOfChannels): 32768
IS_NUM_FIELD(RecYear): 32768
IS_NUM_FIELD(ArtistWebPage): 0
IS_NUM_FIELD(TrackType): 32768
IS_NUM_FIELD(TrackFormat): 0
IS_NUM_FIELD(File_name): 0
IS_NUM_FIELD(DateAdded): 0
IS_NUM_FIELD(ModTime): 0
IS_NUM_FIELD(DateLastPlayed): 0
IS_NUM_FIELD(TrackPlayCount): 32768
IS_NUM_FIELD(FileSizeInBytes): 32768
IS_NUM_FIELD(DiskNumberInSet): 32768
IS_NUM_FIELD(TotalDisksInSet): 32768
IS_NUM_FIELD(Composer): 0
IS_NUM_FIELD(FeaturedArtists): 0
IS_NUM_FIELD(Instrument): 0
IS_NUM_FIELD(Lyrics): 0
IS_NUM_FIELD(Notes): 0
IS_NUM_FIELD(ArtistBio): 0
[2 May 2005 2:33] Ritesh Nadhani 
Does verified means its a bug and will be fixed?

When can I expect the bug fix?

currently I have defined another macro:

IS_NUMBER ( field->type )

#define IS_NUMBER(t) ((t)==FIELD_TYPE_LONG || (t)==FIELD_TYPE_LONGLONG || (t)==FIELD_TYPE_DECIMAL || (t)==FIELD_TYPE_DOUBLE || (t)==FIELD_TYPE_FLOAT || (t)==FIELD_TYPE_INT24 || (t)==FIELD_TYPE_YEAR || (t)==FIELD_TYPE_SHORT || (t)==FIELD_TYPE_TIMESTAMP || (t)==FIELD_TYPE_TINY )

which works for me but IS_NUM_FIELD() should work.

Regards
[24 May 2005 0:24] Jim Winstead 
Sorry, there's not a usable test case here. The test needs to include the table, C code, the output, and the desired output.

Miguel, please create a real test case for this bug.

Thanks!
[24 May 2005 4:43] MySQL Verification Team 
I have tested now with latest 4.1.13 and the behavior presented
by 4.1.11 not exists anymore. Test case:

Create the table on test database:

mysql> use test;
Database changed
mysql> CREATE TABLE `mp3_files` (
    ->   `ID` bigint(20) NOT NULL auto_increment,
    ->   `Title` varchar(128) default NULL,
    ->   `Artist` varchar(128) default NULL,
    ->   `Album` varchar(128) default NULL,
    ->   `Genre` varchar(32) default NULL,
    ->   `Rating` tinyint(4) default NULL,
    ->   `TrackTime` int(11) default NULL,
    ->   `Nr` smallint(6) default NULL,
    ->   `Bitrate` int(11) default NULL,
    ->   `NumberOfChannels` smallint(6) default NULL,
    ->   `RecYear` smallint(6) default NULL,
    ->   `ArtistWebPage` varchar(255) default NULL,
    ->   `TrackType` smallint(6) default NULL,
    ->   `TrackFormat` varchar(50) default NULL,
    ->   `File_name` varchar(255) NOT NULL,
    ->   `DateAdded` varchar(40) default NULL,
    ->   `ModTime` varchar(40) default NULL,
    ->   `DateLastPlayed` varchar(40) default NULL,
    ->   `TrackPlayCount` int(11) default NULL,
    ->   `FileSizeInBytes` double default NULL,
    ->   `DiskNumberInSet` smallint(6) default NULL,
    ->   `TotalDisksInSet` smallint(6) default NULL,
    ->   `Composer` varchar(128) default NULL,
    ->   `FeaturedArtists` varchar(128) default NULL,
    ->   `Instrument` varchar(128) default NULL,
    ->   `Lyrics` mediumtext,
    ->   `Notes` mediumtext,
    ->   `ArtistBio` mediumtext,
    ->   PRIMARY KEY  (`ID`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.09 sec)

Create the below C API console application:

#include <stdio.h>
#include <my_global.h>
#include <mysql.h>
#include <errmsg.h>

int do_select(MYSQL *mysql, const char *qry)
{
  MYSQL_RES *res;
  MYSQL_FIELD *field;

  if ((mysql_query(mysql, qry) != 0))
    return (0);

  if ((res = mysql_store_result(mysql)))
    {
      printf("Query: %s\n", qry);
      while((field = mysql_fetch_field(res)))
        printf("IS_NUM_FIELD(%s): %d\n", field->name, IS_NUM_FIELD(field));
      mysql_free_result(res);
      printf("\n");
      return 1;
    }
  return 0;
}

int main ()
{
  MYSQL *mysql;

  mysql= mysql_init(NULL);
  mysql_real_connect(mysql,"localhost","root","","test",0,NULL,0);

  do_select(mysql, "SELECT * FROM mp3_files");
  do_select(mysql, "SELECT * FROM mp3_files LIMIT 10");

  mysql_close(mysql);
  return 0;
}

Run the console application:

D:\temp>bug10276
Query: SELECT * FROM mp3_files
IS_NUM_FIELD(ID): 32768
IS_NUM_FIELD(Title): 0
IS_NUM_FIELD(Artist): 0
IS_NUM_FIELD(Album): 0
IS_NUM_FIELD(Genre): 0
IS_NUM_FIELD(Rating): 32768
IS_NUM_FIELD(TrackTime): 32768
IS_NUM_FIELD(Nr): 32768
IS_NUM_FIELD(Bitrate): 32768
IS_NUM_FIELD(NumberOfChannels): 32768
IS_NUM_FIELD(RecYear): 32768
IS_NUM_FIELD(ArtistWebPage): 0
IS_NUM_FIELD(TrackType): 32768
IS_NUM_FIELD(TrackFormat): 0
IS_NUM_FIELD(File_name): 0
IS_NUM_FIELD(DateAdded): 0
IS_NUM_FIELD(ModTime): 0
IS_NUM_FIELD(DateLastPlayed): 0
IS_NUM_FIELD(TrackPlayCount): 32768
IS_NUM_FIELD(FileSizeInBytes): 32768
IS_NUM_FIELD(DiskNumberInSet): 32768
IS_NUM_FIELD(TotalDisksInSet): 32768
IS_NUM_FIELD(Composer): 0
IS_NUM_FIELD(FeaturedArtists): 0
IS_NUM_FIELD(Instrument): 0
IS_NUM_FIELD(Lyrics): 0
IS_NUM_FIELD(Notes): 0
IS_NUM_FIELD(ArtistBio): 0

Query: SELECT * FROM mp3_files LIMIT 10
IS_NUM_FIELD(ID): 32768
IS_NUM_FIELD(Title): 0
IS_NUM_FIELD(Artist): 0
IS_NUM_FIELD(Album): 0
IS_NUM_FIELD(Genre): 0
IS_NUM_FIELD(Rating): 32768
IS_NUM_FIELD(TrackTime): 32768
IS_NUM_FIELD(Nr): 32768
IS_NUM_FIELD(Bitrate): 32768
IS_NUM_FIELD(NumberOfChannels): 32768
IS_NUM_FIELD(RecYear): 32768
IS_NUM_FIELD(ArtistWebPage): 0
IS_NUM_FIELD(TrackType): 32768
IS_NUM_FIELD(TrackFormat): 0
IS_NUM_FIELD(File_name): 0
IS_NUM_FIELD(DateAdded): 0
IS_NUM_FIELD(ModTime): 0
IS_NUM_FIELD(DateLastPlayed): 0
IS_NUM_FIELD(TrackPlayCount): 32768
IS_NUM_FIELD(FileSizeInBytes): 32768
IS_NUM_FIELD(DiskNumberInSet): 32768
IS_NUM_FIELD(TotalDisksInSet): 32768
IS_NUM_FIELD(Composer): 0
IS_NUM_FIELD(FeaturedArtists): 0
IS_NUM_FIELD(Instrument): 0
IS_NUM_FIELD(Lyrics): 0
IS_NUM_FIELD(Notes): 0
IS_NUM_FIELD(ArtistBio): 0