Hi there

ASA 9.0.2.2451

I am issuing this statemnt from a ColdFusion app:


---
begin atomic
declare @ttid integer;
insert into TEMPLATE (KUNDE_ID,
NAME)
values ((select KUNDE_ID
from KUNDEN
where SESSION_KEY = '#cookie.custid#'),
'#form.new_template#'
);
select @@identity into @ttid;
insert into CONTAINER
with auto name
select @ttid as TEMPLATE_ID,
"PARENT_CONTAINER_ID",
"LANGUAGE_CODE",
"VORDERGRUND_BILD_ID",
"HINTERGRUND_BILD_ID",
"NAME",
"CONTAINERTYP",
"INHALTSTYP",
"TEXTINHALT",
"POSITION",
"FLOAT",
"CLEAR",
"ZINDEX",
"TOP",
"LEFT",
"WIDTH",
"HEIGHT",
"FONTFAMILY",
"FONTSIZE",
"FONTSTYLE",
"FONTWEIGHT",
"TEXTALIGN",
"COLOR",
"BACKGROUNDCOLOR",
"BACKGROUNDATTACHMENT",
"BACKGROUNDPOSITION",
"BACKGROUNDREPEAT",
"LINEHEIGHT",
"MARGINLEFT",
"MARGINTOP",
"MARGINRIGHT",
"MARGINBOTTOM",
"BORDERLEFT",
"BORDERTOP",
"BORDERRIGHT",
"BORDERBOTTOM",
"PADDINGLEFT",
"PADDINGTOP",
"PADDINGRIGHT",
"PADDINGBOTTOM",
"CREATION_DATE",
"LAST_UPDATE",
"NEEDS_PARSING_"
from CONTAINER
where TEMPLATE_ID = (select TEMPLATE_ID
from TEMPLATE
where HASH = '#copy_template#'
and IS_MASTER_ = 1);
end;
---

Now, this statement fails. I have not yet figured why. There is a Unique Index on table TEMPLATE (KUNDE_ID, NAME).

---
CREATE UNIQUE INDEX "NAME_UIDX" ON "DBA"."TEMPLATE" ( "KUNDE_ID" ASC, "NAME" ASC ) IN "SYSTEM";
---

The statment block generates the following error:

---
[ODBC Socket][Sybase][ODBC Driver][Adaptive Server Anywhere]Index 'NAME_UIDX' for table 'TEMPLATE' would not be unique
---

I expected this batch statement to work - regarding the data involved.

This is not what bother me (casue I will find out what is going wrong there. The statement is copying one template from one customer (KUNDE_ID) to another.

What really bothers me is WHY do I SEE an inserted row in TEMPLATE in Central but no records in the depending table CONTAINER - DESPITE that the entire block is in BEGIN ATOMIC ... END;

How can it come that I get the above exception AND STILL FIND the first part of the batch DONE?

I expect the entire thing to be done or nothing - this is what BEGIN ATOMIC means.

What am I possibly overlooking here?

Thanks.

Martin

Are you *sure* that row wasn't there *before* you ran the batch? If it
was, it might explain both symptoms.

Is it possible the batch is being run twice?

Breck Just Guessing

On 21 Nov 2004 20:21:30 -0800, Martin Baur <tinu (AT) mindpower (DOT) com> wrote:

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/AS.../risingroad-20
bcarter (AT) risingroad (DOT) com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Hi Guessing Breck

I of course also suspected a double run there .... however, there are 2 reasons against this:

1. I canot imagine how this should happen from the application logic (weak reason)
2. IF there had be ONE successful run, THEN there should be records in CONTAINER. I checked the select part of the insert for delivering rows. (strong reason)

My tables involved here are:

CREATE TABLE "DBA"."TEMPLATE" (
"TEMPLATE_ID" integer NOT NULL DEFAULT autoincrement,
"KUNDE_ID" integer NOT NULL,
"NAME" varchar(80) NOT NULL,
"IS_MASTER_" smallint NOT NULL DEFAULT 0,
"HASH" varchar(40) NULL COMPUTE( hash(cast(CREATION_DATE as varchar)+cast(KUNDE_ID as varchar),'MD5') ),
"CREATION_DATE" timestamp NULL DEFAULT current timestamp,
"LAST_UPDATE" timestamp NULL DEFAULT timestamp,
CONSTRAINT "PK_TEMPLATE" PRIMARY KEY ( "TEMPLATE_ID" )
);

with those (interesting) indizes:

CREATE UNIQUE INDEX "NAME_UIDX" ON "DBA"."TEMPLATE" ( "KUNDE_ID" ASC, "NAME" ASC ) IN "SYSTEM";
CREATE UNIQUE INDEX "HASH_UIDX" ON "DBA"."TEMPLATE" ( "HASH" ASC ) IN "SYSTEM";


and

CREATE TABLE "DBA"."CONTAINER" (
"CONTAINER_ID" integer NOT NULL DEFAULT autoincrement,
"TEMPLATE_ID" integer NOT NULL,
"PARENT_CONTAINER_ID" integer NULL,
"LANGUAGE_CODE" varchar(3) NOT NULL,
"VORDERGRUND_BILD_ID" integer NULL,
"HINTERGRUND_BILD_ID" integer NULL,
"NAME" varchar(80) NOT NULL,
"CONTAINERTYP" varchar(20) NOT NULL DEFAULT 'div',
"INHALTSTYP" varchar(2) NOT NULL DEFAULT 'T',
"TEXTINHALT" long varchar NULL,
"POSITION" varchar(10) NOT NULL DEFAULT 'absolute',
"FLOAT" varchar(10) NULL DEFAULT 'left',
"CLEAR" varchar(10) NULL DEFAULT 'none',
"ZINDEX" varchar(10) NOT NULL DEFAULT '0',
"TOP" varchar(10) NOT NULL DEFAULT '0',
"LEFT" varchar(10) NOT NULL DEFAULT '0',
"WIDTH" varchar(10) NULL DEFAULT 'auto',
"HEIGHT" varchar(10) NULL DEFAULT 'auto',
"FONTFAMILY" varchar(40) NULL,
"FONTSIZE" varchar(20) NULL,
"FONTSTYLE" varchar(20) NULL DEFAULT 'normal',
"FONTWEIGHT" varchar(20) NULL,
"TEXTALIGN" varchar(20) NULL,
"COLOR" varchar(20) NULL DEFAULT '#000000',
"BACKGROUNDCOLOR" varchar(20) NULL DEFAULT '#ffffff',
"BACKGROUNDATTACHMENT" varchar(20) NULL DEFAULT 'scroll',
"BACKGROUNDPOSITION" varchar(20) NULL DEFAULT '0% 0%',
"BACKGROUNDREPEAT" varchar(20) NULL DEFAULT 'repeat',
"LINEHEIGHT" varchar(20) NULL,
"MARGINLEFT" varchar(20) NULL,
"MARGINTOP" varchar(20) NULL,
"MARGINRIGHT" varchar(20) NULL,
"MARGINBOTTOM" varchar(20) NULL,
"BORDERLEFT" varchar(20) NULL,
"BORDERTOP" varchar(20) NULL,
"BORDERRIGHT" varchar(20) NULL,
"BORDERBOTTOM" varchar(20) NULL,
"PADDINGLEFT" varchar(20) NULL,
"PADDINGTOP" varchar(20) NULL,
"PADDINGRIGHT" varchar(20) NULL,
"PADDINGBOTTOM" varchar(20) NULL,
"CREATION_DATE" timestamp NULL DEFAULT current timestamp,
"LAST_UPDATE" timestamp NULL DEFAULT timestamp,
"NEEDS_PARSING_" smallint NOT NULL DEFAULT 0,
CONSTRAINT "PK_CONTAINER" PRIMARY KEY ( "CONTAINER_ID" )
);

with that index

CREATE UNIQUE INDEX "NAME_UIDX" ON "DBA"."CONTAINER" ( "TEMPLATE_ID" ASC, "NAME" ASC ) IN "SYSTEM";


There are no triggers or other contraints on both tables.


I report this because 1. I have no idea what is wrong there and 2. because I did report a bug regarding the COMPUTE clause in DDL of TEMPLATE which was fixed then in 9.0.1.1943

Maybe this problem is somehow related to that.

Regards

Desperat Martin

The following kind of statement is helpful when diagnosing
problems with the order of things happening:

MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, ' other stuff'
) TO CONSOLE;

Run the engine with -o filespec.txt -os 1M

Request level logging can also help, but it won't show
what's going on *inside* batches or procedures or triggers.
There is always the profiler, too.

And tech support is just a phone call away

Breck

Hi Breck

I figured the problem ... maybe this should be given to ASA tech ...

In the INSERT .... SELECT I extracted the select and checked it again

---
select @ttid as TEMPLATE_ID,
"PARENT_CONTAINER_ID",
...
from CONTAINER
where TEMPLATE_ID = (select TEMPLATE_ID
from TEMPLATE
where HASH = '3dd49c867681b230194c1f959c7cfbfc'
and IS_MASTER_ = 1);
---

This does not deliver any rows. Omitting this

---
select -- the constant omitted
"PARENT_CONTAINER_ID",
...
from CONTAINER
where TEMPLATE_ID = (select TEMPLATE_ID
from TEMPLATE
where HASH = '3dd49c867681b230194c1f959c7cfbfc'
and IS_MASTER_ = 1);
---

does deliver the rows I expect.


So, the problem was that I wanted to include the previously inserted template_id stored in the variable @ttid.

The example finds 14 as result of the subselect. The value in @ttid is 32.

I assume that the optimizer figures the fixed nature of @ttid and eliminates the entire where clause and concludes that @ttid <> subselect, so no rows to insert.

Isn't this a wrong behavior?

How is one supposed to INSERT ... SELECT if one has to deliver a constant value for a field in the select list which is also a criteria in the where clause?

CAn I hint the optimizer to not trying to optimize this satement?

Regrads

Martin

I think you have two different objects called TEMPLATE_ID:

"CONTAINER.TEMPLATE_ID" and "@tid as TEMPLATE_ID"

.... which one do you want to be used in "where TEMPLATE_ID
="?

Perhaps you should be explicit in the WHERE clause, or drop
the "as TEMPLATE_ID".

Breck

select @ttid as TEMPLATE_ID,
"PARENT_CONTAINER_ID",
...
from CONTAINER
where TEMPLATE_ID = (select TEMPLATE_ID
from TEMPLATE
where HASH =
'3dd49c867681b230194c1f959c7cfbfc'
and IS_MASTER_ = 1);

Well, I already tried several other things, among them:

1. I aliased columns in the subselect clause. No change
2. I set where "TEMPLATE_ID = 14". No change

Your sugesstion that I should get rid of @ttid as TEMPLATE_ID is saying: You don't get waht you want.

I need a Column Name there for insert ... with auto name to work properly. The TEMPLATE_ID is a foreign key column and must not be null.

So, I understand thatt ASA9 cannot make it, so I cannot use INSERT ... SELECT here

In my view this is a VERITABLE BUG at least a case of an ambiguity which can be clearly determined in terms of semantics.


As said you are correct.

Martin

I'm sorry, I was in a rush last night, "be explicit in the WHERE
clause" doesn't meet my usual standards of verbosity

I think it's a name scope issue. Try changing this:

where TEMPLATE_ID = ...

to this:

where CONTAINER.TEMPLATE_ID = ...

Here is a small demonstration where the INSERT does not insert
anything:

=====
CREATE TABLE t1 (
a INTEGER NOT NULL PRIMARY KEY,
b INTEGER NOT NULL );
INSERT t1 VALUES ( 1, 1 );
COMMIT;

CREATE TABLE t2 (
a INTEGER NOT NULL PRIMARY KEY,
b INTEGER NOT NULL );
INSERT t2 VALUES ( 1, 1 );
COMMIT;

BEGIN
DECLARE @a INTEGER;
SET @a = 2;
INSERT INTO t1
WITH AUTO NAME
SELECT @a AS a,
b
FROM t2
WHERE a = 1;
END;

SELECT * FROM t1;
=====

Changing the INSERT as follows makes it work as desired:

INSERT INTO t1
WITH AUTO NAME
SELECT @a AS a,
b
FROM t2
WHERE t2.a = 1;

If you don't qualify the name in the WHERE clause, it uses the local
name from the SELECT list, and *that* value is 2, not 1.

Breck

On 22 Nov 2004 15:38:34 -0800, Martin Baur <tinu (AT) mindpower (DOT) com> wrote:

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/AS.../risingroad-20
bcarter (AT) risingroad (DOT) com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Hi Breck

Thanks for the verbose answer ...

it shows again why you be the genius ... we be the dummies .. :-)

PS: I solved in another way ...

Martin