I've written a simple aggregation view over a table, and the view returns different data types that the table. This causes some issues on the application side.

Here's a simplified query for the sake of the example:

SELECT id, FLOOR(date_id / 100) month_id, SUM(value) FROM some_table WHERE....;

The problem is that data_id is an INTEGER(11) column, represented in my app as an int32, and the result of FLOOR(date_id / 100) is a BIGINT, represented in my app as an int64.

The type of id also changes, for reasons I can't explain, from int64 to int32.

Is there any way to cast the column to an int32? CAST seemed not to work. I haven't found any documentation for a way to see what the expected data type of a view result set is.

2 Answers 2


You can hunt down the column definitions in the INFORMATION_SCHEMA. For example, for the table mydb.mytb and the column name needed is called mycol, do this:

SELECT column_type FROM information_schema.columns
WHERE table_schema='mydb'
AND table_name='mytb'
AND column_name='mycol';

For the query with formulas that you have in your question:

SELECT id, FLOOR(date_id / 100) month_id, SUM(value) FROM some_table WHERE....;

Try creating your own empty temp table using your query with a FALSE-evaluated WHERE clause:

SELECT id, FLOOR(date_id / 100) month_id, SUM(value) value FROM some_table WHERE 1=2;

That table, though empty, has a definite table structure and some columns types defined. Now, just run the same query to get the column types from the temp table:

SELECT column_name,column_type FROM information_schema.columns
WHERE table_schema='test'
AND table_name='mycolumnmap'
AND column_name IN ('id','month_id','value');

Another alternative is to just ask the INFORMATION_SCHEMA about the columns of the view directly:

SELECT column_name,column_type FROM information_schema.columns
WHERE table_schema='test'
AND table_name='view_name';

Give it a Try !!!

  • I didn't know VIEW's columns where also available in the information_schema.columns table. Now, at least, I can test some conversion methods and see if this works.
    – OmerGertel
    Nov 24, 2011 at 7:30
  • I combined this with SHOW FIELDS FROM ... (described here) to quickly get the datatypes of a sticky query. Thanks!
    – Edward
    Feb 27, 2013 at 23:53

This is taken directly from the MySQL web site and might be your solution:

Posted by The Lynxy on March 9 2009 8:56am

MySQL 5.1 returns a 64-bit integer when using CAST(). For those looking to cast a 32-bit signed integer to a 32-bit unsigned integer: you can use the function this way.

Converting -1062731519 to an unsigned integer (this number is the decimal value of


Result is 3232235903, which is correct.


  • I'll have to check if this helps. The problem is that we are loading the results into an MS OLAP cube, and if the data types are not the same, we have problems defining the relationship between the dimension table and the view described above. The result is not the issue as much as the data type itself.
    – OmerGertel
    Nov 24, 2011 at 7:29
  • Data compatibility across vendors certainly is an interesting dilemma. :-)
    – randomx
    Nov 24, 2011 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.