Data Truncation when converting to smaller datatype

[Corey Baytala]

Hi everyone,

New here, just signed up as I have an interesting question. I'm typically more used to MSSQL but had somebody ask for my thoughts on some odd results of something they did and the results I reproduced piqued my interest.

Let me preface this by saying this is a rare situation that I've never come across even in MSSQL as it's a bit of an odd thing to do from my perspective, so please don't ask me why I'm doing this, I'm not, I'm just trying to understand the reason on the behavior that was observed.

Basically, I'm trying to understand some strange behavior that occurs when you are changing from a larger text datatype to a smaller one and have values that are too large and would therefore be truncated.

When I reproduced it I used text to tinytext, but it was reported to me using longtext to text originally. I also didn't try it on any other data types.


I simply created a test table and test column with datatype of text.

I then inserted 2 rows of random text data into this column.
-The first row was a size of 303 (larger than tinytext can fit)
-The second row was a size of 200 (small enough for tinytext)

Next I did the following query, pretty basic:

---------------------------------
alter table DataTypeTruncation
modify column DataTypeTruncation tinytext;

('change column DataTypeTruncation DataTypeTruncation tinytext' results were the same)
---------------------------------

Okay, so now the results:
-First row was truncated, but not to a size of 255, a size of 48, which happens to be the difference between 303 and 255
-Second row was untouched

I did another test as well with a text field value with size of 575, which when converted to tinytext, truncated to a size of 63 (close to the difference between 510 (255 x 2) and 575

The text that was present however was still the text at the beginning, so it did chop off the end of it.

I'd really just like to understand why this happens the way it does, even if this is an extremely rare situation that I can't necessarily see any reason to do.