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 Duncan Groenewald <du...@xtra.co.nz> on 2006/08/30 14:42:18 UTC

Re: SQL Parser failing on NULL column contraint

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