You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Selvaganesan Govindarajan (JIRA)" <ji...@apache.org> on 2016/05/01 22:58:12 UTC
[jira] [Created] (TRAFODION-1960) Invalid query plan produced for a
select query with specific set of CQDs
Selvaganesan Govindarajan created TRAFODION-1960:
----------------------------------------------------
Summary: Invalid query plan produced for a select query with specific set of CQDs
Key: TRAFODION-1960
URL: https://issues.apache.org/jira/browse/TRAFODION-1960
Project: Apache Trafodion
Issue Type: Bug
Components: sql-cmp
Affects Versions: 2.0-incubating
Environment: cqd MDAM_SCAN_METHOD 'OFF' ;
cqd QUERY_CACHE '0' ;
prepare s1 from
SELECT TRIM(object_name), TRIM(object_type)
FROM TRAFODION."_MD_".OBJECTS
WHERE catalog_name = 'TRAFODION' AND schema_name =
'SEABASE' AND object_name <> '__SCHEMA__' AND
object_type <> 'PK'
FOR READ COMMITTED ACCESS ;
explain s1 ;
Explain output
------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... S1
PLAN_ID .................. 212328895913864868
ROWS_OUT ................. 2
EST_TOTAL_COST ........... 0.01
STATEMENT ................ SELECT TRIM(object_name), TRIM(object_type)
FROM TRAFODION."_MD_".OBJECTS
WHERE catalog_name = 'TRAFODION' AND schema_name =
't4sch' AND object_name <> '__SCHEMA__' AND
object_type <> 'PK'
FOR READ COMMITTED ACCESS ;
------------------------------------------------------------------ NODE LISTING
ROOT ====================================== SEQ_NO 2 ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 2
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0.01
DESCRIPTION
max_card_est .......... 50
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
statement_index ........ 0
affinity_value ......... 0
max_max_cardinality .... 2
total_overflow_size .... 0.00 KB
xn_access_mode ......... read_only
xn_autoabort_interval 0
auto_query_retry ....... enabled
plan_version ....... 2,600
embedded_arkcmp ........ used
IS_SQLCI ............... ON
LDAP_USERNAME
MDAM_SCAN_METHOD ....... OFF
QUERY_CACHE ............ 0
select_list ............ (' ' trim TRAFODION."_MD_".OBJECTS.OBJECT_NAME),
(' ' trim TRAFODION."_MD_".OBJECTS.OBJECT_TYPE)
TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN
TABLE_NAME ............... OBJECTS
REQUESTS_IN .............. 1
ROWS_OUT ................. 2
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.01
DESCRIPTION
max_card_est .......... 50
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
scan_type .............. subset scan of table TRAFODION."_MD_".OBJECTS
object_type ............ Trafodion_MD
columns ................ all
begin_keys(incl) ....... TRAFODION,t4sch,,
end_keys(excl) ......... TRAFODION,t4sch,__SCHEMA__,
columns ................ all
begin_keys(incl) ....... TRAFODION,t4sch,,
end_keys(excl) ......... TRAFODION,t4sch,__SCHEMA__,
columns ................ all
begin_keys(excl) ....... TRAFODION,t4sch,__SCHEMA__,??
end_keys(incl) ......... TRAFODION,t4sch,?????????
????????????
????????????
????????????
????????????
???????,??
columns ................ all
begin_keys(excl) ....... TRAFODION,t4sch,__SCHEMA__,??
end_keys(incl) ......... TRAFODION,t4sch,?????????
????????????
????????????
????????????
????????????
???????,??
cache_size ........... 100
probes ................. 1
rows_accessed .......... 1
column_retrieved ....... #1:1,#1:2,#1:3,#1:4
key_columns ............ TRAFODION."_MD_".OBJECTS.CATALOG_NAME,
TRAFODION."_MD_".OBJECTS.SCHEMA_NAME,
TRAFODION."_MD_".OBJECTS.OBJECT_NAME,
TRAFODION."_MD_".OBJECTS.OBJECT_TYPE
executor_predicates .... (TRAFODION."_MD_".OBJECTS.CATALOG_NAME =
'TRAFODION') and (TRAFODION."_MD_".OBJECTS.SCHEMA_
NAME = 't4sch') and ((TRAFODION."_MD_".OBJECTS.OBJ
ECT_NAME < '__SCHEMA__') or
(TRAFODION."_MD_".OBJECTS.OBJECT_NAME >
'__SCHEMA__')) and ((TRAFODION."_MD_".OBJECTS.OBJE
CT_TYPE < 'PK') or (TRAFODION."_MD_".OBJECTS.OBJEC
T_TYPE > 'PK'))
--- SQL operation complete.
The produced plan has 4 scan ranges. These ranges seem to be overlapping and hence produces duplicate rows.
Reporter: Selvaganesan Govindarajan
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)