Predicate functions

[Nicolas Fortin (OrbisGIS)]

Discussion about user defined index on OTHER type.

In spatial SQL the following request on an indexed geometry column would 
use spatial index:

select * from spatialTable where ST_Intersects(the_geom, 
> ST_PolyFromText('POLYGON ((67 13, 67 18, 59 18, 59 13, 67 13))',1));
>

Usage of ST_Intersects filter the_geom field by using BoundingBox stored 
index of the spatialTable, then the method is applied on filtered rows.

Can I do this by creating my own TableEngine ? There is an easier way 
without modifying the sql request ? Maybe a way to let special kind of 
function to alter the execution plan ?

Thanks for your support.

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Predicate functions

[Noel Grandin]

See the section title "Using a function as a table" here
http://h2database.com/html/features.html#user_defined_functions

On 2013-05-21 10:07, Nicolas Fortin (OrbisGIS) wrote:

Discussion about user defined index on OTHER type.

In spatial SQL the following request on an indexed geometry column 
would use spatial index:


select * from spatialTable where ST_Intersects(the_geom,
ST_PolyFromText('POLYGON ((67 13, 67 18, 59 18, 59 13, 67 13))',1));


Usage of ST_Intersects filter the_geom field by using BoundingBox 
stored index of the spatialTable, then the method is applied on 
filtered rows.


Can I do this by creating my own TableEngine ? There is an easier way 
without modifying the sql request ? Maybe a way to let special kind of 
function to alter the execution plan ?


Thanks for your support.

--
You received this message because you are subscribed to the Google 
Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.




--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Predicate functions

[Nicolas Fortin (OrbisGIS)]

I know function table but this lead to modifying the sql request and it is 
not in standard.

Le mardi 21 mai 2013 11:52:15 UTC+2, Noel Grandin a écrit :
>
>  See the section title "Using a function as a table" here
> http://h2database.com/html/features.html#user_defined_functions
>
> On 2013-05-21 10:07, Nicolas Fortin (OrbisGIS) wrote:
>  
> Discussion about user defined index on OTHER type.
>
> In spatial SQL the following request on an indexed geometry column would 
> use spatial index:
>
> select * from spatialTable where ST_Intersects(the_geom, 
>> ST_PolyFromText('POLYGON ((67 13, 67 18, 59 18, 59 13, 67 13))',1));
>>
>
> Usage of ST_Intersects filter the_geom field by using BoundingBox stored 
> index of the spatialTable, then the method is applied on filtered rows.
>
> Can I do this by creating my own TableEngine ? There is an easier way 
> without modifying the sql request ? Maybe a way to let special kind of 
> function to alter the execution plan ?
>
> Thanks for your support.
>  
> -- 
> You received this message because you are subscribed to the Google Groups 
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to h2-database...@googlegroups.com .
> To post to this group, send email to h2-da...@googlegroups.com
> .
> Visit this group at http://groups.google.com/group/h2-database?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>  
>  
>
>
> 

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

1.3.172 when?

[Jason Pell]

Hi,

Due to a bug in 1.3.171 with ranges I downgraded to 1.3.169.  I would love 
to upgrade to 1.3.172

when is this due to be available?

Thanks
Jason

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Predicate functions

[Noel Grandin]

I don't understand the problem?

What I'm saying is that you can define a function called ST_Intersects 
using CREATE ALIAS,

and inside that function you can implement the necessary logic.

From the perspective of a user of your extension, it should look like a 
normal part of the database.


Or you are asking about something different?

On 2013-05-21 13:27, Nicolas Fortin (OrbisGIS) wrote:
I know function table but this lead to modifying the sql request and 
it is not in standard.


Le mardi 21 mai 2013 11:52:15 UTC+2, Noel Grandin a écrit :

See the section title "Using a function as a table" here
http://h2database.com/html/features.html#user_defined_functions


On 2013-05-21 10:07, Nicolas Fortin (OrbisGIS) wrote:

Discussion about user defined index on OTHER type.

In spatial SQL the following request on an indexed geometry
column would use spatial index:

select * from spatialTable where ST_Intersects(the_geom,
ST_PolyFromText('POLYGON ((67 13, 67 18, 59 18, 59 13, 67
13))',1));


Usage of ST_Intersects filter the_geom field by using BoundingBox
stored index of the spatialTable, then the method is applied on
filtered rows.

Can I do this by creating my own TableEngine ? There is an easier
way without modifying the sql request ? Maybe a way to let
special kind of function to alter the execution plan ?

Thanks for your support.

-- 
You received this message because you are subscribed to the

Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it,
send an email to h2-database...@googlegroups.com .
To post to this group, send email to h2-da...@googlegroups.com
.
Visit this group at
http://groups.google.com/group/h2-database?hl=en
.
For more options, visit https://groups.google.com/groups/opt_out
.




--
You received this message because you are subscribed to the Google 
Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.




--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Predicate functions

[bocher]

Dear Noel,

The spatial predicates and operators  are specified in OGC standard 
specification (http://www.opengeospatial.org/standards/sfs). The 
implementation specification mentions that spatial functions must be 
applied using scalar function. 
If I'm not wrong, currently there is no possibility to deal with spatial 
index (RTree available in MVStore) while some functions need it.

Could you please take a way to manage spatial index with scalar function ?

Best regards.


Erwan Bocher

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Predicate functions

[Noel Grandin]

Hi

I don't really understand the problem here.
From the perspective of the user, what is the difference how the 
ST_Intersects function is implemented internally?


Regards, Noel.

On 2013-05-21 14:14, bocher wrote:

Dear Noel,

The spatial predicates and operators  are specified in OGC standard 
specification (http://www.opengeospatial.org/standards/sfs). The 
implementation specification mentions that spatial functions must be 
applied using scalar function.
If I'm not wrong, currently there is no possibility to deal with 
spatial index (RTree available in MVStore) while some functions need it.


Could you please take a way to manage spatial index with scalar function ?

Best regards.


Erwan Bocher
--
You received this message because you are subscribed to the Google 
Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.




--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Predicate functions

[Nicolas Fortin (OrbisGIS)]

Using the h2 table function the sql would be somewhat like this:


select * from ST_Intersects(spatialTable, 'the_geom' , 
> ST_PolyFromText('POLYGON ((67 13, 67 18, 59 18, 59 13, 67 13))',1));


Here the function is in the FROM , not in the WHERE. Using index must be 
transparent to users.

Le mardi 21 mai 2013 14:06:08 UTC+2, Noel Grandin a écrit :
>
>  I don't understand the problem?
>
> What I'm saying is that you can define a function called ST_Intersects 
> using CREATE ALIAS, 
> and inside that function you can implement the necessary logic.
>
> From the perspective of a user of your extension, it should look like a 
> normal part of the database.
>
> Or you are asking about something different?
>
> On 2013-05-21 13:27, Nicolas Fortin (OrbisGIS) wrote:
>  
> I know function table but this lead to modifying the sql request and it is 
> not in standard.
>
> Le mardi 21 mai 2013 11:52:15 UTC+2, Noel Grandin a écrit : 
>>
>>  See the section title "Using a function as a table" here
>> http://h2database.com/html/features.html#user_defined_functions
>>
>> On 2013-05-21 10:07, Nicolas Fortin (OrbisGIS) wrote:
>>  
>> Discussion about user defined index on OTHER type.
>>
>> In spatial SQL the following request on an indexed geometry column would 
>> use spatial index:
>>
>> select * from spatialTable where ST_Intersects(the_geom, 
>>> ST_PolyFromText('POLYGON ((67 13, 67 18, 59 18, 59 13, 67 13))',1));
>>>
>>
>> Usage of ST_Intersects filter the_geom field by using BoundingBox stored 
>> index of the spatialTable, then the method is applied on filtered rows.
>>
>> Can I do this by creating my own TableEngine ? There is an easier way 
>> without modifying the sql request ? Maybe a way to let special kind of 
>> function to alter the execution plan ?
>>
>> Thanks for your support.
>>  
>> -- 
>> You received this message because you are subscribed to the Google Groups 
>> "H2 Database" group.
>> To unsubscribe from this group and stop receiving emails from it, send an 
>> email to h2-database...@googlegroups.com.
>> To post to this group, send email to h2-da...@googlegroups.com.
>> Visit this group at http://groups.google.com/group/h2-database?hl=en.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>  
>>  
>>
>>
>>  -- 
> You received this message because you are subscribed to the Google Groups 
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to h2-database...@googlegroups.com .
> To post to this group, send email to h2-da...@googlegroups.com
> .
> Visit this group at http://groups.google.com/group/h2-database?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>  
>  
>
>
> 

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Predicate functions

[Noel Grandin]

Ah, my apologies, I see your problem, I'm a bit slow today.

I don't think there is any way to implement what you want without 
modifying the H2 source code.

But I don't think that is a problem.
If you want to implement a new BoundingBox index type, then we can 
include that directly into H2.
We would probably need to implement ST_Intersects as a new comparison 
type inside H2, and make it use the BoundingBox index.
I can hook up the parsing and basic infrastructure for you, but I don't 
know enough to implement a new index type.



On 2013-05-21 14:19, Nicolas Fortin (OrbisGIS) wrote:

Using the h2 table function the sql would be somewhat like this:


select * from ST_Intersects(spatialTable, 'the_geom' ,
ST_PolyFromText('POLYGON ((67 13, 67 18, 59 18, 59 13, 67 13))',1));


Here the function is in the FROM , not in the WHERE. Using index must 
be transparent to users.


Le mardi 21 mai 2013 14:06:08 UTC+2, Noel Grandin a écrit :

I don't understand the problem?

What I'm saying is that you can define a function called
ST_Intersects using CREATE ALIAS,
and inside that function you can implement the necessary logic.

From the perspective of a user of your extension, it should look
like a normal part of the database.

Or you are asking about something different?

On 2013-05-21 13:27, Nicolas Fortin (OrbisGIS) wrote:

I know function table but this lead to modifying the sql request
and it is not in standard.

Le mardi 21 mai 2013 11:52:15 UTC+2, Noel Grandin a écrit :

See the section title "Using a function as a table" here
http://h2database.com/html/features.html#user_defined_functions


On 2013-05-21 10:07, Nicolas Fortin (OrbisGIS) wrote:

Discussion about user defined index on OTHER type.

In spatial SQL the following request on an indexed geometry
column would use spatial index:

select * from spatialTable where ST_Intersects(the_geom,
ST_PolyFromText('POLYGON ((67 13, 67 18, 59 18, 59 13,
67 13))',1));


Usage of ST_Intersects filter the_geom field by using
BoundingBox stored index of the spatialTable, then the
method is applied on filtered rows.

Can I do this by creating my own TableEngine ? There is an
easier way without modifying the sql request ? Maybe a way
to let special kind of function to alter the execution plan ?

Thanks for your support.

-- 
You received this message because you are subscribed to the

Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails
from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at
http://groups.google.com/group/h2-database?hl=en
.
For more options, visit
https://groups.google.com/groups/opt_out
.




-- 
You received this message because you are subscribed to the

Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it,
send an email to h2-database...@googlegroups.com .
To post to this group, send email to h2-da...@googlegroups.com
.
Visit this group at
http://groups.google.com/group/h2-database?hl=en
.
For more options, visit https://groups.google.com/groups/opt_out
.




--
You received this message because you are subscribed to the Google 
Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.




--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: SQLState 23502

[Thomas Mueller]

Hi,

I have tested this with a few databases, and I think 23502 is the most
standard SQLState. Test case: create table test(id int, data varchar(255)
not null); insert into test values(1, null). Result:

jdbc:h2:mem:
  SQLState: 23502
  ErrorCode: 23502
  Message: NULL not allowed for column "DATA"

jdbc:hsqldb:/tmp/db/test
  SQLState: 23502
  ErrorCode: -10
  Message: integrity constraint violation: NOT NULL check constraint;
SYS_CT_10028 table: TEST column: DATA

jdbc:derby:/tmp/db/test2;create=true
  SQLState: 23502
  ErrorCode: 3
  Message: Column 'DATA'  cannot accept a NULL value.

jdbc:mysql://localhost:3306/test
  SQLState: 23000
  ErrorCode: 1048
  Message: Column 'data' cannot be null

jdbc:postgresql:test
  SQLState: 23502
  ErrorCode: 0
  Message: ERROR: null value in column "data" violates not-null constraint

Regards,
Thomas


On Tue, May 21, 2013 at 12:51 AM, Gili  wrote:
>
> Hi,
>
> I believe H2 should throw SQLState 2201C instead of 23502 when users
attempt to insert NULL into a column that disallows it.
>
> H2's documentation can be found here:
http://www.h2database.com/javadoc/org/h2/constant/ErrorCode.html#c23502
> SQLState 2201C is defined as "null row not permitted in table" by the SQL
2008 standard as found in 5CD-02-Foundation-2006-01.pdf in:
http://en.wikipedia.org/wiki/SQL#cite_note-39
>
> Gili
>
> --
> You received this message because you are subscribed to the Google Groups
"H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
email to h2-database+unsubscr...@googlegroups.com.
> To post to this group, send email to h2-database@googlegroups.com.
> Visit this group at http://groups.google.com/group/h2-database?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: 1.3.172 when?

[Thomas Mueller]

Hi,

I was hoping to get the MVStoreTableEngine to work, but I guess it's not
quite so easy.

I suggest to do a new release this weekend (unless we find urgent problems).

Regards,
Thomas



On Tue, May 21, 2013 at 1:32 PM, Jason Pell  wrote:

> Hi,
>
> Due to a bug in 1.3.171 with ranges I downgraded to 1.3.169.  I would love
> to upgrade to 1.3.172
>
> when is this due to be available?
>
> Thanks
> Jason
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to h2-database+unsubscr...@googlegroups.com.
> To post to this group, send email to h2-database@googlegroups.com.
> Visit this group at http://groups.google.com/group/h2-database?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Predicate functions

[Thomas Mueller]

Hi,

It's a good question. The optimizer currently only knows a few ways to use
an index: range conditions (<=, >=, >, <, =, between), in(list),
in(select), like (which is converted to a range condition), is null, is not
null, min(x), max(x), count(*).

I wonder if there is a "generic" way to support index usage. It's won't be
that easy I guess. For the MVStore it would be nice to have a good solution
(so that the r-tree can be used), but we might end up "hardcoding" it
within H2.

Could you tell me what are possible operations that could use a (spatial or
other) index, and how such an index could be used?

Regards,
Thomas



On Tue, May 21, 2013 at 1:27 PM, Nicolas Fortin (OrbisGIS) <
nico.de...@gmail.com> wrote:

> I know function table but this lead to modifying the sql request and it is
> not in standard.
>
> Le mardi 21 mai 2013 11:52:15 UTC+2, Noel Grandin a écrit :
>>
>>  See the section title "Using a function as a table" here
>> http://h2database.com/html/**features.html#user_defined_**functions
>>
>> On 2013-05-21 10:07, Nicolas Fortin (OrbisGIS) wrote:
>>
>> Discussion about user defined index on OTHER type.
>>
>> In spatial SQL the following request on an indexed geometry column would
>> use spatial index:
>>
>> select * from spatialTable where ST_Intersects(the_geom,
>>> ST_PolyFromText('POLYGON ((67 13, 67 18, 59 18, 59 13, 67 13))',1));
>>>
>>
>> Usage of ST_Intersects filter the_geom field by using BoundingBox stored
>> index of the spatialTable, then the method is applied on filtered rows.
>>
>> Can I do this by creating my own TableEngine ? There is an easier way
>> without modifying the sql request ? Maybe a way to let special kind of
>> function to alter the execution plan ?
>>
>> Thanks for your support.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "H2 Database" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to h2-database...@**googlegroups.com.
>> To post to this group, send email to h2-da...@googlegroups.com.
>>
>> Visit this group at 
>> http://groups.google.com/**group/h2-database?hl=en
>> .
>> For more options, visit 
>> https://groups.google.com/**groups/opt_out
>> .
>>
>>
>>
>>
>>   --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to h2-database+unsubscr...@googlegroups.com.
> To post to this group, send email to h2-database@googlegroups.com.
> Visit this group at http://groups.google.com/group/h2-database?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Predicate functions

[bocher]

Hi Thomas,

The spatial index is used to limit the number  of return tuples. It is used 
after the where condition and usually with spatial predicates 
(ST_Intersects, ST_Contains, ST_Iswithin...).

Here an example :

SELECT a.* FROM TRONCON_HYDRO a, dep_france_dom b WHERE ST_Contains(b.the_geom, 
a.the_geom) AND b."NOM_DEPT"='LOIRE-ATLANTIQUE';
Explanation  : 
http://www.orbisgis.org/documentation/user-doc/3-0-documentation/sql/examples/st_contains/

The query plan is adapted to support spatial index if the column a.the_geom or 
b.the_geom is indexed. 
The index of largest table is queried to  speed improvement of the query. In 
GDMS, the scala SQL engine of your software OrbisGIS*, 
the developer must implements a SpatialIndexedFunction. This interface is 
"casted" by the logic plan builder of our SQL engine to query the index. 
So hard-coded in SQL engine ;-(.

Moreover,  PostGIS, the spatial extension spatial for 
PostgreSQL overloads 
the && operator to use the intersects operator (the bounding boxes of 
geometries intersect). 


IE

SELECT id, the_geom 
FROM thetable 
WHERE 
  the_geom && 'POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))' ;

@Nicolas

It would be interesting to have a look at st_contains function in the POSTGIS 
code to give a a work trail to Thomas and Noel ?


Best regards

Erwan.




* 
https://github.com/irstv/orbisgis/tree/master/gdms/src/main/scala/org/gdms/sql/engine/step

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: SQLState 23502

[cowwoc]

Makes sense given this data. Thanks for the research.

Gili

On 21/05/2013 1:22 PM, Thomas Mueller wrote:

Hi,

I have tested this with a few databases, and I think 23502 is the most 
standard SQLState. Test case: create table test(id int, data 
varchar(255) not null); insert into test values(1, null). Result:


jdbc:h2:mem:
  SQLState: 23502
  ErrorCode: 23502
  Message: NULL not allowed for column "DATA"

jdbc:hsqldb:/tmp/db/test
  SQLState: 23502
  ErrorCode: -10
  Message: integrity constraint violation: NOT NULL check constraint; 
SYS_CT_10028 table: TEST column: DATA


jdbc:derby:/tmp/db/test2;create=true
  SQLState: 23502
  ErrorCode: 3
  Message: Column 'DATA'  cannot accept a NULL value.

jdbc:mysql://localhost:3306/test
  SQLState: 23000
  ErrorCode: 1048
  Message: Column 'data' cannot be null

jdbc:postgresql:test
  SQLState: 23502
  ErrorCode: 0
  Message: ERROR: null value in column "data" violates not-null constraint

Regards,
Thomas


On Tue, May 21, 2013 at 12:51 AM, Gili > wrote:

>
> Hi,
>
> I believe H2 should throw SQLState 2201C instead of 23502 when users 
attempt to insert NULL into a column that disallows it.

>
> H2's documentation can be found here: 
http://www.h2database.com/javadoc/org/h2/constant/ErrorCode.html#c23502
> SQLState 2201C is defined as "null row not permitted in table" by 
the SQL 2008 standard as found in 5CD-02-Foundation-2006-01.pdf in: 
http://en.wikipedia.org/wiki/SQL#cite_note-39

>
> Gili
>
> --
> You received this message because you are subscribed to the Google 
Groups "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, 
send an email to h2-database+unsubscr...@googlegroups.com 
.
> To post to this group, send email to h2-database@googlegroups.com 
.

> Visit this group at http://groups.google.com/group/h2-database?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
--
You received this message because you are subscribed to a topic in the 
Google Groups "H2 Database" group.
To unsubscribe from this topic, visit 
https://groups.google.com/d/topic/h2-database/JFH-hix5D-A/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to 
h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.




--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: 1.3.172 when?

[Jason Pell]

awesome that will be great.

On Tuesday, May 21, 2013 9:32:34 PM UTC+10, Jason Pell wrote:
>
> Hi,
>
> Due to a bug in 1.3.171 with ranges I downgraded to 1.3.169.  I would love 
> to upgrade to 1.3.172
>
> when is this due to be available?
>
> Thanks
> Jason
>

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

IDENTITY column increments even after error in previous SQL statement

[Rustam Ismailov]

Hi! I don't know is it a bug or feature, please explain.
Identity column is autoincremented, even previous statment completed with 
an error (so I expect ID column value will not incremented).

Example snippet:

drop table TEST_TABLE if exists;

create table TEST_TABLE (
ID IDENTITY NOT NULL PRIMARY KEY, -- this column value we will observe
REQ_FIELD VARCHAR2(32) NOT NULL,
NOT_REQ_FIELD VARCHAR2(32)
);

insert into TEST_TABLE (NOT_REQ_FIELD) values ('foo'); -- this will fail 
because of REQ_FIELD is still NULL - it is expected behavior, but identity 
value incremented inside H2!

insert into TEST_TABLE (REQ_FIELD) values ('test'); -- this will be 
executed fine, but ID value becomes 2 instead of (IMHO) expected 1, see 
select statament below
COMMIT;

select * from TEST_TABLE; -- we see 2 in "ID" column, but expected 1

H2 Database version 1.3.171

java -version:
java version "1.7.0_21"
Java(TM) SE Runtime Environment (build 1.7.0_21-b11)
Java HotSpot(TM) 64-Bit Server VM (build 23.21-b01, mixed mode)

Windows 7 Ultimate x64 Russian.

Thanks!

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: IDENTITY column increments even after error in previous SQL statement

[Ryan How]

Hi,

That is how the identity columns work. Also if a failed transaction then 
they will still increment. If the database is not closed correctly they 
may jump by a large number. There is no guarantee that they will be in 
increments of 1, just that they will be higher in value than the last.


If you need to guarantee that it increments by 1 you'll need to create 
your own mechanism for doing that.


I guess in the case of the failed statement below, internally H2 it 
still grabs the next value in the sequence to try the insert.


Hope that helps,

Ryan


On 22/05/2013 6:48 AM, Rustam Ismailov wrote:

Hi! I don't know is it a bug or feature, please explain.
Identity column is autoincremented, even previous statment completed 
with an error (so I expect ID column value will not incremented).


Example snippet:

drop table TEST_TABLE if exists;

create table TEST_TABLE (
ID IDENTITY NOT NULL PRIMARY KEY, -- this column value we will observe
REQ_FIELD VARCHAR2(32) NOT NULL,
NOT_REQ_FIELD VARCHAR2(32)
);

insert into TEST_TABLE (NOT_REQ_FIELD) values ('foo'); -- this will 
fail because of REQ_FIELD is still NULL - it is expected behavior, but 
identity value incremented inside H2!


insert into TEST_TABLE (REQ_FIELD) values ('test'); -- this will be 
executed fine, but ID value becomes 2 instead of (IMHO) expected 1, 
see select statament below

COMMIT;

select * from TEST_TABLE; -- we see 2 in "ID" column, but expected 1

H2 Database version 1.3.171

java -version:
java version "1.7.0_21"
Java(TM) SE Runtime Environment (build 1.7.0_21-b11)
Java HotSpot(TM) 64-Bit Server VM (build 23.21-b01, mixed mode)

Windows 7 Ultimate x64 Russian.

Thanks!
--
You received this message because you are subscribed to the Google 
Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to h2-database+unsubscr...@googlegroups.com.

To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.




--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.