Re: [h2] Re: CustomDataTypesHandler

e'd certainly all be using rationals. Fortunately I'm not doing much sin/squareroot when dealing with currencies and quantities ... The biggest challenge is perhaps handling the rounding, in anycase, in practice it's not really an issue if the value is merely the result of dividing a couple of user inpute

Re: [h2] Re: CustomDataTypesHandler

Thanks Matt, Yeah, I know this is possible, but obviously it's pretty cumbersome, and if you do something as simple as SELECT * FROM T, you will not get to see the contents of the rational fields. -- You received this message because you are subscribed to the Google Groups "H2 Database" group.

Re: [h2] Re: CustomDataTypesHandler

> > > What do you see as the driver for having the field be a column type? Have > you looked into H2 function support? > Hi Matt, Mostly just selfish reasons, but I do think there is a sound engineering/computer science basis for having another numeric type. Decimal types are arbitrary for

Re: [h2] Re: CustomDataTypesHandler

Ok. Yes it was so things such as computation, comparison and other conversions could work seemlessly. Obviously just storing the values is not such a huge challenge. Probably the way you outline with the JavaObjectSerializer is the way to go. In fact in my project I am more or less attempting to

Re: [h2] Re: CustomDataTypesHandler

Thanks for the complete reply and all of the hardwork in general. I do think from the outside it has been a little unclear about the direction and motivation of the project, but obviously that clears things up. The type I wanted to implement was going to be a rational numeric (i.e. a/b). Do you

[h2] CustomDataTypesHandler

Hi Evgenij, I noticed that this api was removed (unceremoniously). I was wondering what were the reasons, and is there an alternative? >From my point of view it would have been good to have an explanation as to why it was removed (e.g. in the commit) given that it was a public API. It's still

Re: [h2] MVStore - Check Unique Loops 1000s times per insert

> > the patch looks reasonable, but it will need extra logic to cope with > nullsFirst/nullsLast ​ > Not sure I follow. Do you mean there is more to be fixed, or that the change breaks things in some way in some cases? Could you expand on what you mean exactly by 'logic to cope with

Re: [h2] MVStore - Check Unique Loops 1000s times per insert

So the issue is null values in the index. I believe the loop should be exiting after detecting a match in the index, but deciding not to throw an error. This happens in 2 places. See the patch below. My understanding is that if it reaches the point where the breaks happen then it must be a null

Re: [h2] MVStore - Check Unique Loops 1000s times per insert

Sorry, I think I see. It is supposed to be iterating over just the 'equal' rows (of which there should be at most one, right?). I will investigate. On Mon, Jan 15, 2018 at 7:25 AM, Noel Grandin wrote: > > I don't see any assumption there about the inserted values. > >

[h2] MVStore - Check Unique Loops 1000s times per insert

I have a performance problem with if I am understanding correctly unique (secondary) indexes (or indices) when using the MVStore. It is very apparent because I am running a bulk process inserting 10s of rows and it slows to a crawl on some tables, and I believe it is because of the unique

Re: [h2] NPE in simple Common Table Expression

Yes, I did try with the head (I assume master) revision at the time - 7099 I think. On Fri, Oct 6, 2017 at 7:34 AM, Noel Grandin wrote: > Can you try a master build, we have fixed various WITH problems recently > (thanks to people like @stumc)​ > > -- > You received this

[h2] NPE in simple Common Table Expression

Hi, I have a simple test case, where it cannot run but throws an NPE. I have tested in 1.4.196 and in the head revision. There seems to be a second issue whereby this cannot be rerun (in the console at least) since you get this error, as if the WITH condition has created a persistent view. Hope

[h2] Regression + Test Case

Hi, Have encountered a regression somewhere imbetween 1.4.186 and 1.4.193 (pretty sure broken in 1.4.192 as well). Checked against the current head version. Just got around to reducing my problem to a test case. Hope this helps! - mike DROP SCHEMA "x"; CREATE SCHEMA IF NOT EXISTS "x";

[h2] Unhandled exception parsing date

Stack trace below. The sql itself is massive so I've edited it out. Presumably the handling could just be improved. org.h2.jdbc.JdbcSQLException: Cannot parse "DATE" constant ; SQL statement: ... FROM (SELECT NULL) WHERE (true) LIMIT 25000 [22007-193] at

Re: [h2] Re: Remote Connection to a single database.

On Sun, Jul 31, 2016 at 7:46 AM, Noel Grandin wrote: > Ah, I see what you are getting at. > > Yeah, this is not a common use-case for H2 because exposing your DB to > anything outside of your systems trusted zone is a really bad idea. > Hmmm. I think you're being too

Re: [h2] Re: Remote Connection to a single database.

Does it sanitise the paths that are passed to it (so they cannot go up a directory with '..')? I mean that's not mentioned in the documentation (on that page I cannot find any reference to 'baseDir' and the command line help merely says 'The base directory for H2 databases' and it's not

[h2] Re: Remote Connection to a single database.

= new Server(service, new String[]{"-tcpPort", port, "-tcpDaemon", "-tcpAllowOthers"}); service.setShutdownHandler(server); server.start(); return server; On Tue, Jul 26, 2016 at 6:46 PM, Mike Goodwin <m...@webenableit.com> w

[h2] Remote Connection to a single database.

Hi, I was wondering how best to do a remote tcp connection to a single (already existing) database. The auto server mode would be ideal, but apparently you cannot access it using a server+port url. Is there a technical reason for this limitation? For me the documentation is a little confusing

Re: [h2] Converting a H2 database to SQLite

I think the best your going to do is export to SQL and then reimport that (making any necessary compatability) SCRIPT TO 'export.sql' http://www.h2database.com/html/grammar.html#script - mike On Sun, Sep 20, 2015 at 6:01 PM, Cecil Westerhof wrote: > I need

[h2] Re: Query index selection with 'sub types'

FROM b INNER JOIN c ON b.id=c.id GROUP BY groupby ) as aggr ON a.id=aggr.groupby; On Mon, Mar 9, 2015 at 1:24 PM, Mike Goodwin mkpgood...@gmail.com wrote: Hi, I have a query performance problem that has come, gone away and come back again. The issue seems to be when using what

Re: [h2] Query index selection with 'sub types'

, 2015 at 6:17 AM, Noel Grandin noelgran...@gmail.com wrote: On 2015-03-12 01:50 AM, Mike Goodwin wrote: So aside from improving the optimizer what would fix my problem would be the ability to add hints to fix the join ordering. I know this is on the road map and I wouldn't mind doing

Re: [h2] Query index selection with 'sub types'

Sorry, I missed this e-mail before I send mine. My initial idea about join_collapse_limit was already proposed. I didn't understand the SQL Server syntaxe, how force the ordering for an internal Select node? I agree, the postgres way is less flexible, but this saved me in some of situations.

Re: [h2] Query index selection with 'sub types'

So aside from improving the optimizer what would fix my problem would be the ability to add hints to fix the join ordering. I know this is on the road map and I wouldn't mind doing it and do not think it should be too hard to implement (it amounts to adding something to pick up hints in the parser

Re: [h2] Query index selection with 'sub types'

previously that the problem gone and back again. In which version the issue gone? How looks like your URL? Regards, Fred 2015-03-09 14:33 GMT-03:00 Mike Goodwin mike.good...@cantab.net: Hi, I replied off list with the data. Thanks Fred. I did a little bit of investigation myself. I

Re: [h2] Re: Insert with multi column constraint issue

: On Thu, Mar 5, 2015 at 5:57 PM, Mike Goodwin mike.good...@cantab.net wrote: Ok, I can see that the constraint is specified incorrectly (in the wrong order). It really had to be something like that. Still, should it be possible to create a constraint with mismatched columns like

[h2] Query index selection with 'sub types'

Hi, I have a query performance problem that has come, gone away and come back again. The issue seems to be when using what are sometimes referred to as sub types. A sub type is a table that share a primary key with the super type table. This explains the scheme a bit better.

Re: [h2] Query index selection with 'sub types'

data to reproduce the cited situation? Regards, Fred 2015-03-09 10:24 GMT-03:00 Mike Goodwin mkpgood...@gmail.com: Hi, I have a query performance problem that has come, gone away and come back again. The issue seems to be when using what are sometimes referred to as sub types. A sub

[h2] Insert with multi column constraint issue

Hi, I am having an issue with a particular insert not working. I have made a simplified test case below. I've tried on 1.4.186 and 1.3.171 where I originally had the issue. Remove the constraint and there is no error thrown. DROP ALL OBJECTS; SET DB_CLOSE_DELAY -1; CREATE USER IF NOT EXISTS SA

[h2] Re: Insert with multi column constraint issue

Ok, I can see that the constraint is specified incorrectly (in the wrong order). It really had to be something like that. Still, should it be possible to create a constraint with mismatched columns like that? Sorry! Mike On Thu, Mar 5, 2015 at 3:35 PM, Mike Goodwin mkpgood...@gmail.com

Re: [h2] how to compile source

Maven, I would say don't do it. I just think the whole thing is an extremely mediocre tool at best. One of the problems in debating this will be that people who know it well generally like it, because if you do not like it then don't use it. The culture also seems to be one of benign tyranny.

[h2] Sequences with a negative value no longer supported (regression?)

Hi, I just got this error upgrading from 1.3.171 - 1.3.175 Is this by design? regards, Mike Caused by: org.h2.jdbc.JdbcSQLException: Unable to create or alter sequence TSEQ_F000 because of invalid attributes (start value -7320917853639540658, min value 1, max value

Re: [h2] Move database files after shutdown

JVM and copy them around, so it does work. On 11/11/2013 8:13 AM, Mike Goodwin wrote: Hi, I have found I am unable to move the database directory after a shutdown. I have tried waiting, gc(), calling for a shutdown (using the SHUTDOWN sql command). I have made sure there are no more

[h2] Move database files after shutdown

Hi, I have found I am unable to move the database directory after a shutdown. I have tried waiting, gc(), calling for a shutdown (using the SHUTDOWN sql command). I have made sure there are no more connections open. Is there any expectation that it should be possible to close a database after

Re: [h2] Parameter Handling Bug

at 7:35 PM, Mike Goodwin mkpgood...@gmail.comwrote: I replied off the list with a link to download a test case. - mike On Tue, Sep 24, 2013 at 6:29 PM, Thomas Mueller thomas.tom.muel...@gmail.com wrote: Hi, There were a few issues about parameter indexes in combination with views

Re: [h2] Parameter Handling Bug

I replied off the list with a link to download a test case. - mike On Tue, Sep 24, 2013 at 6:29 PM, Thomas Mueller thomas.tom.muel...@gmail.com wrote: Hi, There were a few issues about parameter indexes in combination with views or subqueries in the past, it's possible that there are

[h2] Parameter Handling Bug

Hi, I have found what I believe to be an issue with parameter handling in a complex query. It seems it is possible for the incorrect parameter to be returned as a value when evaluating the query (in my case a timestamp parameter was evaluating to a long - using the value of a separate parameter

Re: Left Join vs Inner Join performance

Hi, - What version are you testing on? I am using 1.3.171. I was on 1.3.168. I would say for some of my slower queries 1.3.171 seems to be about 15-20% faster. Would be great if you could test with the nightly build because I made some index selection improvements recently. I tried it out

Re: Clearing Cache

around to figure it out for your OS. On 2013-04-15 04:52, Mike Goodwin wrote: Hi, Is it possible to clear the query cache? For the purposes of comparing query performance. Even restarting the database does not seem to work, so they must be getting cached to disk, thanks, Mike -- You

Re: Clearing Cache

cache to help when dealing with this situation. Regards, Steve --- Steve McLeod Founder, Poker Copilot http://www.pokercopilot.com On Monday, 15 April 2013 04:52:30 UTC+2, Mike Goodwin wrote: Hi, Is it possible to clear the query cache

Left Join vs Inner Join performance

Hi, On a number of occasions I have found that replacing LEFT JOIN with INNER JOIN dramatically improved poorly performing queries. Generally the queries I run are generated so it was more convenient to use LEFT JOIN but depending on various criteria I have been able to use inner joins where it

Clearing Cache

Hi, Is it possible to clear the query cache? For the purposes of comparing query performance. Even restarting the database does not seem to work, so they must be getting cached to disk, thanks, Mike -- You received this message because you are subscribed to the Google Groups H2 Database

Cannot use column expression in order by clause even though it is in the group by

Hi, I have an issue (with sql generated by a 3rd party library - mondrian) which does not work in h2. The second query (below) does not work, I assume that it should be valid, Thanks for any help, Mike drop table x if exists; create table x (date date) ; insert into x (date)

Re: Performance dramatically slower on left join

Hi, We also have an issue with slow left joins. For example a query which takes less than 1 second on average using an inner join will take consistently around 150 seconds when using a left join. In some cases for the time being it is going to be preferable to just use an inner join to overcome

Inconsistent behaviour adding to dates

Hi, I came across this issue. Essentially the problem seems to be that: CURRENT_DATE+1 // works (adds one day) ?+1 // where ? is set as a date does not work Which seems to me to be inconsistent. I realise that I should be using the dateadd/datediff

Re: Performance Improvements

Hi, Unfortunately not. Since it is backed by real data, sorry. - mike -- You received this message because you are subscribed to the Google Groups H2 Database group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to

Performance Improvements

Just thought I would offer our profiling results. We have some fairly complex queries which were running slowly. It appears to be a consistent factor of 4 speed up moving to the newer versions. All the queries do deal with a similar set of tables so it is possible they have benefited from the

Re: H2 Database table size

Hi Vali, Presumably you mean the size on disk? I think the answer will be no. Between different storage mechanisms and string data being shared there is no easy way to define what size of a single table is in bytes. Regards, mike On Wed, Nov 18, 2009 at 2:14 PM, Vali valitugu...@gmail.com

Reference primary key column name in Update Subquery

Hi, I was wondering if anyone knows how to overcome the following issue in h2 sql. If you have a scalar sub query in an update clause, which refers to the same table, how can you reference the key of the row being updated in that query (so that it selects only one row). In the example code

Re: Reference primary key column name in Update Subquery

Thanks, wasn't trying to cast aspersions! Actually I saw that some other databases have some special syntax for variable scoping ... Seems obvious now you've pointed it out, I didn't think of adding an alias there. On Tue, Nov 3, 2009 at 6:47 PM, Sergi Vladykin sergi.vlady...@googlemail.com

Re: Problem passing objects into user defined function

Hi Thomas, Could you explain what the method does? The particular function was to create what i've been calling a 'struct', which means a map with a fixed set of (string) keys. Actually as it happens i don't need the string information in the sql, so i can get away with using an array, so

Re: Problem passing objects into user defined function

Hi, In the end I worked around this problem using the sql array construction (a,b,c...) and omitting the string constants (not sure the 2nd step was necessary). So its not a current issue for me. Still I think the user function declaration api is not completely general as it is at the moment.

Network filesystem

Hi, Does anyone have any experience running h2 on a network filesystem? Is it possible? What are the implications for performance and robustness of the database, thanks, mike --~--~-~--~~~---~--~~ You received this message because you are subscribed to the

Re: Sum of zero rows

thanks for the answers mike --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups H2 Database group. To post to this group, send email to h2-database@googlegroups.com To unsubscribe from this group, send email to

Re: Sum of zero rows

... something induction and base cases ... blah On Tue, Feb 3, 2009 at 11:12 PM, Thomas Kellerer google-gro...@sql-workbench.net wrote: On 2 Feb., 19:36, Mike Goodwin mkpgood...@gmail.com wrote: It surprised me that the sum over 0 rows comes to null and not zero. So what is the sum of nothing

Sum of zero rows

It surprised me that the sum over 0 rows comes to null and not zero. DROP TABLE IF EXISTS x; CREATE TABLE x (val INTEGER); SELECT sum(val) FROM x; I checked and it does the same in mysql, but was wondering a) why is it like this? if its because of a standard, again why is the standard like

Re: Fwd: Poleposition benchmarks

Ok, thanks for clearing that up! On Mon, Dec 15, 2008 at 6:38 PM, Thomas Mueller thomas.tom.muel...@gmail.com wrote: Hi, Isn't there a fundamental difference though? hsqldb in memory mode is still persistent, something which is not possible with h2, is it? It is possible. Both H2 and

Re: Fwd: Poleposition benchmarks

Both H2 and HSQLDB support in-memory operation, the others do not. I believe this scenario is not all that common. From what I know, most people use persistent databases / tables. Isn't there a fundamental difference though? hsqldb in memory mode is still persistent, something which is not

Re: Poleposition benchmarks

Are you using the default in memory tables for HSQLDB? (In which case its not a like for like comparison as your HSQLDB will have O(n) memory requirements). On Sun, Dec 7, 2008 at 1:38 AM, Brish [EMAIL PROTECTED] wrote: In the H2 benchmark it looks like H2 is significantly faster than

Re: Standalone SQL parser based on the H2 Parser code

If the parser should be compatible with the regular H2 parser, somebody would have to maintain it. I'm afraid I will not have time to do that. On the other hand, maybe the parser shouldn't be fully compatible with H2. May as well be fully compatible with at least one database! Though I'm

Standalone SQL parser based on the H2 Parser code

Hi Thomas, I have converted org.h2.command.Parser so that it returns sql abstract syntax objects - i.e. cut-down versions of many of the objects under org.h2.value org.h2.expression org.h2.command.dml I have done this as there does not appear to be any suitable open source option for

Accessing the H2 SQL parser

Hi, I was wondering is it possible to use H2 to parse sql and get some kind of abstract syntax tree. I have looked and it does not appear to be easy to use in this way. Can someone confirm this? thanks, Mike --~--~-~--~~~---~--~~ You received this message