You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Robert Enyedi <ro...@intland.com> on 2006/08/28 17:15:11 UTC
Alias referencing from the HAVING clause
Hi,
I'm trying to write a simple query in Derby which looks the following way:
SELECT SUM(PROD.visible) AS visible_val
FROM products AS PROD
HAVING visible_val > 0
However I receive an error from Derby:
/ERROR 42X04: Column 'VISIBLE_VAL' is either not in any table in the
FROM list or appears within a join specification and is outside the
scope of the join specification or appears in a HAVING clause and is not
in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then
'VISIBLE_VAL' is not a column in the target table./
Rewriting the query this way produces the desired result:
SELECT SUM(PROD.visible)
FROM products AS PROD
HAVING SUM(PROD.visible) > 0
but I find this variant to be hard to read and unmaintainable in a large
query.
Isn't it possible to reference an alias from inside the HAVING clause?
The documentation does not touch this issue. Or is there a more
maintainable way to rewrite the erroneous query?
Regards,
Robert
Re: Alias referencing from the HAVING clause
Posted by Robert Enyedi <ro...@intland.com>.
Thanks for the info. Right now I prefer to work with the latest stable
release.
For now I will avoid this problem by duplicating the expression
referenced by the alias, but I'll be watching the DERBY-883
incorporation process into the 10.2 release.
Thanks,
Robert
Daniel John Debrunner wrote:
> Stanley Bradbury wrote:
>
>> Robert Enyedi wrote:
>>
>>
>
>
>>> Isn't it possible to reference an alias from inside the HAVING clause?
>>> The documentation does not touch this issue. Or is there a more
>>> maintainable way to rewrite the erroneous query?
>>>
>>> Regards,
>>> Robert
>>>
>> It looks to me like the fix for DERBY-883 might address this problem.
>> You can download the beta version of 10.2 and test it, if you do so
>> please post your results to this thread as feedback on the 10.2 feature.
>>
>
> The fix for DERBY-883 is not in the 10.2 beta yet. Hopefully it will be
> in the next 10.2 snapshot, beta or release candidate.
>
> You can try it out by downloading the trunk source and building it.
>
> Dan.
>
>
>
>
Re: Alias referencing from the HAVING clause
Posted by Daniel John Debrunner <dj...@apache.org>.
Stanley Bradbury wrote:
> Robert Enyedi wrote:
>
>> Isn't it possible to reference an alias from inside the HAVING clause?
>> The documentation does not touch this issue. Or is there a more
>> maintainable way to rewrite the erroneous query?
>>
>> Regards,
>> Robert
>
> It looks to me like the fix for DERBY-883 might address this problem.
> You can download the beta version of 10.2 and test it, if you do so
> please post your results to this thread as feedback on the 10.2 feature.
The fix for DERBY-883 is not in the 10.2 beta yet. Hopefully it will be
in the next 10.2 snapshot, beta or release candidate.
You can try it out by downloading the trunk source and building it.
Dan.
Re: SQL Parser failing on NULL column contraint
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
I can't see any reason why anyone would spend time on this issue.
1) "NULL" is not a constraint. All columns are nullable by default
(SQL standard and in all SQL databases to my knowledge).
2) It will not add any new functionality.
3) You will not gain anything in speed, resource usage etc.
4) By removing the "NULL constraint" from your create statements, they
will work with Derby and be portable back to the database that
allowed them (if it is a reasonable implementation of SQL).
5) The SQL standard does not allow this syntax, so by incorporating it
into Derby we run the risk that some uses it and creates SQL
statements that are not portable to other databases.
So, I wonder: Why bother?
Duncan Groenewald wrote:
> Thanks - is there any documentation on how the parser works and
> explaining what is the sqlgrammer.jj file is and how is it used, etc.
>
> Duncan
>
> Bryan Pendleton wrote:
>> Duncan Groenewald wrote:
>>> Any chance someone can explain how I could modify the parser (or
>>> whatever) to be able to handle the NULL constraint ?
>>
>> Beware: I haven't tried this, but you could have a look at
>> java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj and
>> experimentally try to modify it for yourself.
>>
>> In terms of the modifications, I think you'll want to look at the
>> subroutine columnConstraint(), and observe how the explicitNotNull
>> and explicitNull flags are handled.
>>
>> Notice that there is an explicit parser block for
>>
>> <NOT> <NULL>
>>
>> I think you'll either want to add a second explicit parser block for
>>
>> <NULL>
>>
>> or modify the existing one to
>>
>> [<NOT>] <NULL>
>>
>> and either way you want to make sure that you manage the explicitNull
>> and explicitNotNull flags properly, and that you call setNullability()
>> on the dataTypeDescriptor to record the user's NULL / NOT NULL choice.
>>
>> Hope this helps,
>>
>> bryan
>>
>>
>>
>
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: SQL Parser failing on NULL column contraint
Posted by Duncan Groenewald <du...@xtra.co.nz>.
Thanks, you've been most helpful. I'll switch to the standard and change
the sybase config in future...
I guess I'll never get to figure out how a sql parser works now.
Duncan
Bernt M. Johnsen wrote:
> Duncan Groenewald wrote:
>
>> Point 5 is the reason, one of the databases I use does not handle no
>> definition and requires a NULL or NOT NULL definition. But its been a
>> long time since I checked this so the newer version may be more
>> compliant. I'll check before I waste any more time on this.
>>
>> Interestingly derby is the first database I have encountered(I think)
>> that throws an error on a NULL definition.
>>
>
> I did a brief survey on the net, and found that MySQL, Sybase,
> PostgreSQL, HSQLDB, H2, Oracle and SQLServer allows the user to
> specify the non-standard "constraint" NULL. PostgreSQL has the most
> sensible note in the docs:
>
> "This clause is only available for compatibility with non-standard SQL
> databases. Its use is discouraged in new applications."
>
> DB2 and Derby does not allow NULL.
>
> And Sybase defaults to NOT NULL (in default configuration and as you
> point out in another mail) which is definitely against the SQL
> standard, and that means that portable CREATE TABLE statements will
> not work on Sybase (even if they do syntactically).
>
> And, you can't write SQL which is compatible between Sybase (in
> default configuration) and Derby (or DB2 for that matter).
>
>
Re: SQL Parser failing on NULL column contraint
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
Duncan Groenewald wrote:
> Point 5 is the reason, one of the databases I use does not handle no
> definition and requires a NULL or NOT NULL definition. But its been a
> long time since I checked this so the newer version may be more
> compliant. I'll check before I waste any more time on this.
>
> Interestingly derby is the first database I have encountered(I think)
> that throws an error on a NULL definition.
I did a brief survey on the net, and found that MySQL, Sybase,
PostgreSQL, HSQLDB, H2, Oracle and SQLServer allows the user to
specify the non-standard "constraint" NULL. PostgreSQL has the most
sensible note in the docs:
"This clause is only available for compatibility with non-standard SQL
databases. Its use is discouraged in new applications."
DB2 and Derby does not allow NULL.
And Sybase defaults to NOT NULL (in default configuration and as you
point out in another mail) which is definitely against the SQL
standard, and that means that portable CREATE TABLE statements will
not work on Sybase (even if they do syntactically).
And, you can't write SQL which is compatible between Sybase (in
default configuration) and Derby (or DB2 for that matter).
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: SQL Parser failing on NULL column contraint
Posted by Duncan Groenewald <du...@xtra.co.nz>.
Point 5 is the reason, one of the databases I use does not handle no
definition and requires a NULL or NOT NULL definition. But its been a
long time since I checked this so the newer version may be more
compliant. I'll check before I waste any more time on this.
Interestingly derby is the first database I have encountered(I think)
that throws an error on a NULL definition.
Thanks
Bernt M. Johnsen wrote:
> I can't see any reason why anyone would spend time on this issue.
>
> 1) "NULL" is not a constraint. All columns are nullable by default
> (SQL standard and in all SQL databases to my knowledge).
>
> 2) It will not add any new functionality.
>
> 3) You will not gain anything in speed, resource usage etc.
>
> 4) By removing the "NULL constraint" from your create statements, they
> will work with Derby and be portable back to the database that
> allowed them (if it is a reasonable implementation of SQL).
>
> 5) The SQL standard does not allow this syntax, so by incorporating it
> into Derby we run the risk that some uses it and creates SQL
> statements that are not portable to other databases.
>
> So, I wonder: Why bother?
>
> Duncan Groenewald wrote:
>
>> Thanks - is there any documentation on how the parser works and
>> explaining what is the sqlgrammer.jj file is and how is it used, etc.
>>
>> Duncan
>>
>> Bryan Pendleton wrote:
>>
>>> Duncan Groenewald wrote:
>>>
>>>> Any chance someone can explain how I could modify the parser (or
>>>> whatever) to be able to handle the NULL constraint ?
>>>>
>>> Beware: I haven't tried this, but you could have a look at
>>> java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj and
>>> experimentally try to modify it for yourself.
>>>
>>> In terms of the modifications, I think you'll want to look at the
>>> subroutine columnConstraint(), and observe how the explicitNotNull
>>> and explicitNull flags are handled.
>>>
>>> Notice that there is an explicit parser block for
>>>
>>> <NOT> <NULL>
>>>
>>> I think you'll either want to add a second explicit parser block for
>>>
>>> <NULL>
>>>
>>> or modify the existing one to
>>>
>>> [<NOT>] <NULL>
>>>
>>> and either way you want to make sure that you manage the explicitNull
>>> and explicitNotNull flags properly, and that you call setNullability()
>>> on the dataTypeDescriptor to record the user's NULL / NOT NULL choice.
>>>
>>> Hope this helps,
>>>
>>> bryan
>>>
>>>
>>>
>>>
>
>
>
Re: SQL Parser failing on NULL column contraint
Posted by Duncan Groenewald <du...@xtra.co.nz>.
I just checked - Sybase defaults to NOT NULL but this can be changed as
a database config option.
Thanks
(thinks to self "duh")
Bernt M. Johnsen wrote:
> I can't see any reason why anyone would spend time on this issue.
>
> 1) "NULL" is not a constraint. All columns are nullable by default
> (SQL standard and in all SQL databases to my knowledge).
>
> 2) It will not add any new functionality.
>
> 3) You will not gain anything in speed, resource usage etc.
>
> 4) By removing the "NULL constraint" from your create statements, they
> will work with Derby and be portable back to the database that
> allowed them (if it is a reasonable implementation of SQL).
>
> 5) The SQL standard does not allow this syntax, so by incorporating it
> into Derby we run the risk that some uses it and creates SQL
> statements that are not portable to other databases.
>
> So, I wonder: Why bother?
>
> Duncan Groenewald wrote:
>
>> Thanks - is there any documentation on how the parser works and
>> explaining what is the sqlgrammer.jj file is and how is it used, etc.
>>
>> Duncan
>>
>> Bryan Pendleton wrote:
>>
>>> Duncan Groenewald wrote:
>>>
>>>> Any chance someone can explain how I could modify the parser (or
>>>> whatever) to be able to handle the NULL constraint ?
>>>>
>>> Beware: I haven't tried this, but you could have a look at
>>> java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj and
>>> experimentally try to modify it for yourself.
>>>
>>> In terms of the modifications, I think you'll want to look at the
>>> subroutine columnConstraint(), and observe how the explicitNotNull
>>> and explicitNull flags are handled.
>>>
>>> Notice that there is an explicit parser block for
>>>
>>> <NOT> <NULL>
>>>
>>> I think you'll either want to add a second explicit parser block for
>>>
>>> <NULL>
>>>
>>> or modify the existing one to
>>>
>>> [<NOT>] <NULL>
>>>
>>> and either way you want to make sure that you manage the explicitNull
>>> and explicitNotNull flags properly, and that you call setNullability()
>>> on the dataTypeDescriptor to record the user's NULL / NOT NULL choice.
>>>
>>> Hope this helps,
>>>
>>> bryan
>>>
>>>
>>>
>>>
>
>
>
Re: SQL Parser failing on NULL column contraint
Posted by Duncan Groenewald <du...@xtra.co.nz>.
Thanks - is there any documentation on how the parser works and
explaining what is the sqlgrammer.jj file is and how is it used, etc.
Duncan
Bryan Pendleton wrote:
> Duncan Groenewald wrote:
>> Any chance someone can explain how I could modify the parser (or
>> whatever) to be able to handle the NULL constraint ?
>
> Beware: I haven't tried this, but you could have a look at
> java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj and
> experimentally try to modify it for yourself.
>
> In terms of the modifications, I think you'll want to look at the
> subroutine columnConstraint(), and observe how the explicitNotNull
> and explicitNull flags are handled.
>
> Notice that there is an explicit parser block for
>
> <NOT> <NULL>
>
> I think you'll either want to add a second explicit parser block for
>
> <NULL>
>
> or modify the existing one to
>
> [<NOT>] <NULL>
>
> and either way you want to make sure that you manage the explicitNull
> and explicitNotNull flags properly, and that you call setNullability()
> on the dataTypeDescriptor to record the user's NULL / NOT NULL choice.
>
> Hope this helps,
>
> bryan
>
>
>
Re: SQL Parser failing on NULL column contraint
Posted by Bryan Pendleton <bp...@amberpoint.com>.
Duncan Groenewald wrote:
> Any chance someone can explain how I could modify the parser (or
> whatever) to be able to handle the NULL constraint ?
Beware: I haven't tried this, but you could have a look at
java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj and
experimentally try to modify it for yourself.
In terms of the modifications, I think you'll want to look at the
subroutine columnConstraint(), and observe how the explicitNotNull
and explicitNull flags are handled.
Notice that there is an explicit parser block for
<NOT> <NULL>
I think you'll either want to add a second explicit parser block for
<NULL>
or modify the existing one to
[<NOT>] <NULL>
and either way you want to make sure that you manage the explicitNull
and explicitNotNull flags properly, and that you call setNullability()
on the dataTypeDescriptor to record the user's NULL / NOT NULL choice.
Hope this helps,
bryan
Re: SQL Parser failing on NULL column contraint
Posted by Duncan Groenewald <du...@xtra.co.nz>.
Can someone confirm that the following statement will fail because Derby
can't handle a NULL constraint
create table SampleTable(
code int NOT NULL,
description varchar(32) NOT NULL,
address varchar(256) NULL,
phone varchar(32) NULL
)
whereas this will work fine
create table SampleTable(
code int NOT NULL,
description varchar(32) NOT NULL,
address varchar(256),
phone varchar(32)
)
Any chance someone can explain how I could modify the parser (or
whatever) to be able to handle the NULL constraint ?
Thanks
Duncan
Re: Alias referencing from the HAVING clause
Posted by Manish Khettry <ma...@gmail.com>.
Derby-883 allows expressions in the group by list whereas this has to do
with how Derby binds expressions in group by/having queries. Another similar
issue is Derby-1624.
Manish
On 8/28/06, Stanley Bradbury <St...@gmail.com> wrote:
>
> Robert Enyedi wrote:
> > Hi,
> >
> > I'm trying to write a simple query in Derby which looks the following
> way:
> >
> > SELECT SUM(PROD.visible) AS visible_val
> > FROM products AS PROD
> > HAVING visible_val > 0
> >
> > However I receive an error from Derby:
> >
> > /ERROR 42X04: Column 'VISIBLE_VAL' is either not in any table in the
> > FROM list or appears within a join specification and is outside the
> > scope of the join specification or appears in a HAVING clause and is
> > not in the GROUP BY list. If this is a CREATE or ALTER TABLE
> > statement then 'VISIBLE_VAL' is not a column in the target table./
> >
> > Rewriting the query this way produces the desired result:
> >
> > SELECT SUM(PROD.visible)
> > FROM products AS PROD
> > HAVING SUM(PROD.visible) > 0
> >
> > but I find this variant to be hard to read and unmaintainable in a
> > large query.
> >
> > Isn't it possible to reference an alias from inside the HAVING clause?
> > The documentation does not touch this issue. Or is there a more
> > maintainable way to rewrite the erroneous query?
> >
> > Regards,
> > Robert
> It looks to me like the fix for DERBY-883 might address this problem.
> You can download the beta version of 10.2 and test it, if you do so
> please post your results to this thread as feedback on the 10.2 feature.
>
> The issue can be viewed at:
> http://issues.apache.org/jira/browse/DERBY-883
>
> HTH
>
>
>
>
Re: Alias referencing from the HAVING clause
Posted by Stanley Bradbury <St...@gmail.com>.
Robert Enyedi wrote:
> Hi,
>
> I'm trying to write a simple query in Derby which looks the following way:
>
> SELECT SUM(PROD.visible) AS visible_val
> FROM products AS PROD
> HAVING visible_val > 0
>
> However I receive an error from Derby:
>
> /ERROR 42X04: Column 'VISIBLE_VAL' is either not in any table in the
> FROM list or appears within a join specification and is outside the
> scope of the join specification or appears in a HAVING clause and is
> not in the GROUP BY list. If this is a CREATE or ALTER TABLE
> statement then 'VISIBLE_VAL' is not a column in the target table./
>
> Rewriting the query this way produces the desired result:
>
> SELECT SUM(PROD.visible)
> FROM products AS PROD
> HAVING SUM(PROD.visible) > 0
>
> but I find this variant to be hard to read and unmaintainable in a
> large query.
>
> Isn't it possible to reference an alias from inside the HAVING clause?
> The documentation does not touch this issue. Or is there a more
> maintainable way to rewrite the erroneous query?
>
> Regards,
> Robert
It looks to me like the fix for DERBY-883 might address this problem.
You can download the beta version of 10.2 and test it, if you do so
please post your results to this thread as feedback on the 10.2 feature.
The issue can be viewed at: http://issues.apache.org/jira/browse/DERBY-883
HTH
Re: Alias referencing from the HAVING clause
Posted by Christine Johnson <cj...@willowtech.com>.
----- Original Message -----
From: Robert Enyedi
To: Derby Discussion
Sent: Monday, August 28, 2006 8:15 AM
Subject: Alias referencing from the HAVING clause
Hi,
I'm trying to write a simple query in Derby which looks the following way:
SELECT SUM(PROD.visible) AS visible_val
FROM products AS PROD
HAVING visible_val > 0
However I receive an error from Derby:
ERROR 42X04: Column 'VISIBLE_VAL' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'VISIBLE_VAL' is not a column in the target table.
Rewriting the query this way produces the desired result:
SELECT SUM(PROD.visible)
FROM products AS PROD
HAVING SUM(PROD.visible) > 0
but I find this variant to be hard to read and unmaintainable in a large query.
Isn't it possible to reference an alias from inside the HAVING clause? The documentation does not touch this issue. Or is there a more maintainable way to rewrite the erroneous query?
Regards,
Robert
Re: Alias referencing from the HAVING clause
Posted by Robert Enyedi <ro...@intland.com>.
Chris,
Thanks anyway!
Regards,
Robert
Christine Johnson wrote:
> Sorry about the reply-free post a bit earlier.
>
> I thought adding a GROUP BY clause to the SQL might be useful, but
> tinkering shows me it's not.
>
> Regards,
> Chris Johnson
>
> ----- Original Message -----
> *From:* Robert Enyedi <ma...@intland.com>
> *To:* Derby Discussion <ma...@db.apache.org>
> *Sent:* Monday, August 28, 2006 8:15 AM
> *Subject:* Alias referencing from the HAVING clause
>
> Hi,
>
> I'm trying to write a simple query in Derby which looks the
> following way:
>
> SELECT SUM(PROD.visible) AS visible_val
> FROM products AS PROD
> HAVING visible_val > 0
>
> However I receive an error from Derby:
>
> /ERROR 42X04: Column 'VISIBLE_VAL' is either not in any table in
> the FROM list or appears within a join specification and is
> outside the scope of the join specification or appears in a HAVING
> clause and is not in the GROUP BY list. If this is a CREATE or
> ALTER TABLE statement then 'VISIBLE_VAL' is not a column in the
> target table./
>
> Rewriting the query this way produces the desired result:
>
> SELECT SUM(PROD.visible)
> FROM products AS PROD
> HAVING SUM(PROD.visible) > 0
>
> but I find this variant to be hard to read and unmaintainable in a
> large query.
>
> Isn't it possible to reference an alias from inside the HAVING
> clause? The documentation does not touch this issue. Or is there a
> more maintainable way to rewrite the erroneous query?
>
> Regards,
> Robert
>
Re: Alias referencing from the HAVING clause
Posted by Christine Johnson <cj...@willowtech.com>.
Sorry about the reply-free post a bit earlier.
I thought adding a GROUP BY clause to the SQL might be useful, but tinkering shows me it's not.
Regards,
Chris Johnson
----- Original Message -----
From: Robert Enyedi
To: Derby Discussion
Sent: Monday, August 28, 2006 8:15 AM
Subject: Alias referencing from the HAVING clause
Hi,
I'm trying to write a simple query in Derby which looks the following way:
SELECT SUM(PROD.visible) AS visible_val
FROM products AS PROD
HAVING visible_val > 0
However I receive an error from Derby:
ERROR 42X04: Column 'VISIBLE_VAL' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'VISIBLE_VAL' is not a column in the target table.
Rewriting the query this way produces the desired result:
SELECT SUM(PROD.visible)
FROM products AS PROD
HAVING SUM(PROD.visible) > 0
but I find this variant to be hard to read and unmaintainable in a large query.
Isn't it possible to reference an alias from inside the HAVING clause? The documentation does not touch this issue. Or is there a more maintainable way to rewrite the erroneous query?
Regards,
Robert