You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Bernd Ruehlicke (JIRA)" <de...@db.apache.org> on 2005/03/02 00:06:49 UTC
[jira] Created: (DERBY-156) Delete with alias on column fails
Delete with alias on column fails
---------------------------------
Key: DERBY-156
URL: http://issues.apache.org/jira/browse/DERBY-156
Project: Derby
Type: Bug
Reporter: Bernd Ruehlicke
Priority: Critical
DELETE FROM MY_TABLE x WHERE x.MY_COLUMN='value';
fails with
ERROR 42X01: Syntax error: Encountered "x" at line 1, column 24
This is the core of the problem. I found it form a more complicated statement but it cooks down to that this should work but dose not.
B-)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-156) Delete with alias on column fails
Posted by "Andrew McIntyre (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-156?page=all ]
Andrew McIntyre updated DERBY-156:
----------------------------------
Fix Version: 10.2.0.0
(was: 10.1.0.0)
Description:
DELETE FROM MY_TABLE x WHERE x.MY_COLUMN='value';
fails with
ERROR 42X01: Syntax error: Encountered "x" at line 1, column 24
This is the core of the problem. I found it form a more complicated statement but it cooks down to that this should work but dose not.
B-)
was:
DELETE FROM MY_TABLE x WHERE x.MY_COLUMN='value';
fails with
ERROR 42X01: Syntax error: Encountered "x" at line 1, column 24
This is the core of the problem. I found it form a more complicated statement but it cooks down to that this should work but dose not.
B-)
Version: 10.2.0.0
Environment:
> Delete with alias on column fails
> ---------------------------------
>
> Key: DERBY-156
> URL: http://issues.apache.org/jira/browse/DERBY-156
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.0.2.0, 10.0.2.1, 10.0.2.2, 10.2.0.0, 10.1.0.0
> Reporter: Bernd Ruehlicke
> Priority: Critical
> Fix For: 10.2.0.0
>
> DELETE FROM MY_TABLE x WHERE x.MY_COLUMN='value';
> fails with
> ERROR 42X01: Syntax error: Encountered "x" at line 1, column 24
> This is the core of the problem. I found it form a more complicated statement but it cooks down to that this should work but dose not.
> B-)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-156) Delete with alias on column fails
Posted by "Samuel Andrew McIntyre (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-156?page=all ]
Samuel Andrew McIntyre updated DERBY-156:
-----------------------------------------
Version: 10.0.2.1
10.0.2.0
10.0.2.2
10.1.0.0
Fix Version: 10.1.0.0
Marking as Fix in 10.1.0.0 as a patch has been submitted by Shreyas Kaushik and is in review.
> Delete with alias on column fails
> ---------------------------------
>
> Key: DERBY-156
> URL: http://issues.apache.org/jira/browse/DERBY-156
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.0.2.1, 10.0.2.0, 10.0.2.2, 10.1.0.0
> Reporter: Bernd Ruehlicke
> Priority: Critical
> Fix For: 10.1.0.0
>
> DELETE FROM MY_TABLE x WHERE x.MY_COLUMN='value';
> fails with
> ERROR 42X01: Syntax error: Encountered "x" at line 1, column 24
> This is the core of the problem. I found it form a more complicated statement but it cooks down to that this should work but dose not.
> B-)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: [jira] Commented: (DERBY-156) Delete with alias on column fails
Posted by Danesh Irani <da...@gmail.com>.
If I'm not mistaken the place to start for modifying the way SQL is
parsed is to look at sqlgrammar.jj under
org.apache.derby.impl.sql.compile. I'm just starting with Derby dev
myself so dont take my word for it.
~Danesh
On Apr 3, 2005 10:21 AM, Shreyas Kaushik <Sh...@sun.com> wrote:
> As this is a new feature and also involves changes the way SQL commands
> for delete are parsed ( we need to support both with and without AS
> keyword ) can someone give some pointers for starting. I have previously
> worked in solving some correlation name bugs for Derby , this would be a
> good one to dive in more deep.
>
> ~ Shreyas
>
> Shreyas Kaushik wrote:
>
> > Yes, this definitely a good feature add.
> >
> > thanks
> > Shreyas
> >
> > Jack Klebanoff (JIRA) wrote:
> >
> >> [
> >> http://issues.apache.org/jira/browse/DERBY-156?page=comments#action_60015
> >> ]
> >> Jack Klebanoff commented on DERBY-156:
> >> --------------------------------------
> >>
> >> The Derby reference manual does not document the use of correlation
> >> names in DELETE statements, so this is a feature request not a bug.
> >>
> >> The SQL 2003 spec does allow correlation names in delete statements,
> >> so this is a very reasonable request.
> >>
> >> SQL allows an optional 'AS' in front of the correlation name:
> >>
> >> <delete statement: searched> ::=
> >> DELETE FROM <target table> [ [ AS ] <correlation name> ]
> >> [ WHERE <search condition> ]
> >>
> >> If we do add this to Derby we should allow the optional "AS".
> >>
> >>
> >>
> >>> Delete with alias on column fails
> >>> ---------------------------------
> >>>
> >>> Key: DERBY-156
> >>> URL: http://issues.apache.org/jira/browse/DERBY-156
> >>> Project: Derby
> >>> Type: Bug
> >>> Reporter: Bernd Ruehlicke
> >>> Priority: Critical
> >>>
> >>
> >>
> >>
> >>
> >>> DELETE FROM MY_TABLE x WHERE x.MY_COLUMN='value';
> >>> fails with ERROR 42X01: Syntax error: Encountered "x" at line 1,
> >>> column 24
> >>> This is the core of the problem. I found it form a more complicated
> >>> statement but it cooks down to that this should work but dose not.
> >>> B-)
> >>>
> >>
> >>
> >>
> >>
>
>
Re: (How To Analyze a Test Failure and Asking Questions) Was Re:
[jira] Commented: (DERBY-156) Delete with alias on column fails
Posted by Shreyas Kaushik <Sh...@Sun.COM>.
Hi Kathey,
This is definitely on my plate of to-do things. I will address this
after I debug my tests and would definitelt wan to help people who start
off.
thanks
Shreyas
Kathey Marsden wrote:
>Jack Klebanoff wrote:
>
>
>
>>Kathey Marsden wrote:
>>
>>
>>
>>>Shreyas Kaushik wrote:
>>>
>>>
>>>
>>>
>>>
>>>>Hi Kathey,
>>>>
>>>>Thanks for all the guidance and help. Yes got one of my implicit
>>>>questions right :-) of how to debug the tests in the framework.
>>>>I would definitely do a wrie up for this and put it up on the web when
>>>>I am comfortable doing this.
>>>>Yes this sort of information would definitely hep users debug the
>>>>tests in the framework.
>>>>
>>>>Evan after running the tests with the properties that you had
>>>>mentioned the tmp file did not have any major changes ( no stack
>>>>tarces for the error I got ),
>>>>except for a few warning message relating to empty tables.
>>>>
>>>>One interesting I noticed is that the select statement in the test
>>>>that is prior to this is, does a similar operation and the result
>>>>fetches two rows.
>>>>So I presume the delte should delete two rows am I missing something
>>>>obvious here ?
>>>>
>>>>I did a pass of code that I have changed, I just pull the correlation
>>>>name from the query and pass it to the DeleteNode. Could this be a
>>>>binding problem?
>>>>Looks like that from the error I am getting. I will look at this
>>>>aspect more, any more pointers/comments on my code changes will
>>>>definitely help.
>>>>
>>>>~ Shreyas
>>>>
>>>>Kathey Marsden wrote:
>>>>
>>>>
>>>>
>>>>
>>>Hi Shreyas,
>>>
>>>I am glad you are back on the trail with this issue and have some
>>>ideas. I really can't answer your specific question at all because I
>>>don't know *what* SQL we are talking about here, but if after some more
>>>research, you are still stuck, feel free to post a question. Don't
>>>forget to include the five key ingredients to getting answers from busy
>>>people on the list.
>>>
>>>1) The small bit of sql or java code in question.
>>>2) A description of the old and new behaviour and what you think the
>>>right behavior should be.
>>>3) The stack trace if there is one.
>>>4) What you have learned so far from your own evaluation of 1-3 above.
>>>5) A specific question that is not going to take a lot of research for
>>>someone to answer that you think might send you back along your way if
>>>answered.
>>>
>>>
>>>For this post I think you would have done a pretty good job with 4 and 5
>>>if 1, 2, and 3 were here too.
>>>I think you forgot 1 and 2. below are some tips for step 3 to get a
>>>stack trace.
>>>
>>> a) Create a small repro.sql script
>>> Take just enough sql to get the specific sql from step 1
>>>working. (Usually some ddl some inserts and then the
>>>trouble maker)
>>> b) start ij with -Dij.exceptionTrace=true and execute
>>>the sql.
>>>
>>>Here is an example. Looks like I need help with my select
>>>statement #:)
>>>$ java -Dij.exceptionTrace=true org.apache.derby.tools.ij
>>>ij version 10.1
>>>ij> run 'repro.sql';
>>>ij> connect 'jdbc:derby:wombat;create=true';
>>>ij> create table mytab(i int);
>>>0 rows inserted/updated/deleted
>>>ij> insert into mytab values(1);
>>>1 row inserted/updated/deleted
>>>ij> selectoops from mytab;
>>>ERROR 42X01: Syntax error: Encountered "selectoops" at line 1, column 1.
>>>ERROR 42X01: Syntax error: Encountered "selectoops" at line 1, column 1.
>>> at
>>>org.apache.derby.iapi.error.StandardException.newException(StandardException.java:311)
>>>
>>> at
>>>org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(ParserImpl.java:156)
>>>
>>> at
>>>org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:298)
>>>
>>> at
>>>org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:107)
>>>
>>> at
>>>org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:688)
>>>
>>> at
>>>org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:501)
>>>
>>> at
>>>org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:475)
>>>
>>> at
>>>org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:299)
>>> at
>>>org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:433)
>>> at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:310)
>>> at org.apache.derby.impl.tools.ij.Main.go(Main.java:209)
>>> at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:175)
>>> at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:55)
>>> at org.apache.derby.tools.ij.main(ij.java:60)
>>>ij>
>>>
>>>As for reviewing your code so far, I am sorry, but I don't think I have
>>>time to do that right now. Just too busy, but perhaps someone else from
>>>the community can. I have noticed others from your company on the
>>>list. I know that a lot of times when I need a review, I ping someone
>>>that I work with and have pretty good results that way.
>>>Kathey
>>>
>>>
>>>
>>>
>>>
>>Another good place to look for debugging information is the derb.log
>>file. When a test is run using the Derby test harness this file is
>>found one level down from the test output. If the test output is in
>>file dir/test.out then the log file is in dir/test/derby.log. The
>>derby.log file has a record of the SQLExceptions, the statement that
>>caused them, and the stack traces. I often look in derby.log first,
>>though if the test does a lot of negative testing derby.log may be
>>cluttered with a lot of expected SQLExceptions.
>>
>>Jack Klebanoff
>>
>>
>>
>>
>Excellent point Jack.
>
>If you running outside of the test harness you probably want to put
>these in your derby.properties to get the errors in your derby.log
>
>derby.language.logStatementText=true
>derby.stream.error.logSeverityLevel=0
>
>Shreyas, I think your "How To Analyze a Test Failure" page is going to
>be a big hit and a valuable resource to the community. It would be
>great if you could get an initial draft posted to the list within the
>next few weeks while you still have your fresh perspective. (I think
>some of us that have been working on these kinds of issues for a while,
>start to forget to mention the things that have become automatic.)
>Then over time folks can iterate over it, expand and improve. I'd be
>more than happy to review and will try to gather together some more tips
>for you. Over time you could produce a whole "How To" series for
>beginners.
>
>On your questions, I want to make sure I am in no way discouraging
>questions, but just before you hit send take a look at the post from
>the audience's perspective. A good question to ask yourself is, "If I
>were a Derby expert, could I effectively answer this question given the
>information provided?" If we are talking about the behaviour of a
>particular SQL statement, it is really critical to include the SQL
>statement in question and probably the ddl that is necessary to run
>it. Typically, your audience wouldn't have time to apply an entire
>in progress patch, figure out what you are doing. So, you have to
>summarize effectively, it is usually not the quantity but the quality of
>information that is key.
>Let's take a look at your refeactions1 diff again, Here is the diff
>you were asking about for those just joining.
>
>** Start: refActions1 jdk1.5.0 derbyall:derbylang 2005-04-04 15:53:53 ***
>6009 del
>< ERROR 42X01: Syntax error: Encountered "d" at line 1, column 26.
>6009a6009
>
>
>
>>>ERROR 42X04: Column 'DB2TEST.DEPT.DNO' is not in any table in the FROM
>>>
>>>
>
>list or it appears within a join specification and is outside the scope
>of the join specification or it appears in a HAVING clause and is not in
>the GROUP BY list. If this is a CREATE or ALTER TABLE statement then
>'DB2TEST.DEPT.DNO' is not a column in the target table.
>
>
>
>
>
>Here is how you can form your next question on this issue so that busy
>people in the community might able to help. Fill in the <>'s with
>the missing information.
>
>I am implementing delete with alias,
> < insert SQL Syntax from the SQL spec here >
>
>
>I almost have it working but am stuck on this case.
> < insert general description of the case in question here>
>
> This was disallowed before becuase <... >and gave a Syntax error.
>Now it <{should work | should still give a syntax error | something
>else}> , but with my changes I get an error that the column is not
>in any table in the from list.
>
>My general strategy in this case was to
> < ....>
> pull the correlation name from the query and pass it to the DeleteNode.
> <....>
>
>I thought in this case there might be a binding problem because <...>.
>In debugging that I found <....>
>
>
>
>Here is an example of the SQL for this case and the trace I get with my
>changes.
>
>create table <...>.
>insert into <....>
><select | delete ...>
>
>< A question that someone could answer given just the information you
>have provided >
>
>It's ok to post your patch so far for reference, but you can't really
>expect that anyone is going to have time to apply it and debug the
>problem themselves. You can see I never even needed to know that
>refactions1 failed, but I really do need to know what SQL you are trying
>to execute and how you expect it to behave. The great thing about
>composing your question in this way is that you will probably find out
>what's wrong yourself along the way #:).
>
>Dan, was mentioning an article on "How To Ask Questions the Right Way",
>but I am having trouble finding it. Dan could you post a link?
>
>
>Kathey
>
>
>
>
>
>
Re: (How To Analyze a Test Failure and Asking Questions)
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Kathey Marsden wrote:
> Dan, was mentioning an article on "How To Ask Questions the Right Way",
> but I am having trouble finding it. Dan could you post a link?
The Derby site includes a link to Eric & Rick's document and a link to
the Jakarta mailing list guidelines. Both are well worth reading.
The links are at
http://incubator.apache.org/derby/derby_mail.html
Dan.
Re: (How To Analyze a Test Failure and Asking Questions) Was Re:
[jira] Commented: (DERBY-156) Delete with alias on column fails
Posted by Jeremy Boynes <jb...@apache.org>.
Kathey Marsden wrote:
>
> Dan, was mentioning an article on "How To Ask Questions the Right Way",
> but I am having trouble finding it. Dan could you post a link?
>
There is Eric Raymond's treatise on this:
http://www.catb.org/~esr/faqs/smart-questions.html
--
Jeremy
(How To Analyze a Test Failure and Asking Questions) Was Re: [jira]
Commented: (DERBY-156) Delete with alias on column fails
Posted by Kathey Marsden <km...@sbcglobal.net>.
Jack Klebanoff wrote:
> Kathey Marsden wrote:
>
>> Shreyas Kaushik wrote:
>>
>>
>>
>>> Hi Kathey,
>>>
>>> Thanks for all the guidance and help. Yes got one of my implicit
>>> questions right :-) of how to debug the tests in the framework.
>>> I would definitely do a wrie up for this and put it up on the web when
>>> I am comfortable doing this.
>>> Yes this sort of information would definitely hep users debug the
>>> tests in the framework.
>>>
>>> Evan after running the tests with the properties that you had
>>> mentioned the tmp file did not have any major changes ( no stack
>>> tarces for the error I got ),
>>> except for a few warning message relating to empty tables.
>>>
>>> One interesting I noticed is that the select statement in the test
>>> that is prior to this is, does a similar operation and the result
>>> fetches two rows.
>>> So I presume the delte should delete two rows am I missing something
>>> obvious here ?
>>>
>>> I did a pass of code that I have changed, I just pull the correlation
>>> name from the query and pass it to the DeleteNode. Could this be a
>>> binding problem?
>>> Looks like that from the error I am getting. I will look at this
>>> aspect more, any more pointers/comments on my code changes will
>>> definitely help.
>>>
>>> ~ Shreyas
>>>
>>> Kathey Marsden wrote:
>>>
>>>
>>
>> Hi Shreyas,
>>
>> I am glad you are back on the trail with this issue and have some
>> ideas. I really can't answer your specific question at all because I
>> don't know *what* SQL we are talking about here, but if after some more
>> research, you are still stuck, feel free to post a question. Don't
>> forget to include the five key ingredients to getting answers from busy
>> people on the list.
>>
>> 1) The small bit of sql or java code in question.
>> 2) A description of the old and new behaviour and what you think the
>> right behavior should be.
>> 3) The stack trace if there is one.
>> 4) What you have learned so far from your own evaluation of 1-3 above.
>> 5) A specific question that is not going to take a lot of research for
>> someone to answer that you think might send you back along your way if
>> answered.
>>
>>
>> For this post I think you would have done a pretty good job with 4 and 5
>> if 1, 2, and 3 were here too.
>> I think you forgot 1 and 2. below are some tips for step 3 to get a
>> stack trace.
>>
>> a) Create a small repro.sql script
>> Take just enough sql to get the specific sql from step 1
>> working. (Usually some ddl some inserts and then the
>> trouble maker)
>> b) start ij with -Dij.exceptionTrace=true and execute
>> the sql.
>>
>> Here is an example. Looks like I need help with my select
>> statement #:)
>> $ java -Dij.exceptionTrace=true org.apache.derby.tools.ij
>> ij version 10.1
>> ij> run 'repro.sql';
>> ij> connect 'jdbc:derby:wombat;create=true';
>> ij> create table mytab(i int);
>> 0 rows inserted/updated/deleted
>> ij> insert into mytab values(1);
>> 1 row inserted/updated/deleted
>> ij> selectoops from mytab;
>> ERROR 42X01: Syntax error: Encountered "selectoops" at line 1, column 1.
>> ERROR 42X01: Syntax error: Encountered "selectoops" at line 1, column 1.
>> at
>> org.apache.derby.iapi.error.StandardException.newException(StandardException.java:311)
>>
>> at
>> org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(ParserImpl.java:156)
>>
>> at
>> org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:298)
>>
>> at
>> org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:107)
>>
>> at
>> org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:688)
>>
>> at
>> org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:501)
>>
>> at
>> org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:475)
>>
>> at
>> org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:299)
>> at
>> org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:433)
>> at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:310)
>> at org.apache.derby.impl.tools.ij.Main.go(Main.java:209)
>> at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:175)
>> at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:55)
>> at org.apache.derby.tools.ij.main(ij.java:60)
>> ij>
>>
>> As for reviewing your code so far, I am sorry, but I don't think I have
>> time to do that right now. Just too busy, but perhaps someone else from
>> the community can. I have noticed others from your company on the
>> list. I know that a lot of times when I need a review, I ping someone
>> that I work with and have pretty good results that way.
>> Kathey
>>
>>
>>
> Another good place to look for debugging information is the derb.log
> file. When a test is run using the Derby test harness this file is
> found one level down from the test output. If the test output is in
> file dir/test.out then the log file is in dir/test/derby.log. The
> derby.log file has a record of the SQLExceptions, the statement that
> caused them, and the stack traces. I often look in derby.log first,
> though if the test does a lot of negative testing derby.log may be
> cluttered with a lot of expected SQLExceptions.
>
> Jack Klebanoff
>
>
Excellent point Jack.
If you running outside of the test harness you probably want to put
these in your derby.properties to get the errors in your derby.log
derby.language.logStatementText=true
derby.stream.error.logSeverityLevel=0
Shreyas, I think your "How To Analyze a Test Failure" page is going to
be a big hit and a valuable resource to the community. It would be
great if you could get an initial draft posted to the list within the
next few weeks while you still have your fresh perspective. (I think
some of us that have been working on these kinds of issues for a while,
start to forget to mention the things that have become automatic.)
Then over time folks can iterate over it, expand and improve. I'd be
more than happy to review and will try to gather together some more tips
for you. Over time you could produce a whole "How To" series for
beginners.
On your questions, I want to make sure I am in no way discouraging
questions, but just before you hit send take a look at the post from
the audience's perspective. A good question to ask yourself is, "If I
were a Derby expert, could I effectively answer this question given the
information provided?" If we are talking about the behaviour of a
particular SQL statement, it is really critical to include the SQL
statement in question and probably the ddl that is necessary to run
it. Typically, your audience wouldn't have time to apply an entire
in progress patch, figure out what you are doing. So, you have to
summarize effectively, it is usually not the quantity but the quality of
information that is key.
Let's take a look at your refeactions1 diff again, Here is the diff
you were asking about for those just joining.
** Start: refActions1 jdk1.5.0 derbyall:derbylang 2005-04-04 15:53:53 ***
6009 del
< ERROR 42X01: Syntax error: Encountered "d" at line 1, column 26.
6009a6009
>> ERROR 42X04: Column 'DB2TEST.DEPT.DNO' is not in any table in the FROM
list or it appears within a join specification and is outside the scope
of the join specification or it appears in a HAVING clause and is not in
the GROUP BY list. If this is a CREATE or ALTER TABLE statement then
'DB2TEST.DEPT.DNO' is not a column in the target table.
>>
Here is how you can form your next question on this issue so that busy
people in the community might able to help. Fill in the <>'s with
the missing information.
I am implementing delete with alias,
< insert SQL Syntax from the SQL spec here >
I almost have it working but am stuck on this case.
< insert general description of the case in question here>
This was disallowed before becuase <... >and gave a Syntax error.
Now it <{should work | should still give a syntax error | something
else}> , but with my changes I get an error that the column is not
in any table in the from list.
My general strategy in this case was to
< ....>
pull the correlation name from the query and pass it to the DeleteNode.
<....>
I thought in this case there might be a binding problem because <...>.
In debugging that I found <....>
Here is an example of the SQL for this case and the trace I get with my
changes.
create table <...>.
insert into <....>
<select | delete ...>
< A question that someone could answer given just the information you
have provided >
It's ok to post your patch so far for reference, but you can't really
expect that anyone is going to have time to apply it and debug the
problem themselves. You can see I never even needed to know that
refactions1 failed, but I really do need to know what SQL you are trying
to execute and how you expect it to behave. The great thing about
composing your question in this way is that you will probably find out
what's wrong yourself along the way #:).
Dan, was mentioning an article on "How To Ask Questions the Right Way",
but I am having trouble finding it. Dan could you post a link?
Kathey
Re: [jira] Commented: (DERBY-156) Delete with alias on column fails
Posted by Jack Klebanoff <kl...@sbcglobal.net>.
Kathey Marsden wrote:
>Shreyas Kaushik wrote:
>
>
>
>>Hi Kathey,
>>
>>Thanks for all the guidance and help. Yes got one of my implicit
>>questions right :-) of how to debug the tests in the framework.
>>I would definitely do a wrie up for this and put it up on the web when
>>I am comfortable doing this.
>>Yes this sort of information would definitely hep users debug the
>>tests in the framework.
>>
>>Evan after running the tests with the properties that you had
>>mentioned the tmp file did not have any major changes ( no stack
>>tarces for the error I got ),
>>except for a few warning message relating to empty tables.
>>
>>One interesting I noticed is that the select statement in the test
>>that is prior to this is, does a similar operation and the result
>>fetches two rows.
>>So I presume the delte should delete two rows am I missing something
>>obvious here ?
>>
>>I did a pass of code that I have changed, I just pull the correlation
>>name from the query and pass it to the DeleteNode. Could this be a
>>binding problem?
>>Looks like that from the error I am getting. I will look at this
>>aspect more, any more pointers/comments on my code changes will
>>definitely help.
>>
>>~ Shreyas
>>
>>Kathey Marsden wrote:
>>
>>
>>
>Hi Shreyas,
>
>I am glad you are back on the trail with this issue and have some
>ideas. I really can't answer your specific question at all because I
>don't know *what* SQL we are talking about here, but if after some more
>research, you are still stuck, feel free to post a question. Don't
>forget to include the five key ingredients to getting answers from busy
>people on the list.
>
>1) The small bit of sql or java code in question.
>2) A description of the old and new behaviour and what you think the
>right behavior should be.
>3) The stack trace if there is one.
>4) What you have learned so far from your own evaluation of 1-3 above.
>5) A specific question that is not going to take a lot of research for
>someone to answer that you think might send you back along your way if
>answered.
>
>
>For this post I think you would have done a pretty good job with 4 and 5
>if 1, 2, and 3 were here too.
> I think you forgot 1 and 2. below are some tips for step 3 to get a
>stack trace.
>
> a) Create a small repro.sql script
> Take just enough sql to get the specific sql from step 1
>working.
> (Usually some ddl some inserts and then the trouble maker)
> b) start ij with -Dij.exceptionTrace=true and execute the sql.
>
>Here is an example. Looks like I need help with my select statement #:)
>$ java -Dij.exceptionTrace=true org.apache.derby.tools.ij
>ij version 10.1
>ij> run 'repro.sql';
>ij> connect 'jdbc:derby:wombat;create=true';
>ij> create table mytab(i int);
>0 rows inserted/updated/deleted
>ij> insert into mytab values(1);
>1 row inserted/updated/deleted
>ij> selectoops from mytab;
>ERROR 42X01: Syntax error: Encountered "selectoops" at line 1, column 1.
>ERROR 42X01: Syntax error: Encountered "selectoops" at line 1, column 1.
> at
>org.apache.derby.iapi.error.StandardException.newException(StandardException.java:311)
> at
>org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(ParserImpl.java:156)
> at
>org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:298)
> at
>org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:107)
> at
>org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:688)
> at
>org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:501)
> at
>org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:475)
> at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:299)
> at
>org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:433)
> at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:310)
> at org.apache.derby.impl.tools.ij.Main.go(Main.java:209)
> at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:175)
> at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:55)
> at org.apache.derby.tools.ij.main(ij.java:60)
>ij>
>
>As for reviewing your code so far, I am sorry, but I don't think I have
>time to do that right now. Just too busy, but perhaps someone else from
>the community can. I have noticed others from your company on the
>list. I know that a lot of times when I need a review, I ping someone
>that I work with and have pretty good results that way.
>
>Kathey
>
>
>
Another good place to look for debugging information is the derb.log
file. When a test is run using the Derby test harness this file is found
one level down from the test output. If the test output is in file
dir/test.out then the log file is in dir/test/derby.log. The derby.log
file has a record of the SQLExceptions, the statement that caused them,
and the stack traces. I often look in derby.log first, though if the
test does a lot of negative testing derby.log may be cluttered with a
lot of expected SQLExceptions.
Jack Klebanoff
Re: [jira] Commented: (DERBY-156) Delete with alias on column fails
Posted by Kathey Marsden <km...@sbcglobal.net>.
Shreyas Kaushik wrote:
> Hi Kathey,
>
> Thanks for all the guidance and help. Yes got one of my implicit
> questions right :-) of how to debug the tests in the framework.
> I would definitely do a wrie up for this and put it up on the web when
> I am comfortable doing this.
> Yes this sort of information would definitely hep users debug the
> tests in the framework.
>
> Evan after running the tests with the properties that you had
> mentioned the tmp file did not have any major changes ( no stack
> tarces for the error I got ),
> except for a few warning message relating to empty tables.
>
> One interesting I noticed is that the select statement in the test
> that is prior to this is, does a similar operation and the result
> fetches two rows.
> So I presume the delte should delete two rows am I missing something
> obvious here ?
>
> I did a pass of code that I have changed, I just pull the correlation
> name from the query and pass it to the DeleteNode. Could this be a
> binding problem?
> Looks like that from the error I am getting. I will look at this
> aspect more, any more pointers/comments on my code changes will
> definitely help.
>
> ~ Shreyas
>
> Kathey Marsden wrote:
>
Hi Shreyas,
I am glad you are back on the trail with this issue and have some
ideas. I really can't answer your specific question at all because I
don't know *what* SQL we are talking about here, but if after some more
research, you are still stuck, feel free to post a question. Don't
forget to include the five key ingredients to getting answers from busy
people on the list.
1) The small bit of sql or java code in question.
2) A description of the old and new behaviour and what you think the
right behavior should be.
3) The stack trace if there is one.
4) What you have learned so far from your own evaluation of 1-3 above.
5) A specific question that is not going to take a lot of research for
someone to answer that you think might send you back along your way if
answered.
For this post I think you would have done a pretty good job with 4 and 5
if 1, 2, and 3 were here too.
I think you forgot 1 and 2. below are some tips for step 3 to get a
stack trace.
a) Create a small repro.sql script
Take just enough sql to get the specific sql from step 1
working.
(Usually some ddl some inserts and then the trouble maker)
b) start ij with -Dij.exceptionTrace=true and execute the sql.
Here is an example. Looks like I need help with my select statement #:)
$ java -Dij.exceptionTrace=true org.apache.derby.tools.ij
ij version 10.1
ij> run 'repro.sql';
ij> connect 'jdbc:derby:wombat;create=true';
ij> create table mytab(i int);
0 rows inserted/updated/deleted
ij> insert into mytab values(1);
1 row inserted/updated/deleted
ij> selectoops from mytab;
ERROR 42X01: Syntax error: Encountered "selectoops" at line 1, column 1.
ERROR 42X01: Syntax error: Encountered "selectoops" at line 1, column 1.
at
org.apache.derby.iapi.error.StandardException.newException(StandardException.java:311)
at
org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(ParserImpl.java:156)
at
org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:298)
at
org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:107)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:688)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:501)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:475)
at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:299)
at
org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:433)
at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:310)
at org.apache.derby.impl.tools.ij.Main.go(Main.java:209)
at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:175)
at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:55)
at org.apache.derby.tools.ij.main(ij.java:60)
ij>
As for reviewing your code so far, I am sorry, but I don't think I have
time to do that right now. Just too busy, but perhaps someone else from
the community can. I have noticed others from your company on the
list. I know that a lot of times when I need a review, I ping someone
that I work with and have pretty good results that way.
Kathey
Re: [jira] Commented: (DERBY-156) Delete with alias on column fails
Posted by Shreyas Kaushik <Sh...@Sun.COM>.
Hi Kathey,
Thanks for all the guidance and help. Yes got one of my implicit
questions right :-) of how to debug the tests in the framework.
I would definitely do a wrie up for this and put it up on the web when I
am comfortable doing this.
Yes this sort of information would definitely hep users debug the tests
in the framework.
Evan after running the tests with the properties that you had mentioned
the tmp file did not have any major changes ( no stack tarces for the
error I got ),
except for a few warning message relating to empty tables.
One interesting I noticed is that the select statement in the test that
is prior to this is, does a similar operation and the result fetches
two rows.
So I presume the delte should delete two rows am I missing something
obvious here ?
I did a pass of code that I have changed, I just pull the correlation
name from the query and pass it to the DeleteNode. Could this be a
binding problem?
Looks like that from the error I am getting. I will look at this aspect
more, any more pointers/comments on my code changes will definitely help.
~ Shreyas
Kathey Marsden wrote:
>Shreyas Kaushik wrote:
>
>
>
>>Please see inline.
>>
>>Kathey Marsden wrote:
>>
>>
>>
>>>Shreyas Kaushik wrote:
>>>
>>>
>>>
>>>
>>>
>>>>Can somebody reply on this?
>>>>
>>>>~ Shreyas
>>>>
>>>>Shreyas Kaushik wrote:
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>>Did any1 have a chance to look at this?
>>>>>
>>>>>~ Shreyas
>>>>>
>>>>>Shreyas Kaushik wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>I did some changes to sqlgrammar.jj file to make a fix for this.
>>>>>>With the patch , the following cases are taken care :
>>>>>>1) delete from <tablename> <correlationName> where
>>>>>><correlatioName>.<columnName> = <value>
>>>>>>2) delete from <tablename> AS <correlationName> where
>>>>>><correlatioName>.<columnName> = <value>
>>>>>>
>>>>>>But when I ran derbyall I had a couple of failures.
>>>>>>I am attaching the test failure diffs here as well.
>>>>>>
>>>>>>Please comment.
>>>>>>
>>>>>>~ Shreyas
>>>>>>
>>>>>>
>>>>>>
>>>What did you find in anlalyzing your test failures? I have not looked
>>>at your diffs specifically, but one thing I have found helpful is that
>>>often if there is a trace it is in the .tmp file, and has been filtered
>>>out of the .out file.
>>>
>>>
>>No, nothing has been filtered out.
>>
>>
>>
>>>Take a look at the failures and try to
>>>determine how they might relate to your changes and let us know if you
>>>have some more specific questions, what is failing how you think it may
>>>or may not be related to your change.
>>>
>>>
>>>
>>>
>>w.r.t floattypes I don't see how my changes could have made any impact
>>there.
>>But the tests in refActions1 that have failed were earlier failing as
>>there was no correlation name handling for
>>delete statements. These were giving a different error as the diffs show.
>>But now with the correlation name handling fixed ( hopefully ;-) ) I
>>don't know whether the queries that are
>>erroring out are correct ? It could be that I might have also missed
>>something. I want people here to have a look at it
>>so that they might spot something that I may have missed.
>>
>>~ Shreyas
>>
>>
>>
>>>Kathey
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>Hi Shreyas,
>
>Sounds like you are really stuck on this thing so I would like to try to
>help.
>Let me take a look at this refActions1 diff you sent.
>
>
>** Start: refActions1 jdk1.5.0 derbyall:derbylang 2005-04-04 15:53:53 ***
>6009 del
>< ERROR 42X01: Syntax error: Encountered "d" at line 1, column 26.
>6009a6009
>
>
>
>
>>ERROR 42X04: Column 'DB2TEST.DEPT.DNO' is not in any table in the FROM
>>
>>
>list or it appears within a join specification and is outside the scope
>of the join specification or it appears in a HAVING clause and is not in
>the GROUP BY list. If this is a CREATE or ALTER TABLE statement then
>'DB2TEST.DEPT.DNO' is not a column in the target table.
>
>
>>
>>
>>
>
>Hmmm, we used to perform some sql command, and got a syntax error and
>now we perform the same sql command and get a different error. I can't
>really tell from this diff whether considering the changes you made ...
> a) The syntax error is correct.
> b) The new error is correct
> c) That sql command (whatever it is) should have completed just fine.
>
>It seems to me maybe you have a bigger question of. "How do I debug a
>test failure"? And that I think is an excellent question, because our
>test harness is not always the easiest thing to figure out. So here are
>some quick notes on the topic to get you started. Nothing formal, so
>forgive typos and such. I hope they help you get past your current
>sticking point. I also hope that after you iterate over this process a
>few times and improve upon it, you will consider making a short write up
>for the web site on how to debug test failures. I think new folks would
>find it useful. I'll just stick to embedded here since that is the
>problem at hand, but certainly there are other tips and tricks for
>network server, so I would be happy to provide those if you are
>interested in making a web page with this info.
>
>How to debug a test failure.
>
>1) Run the test. take a look at ${derby.src}/java/testing/README for
>all sorts of good info on tests). I think I would run this test
>something like this.
>
>java -Dij.exceptionTrace=true -Dkeepfiles=true
>org.apache.derbyTesting.functionTests.harness.RunTest lang/refActions1.sql
>
>For network server add -Dframework=DerbyNet (OK 1 network server tip.)
>
>2) Do a visual diff of the ouptut with the canon
> It will give you more context in approaching the problem.
> In the test output directory you will see
> refactions1.out (filitered test output)
> refactions1.tmp (unfiltered test output - this one should have a trace)
>
> To get the most information, I usually initially vdiff the tmp file
>with the canon which is checked in under
> java/testing/org/apache/derbyTesting/functionTests/master/ or
>appropriate framework or jdk subdirectory.
>
>
>3) Identify the sql statement or java code causing the diff.
> For sql scripts this is usually pretty obvious. For java programs
>you have to look at the test source to figure out what is going on.
>
>4) Evaluate the diff. This of course starts the tricky and interesting
>part.
> Look carefully at the sql command or java code that caused the diff.
> Think about what it should do and how it relates to your change.
> Decide what you think the right behaviour should be.
> a) The old behaviour
> b) The new behaviour
> c) something else
> If you have a trace, look at the the trace and see if it holds any clue.
> If you are lucky it passes right through that code you changed.
> Often it is helpful to put that one sql command or bit of java code in
> a separate script or stand alone program, so you can evaluate it
> independently outside of the harness and evaluate in the debugger.
>
> Common results of the evaluation phase are:
> a) An error in your code.
> b) Someone elses test failure all together.
> Good to keep a clean client for testing this.
> c) A master update. Be careful with this one. make sure you have a
>valid reason to update a master. Be especially cognizant of backward
>compatibility. We don't want any real or perceived regressions to catch
>us by surprise.
>Two good questions to ask your self and then answer when you post your
>patch.
> 1) What is my valid reason for updating this master.
> 2) Might someone be surprised by this difference and perceive it as
>a regression?
>
>
>5) Resolve the issue.
> Here are some possible actions based on your evaluation in step 4
> a) An error in your code.
> Fix it #:)
> b) Someone else's test failure all together.
> Look at the recent checkins and try to guess the culprit and post.
> (See Dan's mail earlier today entitled
> jdbcapi/testRelative failing & Derby-186) for an example of this.)
> c) A master update.
> update the master and make sure you include an explanatio in
>your patch.
>
>
>If you get stuck along the way, please post to the list, but make sure
>you include.
>1) The small bit of sql or java code in question.
>2) A description of the old and new behaviour and what you think the
>right behavior should be.
>3) The stack trace if there is one.
>4) What you have learned so far from your own evaluation of 1-3 above.
>5) A specific question that is not going to take a lot of research for
>someone to answer that you think might send you back along your way if
>answered.
>
>Hope this helped. Good luck and happy debugging. I really do hope you
>will consider writing up the process as you become comfortable with it.
>
>Thanks
>
>Kathey
>
>
>
>
>
>
>
>
>
>
Re: [jira] Commented: (DERBY-156) Delete with alias on column fails
Posted by Kathey Marsden <km...@sbcglobal.net>.
Shreyas Kaushik wrote:
> Please see inline.
>
> Kathey Marsden wrote:
>
>> Shreyas Kaushik wrote:
>>
>>
>>
>>> Can somebody reply on this?
>>>
>>> ~ Shreyas
>>>
>>> Shreyas Kaushik wrote:
>>>
>>>
>>>
>>>> Did any1 have a chance to look at this?
>>>>
>>>> ~ Shreyas
>>>>
>>>> Shreyas Kaushik wrote:
>>>>
>>>>
>>>>
>>>>> I did some changes to sqlgrammar.jj file to make a fix for this.
>>>>> With the patch , the following cases are taken care :
>>>>> 1) delete from <tablename> <correlationName> where
>>>>> <correlatioName>.<columnName> = <value>
>>>>> 2) delete from <tablename> AS <correlationName> where
>>>>> <correlatioName>.<columnName> = <value>
>>>>>
>>>>> But when I ran derbyall I had a couple of failures.
>>>>> I am attaching the test failure diffs here as well.
>>>>>
>>>>> Please comment.
>>>>>
>>>>> ~ Shreyas
>>>>>
>>>>
>>
>> What did you find in anlalyzing your test failures? I have not looked
>> at your diffs specifically, but one thing I have found helpful is that
>> often if there is a trace it is in the .tmp file, and has been filtered
>> out of the .out file.
>
> No, nothing has been filtered out.
>
>> Take a look at the failures and try to
>> determine how they might relate to your changes and let us know if you
>> have some more specific questions, what is failing how you think it may
>> or may not be related to your change.
>>
>>
> w.r.t floattypes I don't see how my changes could have made any impact
> there.
> But the tests in refActions1 that have failed were earlier failing as
> there was no correlation name handling for
> delete statements. These were giving a different error as the diffs show.
> But now with the correlation name handling fixed ( hopefully ;-) ) I
> don't know whether the queries that are
> erroring out are correct ? It could be that I might have also missed
> something. I want people here to have a look at it
> so that they might spot something that I may have missed.
>
> ~ Shreyas
>
>> Kathey
>>
>>
>>
>>
>>
>>
>
>
Hi Shreyas,
Sounds like you are really stuck on this thing so I would like to try to
help.
Let me take a look at this refActions1 diff you sent.
** Start: refActions1 jdk1.5.0 derbyall:derbylang 2005-04-04 15:53:53 ***
6009 del
< ERROR 42X01: Syntax error: Encountered "d" at line 1, column 26.
6009a6009
> ERROR 42X04: Column 'DB2TEST.DEPT.DNO' is not in any table in the FROM
list or it appears within a join specification and is outside the scope
of the join specification or it appears in a HAVING clause and is not in
the GROUP BY list. If this is a CREATE or ALTER TABLE statement then
'DB2TEST.DEPT.DNO' is not a column in the target table.
>
Hmmm, we used to perform some sql command, and got a syntax error and
now we perform the same sql command and get a different error. I can't
really tell from this diff whether considering the changes you made ...
a) The syntax error is correct.
b) The new error is correct
c) That sql command (whatever it is) should have completed just fine.
It seems to me maybe you have a bigger question of. "How do I debug a
test failure"? And that I think is an excellent question, because our
test harness is not always the easiest thing to figure out. So here are
some quick notes on the topic to get you started. Nothing formal, so
forgive typos and such. I hope they help you get past your current
sticking point. I also hope that after you iterate over this process a
few times and improve upon it, you will consider making a short write up
for the web site on how to debug test failures. I think new folks would
find it useful. I'll just stick to embedded here since that is the
problem at hand, but certainly there are other tips and tricks for
network server, so I would be happy to provide those if you are
interested in making a web page with this info.
How to debug a test failure.
1) Run the test. take a look at ${derby.src}/java/testing/README for
all sorts of good info on tests). I think I would run this test
something like this.
java -Dij.exceptionTrace=true -Dkeepfiles=true
org.apache.derbyTesting.functionTests.harness.RunTest lang/refActions1.sql
For network server add -Dframework=DerbyNet (OK 1 network server tip.)
2) Do a visual diff of the ouptut with the canon
It will give you more context in approaching the problem.
In the test output directory you will see
refactions1.out (filitered test output)
refactions1.tmp (unfiltered test output - this one should have a trace)
To get the most information, I usually initially vdiff the tmp file
with the canon which is checked in under
java/testing/org/apache/derbyTesting/functionTests/master/ or
appropriate framework or jdk subdirectory.
3) Identify the sql statement or java code causing the diff.
For sql scripts this is usually pretty obvious. For java programs
you have to look at the test source to figure out what is going on.
4) Evaluate the diff. This of course starts the tricky and interesting
part.
Look carefully at the sql command or java code that caused the diff.
Think about what it should do and how it relates to your change.
Decide what you think the right behaviour should be.
a) The old behaviour
b) The new behaviour
c) something else
If you have a trace, look at the the trace and see if it holds any clue.
If you are lucky it passes right through that code you changed.
Often it is helpful to put that one sql command or bit of java code in
a separate script or stand alone program, so you can evaluate it
independently outside of the harness and evaluate in the debugger.
Common results of the evaluation phase are:
a) An error in your code.
b) Someone elses test failure all together.
Good to keep a clean client for testing this.
c) A master update. Be careful with this one. make sure you have a
valid reason to update a master. Be especially cognizant of backward
compatibility. We don't want any real or perceived regressions to catch
us by surprise.
Two good questions to ask your self and then answer when you post your
patch.
1) What is my valid reason for updating this master.
2) Might someone be surprised by this difference and perceive it as
a regression?
5) Resolve the issue.
Here are some possible actions based on your evaluation in step 4
a) An error in your code.
Fix it #:)
b) Someone else's test failure all together.
Look at the recent checkins and try to guess the culprit and post.
(See Dan's mail earlier today entitled
jdbcapi/testRelative failing & Derby-186) for an example of this.)
c) A master update.
update the master and make sure you include an explanatio in
your patch.
If you get stuck along the way, please post to the list, but make sure
you include.
1) The small bit of sql or java code in question.
2) A description of the old and new behaviour and what you think the
right behavior should be.
3) The stack trace if there is one.
4) What you have learned so far from your own evaluation of 1-3 above.
5) A specific question that is not going to take a lot of research for
someone to answer that you think might send you back along your way if
answered.
Hope this helped. Good luck and happy debugging. I really do hope you
will consider writing up the process as you become comfortable with it.
Thanks
Kathey
Re: [jira] Commented: (DERBY-156) Delete with alias on column fails
Posted by Shreyas Kaushik <Sh...@Sun.COM>.
Please see inline.
Kathey Marsden wrote:
>Shreyas Kaushik wrote:
>
>
>
>>Can somebody reply on this?
>>
>>~ Shreyas
>>
>>Shreyas Kaushik wrote:
>>
>>
>>
>>>Did any1 have a chance to look at this?
>>>
>>>~ Shreyas
>>>
>>>Shreyas Kaushik wrote:
>>>
>>>
>>>
>>>>I did some changes to sqlgrammar.jj file to make a fix for this.
>>>>With the patch , the following cases are taken care :
>>>>1) delete from <tablename> <correlationName> where
>>>><correlatioName>.<columnName> = <value>
>>>>2) delete from <tablename> AS <correlationName> where
>>>><correlatioName>.<columnName> = <value>
>>>>
>>>>But when I ran derbyall I had a couple of failures.
>>>>I am attaching the test failure diffs here as well.
>>>>
>>>>Please comment.
>>>>
>>>>~ Shreyas
>>>>
>>>>
>
>What did you find in anlalyzing your test failures? I have not looked
>at your diffs specifically, but one thing I have found helpful is that
>often if there is a trace it is in the .tmp file, and has been filtered
>out of the .out file.
>
No, nothing has been filtered out.
>Take a look at the failures and try to
>determine how they might relate to your changes and let us know if you
>have some more specific questions, what is failing how you think it may
>or may not be related to your change.
>
>
w.r.t floattypes I don't see how my changes could have made any impact
there.
But the tests in refActions1 that have failed were earlier failing as
there was no correlation name handling for
delete statements. These were giving a different error as the diffs show.
But now with the correlation name handling fixed ( hopefully ;-) ) I
don't know whether the queries that are
erroring out are correct ? It could be that I might have also missed
something. I want people here to have a look at it
so that they might spot something that I may have missed.
~ Shreyas
>Kathey
>
>
>
>
>
>
Re: [jira] Commented: (DERBY-156) Delete with alias on column fails
Posted by Kathey Marsden <km...@sbcglobal.net>.
Shreyas Kaushik wrote:
> Can somebody reply on this?
>
> ~ Shreyas
>
> Shreyas Kaushik wrote:
>
>> Did any1 have a chance to look at this?
>>
>> ~ Shreyas
>>
>> Shreyas Kaushik wrote:
>>
>>> I did some changes to sqlgrammar.jj file to make a fix for this.
>>> With the patch , the following cases are taken care :
>>> 1) delete from <tablename> <correlationName> where
>>> <correlatioName>.<columnName> = <value>
>>> 2) delete from <tablename> AS <correlationName> where
>>> <correlatioName>.<columnName> = <value>
>>>
>>> But when I ran derbyall I had a couple of failures.
>>> I am attaching the test failure diffs here as well.
>>>
>>> Please comment.
>>>
>>> ~ Shreyas
>>
>
What did you find in anlalyzing your test failures? I have not looked
at your diffs specifically, but one thing I have found helpful is that
often if there is a trace it is in the .tmp file, and has been filtered
out of the .out file. Take a look at the failures and try to
determine how they might relate to your changes and let us know if you
have some more specific questions, what is failing how you think it may
or may not be related to your change.
Kathey
Re: [jira] Commented: (DERBY-156) Delete with alias on column fails
Posted by Shreyas Kaushik <Sh...@Sun.COM>.
Can somebody reply on this?
~ Shreyas
Shreyas Kaushik wrote:
> Did any1 have a chance to look at this?
>
> ~ Shreyas
>
> Shreyas Kaushik wrote:
>
>> I did some changes to sqlgrammar.jj file to make a fix for this.
>> With the patch , the following cases are taken care :
>> 1) delete from <tablename> <correlationName> where
>> <correlatioName>.<columnName> = <value>
>> 2) delete from <tablename> AS <correlationName> where
>> <correlatioName>.<columnName> = <value>
>>
>> But when I ran derbyall I had a couple of failures.
>> I am attaching the test failure diffs here as well.
>>
>> Please comment.
>>
>> ~ Shreyas
>>
>> Shreyas Kaushik wrote:
>>
>>> As this is a new feature and also involves changes the way SQL
>>> commands for delete are parsed ( we need to support both with and
>>> without AS keyword ) can someone give some pointers for starting. I
>>> have previously worked in solving some correlation name bugs for
>>> Derby , this would be a good one to dive in more deep.
>>>
>>> ~ Shreyas
>>>
>>> Shreyas Kaushik wrote:
>>>
>>>> Yes, this definitely a good feature add.
>>>>
>>>> thanks
>>>> Shreyas
>>>>
>>>> Jack Klebanoff (JIRA) wrote:
>>>>
>>>>> [
>>>>> http://issues.apache.org/jira/browse/DERBY-156?page=comments#action_60015
>>>>> ]
>>>>> Jack Klebanoff commented on DERBY-156:
>>>>> --------------------------------------
>>>>>
>>>>> The Derby reference manual does not document the use of
>>>>> correlation names in DELETE statements, so this is a feature
>>>>> request not a bug.
>>>>>
>>>>> The SQL 2003 spec does allow correlation names in delete
>>>>> statements, so this is a very reasonable request.
>>>>>
>>>>> SQL allows an optional 'AS' in front of the correlation name:
>>>>>
>>>>> <delete statement: searched> ::=
>>>>> DELETE FROM <target table> [ [ AS ] <correlation name> ]
>>>>> [ WHERE <search condition> ]
>>>>>
>>>>> If we do add this to Derby we should allow the optional "AS".
>>>>>
>>>>>
>>>>>
>>>>>> Delete with alias on column fails
>>>>>> ---------------------------------
>>>>>>
>>>>>> Key: DERBY-156
>>>>>> URL: http://issues.apache.org/jira/browse/DERBY-156
>>>>>> Project: Derby
>>>>>> Type: Bug
>>>>>> Reporter: Bernd Ruehlicke
>>>>>> Priority: Critical
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>> DELETE FROM MY_TABLE x WHERE x.MY_COLUMN='value';
>>>>>> fails with ERROR 42X01: Syntax error: Encountered "x" at line 1,
>>>>>> column 24
>>>>>> This is the core of the problem. I found it form a more
>>>>>> complicated statement but it cooks down to that this should work
>>>>>> but dose not.
>>>>>> B-)
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>
>>>
>> ------------------------------------------------------------------------
>>
>> Index: java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
>> ===================================================================
>> --- java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
>> (revision 159559)
>> +++ java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
>> (working copy)
>> @@ -2552,16 +2552,22 @@
>> QueryTreeNode retval;
>> Properties targetProperties = null;
>> Token whereToken = null;
>> + String correlationName = null;
>> + Object []objArr = null;
>> }
>> {
>> LOOKAHEAD( { fromNewInvocationFollows() } ) <FROM>
>> javaToSQLNode = newInvocation()
>> + {
>> + objArr = optionalTableClauses();
>> + correlationName =
>> (String)objArr[OPTIONAL_TABLE_CLAUSES_CORRELATION_NAME];
>> + }
>> [ whereToken = <WHERE> whereClause = whereClause(whereToken) ]
>> {
>> fromTable = (FromTable) nodeFactory.getNode(
>> C_NodeTypes.FROM_VTI,
>> javaToSQLNode.getJavaValueNode(),
>> - (String) null,
>> + correlationName,
>> null,
>> (Properties) null,
>> getContextManager()); @@ -2569,7
>> +2575,11 @@
>> return getDeleteNode(fromTable, tableName, whereClause);
>> }
>> |
>> - <FROM> tableName =
>> qualifiedName(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128) + <FROM>
>> tableName = qualifiedName(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128)
>> + {
>> + objArr = optionalTableClauses();
>> + correlationName =
>> (String)objArr[OPTIONAL_TABLE_CLAUSES_CORRELATION_NAME];
>> + }
>> [targetProperties = propertyList() ] [
>> whereToken = <WHERE>
>> @@ -2601,7 +2611,7 @@
>> fromTable = (FromTable) nodeFactory.getNode(
>> C_NodeTypes.FROM_BASE_TABLE,
>> tableName,
>> - null,
>> + correlationName,
>> ReuseFactory.getInteger(
>> FromBaseTable.DELETE),
>> null,
>>
>>
>> ------------------------------------------------------------------------
>>
>> *** Start: floattypes jdk1.5.0 derbyall:derbylang 2005-04-04 15:30:58
>> ***
>> 152,165d151
>> < ERROR 22003: The resulting value is outside the range for the data
>> type DECIMAL/NUMERIC(31,0).
>> < ij> select * from tmp;
>> < D < --------------------------------
>> < 100000000000000000000000000000 < ij> drop table tmp;
>> < 0 rows inserted/updated/deleted
>> < ij> -- try inserting various types into decimal.
>> < -- we expect silent truncation of the fraction
>> < drop table tmp;
>> < ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TMP' because it
>> does not exist.
>> < ij> create table tmp (d decimal(5,2));
>> < 0 rows inserted/updated/deleted
>> < ij> insert into tmp values (100);
>> 166a153,167
>>
>>
>>> ij> select * from tmp;
>>> D --------------------------------
>>> 100000000000000000000000000000 1797690000000000000000000000000&
>>> ij> drop table tmp;
>>> 0 rows inserted/updated/deleted
>>> ij> -- try inserting various types into decimal.
>>> -- we expect silent truncation of the fraction
>>> drop table tmp;
>>> ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TMP' because it
>>> does not exist.
>>> ij> create table tmp (d decimal(5,2));
>>> 0 rows inserted/updated/deleted
>>> ij> insert into tmp values (100);
>>> 1 row inserted/updated/deleted
>>>
>>
>> Test Failed.
>> *** End: floattypes jdk1.5.0 derbyall:derbylang 2005-04-04 15:31:41
>> ***
>>
>>
>> ------------------------------------------------------------------------
>>
>> *** Start: refActions1 jdk1.5.0 derbyall:derbylang 2005-04-04
>> 15:53:53 ***
>> 6009 del
>> < ERROR 42X01: Syntax error: Encountered "d" at line 1, column 26.
>> 6009a6009
>>
>>
>>> ERROR 42X04: Column 'DB2TEST.DEPT.DNO' is not in any table in the
>>> FROM list or it appears within a join specification and is outside
>>> the scope of the join specification or it appears in a HAVING clause
>>> and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE
>>> statement then 'DB2TEST.DEPT.DNO' is not a column in the target table.
>>>
>>
>> 7588 del
>> < ERROR 42X01: Syntax error: Encountered "e" at line 1, column 25.
>> 7588a7588
>>
>>
>>> ERROR 42X04: Column 'DB2TEST.EMP.NAME' is not in any table in the
>>> FROM list or it appears within a join specification and is outside
>>> the scope of the join specification or it appears in a HAVING clause
>>> and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE
>>> statement then 'DB2TEST.EMP.NAME' is not a column in the target table.
>>>
>>
>> 7756 del
>> < ERROR 42X01: Syntax error: Encountered "e" at line 1, column 25.
>> 7756a7756
>>
>>
>>> ERROR 42X04: Column 'DB2TEST.EMP.NAME' is not in any table in the
>>> FROM list or it appears within a join specification and is outside
>>> the scope of the join specification or it appears in a HAVING clause
>>> and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE
>>> statement then 'DB2TEST.EMP.NAME' is not a column in the target table.
>>>
>>
>> Test Failed.
>> *** End: refActions1 jdk1.5.0 derbyall:derbylang 2005-04-04
>> 15:57:14 ***
>>
>>
Re: [jira] Commented: (DERBY-156) Delete with alias on column fails
Posted by Shreyas Kaushik <Sh...@Sun.COM>.
Did any1 have a chance to look at this?
~ Shreyas
Shreyas Kaushik wrote:
> I did some changes to sqlgrammar.jj file to make a fix for this.
> With the patch , the following cases are taken care :
> 1) delete from <tablename> <correlationName> where
> <correlatioName>.<columnName> = <value>
> 2) delete from <tablename> AS <correlationName> where
> <correlatioName>.<columnName> = <value>
>
> But when I ran derbyall I had a couple of failures.
> I am attaching the test failure diffs here as well.
>
> Please comment.
>
> ~ Shreyas
>
> Shreyas Kaushik wrote:
>
>> As this is a new feature and also involves changes the way SQL
>> commands for delete are parsed ( we need to support both with and
>> without AS keyword ) can someone give some pointers for starting. I
>> have previously worked in solving some correlation name bugs for
>> Derby , this would be a good one to dive in more deep.
>>
>> ~ Shreyas
>>
>> Shreyas Kaushik wrote:
>>
>>> Yes, this definitely a good feature add.
>>>
>>> thanks
>>> Shreyas
>>>
>>> Jack Klebanoff (JIRA) wrote:
>>>
>>>> [
>>>> http://issues.apache.org/jira/browse/DERBY-156?page=comments#action_60015
>>>> ]
>>>> Jack Klebanoff commented on DERBY-156:
>>>> --------------------------------------
>>>>
>>>> The Derby reference manual does not document the use of correlation
>>>> names in DELETE statements, so this is a feature request not a bug.
>>>>
>>>> The SQL 2003 spec does allow correlation names in delete
>>>> statements, so this is a very reasonable request.
>>>>
>>>> SQL allows an optional 'AS' in front of the correlation name:
>>>>
>>>> <delete statement: searched> ::=
>>>> DELETE FROM <target table> [ [ AS ] <correlation name> ]
>>>> [ WHERE <search condition> ]
>>>>
>>>> If we do add this to Derby we should allow the optional "AS".
>>>>
>>>>
>>>>
>>>>> Delete with alias on column fails
>>>>> ---------------------------------
>>>>>
>>>>> Key: DERBY-156
>>>>> URL: http://issues.apache.org/jira/browse/DERBY-156
>>>>> Project: Derby
>>>>> Type: Bug
>>>>> Reporter: Bernd Ruehlicke
>>>>> Priority: Critical
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>> DELETE FROM MY_TABLE x WHERE x.MY_COLUMN='value';
>>>>> fails with ERROR 42X01: Syntax error: Encountered "x" at line 1,
>>>>> column 24
>>>>> This is the core of the problem. I found it form a more
>>>>> complicated statement but it cooks down to that this should work
>>>>> but dose not.
>>>>> B-)
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>
>>
>------------------------------------------------------------------------
>
>Index: java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
>===================================================================
>--- java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (revision 159559)
>+++ java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (working copy)
>@@ -2552,16 +2552,22 @@
> QueryTreeNode retval;
> Properties targetProperties = null;
> Token whereToken = null;
>+ String correlationName = null;
>+ Object []objArr = null;
> }
> {
> LOOKAHEAD( { fromNewInvocationFollows() } )
> <FROM> javaToSQLNode = newInvocation()
>+ {
>+ objArr = optionalTableClauses();
>+ correlationName = (String)objArr[OPTIONAL_TABLE_CLAUSES_CORRELATION_NAME];
>+ }
> [ whereToken = <WHERE> whereClause = whereClause(whereToken) ]
> {
> fromTable = (FromTable) nodeFactory.getNode(
> C_NodeTypes.FROM_VTI,
> javaToSQLNode.getJavaValueNode(),
>- (String) null,
>+ correlationName,
> null,
> (Properties) null,
> getContextManager());
>@@ -2569,7 +2575,11 @@
> return getDeleteNode(fromTable, tableName, whereClause);
> }
> |
>- <FROM> tableName = qualifiedName(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128)
>+ <FROM> tableName = qualifiedName(DB2Limit.DB2_MAX_IDENTIFIER_LENGTH128)
>+ {
>+ objArr = optionalTableClauses();
>+ correlationName = (String)objArr[OPTIONAL_TABLE_CLAUSES_CORRELATION_NAME];
>+ }
> [targetProperties = propertyList() ]
> [
> whereToken = <WHERE>
>@@ -2601,7 +2611,7 @@
> fromTable = (FromTable) nodeFactory.getNode(
> C_NodeTypes.FROM_BASE_TABLE,
> tableName,
>- null,
>+ correlationName,
> ReuseFactory.getInteger(
> FromBaseTable.DELETE),
> null,
>
>
>------------------------------------------------------------------------
>
>*** Start: floattypes jdk1.5.0 derbyall:derbylang 2005-04-04 15:30:58 ***
>152,165d151
>< ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(31,0).
>< ij> select * from tmp;
>< D
>< --------------------------------
>< 100000000000000000000000000000
>< ij> drop table tmp;
>< 0 rows inserted/updated/deleted
>< ij> -- try inserting various types into decimal.
>< -- we expect silent truncation of the fraction
>< drop table tmp;
>< ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TMP' because it does not exist.
>< ij> create table tmp (d decimal(5,2));
>< 0 rows inserted/updated/deleted
>< ij> insert into tmp values (100);
>166a153,167
>
>
>>ij> select * from tmp;
>>D
>>--------------------------------
>>100000000000000000000000000000
>>1797690000000000000000000000000&
>>ij> drop table tmp;
>>0 rows inserted/updated/deleted
>>ij> -- try inserting various types into decimal.
>>-- we expect silent truncation of the fraction
>>drop table tmp;
>>ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TMP' because it does not exist.
>>ij> create table tmp (d decimal(5,2));
>>0 rows inserted/updated/deleted
>>ij> insert into tmp values (100);
>>1 row inserted/updated/deleted
>>
>>
>Test Failed.
>*** End: floattypes jdk1.5.0 derbyall:derbylang 2005-04-04 15:31:41 ***
>
>
>------------------------------------------------------------------------
>
>*** Start: refActions1 jdk1.5.0 derbyall:derbylang 2005-04-04 15:53:53 ***
>6009 del
>< ERROR 42X01: Syntax error: Encountered "d" at line 1, column 26.
>6009a6009
>
>
>>ERROR 42X04: Column 'DB2TEST.DEPT.DNO' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'DB2TEST.DEPT.DNO' is not a column in the target table.
>>
>>
>7588 del
>< ERROR 42X01: Syntax error: Encountered "e" at line 1, column 25.
>7588a7588
>
>
>>ERROR 42X04: Column 'DB2TEST.EMP.NAME' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'DB2TEST.EMP.NAME' is not a column in the target table.
>>
>>
>7756 del
>< ERROR 42X01: Syntax error: Encountered "e" at line 1, column 25.
>7756a7756
>
>
>>ERROR 42X04: Column 'DB2TEST.EMP.NAME' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'DB2TEST.EMP.NAME' is not a column in the target table.
>>
>>
>Test Failed.
>*** End: refActions1 jdk1.5.0 derbyall:derbylang 2005-04-04 15:57:14 ***
>
>
Re: [jira] Commented: (DERBY-156) Delete with alias on column fails
Posted by Shreyas Kaushik <Sh...@Sun.COM>.
I did some changes to sqlgrammar.jj file to make a fix for this.
With the patch , the following cases are taken care :
1) delete from <tablename> <correlationName> where
<correlatioName>.<columnName> = <value>
2) delete from <tablename> AS <correlationName> where
<correlatioName>.<columnName> = <value>
But when I ran derbyall I had a couple of failures.
I am attaching the test failure diffs here as well.
Please comment.
~ Shreyas
Shreyas Kaushik wrote:
> As this is a new feature and also involves changes the way SQL
> commands for delete are parsed ( we need to support both with and
> without AS keyword ) can someone give some pointers for starting. I
> have previously worked in solving some correlation name bugs for Derby
> , this would be a good one to dive in more deep.
>
> ~ Shreyas
>
> Shreyas Kaushik wrote:
>
>> Yes, this definitely a good feature add.
>>
>> thanks
>> Shreyas
>>
>> Jack Klebanoff (JIRA) wrote:
>>
>>> [
>>> http://issues.apache.org/jira/browse/DERBY-156?page=comments#action_60015
>>> ]
>>> Jack Klebanoff commented on DERBY-156:
>>> --------------------------------------
>>>
>>> The Derby reference manual does not document the use of correlation
>>> names in DELETE statements, so this is a feature request not a bug.
>>>
>>> The SQL 2003 spec does allow correlation names in delete statements,
>>> so this is a very reasonable request.
>>>
>>> SQL allows an optional 'AS' in front of the correlation name:
>>>
>>> <delete statement: searched> ::=
>>> DELETE FROM <target table> [ [ AS ] <correlation name> ]
>>> [ WHERE <search condition> ]
>>>
>>> If we do add this to Derby we should allow the optional "AS".
>>>
>>>
>>>
>>>> Delete with alias on column fails
>>>> ---------------------------------
>>>>
>>>> Key: DERBY-156
>>>> URL: http://issues.apache.org/jira/browse/DERBY-156
>>>> Project: Derby
>>>> Type: Bug
>>>> Reporter: Bernd Ruehlicke
>>>> Priority: Critical
>>>>
>>>
>>>
>>>
>>>
>>>
>>>> DELETE FROM MY_TABLE x WHERE x.MY_COLUMN='value';
>>>> fails with ERROR 42X01: Syntax error: Encountered "x" at line 1,
>>>> column 24
>>>> This is the core of the problem. I found it form a more complicated
>>>> statement but it cooks down to that this should work but dose not.
>>>> B-)
>>>>
>>>
>>>
>>>
>>>
>>>
>
>
Re: [jira] Commented: (DERBY-156) Delete with alias on column fails
Posted by Shreyas Kaushik <Sh...@Sun.COM>.
As this is a new feature and also involves changes the way SQL commands
for delete are parsed ( we need to support both with and without AS
keyword ) can someone give some pointers for starting. I have previously
worked in solving some correlation name bugs for Derby , this would be a
good one to dive in more deep.
~ Shreyas
Shreyas Kaushik wrote:
> Yes, this definitely a good feature add.
>
> thanks
> Shreyas
>
> Jack Klebanoff (JIRA) wrote:
>
>> [
>> http://issues.apache.org/jira/browse/DERBY-156?page=comments#action_60015
>> ]
>> Jack Klebanoff commented on DERBY-156:
>> --------------------------------------
>>
>> The Derby reference manual does not document the use of correlation
>> names in DELETE statements, so this is a feature request not a bug.
>>
>> The SQL 2003 spec does allow correlation names in delete statements,
>> so this is a very reasonable request.
>>
>> SQL allows an optional 'AS' in front of the correlation name:
>>
>> <delete statement: searched> ::=
>> DELETE FROM <target table> [ [ AS ] <correlation name> ]
>> [ WHERE <search condition> ]
>>
>> If we do add this to Derby we should allow the optional "AS".
>>
>>
>>
>>> Delete with alias on column fails
>>> ---------------------------------
>>>
>>> Key: DERBY-156
>>> URL: http://issues.apache.org/jira/browse/DERBY-156
>>> Project: Derby
>>> Type: Bug
>>> Reporter: Bernd Ruehlicke
>>> Priority: Critical
>>>
>>
>>
>>
>>
>>> DELETE FROM MY_TABLE x WHERE x.MY_COLUMN='value';
>>> fails with ERROR 42X01: Syntax error: Encountered "x" at line 1,
>>> column 24
>>> This is the core of the problem. I found it form a more complicated
>>> statement but it cooks down to that this should work but dose not.
>>> B-)
>>>
>>
>>
>>
>>
Re: [jira] Commented: (DERBY-156) Delete with alias on column fails
Posted by Shreyas Kaushik <Sh...@Sun.COM>.
Yes, this definitely a good feature add.
thanks
Shreyas
Jack Klebanoff (JIRA) wrote:
> [ http://issues.apache.org/jira/browse/DERBY-156?page=comments#action_60015 ]
>
>Jack Klebanoff commented on DERBY-156:
>--------------------------------------
>
>The Derby reference manual does not document the use of correlation names in DELETE statements, so this is a feature request not a bug.
>
>The SQL 2003 spec does allow correlation names in delete statements, so this is a very reasonable request.
>
>SQL allows an optional 'AS' in front of the correlation name:
>
><delete statement: searched> ::=
>DELETE FROM <target table> [ [ AS ] <correlation name> ]
>[ WHERE <search condition> ]
>
>If we do add this to Derby we should allow the optional "AS".
>
>
>
>>Delete with alias on column fails
>>---------------------------------
>>
>> Key: DERBY-156
>> URL: http://issues.apache.org/jira/browse/DERBY-156
>> Project: Derby
>> Type: Bug
>> Reporter: Bernd Ruehlicke
>> Priority: Critical
>>
>>
>
>
>
>>DELETE FROM MY_TABLE x WHERE x.MY_COLUMN='value';
>>fails with
>>ERROR 42X01: Syntax error: Encountered "x" at line 1, column 24
>>This is the core of the problem. I found it form a more complicated statement but it cooks down to that this should work but dose not.
>>B-)
>>
>>
>
>
>
[jira] Commented: (DERBY-156) Delete with alias on column fails
Posted by "Jack Klebanoff (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-156?page=comments#action_60015 ]
Jack Klebanoff commented on DERBY-156:
--------------------------------------
The Derby reference manual does not document the use of correlation names in DELETE statements, so this is a feature request not a bug.
The SQL 2003 spec does allow correlation names in delete statements, so this is a very reasonable request.
SQL allows an optional 'AS' in front of the correlation name:
<delete statement: searched> ::=
DELETE FROM <target table> [ [ AS ] <correlation name> ]
[ WHERE <search condition> ]
If we do add this to Derby we should allow the optional "AS".
> Delete with alias on column fails
> ---------------------------------
>
> Key: DERBY-156
> URL: http://issues.apache.org/jira/browse/DERBY-156
> Project: Derby
> Type: Bug
> Reporter: Bernd Ruehlicke
> Priority: Critical
>
> DELETE FROM MY_TABLE x WHERE x.MY_COLUMN='value';
> fails with
> ERROR 42X01: Syntax error: Encountered "x" at line 1, column 24
> This is the core of the problem. I found it form a more complicated statement but it cooks down to that this should work but dose not.
> B-)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-156) Delete with alias on column fails
Posted by "Bernd Ruehlicke (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-156?page=comments#action_60025 ]
Bernd Ruehlicke commented on DERBY-156:
---------------------------------------
Yes, I agree - must be seen as a new feature.
I got into the trouble when having a (generated) statement like
DELETE FROM myTable x WHERE NOT EXISTS (SELECT * FROM mySecondTable y WHERE y.Col1 = x.Coln)
I may get around this by decomposing this statement.
> Delete with alias on column fails
> ---------------------------------
>
> Key: DERBY-156
> URL: http://issues.apache.org/jira/browse/DERBY-156
> Project: Derby
> Type: Bug
> Reporter: Bernd Ruehlicke
> Priority: Critical
>
> DELETE FROM MY_TABLE x WHERE x.MY_COLUMN='value';
> fails with
> ERROR 42X01: Syntax error: Encountered "x" at line 1, column 24
> This is the core of the problem. I found it form a more complicated statement but it cooks down to that this should work but dose not.
> B-)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-156) Delete with alias on column fails
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-156?page=all ]
Satheesh Bandaram updated DERBY-156:
------------------------------------
type: New Feature (was: Bug)
Component: SQL
This is an Enhancement request.
> Delete with alias on column fails
> ---------------------------------
>
> Key: DERBY-156
> URL: http://issues.apache.org/jira/browse/DERBY-156
> Project: Derby
> Type: New Feature
> Components: SQL
> Reporter: Bernd Ruehlicke
> Priority: Critical
>
> DELETE FROM MY_TABLE x WHERE x.MY_COLUMN='value';
> fails with
> ERROR 42X01: Syntax error: Encountered "x" at line 1, column 24
> This is the core of the problem. I found it form a more complicated statement but it cooks down to that this should work but dose not.
> B-)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Resolved: (DERBY-156) Delete with alias on column fails
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-156?page=all ]
Rick Hillegas resolved DERBY-156:
---------------------------------
Resolution: Fixed
This is fixed by submission 231366, which also resolved the linked bug 171.
> Delete with alias on column fails
> ---------------------------------
>
> Key: DERBY-156
> URL: http://issues.apache.org/jira/browse/DERBY-156
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.0.2.0, 10.0.2.1, 10.0.2.2, 10.2.0.0, 10.1.1.0
> Reporter: Bernd Ruehlicke
> Priority: Critical
> Fix For: 10.2.0.0
>
> DELETE FROM MY_TABLE x WHERE x.MY_COLUMN='value';
> fails with
> ERROR 42X01: Syntax error: Encountered "x" at line 1, column 24
> This is the core of the problem. I found it form a more complicated statement but it cooks down to that this should work but dose not.
> B-)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira