Thread

1. 7.0 bug ??

   claud yu <claud@tera.cs.tku.edu.tw> — 2000-05-01T00:49:42Z

   -----------------------------------------------
   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
   
   
   

2. Re: 7.0 bug ??

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-05-01T01:31:04Z

   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