[
https://issues.apache.org/jira/browse/TRAFODION-2988?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16399062#comment-16399062
]
David Wayne Birdsall commented on TRAFODION-2988:
-------------------------------------------------
I was able to reproduce the issue on the latest trafodion using the following
script:
?section setup
drop table if exists t1;
CREATE TABLE t1
(
C1 INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
SERIALIZED
, C2 INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
SERIALIZED
, C3 CHAR(1) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
, C4 NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
DROPPABLE NOT SERIALIZED
, C5 DATE NO DEFAULT NOT NULL NOT DROPPABLE NOT
SERIALIZED
, C6 CHAR(15) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
, C7 CHAR(15) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
, C8 INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
SERIALIZED
, C9 VARCHAR(79) CHARACTER SET ISO88591 COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
, PRIMARY KEY (C1 ASC)
)
SALT USING 8 PARTITIONS
ATTRIBUTES ALIGNED FORMAT
;
?section populateit
-- only the values for the first column really matter
upsert using load into t1
select c0 + 10*c1 + 100*c2 + 1000*c3 + 10000*c4 + 100000*c5 + 1000000*c6,
c0 + c1 + c2 + c3 + c4 + c5,
case when c1 < 5 then 'Y' else 'N' end,
c4 + 0.1*c3 + 10*c5 + 0.01*c2,
current_date,
'Hi There! ' || cast(c3 + 7*c4 + 13*c1 as char(4)),
'Boring data!',
-4,
cast(c3 + 4*c4 + 7*c5 + 11*c0 as varchar(6)) || 'x'
from (values(1)) t
transpose 0,1,2,3,4,5,6,7,8,9 as c0
transpose 0,1,2,3,4,5,6,7,8,9 as c1
transpose 0,1,2,3,4,5,6,7,8,9 as c2
transpose 0,1,2,3,4,5,6,7,8,9 as c3
transpose 0,1,2,3,4,5,6,7,8,9 as c4
transpose 0,1,2,3,4,5,6,7,8,9 as c5
transpose 0,1 as c6;
update statistics for table t1 on every column;
?section testit1
log testit1.log;
prepare XX from select * from t1 where c1 > 909000 or c1 < 1500;
explain XX;
exit;
?section testit2
log testit2.log;
cqd HBASE_HASH2_PARTITIONING 'OFF';
prepare XX from select * from t1 where c1 > 909000 or c1 < 1500;
explain XX;
exit;
When the table is created and populated (with two million rows in this
example), section testit1 gets a parallel MDAM plan, while section testit2 gets
an internal error. It appears that parallel + MDAM is essential to the case.
When I tried it with just one million rows, I got serial MDAM plans for both
sections.
> Prepare query returns 2006 internal error
> -----------------------------------------
>
> Key: TRAFODION-2988
> URL: https://issues.apache.org/jira/browse/TRAFODION-2988
> Project: Apache Trafodion
> Issue Type: Bug
> Reporter: chenyunren
> Assignee: chenyunren
> Priority: Major
>
> Prepare query returns a 2006 internal error at mdam.cpp:1611 when the CQD
> HBASE_HASH2_PARTITIONING is turned off.
> >>showddl t1;
> CREATE TABLE TRAFODION.T1
> (
> C1 INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
> SERIALIZED
> , C2 INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
> SERIALIZED
> , C3 CHAR(1) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
> , C4 NUMERIC(12, 2) NO DEFAULT NOT NULL NOT
> DROPPABLE NOT SERIALIZED
> , C5 DATE NO DEFAULT NOT NULL NOT DROPPABLE NOT
> SERIALIZED
> , C6 CHAR(15) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
> , C7 CHAR(15) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
> , C8 INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
> SERIALIZED
> , C9 VARCHAR(79) CHARACTER SET ISO88591 COLLATE
> DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE NOT SERIALIZED
> , PRIMARY KEY (C1 ASC)
> )
> SALT USING 8 PARTITIONS
> ATTRIBUTES ALIGNED FORMAT NAMESPACE 'TRAF_QATEST'
> ;
> – GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.T1 TO
> DB__ROOT WITH GRANT OPTION;
> — SQL operation complete.
> >>select count( * ) from t1;
> (EXPR)
> --------------------
> 3000000
> — 1 row(s) selected.
> >>prepare XX from select * from t1 where c1 > 909000 or c1 < 1500;
> — SQL command prepared.
> >>
> >>cqd HBASE_HASH2_PARTITIONING 'OFF';
> — SQL operation complete.
> >>prepare XX from select * from t1 where c1 > 909000 or c1 < 1500;
> ERROR[2006] Internal error: assertion failure (NOT commonPreds.isEmpty()) in
> file ../optimizer/mdam.cpp at line 1611.
> ERROR[8822] The statement was not prepared.
>
> Need update statistics first.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
