Implicit conversion of Integer to VARCHAR

Database

Implicit conversion of Integer to VARCHAR

Hello,

I have the follwing table:

CREATE MULTISET TABLE test ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ORDER_KEY VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC,
      ORDER_NAME VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( ORDER_KEY );

When I pass an interger value to the VARCHAR column filter, I can see implicit conversion happening in the explain plan:

Explain select * from test where order_key = 123

  1) First, we lock a distinct pd."pseudo table" for read on a

     RowHash to prevent global deadlock for pd.test.

  2) Next, we lock pd.test for read.

  3) We do an all-AMPs RETRIEVE step from pd.test by way of an

     all-rows scan with a condition of ("(pd.test.ORDER_KEY

     (FLOAT, FORMAT '-9.99999999999999E-999'))= 1.23000000000000E 002")

     into Spool 1 (group_amps), which is built locally on the AMPs.

     The size of Spool 1 is estimated with no confidence to be 74 rows

     (14,430 bytes).  The estimated time for this step is 0.02 seconds.

  4) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> The contents of Spool 1 are sent back to the user as the result of

     statement 1.  The total estimated time is 0.02 seconds.

 However when I try to execute the query, I get the error 2621: Bad character in format or data in order_key

Why implicit conversion is not happening as per explain plan?

Tags (1)
3 REPLIES
N/A

Re: Implicit conversion of Integer to VARCHAR

But as the explain states it will try to do the coversion for all values of order_key in the table.

The message you get is indicating that at least one value of order_key can be converted to a float value.

order_key = '123'


Should work.

You should always try to avoid these inplicit conversions...

Re: Implicit conversion of Integer to VARCHAR

Hi Ulrich,

In context to the above explain plan, i can see both ALL -amps retreival and a group amp operation in it.

Would it be considered a group amp or all amp operation?

Just asking this as i am preparing for teo-123(physical implementation) exam and i had this doubt in mind..

appreciate your quick reply.

cheers!

Nishant

N/A

Re: Implicit conversion of Integer to VARCHAR

Hi Nishant,

"group_amps" during an all-AMPs step usually means the optimizer assumes that less than 50% of the AMPs in the system will actually do some work, but the actual AMPs are not known before the query runs. E.g. the optimizer remembers which AMPs put some rows in spool and only those receive the next step.

Dieter