db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4803) Sequences do not work in INSERT/SELECT
Date Wed, 22 Sep 2010 12:42:34 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4803?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12913521#action_12913521
] 

Rick Hillegas commented on DERBY-4803:
--------------------------------------

Tests passed cleanly for me. Committed derby-4803-01-aa-simpleInsertSelect.diff at subversion
revision 999908.

Hi DK,

Your script now passes. I had to edit your script because it was trying to insert integers
into varchar columns. Those problems must have been masked by this bug. Here is the modified
script which now runs:

connect 'jdbc:derby:memory:db;create=true';

CREATE SEQUENCE CART_TXN_ID_SEQ START WITH 1;
CREATE TABLE TABLE_1
(
  DOCNO VARCHAR(60) NOT NULL
, OTHER_DOCNO VARCHAR(60)
, CAN VARCHAR(7) NOT NULL
, ICD VARCHAR(6)
, FY VARCHAR(4)
, SGL_DEBIT_AMT DECIMAL(12, 2)
, FACTS_TP_CODE VARCHAR(10)
, AUTHORITY_DOCNO VARCHAR(60)
, ACTION_CODE VARCHAR(20)
, CART_TXN_ID INT
, CART_ID VARCHAR(50)
, LAST_UPDATED_BY_USER_ID VARCHAR(20)
, CREATED_DATE TIMESTAMP
, CONSTRAINT TABLE_1 PRIMARY KEY
  (
    CART_TXN_ID
  )
);

CREATE TABLE TABLE_2
(
  PROJECT_BUDGET CHAR(1)
, ACS_CAN CHAR(7)
, DIRECT_REIMB_FLAG CHAR(1)
, INSTITUTE CHAR(25)
, PROJECT_NBR CHAR(6)
, CURRENT_IND CHAR(1)
);

insert into TABLE_2(project_budget, acs_can, direct_reimb_flag, institute, project_nbr, current_ind)
values('P', '1234567', 'R', 'XYZ', '123456', 'C');

INSERT INTO
  TABLE_1 (
  cart_id,
  can,
  docno,
  fy,
  icd,
  other_docno,
  facts_tp_code,
  authority_docno,
  SGL_DEBIT_AMT,
  action_code,
  cart_txn_id,
  last_updated_by_user_id,
  created_date
) SELECT
  'Abc',
  '1234567',
  'Y21',
  '2010',
  TRIM(acs.institute),
  'Y3123456',
  '123',
  'Y3123456',
  100,
  'Action',
  NEXT VALUE FOR CART_TXN_ID_SEQ,
  'Qwerty',
  CURRENT_TIMESTAMP
FROM
  table_2 acs
WHERE
  acs.acs_can = '1234567';

select * from table_1;


> Sequences do not work in INSERT/SELECT
> --------------------------------------
>
>                 Key: DERBY-4803
>                 URL: https://issues.apache.org/jira/browse/DERBY-4803
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.6.1.0
>         Environment: Mac OS X, Derby Network Server 10.6.1.0
>            Reporter: DK
>            Assignee: Rick Hillegas
>             Fix For: 10.7.0.0
>
>         Attachments: derby-4803-01-aa-simpleInsertSelect.diff, DerbyTestCase.sql
>
>
> Using sequence in SELECT works fine whereas the same SELECT query used in INSERT/SELECT
results in "The statement references the following sequence more than once" error. This happens
even though the SELECT in question returns exactly 1 row of data.
> The Reference Manual states 1. " NEXT VALUE FOR expression may occur in the following
places: SELECT statement: As part of the expression defining a returned column in a SELECT
list" and 2. " NEXT VALUE expression may not appear in any of these situations: CASE expression,
WHERE clause, 
> ORDER BY clause, Aggregate expression, ROW_NUMBER function, DISTINCT select list".
> Nowhere a restriction on INSERT/SELECT is mentioned. Additionally, other databases (i.e.
Oracle) support use of sequences in INSERT/SELECT.
> Therefore, I consider it a bug.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message