Re: TINYINT column returning BigInteger instead of Integer
Posted by: Todd Farmer
Date: July 15, 2013 03:48PM

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":


.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

Options: ReplyQuote

Written By
Re: TINYINT column returning BigInteger instead of Integer
July 15, 2013 03:48PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.