Error "User ID does not exist" with ROWTYPE variables in INTO clause?

[former_SQLA_member1694901]

I have the following script in ISQL:

CREATE TABLE Temp (Col1 VARCHAR(10), Col2 VARCHAR(10));
INSERT INTO Temp VALUES('AAA', 'BBB');
BEGIN
  DECLARE r_Temp Temp%ROWTYPE;
  DECLARE v_Dummy VARCHAR(10);

  SELECT MAX(Col1) INTO r_Temp.Col1 FROM Temp;

  SELECT r_Temp.Col1, v_Dummy;
END;

This gives me the error message "User ID 'r_Temp' does not exist". Changing it a little to

CREATE TABLE Temp (Col1 VARCHAR(10), Col2 VARCHAR(10));
INSERT INTO Temp VALUES('AAA', 'BBB');
BEGIN
  DECLARE r_Temp Temp%ROWTYPE;
  DECLARE v_Dummy VARCHAR(10);

  SELECT MAX(Col1), MAX(Col2) INTO r_Temp.Col1, v_Dummy FROM Temp;

  SELECT r_Temp.Col1, v_Dummy;
END;

works like expected. More experiments give me the impression that a single ROWTYPE-Variable in the INTO clause triggers this problem.

Is this a parser bug or do I overlook anything? Is there a better workaround than using a dummy variable?

I'm using "SQL Anywhere Network Server Version 17.0.11.7672"

[VolkerBarth · Accepted solution]

Does it work with "SELECT ... INTO VARIABLE r_Temp.Col1"?

[former_SQLA_member1694901 · Accepted solution]

Thanks, that fixes the problem!

Is this intended behavior or a workaround? From the docs I'd assume that the INTO VARIABLE clause is intended for putting a result into a row variable, as in "SELECT * INTO VARIABLE r_Temp FROM Temp", but r_Temp.Col1 IMHO is not a row variable but a simple variable...

[VolkerBarth · Accepted solution]

I'm not sure whether this is intended behaviour or not - in my understanding, the basic "INTO clause" (i.e. without specifying LOCAL TEMPORARY TABLE | TABLE | VARIABLE) seems to expect a two-part name only when the select list contains one element and the destination is a new table:

To create a permanent table with one column, the table name must be specified as owner.table.

Apparently the database engine interprets your row variable as such a table destination... Of course it could/should know that you are using a row variable.