Re: "unexpected" query behaviour after i change parser code

On Sab, 2010-05-22 at 14:42 -0400, Robert Haas wrote:
> > Ok this my test result to "customer" and "item" table :
> > - select id_item,name from item;
> > --> failed, because there is "from clause" (failed like i expected)
> >
> > - select item.id_item, item.name;
> > --> work, like i expected
> >
> > - select id_item,name;
> > --> failed, with error : column "id_item" does not exist (failed like i
> > expected)
> >
> > - select item.id_item,customer.fname;
> > --> work, the data not acurate though because there is no joined atribut
> >
> > - select item.id_item,customer.fname where item.id_item=customer.id;
> > --> work, normaly
> >
> > - select item.id,item;
> > --> work, the result was concanted in "item" column. (i expected this
> > query was failed). Try many combination including using more than one
> > table with previous test, the result always work ONLY IF i put
> > "table_name.colId" first.
> >
> > My question :
> > 1) Can someone explain why my last test it's work?
>
> In standard PostgreSQL, "select item from item" is valid SQL. It
> returns a single column whose value is a record containing all the
> columns from the item table. I suspect something similar is happening
> in your case.
>

Hmm.., i know that "select item from item" is valid SQL. But since in my
case "from cause" was deleted. Shouldnt "select item.id_item,item;"
failed? Since "select id_item,name;" was also failed.

What i am not understand why it's always work if i put
"table_name.ColId" first.
In the case "select item from item" PostgreSQL rely on "from_clause" to
find the relation/table right?
So after i delete the "from_clause" in the case "select
item.id_item,item;" i thought PostgreSQL will also lost it ability to
find where those ColId came form, thus it will fail all query.
Instead, it work in PostgreSQL. So what's it the meaning "from_clause"
in PostgreSQL after all?

> > 2) Why PostgreSQL won't query my 3rd test?
> > Considering my last test it's work.
>
> I'm not sure which test you're referring to here, but all of your
> results look like about what would happen with adding_missing_from
> set.
>

I am refering to "select item.id_item,item;" (sorry it was writen
"select item.id,item;") test.

> Which brings me to another point: I'm not really sure what
> you're trying to accomplish with this modification, considering that
> adding_missing_from sounds like it does about what you want, but
> without breaking nearly as much stuff.
>

I am trying to make some kind automate join relation without have to
explicitly declare the join relation key.

Example :
"select item_id,fname;" in my modified query will be eqivalen with SQL
query.

"select item.id_item,customer.fname from item,fname where
item.id_item=customer.id"

Ah, yes my "conversion" will be do after raw_parsertree was forming by
lex and yacc.

Thank You.