You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Alice Chen (JIRA)" <ji...@apache.org> on 2015/07/22 20:15:23 UTC
[jira] [Created] (TRAFODION-257) LP Bug: 1317990 - Got error 20123
for DDL cannot be performed from create view, but view was actually
created.
Alice Chen created TRAFODION-257:
------------------------------------
Summary: LP Bug: 1317990 - Got error 20123 for DDL cannot be performed from create view, but view was actually created.
Key: TRAFODION-257
URL: https://issues.apache.org/jira/browse/TRAFODION-257
Project: Apache Trafodion
Issue Type: Bug
Reporter: Apache Trafodion
Assignee: Anoop Sharma
1) create view got error 20123 within begin work
2) get views showed no view
3) but view was accessible thru select and showddl even after rollback.
SQL>create view debug_string02.emp_view
(first_name, last_name, dept_num,
salary, marital_status, hire_date, start_time) as
(select first_name, last_name,
isnull((select p3 from optable
where p3 = -12390), 333),
salary,
13,
isnull(cast(null as date), dateadd(month, 13, hire_date)),
date_trunc('year', hire_date)
from debug_string02.emp);
*** ERROR[20123] A user-defined transaction has been started. This DDL operation cannot be performed. [2014-05-09 16:36:49]
SQL>set schema debug_string02;
--- SQL operation complete.
SQL>get views;
--- SQL operation complete.
SQL>--#expectfile ${test_dir}/a02exp a02s16
SQL>select * from debug_string02.emp_view;
FIRST_NAME LAST_NAME DEPT_NUM SALARY MARITAL_STATUS HIRE_DATE START_TIME
---------- ---------- -------- ---------- -------------- ---------- --------------------------
CLARK DINAH -12390 37000.50 13 1978-06-02 1977-01-01 00:00:00.000000
CRINAR JESSICA -12390 39500.00 13 1985-09-12 1984-01-01 00:00:00.000000
FName A LName A -12390 NULL 13 NULL NULL
FName A LName A -12390 NULL 13 NULL NULL
FName B LName B -12390 0.00 13 NULL NULL
First Name Last NameL -12390 NULL 13 1909-10-23 1908-01-01 00:00:00.000000
GREEN ROGER -12390 175500.99 13 1909-10-23 1908-01-01 00:00:00.000000
HOWARD JERRY -12390 65000.64 13 1924-12-05 1923-01-01 00:00:00.000000
a123456789 b987654321 -12390 NULL 13 1909-10-23 1908-01-01 00:00:00.000000
hans christian -12390 99000.04 13 1909-10-23 1908-01-01 00:00:00.000000
jerry white -12390 70000.00 13 1924-12-05 1923-01-01 00:00:00.000000
--- 11 row(s) selected.
SQL>showddl emp_view;
CREATE VIEW TRAFODION.DEBUG_STRING02.EMP_VIEW (FIRST_NAME, LAST_NAME, DEPT_NUM,
SALARY, MARITAL_STATUS, HIRE_DATE, START_TIME) AS
(SELECT TRAFODION.DEBUG_STRING02.EMP.FIRST_NAME,
TRAFODION.DEBUG_STRING02.EMP.LAST_NAME, ISNULL ((SELECT
TRAFODION.DEBUG_STRING01.OPTABLE.P3 FROM TRAFODION.DEBUG_STRING01.OPTABLE
WHERE TRAFODION.DEBUG_STRING01.OPTABLE.P3 = -12390), 333),
TRAFODION.DEBUG_STRING02.EMP.SALARY, 13, ISNULL (CAST(NULL AS DATE),
DATEADD (MONTH, 13, TRAFODION.DEBUG_STRING02.EMP.HIRE_DATE)), DATE_TRUNC
('year', TRAFODION.DEBUG_STRING02.EMP.HIRE_DATE) FROM
TRAFODION.DEBUG_STRING02.EMP) ;
--- SQL operation complete.
SQL>--#expect any *--- SQL operation complete.*
SQL> rollback;
--- SQL operation complete.
SQL>set schema debug_string02;
--- SQL operation complete.
SQL>get views;
--- SQL operation complete.
SQL>select * from debug_string02.emp_view;
FIRST_NAME LAST_NAME DEPT_NUM SALARY MARITAL_STATUS HIRE_DATE START_TIME
---------- ---------- -------- ---------- -------------- ---------- --------------------------
CLARK DINAH -12390 37000.50 13 1978-06-02 1977-01-01 00:00:00.000000
CRINAR JESSICA -12390 39500.00 13 1985-09-12 1984-01-01 00:00:00.000000
FName A LName A -12390 NULL 13 NULL NULL
FName A LName A -12390 NULL 13 NULL NULL
FName B LName B -12390 0.00 13 NULL NULL
First Name Last NameL -12390 NULL 13 1909-10-23 1908-01-01 00:00:00.000000
GREEN ROGER -12390 175500.99 13 1909-10-23 1908-01-01 00:00:00.000000
HOWARD JERRY -12390 65000.64 13 1924-12-05 1923-01-01 00:00:00.000000
a123456789 b987654321 -12390 NULL 13 1909-10-23 1908-01-01 00:00:00.000000
hans christian -12390 99000.04 13 1909-10-23 1908-01-01 00:00:00.000000
jerry white -12390 70000.00 13 1924-12-05 1923-01-01 00:00:00.000000
--- 11 row(s) selected.
SQL>showddl emp_view;
CREATE VIEW TRAFODION.DEBUG_STRING02.EMP_VIEW (FIRST_NAME, LAST_NAME, DEPT_NUM,
SALARY, MARITAL_STATUS, HIRE_DATE, START_TIME) AS
(SELECT TRAFODION.DEBUG_STRING02.EMP.FIRST_NAME,
TRAFODION.DEBUG_STRING02.EMP.LAST_NAME, ISNULL ((SELECT
TRAFODION.DEBUG_STRING01.OPTABLE.P3 FROM TRAFODION.DEBUG_STRING01.OPTABLE
WHERE TRAFODION.DEBUG_STRING01.OPTABLE.P3 = -12390), 333),
TRAFODION.DEBUG_STRING02.EMP.SALARY, 13, ISNULL (CAST(NULL AS DATE),
DATEADD (MONTH, 13, TRAFODION.DEBUG_STRING02.EMP.HIRE_DATE)), DATE_TRUNC
('year', TRAFODION.DEBUG_STRING02.EMP.HIRE_DATE) FROM
TRAFODION.DEBUG_STRING02.EMP) ;
--- SQL operation complete.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)