Strange error with Join Query

[nnani]

Hello All,

I am trying to run this query and Vertica gives me the following error

dbadmin=> SELECT b.* FROM DW.CHECK_IN_HIST a LEFT JOIN DW.DEVICE_SHIP_INFO b ON a.serialno=b.HTC_DEVICE_SR WHERE a.checkinid=1268547769290345341457;
INTERNAL: Internal Optimizer Error (11)
DETAIL: !isnan(_c.pathcompcard) && isinf(_c.pathcompcard) == 0 && _c.pathcompcard >= 0
HINT: Please report this error to Vertica; try restating your query

Can anybody let me know what is this error all about?

[JimKnicely]

Hi,

Did you add a node or re-balance the data recently?

On Vertica's web site there is a post about the Internal Optimizer Error (11):

Internal Optimizer Error (11) on queries since adding node and rebalancing

SOLUTION:
This is a known issue. Sessions that were opened prior to expanding the cluster retain stale node state for the cluster.

Recommended actions:

a) close all sessions started prior to cluster expansion, e.g., using the results of a query such as:

=> select session_id from sessions where login_timestamp < '2012-01-30 15:23:00';

b) close all active sessions

=> select close_all_sessions();

c) shut down the cluster and restart with admintools

[nnani]

We haven't done any node expansion or rebalancing of data.
now when i try to see the query explain plan

Code: Select all

dbadmin=> explain SELECT b.* FROM DW.CHECK_IN_HIST a LEFT JOIN DW.DEVICE_SHIP_INFO b ON a.serialno=b.HTC_DEVICE_SR WHERE a.checkInId=1251049636;
INTERNAL:  Internal Optimizer Error (11)
DETAIL:  !isnan(_c.pathcompcard) && isinf(_c.pathcompcard) == 0 && _c.pathcompcard >= 0
HINT:  Please report this error to Vertica; try restating your query
dbadmin=>

While when I use this query, it works completely fine

Code: Select all

SELECT b.* FROM DW.CHECK_IN_HIST a LEFT JOIN DW.DEVICE_SHIP_INFO b ON a.serialno=b.HTC_DEVICE_SR WHERE a.time=1251049636;

dbadmin=> explain SELECT b.* FROM DW.CHECK_IN_HIST a LEFT JOIN DW.DEVICE_SHIP_INFO b ON a.serialno=b.HTC_DEVICE_SR WHERE a.time=1251049636;
                                                                                                                                                                                        QUERY PLAN                                                                                                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 explain SELECT b.* FROM DW.CHECK_IN_HIST a LEFT JOIN DW.DEVICE_SHIP_INFO b ON a.serialno=b.HTC_DEVICE_SR WHERE a.time=1251049636;

 Access Path:
 +-JOIN MERGEJOIN(inputs presorted) [LeftOuter] [Cost: 22M, Rows: 76M] (PATH ID: 1)
 |  Join Cond: (a.serialno = b.Htc_Device_Sr)
 |  Execute on: All Nodes
 | +-- Outer -> STORAGE ACCESS for a [Cost: 22M, Rows: 76M] (PATH ID: 2)
 | |      Projection: DW.Check_In_Hist_DBD_9_seg_T04_T04
 | |      Materialize: a.serialno
 | |      Filter: (a."time" = 1251049636)
 | |      Execute on: All Nodes
 | +-- Inner -> STORAGE ACCESS for b [Cost: 642K, Rows: 100M] (PATH ID: 3)
 | |      Projection: DW.Device_Ship_Info_DBD_8_seg_T03_T03
 | |      Materialize: b.Htc_Device_Sr, b.Item_Cd, b.Ship_Date, b.Ship_Notice, b.Cntry_Cd, b.Update_User, b.Update_Dt
 | |      Execute on: All Nodes


 ------------------------------
 -----------------------------------------------
 PLAN: BASE QUERY PLAN (GraphViz Format)
 -----------------------------------------------
 digraph G {
 graph [rankdir=BT, label = "BASE QUERY PLAN\nQuery: explain SELECT b.* FROM DW.CHECK_IN_HIST a LEFT JOIN DW.DEVICE_SHIP_INFO b ON a.serialno=b.HTC_DEVICE_SR WHERE a.time=1251049636;\n\nAll Nodes Vector: \n\n  node[0]=v_htcpoc_node0001 (initiator) Up\n  node[1]=v_htcpoc_node0002 (executor) Up\n  node[2]=v_htcpoc_node0003 (executor) Up\n", labelloc=t, labeljust=l ordering=out]
 0[label = "Root \nOutBlk=[UncTuple(7)]", color = "green", shape = "house"];
 1[label = "NewEENode \nOutBlk=[UncTuple(7)]", color = "green", shape = "box"];
 2[label = "ExprEval: \n  b.Htc_Device_Sr\n  b.Item_Cd\n  b.Ship_Date\n  b.Ship_Notice\n  b.Cntry_Cd\n  b.Update_User\n  b.Update_Dt\nUnc: Varchar(20)\nUnc: Varchar(20)\nUnc: Timestamp(8)\nUnc: Varchar(20)\nUnc: Varchar(5)\nUnc: Char(3)\nUnc: Timestamp(8)", color = "green", shape = "box"];
 3[label = "Recv\nRecv from: v_htcpoc_node0001,v_htcpoc_node0002,v_htcpoc_node0003\nNet id: 1000\n\nUnc: Varchar(15)\nUnc: Varchar(20)\nUnc: Varchar(20)\nUnc: Timestamp(8)\nUnc: Varchar(20)\nUnc: Varchar(5)\nUnc: Char(3)\nUnc: Timestamp(8)", color = "green", shape = "box"];
 4[label = "Send\nSend to: v_htcpoc_node0001\nNet id: 1000\n\nUnc: Varchar(15)\nUnc: Varchar(20)\nUnc: Varchar(20)\nUnc: Timestamp(8)\nUnc: Varchar(20)\nUnc: Varchar(5)\nUnc: Char(3)\nUnc: Timestamp(8)", color = "green", shape = "box"];
 5[label = "StorageUnionStep: Check_In_Hist_DBD_9_seg_T04_T04\nUnc: Varchar(15)\nUnc: Varchar(20)\nUnc: Varchar(20)\nUnc: Timestamp(8)\nUnc: Varchar(20)\nUnc: Varchar(5)\nUnc: Char(3)\nUnc: Timestamp(8)", color = "purple", shape = "box"];
 6[label = "Join: Merge-Join: \n(DW.Check_In_Hist x DW.Device_Ship_Info) using Check_In_Hist_DBD_9_seg_T04_T04 and Device_Ship_Info_DBD_8_seg_T03_T03 (PATH ID: 1)\n[LeftOuter]\n\nUnc: Varchar(15)\nUnc: Varchar(20)\nUnc: Varchar(20)\nUnc: Timestamp(8)\nUnc: Varchar(20)\nUnc: Varchar(5)\nUnc: Char(3)\nUnc: Timestamp(8)", color = "brown", shape = "box"];
 7[label = "ScanStep: Check_In_Hist_DBD_9_seg_T04_T04\n(a.\"time\" = 1251049636)\nserialno\ntime RLE (not emitted)\nUnc: Varchar(15)", color = "brown", shape = "box"];
 8[label = "StorageMergeStep: Device_Ship_Info_DBD_8_seg_T03_T03; 1 sorted\nUnc: Varchar(20)\nUnc: Varchar(20)\nUnc: Timestamp(8)\nUnc: Varchar(20)\nUnc: Varchar(5)\nUnc: Char(3)\nUnc: Timestamp(8)", color = "purple", shape = "box"];
 9[label = "ScanStep: Device_Ship_Info_DBD_8_seg_T03_T03\nHtc_Device_Sr\nItem_Cd\nShip_Date\nShip_Notice\nCntry_Cd\nUpdate_User\nUpdate_Dt\nUnc: Varchar(20)\nUnc: Varchar(20)\nUnc: Timestamp(8)\nUnc: Varchar(20)\nUnc: Varchar(5)\nUnc: Char(3)\nUnc: Timestamp(8)", color = "brown", shape = "box"];
 1->0 [label = "V[0] C=7",color = "black",style="bold", arrowtail="inv"];
 2->1 [label = "0",color = "blue"];
 3->2 [label = "0",color = "blue"];
 4->3 [label = "0",color = "blue"];
 5->4 [label = "0",color = "blue"];
 6->5 [label = "0",color = "blue"];
 7->6 [label = "0",color = "blue"];
 8->6 [label = "1",color = "blue"];
 9->8 [label = "0",color = "blue"];
 }
(47 rows)

Time: First fetch (47 rows): 28.541 ms. All rows formatted: 28.779 ms

Please help me to find a perfect answer for this behavious by Vertica.

[fsalvelt]

Have you tried analyzing the tables before running the EXPLAIN?

Code: Select all

SELECT ANALYZE_STATISTICS('CHECK_IN_HIST');
SELECT ANALYZE_STATISTICS('DEVICE_SHIP_INFO');

explain SELECT b.* FROM DW.CHECK_IN_HIST a LEFT JOIN DW.DEVICE_SHIP_INFO b ON a.serialno=b.HTC_DEVICE_SR WHERE a.time=1251049636;

Or...

Have you tried creating duplicate tables with the CTAS command? And then running the EXPLAIN on a query against them?

Code: Select all

CREATE TABLE CHECK_IN_HIST2 AS SELECT * FROM CHECK_IN_HIST;
CREATE TABLE DEVICE_SHIP_INFO2 AS SELECT * FROM DEVICE_SHIP_INFO;

explain SELECT b.* FROM DW.CHECK_IN_HIST2 a LEFT JOIN DW.DEVICE_SHIP_INFO2 b ON a.serialno=b.HTC_DEVICE_SR WHERE a.time=1251049636;

I wanted to let you know some of the things I'd try Good luck! You may need Vertica support on this one. They're really good.

[jpcavanaugh]

What version of the database are you using?

[nnani]

I have tried the ANALYZE STATISTICS on this table, but no effect.
The Vertica version is 5.1

Found this as raised bug, in JIRA.