Vertica StringTokenizer Transform Function giving error when executing via ODBC connection

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

Comments

  • SruthiASruthiA Vertica Employee Administrator

    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 ?

  • SruthiASruthiA Vertica Employee Administrator

    Hi,

     

        What is the Locale set to in odbc.ini?

     

     

    Thank you,

     Sruthi

  • Hello ,

     

    It is set as Locale = en_US

     

    Thanks,

    Suhrid Ghosh

  • SruthiASruthiA Vertica Employee Administrator

    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.

  • SruthiASruthiA Vertica Employee Administrator

    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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file