Expression in an Update or Select Query not working

Help Procedural SQL
1

I maybe missing somethin simple to explain what is going on. I am trying to get another problem solved and I encountered the following issue that is very different from other DB’s I use.

I have the following query:

1. Update un_hi_device_loc Set un_hi_rec_id = my_ts_unix

This results in the un_hi_rec_id field to be set correctly with the my_ts_unix value from the associated row

2. Update un_hi_device_loc Set un_hi_rec_id = my_ts_unix / 10

This results in a 0 set in the column un_hi_rec_id for all rows the table

What is happening whenever I add, subtract, divide, or multiply the my_ts_unix field value? It does not do the expected mathematical calculation. The my_ts_unix is a LONG type. The same results happen if I run the same tests with a SELECT statement

2

Good day Manoj!

From my understanding, math operations should work fine in SingleStore.

Is the other datatype for un_hi_rec_id an INT?

Please remember to make sure that the values are within the range of the datatype because an INT value only goes up to of 2,147,483,647

Here are the numerical datatypes and some more info from our docs.

Keep us posted on your tests. Good luck! :four_leaf_clover:

3

This issue is tied to me using a Pipeline with a UNICODE CSV file, instead of an ASCII file. Your doc state UNICODE in many places so I assumed the CSV should be UNICODE. That only works for TEXT type and not any other types. The issue of this thread was related to the CSV coming in as UNICODE. So the issue is resolved.

1 Like
4

Glad to hear it! Yay! :partying_face: Thanks so much for the update, Manoj! You rock :guitar: