Unexpected (wrong?) fetch and load INTO variable logic

[EPluribusUnum]

Hi *!

Looks like the full row evaluation/fetch not happens before loading values INTO variables, but the fetch+load happens for every column one after another. I'd except the full row fetch happen before the variable loading. SQL standard has a rule to this behaviour? 3.0, 4.0 5.0 behaves the same.

This returns X, X, but I expect X, A.

EXECUTE BLOCK
RETURNS (
	c1 VARCHAR(1),
	c2 VARCHAR(1))
AS
BEGIN
  c1 = 'A';

  SELECT IIF(:c1 = 'A', 'X', 'Y'), :c1
  FROM rdb$database
  INTO :c1, :c2;

  SUSPEND;
END

Thank you!

[asfernandes]

I'd say you are in an undefined behavior zone, and anything different than that would be slower.

[mrotteveel]

I'm not sure what, if anything, ISO/IEC 9075-4 (PSM) has to say about this, but as Adriano says, it is likely undefined behaviour.

[EPluribusUnum]

Alternative perspective. This returns X, A as expected and I just switched the column order. From set theory POW I think this is equivalent, columns order switch should not affect the result.

EXECUTE BLOCK
RETURNS (
	c1 VARCHAR(1),
	c2 VARCHAR(1))
AS
BEGIN
  c1 = 'A';

  SELECT :c1, IIF(:c1 = 'A', 'X', 'Y')
  FROM rdb$database
  INTO :c2, :c1;

  SUSPEND;
END

[AlexPeshkoff]

On 12/5/23 12:55, EPluribusUnum wrote:

Alternative perspective. This returns X, A as expected and I just switched the column order. From set theory POW I think this is equivalent, columns order switch should not affect the result.

|EXECUTE BLOCK RETURNS ( c1 VARCHAR(1), c2 VARCHAR(1)) AS BEGIN c1 = 'A'; SELECT :c1, IIF(:c1 = 'A', 'X', 'Y') FROM rdb$database INTO :c2, :c1; SUSPEND; END |

EPluribusUnum, imagine you have C function void fun(int a, int b);

In some place you do: n = 0; fun(n++,n++);

It's undefined what values are passed in fun - 0, 1 or 1, 0. Same for your example. Typical case of bad code.

[aafemt]

Nevertheless update behavior is defined: fields in every calculations are seen in state they were before actual modifications. Shouldn't select follow the same conventions calculating whole returned record on fetch instead of leaving it on assignments?

[dyemanov]

While we fixed the fields references in updates in the past, I believe they still suffer from the same issue when variables are used. I suppose variables are outside any rules declared in the SQL specification.

[mrotteveel]

Rules for variables are likely defined in Part 4 (PSM).