Обсуждение: "unexpected" query behaviour after i change parser code
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? 2) Why PostgreSQL won't query my 3rd test? Considering my last test it's work. Thank You. *) Btw if you try my modification make sure you already have the data. I havent try any command yet, but i assume SET, or CREATE will failed.
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