Hi Rana,
Thanks, it makes much more sense in the context of the LEFT JOIN and the actual query (where the column is manipulated with the IFNULL() function). This is certainly server-side behavior associated with loosely-defined data types. The new index created a different execution plan, which in turn potentially triggers a different evaluation of the IFNULL() function. Because it returns an untyped "0" when the column contents are NULL, the optimizer seems to interpret that as a numeric value that's not limited by constraints of TINYINT (or other smaller INT types). You can't explicitly cast the "0" value to a TINYINT using CAST(), so there's a handful of options:
1. Eliminate the IFNULL() function call and treat NULL values as "zero" within your application code.
2. Write a custom function which explicitly returns a properly-typed TINYINT, and use that instead of the literal "0":
CREATE FUNCTION get_tinyint_zero() RETURNS TINYINT RETURN 0;
...
.append(" IFNULL(patmedicalrecords.record_status,get_tinyint_zero()) as recStatus,")
...
3. Modify your code so that it expects BigInteger data types in response.
4. Create a bridging table with properly typed default values which you can return in the IFNULL() function where appropriate.
It's probably worth pointing out also that the code has a number of no-op IFNULL() function calls, which you might consider eliminating:
...
.append(" IFNULL(patmedicalrecords.dm_document_id,null) as dmDocId,")
.append(" IFNULL(patmedicalrecords.pat_record_id,null) as patRecId,")
.append(" IFNULL(patmedicalrecords.patient_id,null) as patientId,")
Hope that helps!
--
Todd Farmer
MySQL @ Oracle
http://www.oracle.com/mysql/