Vertica StringTokenizer Transform Function giving error when executing via ODBC connection

Suhrid_R__Ghosh · February 2016

Hello,

I have complied StringTokenizer C++ package and created library and transform function for StringTokenizer on Vertica 7.2. When executing from sql command line it works perfectly but when executed via ODBC connection the following below error throws up :

- [unixODBC]INTERNAL 3594: Internal Optimizer Error (11)
DETAIL: "PlanExprFixup: No mapping found" == 0
HINT: Please report this error to Vertica; try restating your query

Can anyone help me on this why its happening and how to trouble shoot this ?

Thanks,

Suhrid Ghosh

SruthiA · February 2016

Hi,

Can you set the following option and retry the query

select set_vertica_options('OPT', 'DISABLE_PREDICATE_PUSHDOWN');

Thank you,

Sruthi

Suhrid_R__Ghosh · February 2016

Thanks for the reply , as per your suggestion we added the below in etc/odbc.ini

ConnSettings=select+set_vertica_options('OPT','DISABLE_PREDICATE_PUSHDOWN')

Following were my observations :

1. If we use INNER JOIN it is giving the o/p but if i use LEFT OUTER JOIN it is ending up with the same above error.

2. Query execution time is 5 seconds via ODBC where as from the command prompt it is 1 sec.

Do we need to check anything further ?

SruthiA · February 2016

Hi,

What is the Locale set to in odbc.ini?

Thank you,

Sruthi

Suhrid_R__Ghosh · February 2016

Hello ,

It is set as Locale = en_US

Thanks,

Suhrid Ghosh

SruthiA · February 2016

Hi,

What is the vertica version you are using? Can you try running the query with /*+ syntactic_optimizer */ hint.

Sruthi

Suhrid_R__Ghosh · February 2016

Hello,

I have tried the below :

        create table DEMO(ID varchar(35), BALANCE_INFO varchar(1000));

        create table SUBS(SUB_KEY int,ID varchar(35));

        insert into DEMO values ('101','1*CORE
BALANCE*76.636300*0.000000*1*+*2*0~2*Free On-Net
SMS*5.000000*0.000000*4*+*2*0');

        insert into SUBS values(1,'101');

select
        /*+ syntactic_optimizer,label(ETL_DEMO_SELECT) */
        count(*)
from
(
                select ID,words BAL

                from
                (
                        select ID,
                        StringTokenizerDelim(BALANCE_INFO,'~') OVER
(partition by ID)

                        from DEMO
                ) a

) as e
LEFT OUTER JOIN
(select SUB_KEY,ID from SUBS) a
on e.ID = a.ID

Execution from sql command prompt : Working fine.
Execution from odbc connection         : Failing.

Fail error is the same as given in the above post.

Thanks,

Suhrid Ghosh

Suhrid_R__Ghosh · February 2016

Hello,

        I got the below traces in vertica.log for the below query :

2016-02-10 12:17:46.383 Init Session:0x7f054c013670 [Session] <INFO>
[PQuery] TX:0(tda3-26959:0x7d2ee) select /*+
syntactic_optimizer,label(ETL_DEMO_SELECT) */ count(*) from ( select
ID,words BAL from ( select ID, StringTokenizerDelim(BALANCE_INFO,'~') OVER
(partition by ID) from DHITMP.DEMO ) a ) as e LEFT OUTER JOIN (select
SUB_KEY,ID from DHITMP.SUBS) a on e.ID = a.ID
2016-02-10 12:17:46.386 Init Session:0x7f054c013670-c00000002e2a3f [Txn]
<INFO> Begin Txn: c00000002e2a3f 'select /*+
syntactic_optimizer,label(ETL_DEMO_SELECT) */ count(*) from ( select
ID,words BAL from ( select ID, StringTokenizerDelim(BALANCE_INFO,'~') OVER
(partition by ID) from DHITMP.DEMO ) a ) as e LEFT OUTER JOIN (select
SUB_KEY,ID from DHITMP.SUBS) a on e.ID = a.ID'
2016-02-10 12:17:46.387 Init Session:0x7f054c013670-c00000002e2a3f [EE]
<INFO> Trying to set up a new UDx side process
2016-02-10 12:17:46.395 Init Session:0x7f054c013670-c00000002e2a3f [Session]
<INFO> [BQuery] TX:c00000002e2a3f(tda3-26959:0x7d2ee) select /*+
syntactic_optimizer,label(ETL_DEMO_SELECT) */ count(*) from ( select
ID,words BAL from ( select ID, StringTokenizerDelim(BALANCE_INFO,'~') OVER
(partition by ID) from DHITMP.DEMO ) a ) as e LEFT OUTER JOIN (select
SUB_KEY,ID from DHITMP.SUBS) a on e.ID = a.ID
2016-02-10 12:17:46.399 Init Session:0x7f054c013670-c00000002e2a3f <LOG>
@v_tdadb_node0003: 00000/3316: Executing statement: 'select /*+
syntactic_optimizer,label(ETL_DEMO_SELECT) */ count(*) from ( select
ID,words BAL from ( select ID, StringTokenizerDelim(BALANCE_INFO,'~') OVER
(partition by ID) from DHITMP.DEMO ) a ) as e LEFT OUTER JOIN (select
SUB_KEY,ID from DHITMP.SUBS) a on e.ID = a.ID'
2016-02-10 12:17:46.418 Init Session:0x7f054c013670-c00000002e2a3f [Opt]
<WARNING> Vertica Internal Error 11 '"PlanExprFixup: No mapping found" == 0'
2016-02-10 12:17:46.418 Init Session:0x7f054c013670-c00000002e2a3f
<INTERNAL> @v_tdadb_node0003: VX001/3594: Internal Optimizer Error (11)
        DETAIL: "PlanExprFixup: No mapping found" == 0
        HINT: Please report this error to Vertica; try restating your query
        LOCATION: fixExprVars,
/scratch_a/release/30958/vbuild/vertica/Optimizer/Path/PlanExprFixup.cpp:239
2016-02-10 12:17:46.441 Init Session:0x7f054c013670-c00000002e2a3f
<INTERNAL> @v_tdadb_node0003: VX001/3594: Internal Optimizer Error (11)
        DETAIL: "PlanExprFixup: No mapping found" == 0
        HINT: Please report this error to Vertica; try restating your query
        LOCATION: fixExprVars,
/scratch_a/release/30958/vbuild/vertica/Optimizer/Path/PlanExprFixup.cpp:239
2016-02-10 12:17:46.441 Init Session:0x7f054c013670-c00000002e2a3f [Txn]
<INFO> Rollback Txn: c00000002e2a3f 'select /*+
syntactic_optimizer,label(ETL_DEMO_SELECT) */ count(*) from ( select
ID,words BAL from ( select ID, StringTokenizerDelim(BALANCE_INFO,'~') OVER
(partition by ID) from DHITMP.DEMO ) a ) as e LEFT OUTER JOIN (select
SUB_KEY,ID from DHITMP.SUBS) a on e.ID = a.ID'
2016-02-10 12:17:46.443 Init Session:0x7f054c013670 <LOG> @v_tdadb_node0003:
00000/4719: Session tda3-26959:0x7d2ee ended; closing connection (connCnt
12)

Hope this is helpful in troubleshooting.

Thanks.

SruthiA · February 2016

Hi,

What is the version of client driver you have installed?? Can you share me?? I will try to reproduce it in house

Sruthi

Suhrid_R__Ghosh · February 2016

Hello,

Vertica server version :

$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.4 (Santiago)

$ cat /proc/version
Linux version 2.6.32-358.el6.x86_64
(mockbuild@x86-022.build.eng.bos.redhat.com) (gcc version 4.4.7 20120313
(Red Hat 4.4.7-3) (GCC) ) #1 SMP Tue Jan 29 11:47:41 EST 2013

Client ODBC Version :

vertica-client-7.2.1-0.x86_64

Thanks,
Suhrid Ghosh

Suhrid_R__Ghosh · February 2016

Hi Sruthi,

Recently we have upgraded to vertica 7.2.1-3 with no changes with the
vertica-client version. Again facing the same error as below :

DETAIL: "PlanExprFixup: No mapping found" == 0
HINT: Please report this error to Vertica; try restating your query
(SQL-VX001)
15-02-2016 12:13:58|||
                select
                        /*+ syntactic_optimizer,label(ETL_DEMO_SELECT) */
                        count(*)
                from
                (
                                select ID,words BAL

                                from
                                (
                                        select ID,
                                        StringTokenizerDelim(BALANCE_INFO,'~')
OVER (partition by ID)

                                        from DHITMP.DEMO
                                ) a

                ) as e
                LEFT OUTER JOIN
                (select SUB_KEY,ID from DHITMP.SUBS) a
                on e.ID = a.ID

                 - [unixODBC]INTERNAL 3594: Internal Optimizer Error (11)
DETAIL: "PlanExprFixup: No mapping found" == 0
HINT: Please report this error to Vertica; try restating your query
(SQL-VX001)

We are trying to execute the queries with perl scripting.

Thanks,

Suhrid Ghosh

Vertica StringTokenizer Transform Function giving error when executing via ODBC connection

Comments

Leave a Comment