Dealing With MySQL Cast() Limitation

If your application depends on data type of your database, you will notice that mysql cast() function has limitation that can't cast to specific datatype, like tinyint, smallint, mediumint, bigint, etc.

There is simple (dumb and idiot) workaround you can do to overcome this situation. Let's try it out.

Create a table with 5 column.

CREATE TABLE datatype_test (
  tiny_int tinyint(4),
  small_int smallint(6),
  medium_int mediumint(9),
  _int int(11),
  big_int bigint(20)
);

Populate each column with value of 10.

INSERT datatype_test (
  tiny_int,
  small_int,
  medium_int,
  _int,
  big_int
)
values (10,10,10,10,10);

Now, let's try with a simple query.

select     (d.tiny_int*1) as tiny,
           (d.small_int*1) as small,
           (d.medium_int*1) as `medium`,
           (d._int*1) as regular,
           (d.big_int*1) as big
from       datatype_test d;

When you multiply the columns with any number, the result data type is not same as the original data type. You can check it using this Java snippet:

import java.sql.*;

public class datatype_test {
    public Connection cn;
    public PreparedStatement st;
    public ResultSet rs;
    
    public static void main(String[] a) throws Exception {
        new datatype_test();
    }
    
    public datatype_test() throws Exception {
        connect();
        
        showcase1();
    }
    
    private void connect() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        
        String url = "jdbc:mysql://localhost/test";            
        cn=DriverManager.getConnection(url,"user","password");
    }
    
    private void showcase1() throws Exception {
        st = cn.prepareStatement(
                "select  (d.tiny_int*1) as tiny, " +
                "        (d.small_int*1) as small, " +
                "        (d.medium_int*1) as `medium`, " +
                "        (d._int*1) as regular, " +
                "        (d.big_int*1) as big " +
                "from    datatype_test d;");

        if (st.execute()) {
            rs = st.getResultSet();
            rs.next();
            
            ResultSetMetaData rsmt = rs.getMetaData();
            int cols = rsmt.getColumnCount();
            
            for (int i=1;i<=cols;i++) {
                System.out.println(rsmt.getColumnLabel(i) + " = " +
                                   rsmt.getColumnTypeName(i));
            }
        }
        
        rs.close();
        st.close();
    }
}

When you run it, the result will looks like this:

tiny = BIGINT
small = BIGINT
medium = BIGINT
regular = BIGINT
big = BIGINT

That's the problem. Let's do something with this. While MySQL cast() has limitation, why not make our own cast function.

CREATE FUNCTION x_cast_to_tinyint(number bigint) RETURNS tinyint
BEGIN
    return number;
END

CREATE FUNCTION x_cast_to_smallint(number bigint) RETURNS smallint
BEGIN
    return number;
END

CREATE FUNCTION x_cast_to_mediumint(number bigint) RETURNS mediumint
BEGIN
    return number;
END

CREATE FUNCTION x_cast_to_int(number bigint) RETURNS int
BEGIN
    return number;
END

CREATE FUNCTION x_cast_to_bigint(number bigint) RETURNS bigint
BEGIN
    return number;
END

Now we already have our cast function, we should alter our query.

select   x_cast_to_tinyint(d.tiny_int*1) as tiny,
         x_cast_to_smallint(d.small_int*1) as small,
         x_cast_to_mediumint(d.medium_int*1) as `medium`,
         x_cast_to_int(d._int*1) as regular,
         x_cast_to_bigint(d.big_int*1) as big
from     datatype_test d;

Let's compare the two queries with our Java snippet:

import java.sql.*;

public class datatype_test {
    public Connection cn;
    public PreparedStatement st;
    public ResultSet rs;
    
    public static void main(String[] a) throws Exception {
        new datatype_test();
    }
    
    public datatype_test() throws Exception {
        connect();
        
        showcase1();
        System.out.println();
        showcase2();
    }
    
    private void connect() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        
        String url = "jdbc:mysql://localhost/test";            
        cn=DriverManager.getConnection(url,"user","password");
    }
    
    private void showcase1() throws Exception {
        st = cn.prepareStatement(
                "select  (d.tiny_int*1) as tiny, " +
                "        (d.small_int*1) as small, " +
                "        (d.medium_int*1) as `medium`, " +
                "        (d._int*1) as regular, " +
                "        (d.big_int*1) as big " +
                "from    datatype_test d;");

        if (st.execute()) {
            rs = st.getResultSet();
            rs.next();
            
            ResultSetMetaData rsmt = rs.getMetaData();
            int cols = rsmt.getColumnCount();
            
            System.out.println("Without Cast");
            
            for (int i=1;i<=cols;i++) {
                System.out.println(rsmt.getColumnLabel(i) + " = " + 
                                   rsmt.getColumnTypeName(i));
            }
        }
        
        rs.close();
        st.close();
    }
    
    private void showcase2() throws Exception {
        st = cn.prepareStatement(
                "select  x_cast_to_tinyint(d.tiny_int*1) as tiny, " +
                "        x_cast_to_smallint(d.small_int*1) as small, " +
                "        x_cast_to_mediumint(d.medium_int*1) as `medium`, " +
                "        x_cast_to_int(d._int*1) as regular, " +
                "        x_cast_to_bigint(d.big_int*1) as big " +
                "from    datatype_test d;");

        if (st.execute()) {
            rs = st.getResultSet();
            rs.next();
            
            ResultSetMetaData rsmt = rs.getMetaData();
            int cols = rsmt.getColumnCount();
            
            System.out.println("With Cast");
            
            for (int i=1;i<=cols;i++) {
                System.out.println(rsmt.getColumnLabel(i) + " = " + 
                                   rsmt.getColumnTypeName(i));
            }
        }
        
        rs.close();
        st.close();
    }
}

The result is:
Without Cast
tiny = BIGINT
small = BIGINT
medium = BIGINT
regular = BIGINT
big = BIGINT

With Cast
tiny = TINYINT
small = SMALLINT
medium = MEDIUMINT
regular = INTEGER
big = BIGINT