Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

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 192.168.1.1):

SELECT CAST(('-1062731519' & 0xFFFFFFFF) AS UNSIGNED INTEGER);

Result is 3232235903, which is correct.

http://dev.mysql.com/doc/refman/5.5/en/cast-functions.html

share|improve this answer
    
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 '11 at 7:29
    
Data compatibility across vendors certainly is an interesting dilemma. :-) –  randy melder Nov 24 '11 at 17:40
add comment

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:

CREATE TABLE IF NOT EXISTS test.mycolumnmap
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 !!!

share|improve this answer
    
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 '11 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 '13 at 23:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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