Can Decimal and Integer be a mismatch for Join?

Database
Enthusiast

Can Decimal and Integer be a mismatch for Join?

Hello All,

I have few questions,

  1. Can Decimal(12,0) and integer be a mismatch for join?
  2. Can Decimal(10,0) and Decimal(20,0) be a mismatch for join?
  3. Considering char and varchar columns have only used 10 bytes of data, char(20) and varchar(20) be a mismatch?
  4. Does join depends on size or only values present no matter how they are stored?

Answer would be probably No for first three questions, but i just want to know how these are treated internally?If they wont be mismatch, will they add overhead for the teradata while joining?

6 REPLIES
Enthusiast

Re: Can Decimal and Integer be a mismatch for Join?

Hi,

1. Can Decimal(12,0) and integer be a mismatch for join?

--> Answer would be No. There won't be mismatch of data values. Since 10.00 is same as 10

2. Can Decimal(10,0) and Decimal(20,0) be a mismatch for join?

--> Answer would be No. There won't be mismatch of data values. Since 20.00 is same as 10.00

3. Considering char and varchar columns have only used 10 bytes of data, char(20) and varchar(20) be a mismatch?

--> Answer would be Yes. The records won't match beacuse the CHAR datatype allows to fill the extra bytes of data with Spaces.

So when we insert the data as 'ABC' in CHAR(20) and in VARCHAR(20). The values would be stored like this,

'ABC' --> CHAR(20)    --> 'ABC                 '

'ABC' --> VARCHAR(20) --> 'ABC'

For the 1st three queries, the Parsing Engine(PE) will do an implicit conversion of the Datatypes. You can check the explain Plan. If the Datatype is different, the PE will convert the data. So it is a headache to do for the PE. So, we should do the conversion.

4. Does join depends on size or only values present no matter how they are stored?

--> Join basically depends on the Index of the Join Tables and size matters when you do an left Outer join. If it is a Left outer join, the master table must be the Big table otherwise unnecessarily the data will be distributed across Amps for the Smaller Tables down the line. So basically if you join with the same Datatype it is not a problem.

Thanks & Regards,

Adharssh HS Rao.

Senior Apprentice

Re: Can Decimal and Integer be a mismatch for Join?

Hi Dominiq,

1: If the decimal has no fractional digits (i.e. DEC(n,0) it's the same as an INT.

DEC(10,0) = DEC(20,0) = INT = BIGINT

2: If the number of fractional digits is the same both decimals are considered equal.

DEC(10,2)  = DEC(20,2)

DEC(10,2) <> DEC(10,3), 1.00 <> 1.000

3: CHAR and VARCHAR are equal as trailing blanks are ignored for comparison (only LIKE considers trailing blank)

'bla' = 'bla      ', ' bla' <> 'bla ')

4: Depends, see above. 

Datatypes considered equal hash to the same Hash value and could be joined directly (if they are PIs). Otherwise there will be preparation step, "redistributed by hash code" in Explain.

Enthusiast

Re: Can Decimal and Integer be a mismatch for Join?

Hi Dieter,

Till Now i was in the perception that if we define the Character data type, then we should use TRIM to avoid the trailing Spaces.

When we use CHAR_LENGTH function, it returns the total length of the Column. So when i define it as CHAR(10). It will give me 10.

Can you explain why the Comparison operator works for 'bla' = 'bla ' and why the like operator won't work for this ' bla' <> 'bla '

Thanks & Regards,

Adharssh.

Enthusiast

Re: Can Decimal and Integer be a mismatch for Join?

Thanks Dieter and Adharssh..

Dieter's point #3 would surely help.

Adharssh, space is ignored for trailing spaces, not for leading. it would be same for like as well. I guess, it works same in all kinds of pattern matching that leading space is considered while trailing is not.

Senior Apprentice

Re: Can Decimal and Integer be a mismatch for Join?

According to Standard SQL the shorter string is padded with trailing blanks to match the length of the longer string, which is similar to "ignore trailing blanks".

But this is only done for comparisons with =,<,>,<>, LIKE will respect blanks, 'bla' LIKE 'bla ' results in FALSE. 

Enthusiast

Re: Can Decimal and Integer be a mismatch for Join?

Thanks Dieter for the Explanation and thanks Dominiq. Yes Trailing Spaces will be ignored.

Thanks,

Adharssh.