1
SEL * FROM TABLE WHERE a=10

VS

SEL * FROM TABLE WHERE a='10'

Here a is BIGINT, Explain plan does not show any difference, how teradata handles this and any difference in these query ?

Vivek Keshri
  • 108
  • 10

1 Answers1

1

Teradata automatically applies a datatype conversion if you compare different datatypes (usually but not always).

Whenever a string is compared to a number the string will be converted to a FLOAT, which is the most flexible numeric format.

In your case this conversion was already done by the parser, so the optimizer didn't know 10 was s string before.

If you do it the other way:

SEL * FROM TABLE WHERE a=10  -- column a is a character

you can spot this cast in explain:

"(table.last_name (FLOAT, FORMAT '-9.99999999999999E-999'))= 1.00000000000000E 001"

Sometimes this automatic conversion is convenient, but in a case like that it's really bad: No index can be used and all existing statistics are lost. So you better know you datatypes :-)

This (FLOAT, FORMAT '-9.99999999999999E-999')) in Explain is one of the first things I check if a query performs badly.

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • what is the big problem in the reverse case ? if its easily doing the conversion for CHAR when the column is BIGINT , why so many problems when column is CHAR and passing INT value – Vivek Keshri May 19 '15 at 13:57
  • In my case there is a PI in column a so this will be used right as you have indicated the optimizer didn't know 10 was s string before. – Vivek Keshri May 19 '15 at 13:59
  • @VivekKeshri: The reason is simple, the value `1` is represented by lots of different strings like `'1'`, `' 1'`, `'1e+0'`, `'1.0'`, `'+1'`, so the only save way is casting the string to numeric :-) – dnoeth May 19 '15 at 14:01