"unexpected" query behaviour after i change parser code

On Sat, May 22, 2010 at 9:21 AM, Mohammad Heykal Abdillah
<heykal.abdillah@gmail.com> wrote:

All,

Lately i have play with "parser"-part. I was try to make valid query
command without using "FROM clause", so far it's work.

I know this modification will make all query that using "FROM clause"
failed, for example "/df" command. But normal or simple "select
statement" so far is work.

Now before my question, this what i do to make query without FROM clause
work :
1) change "src/backend/parser/gram.y" at "simple_select:" delete
from_clause
2) change "src/backend/parser/parse_relation.c" at function
warnAutoRange, comment or delete "if (!add_missing_from)" part and
change the "else" above to "if (add_missing_from)".

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.

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

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

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.

On Sat, May 22, 2010 at 9:33 PM, Mohammad Heykal Abdillah
<heykal.abdillah@gmail.com> wrote:

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.

Well, it's hard for me to speculate about what your code might do. I
think your best bet is to fire up gdb and maybe stick in some
debugging printfs and see if you can figure out what's happening.

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.

It's probably not possible to do this well in general. You might want
to think about writing some kind of preprocessor or query generator
that would rewrite your queries like this for you before sending them
to the database, rather than modifying PostgreSQL itself.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company