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?
Strange error with Join Query
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Strange error with Join Query
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):
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
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Strange error with Join Query
We haven't done any node expansion or rebalancing of data.
now when i try to see the query explain plan
While when I use this query, it works completely fine
Please help me to find a perfect answer for this behavious by Vertica.
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=>
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
Re: Strange error with Join Query
Have you tried analyzing the tables before running the EXPLAIN?
Or...
Have you tried creating duplicate tables with the CTAS command? And then running the EXPLAIN on a query against them?
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.
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;
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;
Thank, Fred
-
- Intermediate
- Posts: 149
- Joined: Mon Apr 30, 2012 10:04 pm
- Location: New York
- Contact:
Re: Strange error with Join Query
What version of the database are you using?
Re: Strange error with Join Query
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.
The Vertica version is 5.1
Found this as raised bug, in JIRA.