Unbelievable SQL0811N on scalar subselect

[4.spam]

Hello.
v8.2.1

Anybody can try this:
----------
--base table
DECLARE GLOBAL TEMPORARY TABLE SESSION.T (I INT)
with replace on commit preserve rows;
--insert 10 rows into table t
INSERT INTO SESSION.T
with t(v) as
(
VALUES (1) union all select v+1 from t where v<10
)
select * from t;

--reference
DECLARE GLOBAL TEMPORARY TABLE SESSION.R
(IND INT GENERATED ALWAYS AS IDENTITY, I INT)
with replace on commit preserve rows;
--insert 10 rows into table r
INSERT INTO SESSION.R (I)
with t(v) as
(
VALUES (1) union all select v+1 from t where v<10
)
select * from t;

--try to update table t
--by random value from reference
update SESSION.T
set I=
(
select IND from session.r where IND=1+round(rand()*(10-1), 0)
);
----------
After last statement I get SQL0811N.
But it's impossible I think because SESSION.R.IND is GENERATED ALWAYS
AS IDENTITY.
How it could be?

Sincerely,
Mark B.

[Knut Stolze]

4.****@mail.ru wrote:

update SESSION.T
set I=
(
select IND from session.r where IND=1+round(rand()*(10-1), 0)
);
----------
After last statement I get SQL0811N.
But it's impossible I think because SESSION.R.IND is GENERATED ALWAYS
AS IDENTITY.
How it could be?

Simple:

one row (IND = 4) in SESSION.R

WHERE ind = 1 + ROUND(RAND() * 9, 0 )

-RAND() = 3
-WHERE 4 = 1 + 3
-true; row qualifies

some other row (IND = 2) in SESSION.R

WHERE ind = 1 + ROUND(RAND() * 9, 0 )

-RAND() = 1
-WHERE 2 = 1 + 1
-true; row qualifies

The thing is that the RAND() function is evaluated for each row in
SESSION.R. You don't reuse the same randomized value for all the rows.
Thus, multiple rows can satisfy the subselect, making it non-scalar.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[4.spam]

I've understood.
Thanks, Knut.

Simple:

one row (IND = 4) in SESSION.R

WHERE ind = 1 + ROUND(RAND() * 9, 0 )

-RAND() = 3
-WHERE 4 = 1 + 3
-true; row qualifies

some other row (IND = 2) in SESSION.R

WHERE ind = 1 + ROUND(RAND() * 9, 0 )

-RAND() = 1
-WHERE 2 = 1 + 1
-true; row qualifies

The thing is that the RAND() function is evaluated for each row in
SESSION.R. You don't reuse the same randomized value for all the rows.
Thus, multiple rows can satisfy the subselect, making it non-scalar.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany