Thread: 7.0 bug ??

-----------------------------------------------
freechange-# \d accountloginrec
            Table "accountloginrec"
      Attribute       |    Type     | Modifier
----------------------+-------------+----------
 regaccounttype       | smallint    | not null
 regloginname         | char(20)    | not null
 reglgoinpassword     | varchar(20) | not null
 regaccounthint       | varchar(32) |
 regaccounteffectdate | date        |
 regaccountexpireeate | date        |
 eshopcode            | integer     | not null
 companycode          | integer     |
freechange-#
freechange=# select eshopcode from accountloginrec;
 eshopcode
-----------
        10
        11
        11
        12
        13
        14
(6 rows)

freechange=# insert into accountloginrec values (1,'test','test','test',NOW(),NOW()+30,Max(eshopcode)+1,null);
ERROR:  Attribute 'eshopcode' not found
-----------------------------------------------
The same syntax at 6.5.3 is OK !!
It happen at 7.0rc1 and 7.0rc2

claud yu <claud@tera.cs.tku.edu.tw> writes:
> freechange=# insert into accountloginrec values (1,'test','test','test',NOW(),NOW()+30,Max(eshopcode)+1,null);
> ERROR:  Attribute 'eshopcode' not found

> The same syntax at 6.5.3 is OK !!

Not a 7.0 bug, but a 6.5 bug --- it shouldn't have accepted that.
The correct SQL-approved way to do this sort of thing is

insert into accountloginrec
    select 1,'test','test','test',NOW(),NOW()+30,Max(eshopcode)+1,null
    from accountloginrec;

A values() expression is a standalone row-value constructor; it can't
legitimately contain any references to the insert destination table.
In fact the way INSERT is supposed to work is that it's processed as
    INSERT INTO dest_table <something>
where the <something> is a row-value-yielding expression that would
have the same meaning whether it appeared in INSERT or not.  6.5 was
in error to accept free variables in the <something> as references
to the insert's destination table.

The INSERT...SELECT way works in 6.5 too, btw.

            regards, tom lane