Casting a DEC(10,4) to INT - strange results

Database

Casting a DEC(10,4) to INT - strange results

Hello!

Consider these four queries:

SELECT *

FROM dbase.qtable

WHERE CAST(c_Order_ID AS INT) = 169

;

SELECT *

FROM dbase.qtable

WHERE TRUNC(c_Order_ID) = 169

;

SELECT *

FROM dbase.qtable

WHERE CAST(CAST(c_Order_ID AS INT) AS VARCHAR(15)) = 169

;

SELECT *

FROM dbase.qtable

WHERE CAST(TRIM(c_Order_ID) AS INT) = 169

;

The c_Order_ID field in the table is a DEC(10,4). Order ID has the order number to the left of the decimal and the lines making up the order to the right. In this instance, we have 10 rows for Order IDs 169.0000 through 169.0009.

The first query returns only one row, the row for Order ID 169.0000. The other three queries returns all 10 rows.

This is on Teradata v15.01 using Teradata.Net connectivity in SQLA. I also tested ODBC with the same results. I am just curious as to why CASTing to INT doesn't behave the same way as the other methods.

2 REPLIES
N/A

Re: Casting a DEC(10,4) to INT - strange results

Looks like an optimizer bug to me, Explain shows a single-AMP PI-access on 15.10.00.08, too.

Open an incident.

Re: Casting a DEC(10,4) to INT - strange results

Hi Dieter!

Thanks for the reply. I thought this may be a bug. I'll open an incident.

Andrew