Vertica StringTokenizer Transform Function giving error when executing via ODBC connection

Hi,

    Can you set the following option and retry the query

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

Thank you,

 Sruthi

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 ?

Hi,

    What is the Locale set to in odbc.ini?

Thank you,

 Sruthi

Hello ,

It is set as Locale = en_US

Thanks,

Suhrid Ghosh

Hi,

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

Sruthi

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

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.

Hi,

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

Sruthi

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

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