[h2] ADBA / AoJ feedback

Hi maintainers of H2 I'm not sure if you're following the progress of ADBA (Oracle's current suggestion of an asynchronous database connectivity API), but it would be great to get feedback by some JDBC implementors. Thus far, mostly JDBC users (like myself, or Spring folks) have given feedback

[h2] Contributing to H2 - JDK / JDBC version support

Hello, As promised, with H2 now being on GitHub (apparently for a while now, I completely missed that), I'd love to contribute 1-2 things that I have always found missing when developing jOOQ. Before I start doing any work though, I do have a couple of questions. I will send individual E-Mails

Re: [h2] Re: Low prio request: Add support for renaming a constraint

Ooh, excellent news, and excellent news also that you're on Github now! That will make it MUCH easier to contribute in the future :) Thanks, Lukas 2016-05-16 11:14 GMT+02:00 Noel Grandin : > Sorry, forgot to respond to this, this was added in > > >

[h2] Low prio request: Add support for renaming a constraint

I've noticed there is currently no way to rename an existing constraint via H2's DDL statements. This might be a useful addition from time to time. Currently, indexes can be renamed only. Best Regards, Lukas -- You received this message because you are subscribed to the Google Groups "H2

Re: [h2] Low prio change request: Add support for ALTER TABLE .. RENAME COLUMN .. TO ..

Thank you very much! 2016-02-17 10:21 GMT+01:00 Noel Grandin : > Added support for these 2 things > > > -- > You received this message because you are subscribed to a topic in the > Google Groups "H2 Database" group. > To unsubscribe from this topic, visit >

[h2] Low prio change request: Add support for ALTER TABLE .. RENAME COLUMN .. TO ..

Hello, This would be nice to have, low prio. Add support for alternative DDL syntaxes in order to improve database interoperability. E.g. support PostgreSQL's simpler column renaming syntax (http://www.postgresql.org/docs/9.5/static/sql-altertable.html) ALTER TABLE distributors RENAME COLUMN

[h2] Allow for VALUES() constructor also as a UNION subquery

Hello, H2 (just like PostgreSQL) currently allows for using the VALUES() constructor as a top-level query, just like a SELECT statement: VALUES (1), (2) yields C1 -- 1 2 In PostgreSQL, it's also possible to use VALUES as a subquery directly, e.g. as a UNION subquery: values (1), (2)

Re: [h2] Add support for OFFSET without LIMIT

That was quick, thanks very much! :) 2015-12-10 10:33 GMT+01:00 Noel Grandin : > sounds reasonable, implemented. > > > -- > You received this message because you are subscribed to a topic in the > Google Groups "H2 Database" group. > To unsubscribe from this topic, visit >

[h2] Add support for OFFSET without LIMIT

Hello there, Many other databases support using OFFSET without LIMIT: SELECT * FROM t ORDER BY id OFFSET 10 It's useful, occasionally, and it could be implemented as syntax sugar for this: SELECT * FROM t ORDER BY id LIMIT [ Long.MAX_VALUE ] OFFSET 10 What do you think? Lukas -- You

Re: [h2] HAVING without GROUP BY

2015-10-28 11:26 GMT+01:00 Rami Ojares : > Ok, now I think I got it. > GROUP BY () groups all the rows into one group (although logically they > were one group already) > but this is needed if one wants to use aggregate operators in the > restriction of rows. > And now that

Re: [h2] HAVING without GROUP BY

2015-10-27 8:51 GMT+01:00 Rami Ojares : > Thanks Lukas! > It is always a pleasure to get these nuggets of important information from > the standard that I don't want to read myself. > As far as I'm concerned, nothing beats a good glass of red wine whilst reading the SQL

Re: [h2] HAVING without GROUP BY

2015-10-27 13:21 GMT+01:00 Rami Ojares : > > > SELECT > string_agg(title, ', ') > FROM > film > WHERE > title LIKE 'AN%' > HAVING > count(*) > 5 > > > "Return a concatenation of all the films starting with "AN", *IF* there > are at least 5 such films." > > > Where

Re: [h2] HAVING without GROUP BY

Folks, I must chime in here. :) Let me quote from the SQL:2011 standard: 7.10 Syntax Rules 1) Let HC be the . Let TE be the that immediately contains HC. If TE does not immediately contain a , then “GROUP BY ()” is implicit. There is no doubt about the fact that HAVING without GROUP BY

[h2] Re: Recursive CTE don't work when recursion predicate uses a bind variable

For the record, I've posted this issue also to Stack Overflow: http://stackoverflow.com/q/32827660/521799 It will help poor souls stumbling upon this issue and spending hours to debug it :) (Still +1 for throwing an UnsupportedOperationException instead of returning the wrong result!) -- You

[h2] Support for JSR-310 and TIME[STAMP] WITH TIME ZONE types

Hello, Just a quick question: JDBC 4.2 / Java 8 added support for JSR-310 in the API, meaning that drivers can now official bind *Existing in H2:* - LocalDate to SQL DATE - LocalTime to SQL TIME - LocalDateTime to SQL TIMESTAMP *New in H2:* - OffsetTime to SQL TIME WITH ZONE

Re: [h2] Has the behaviour of INFORMATION_SCHEMA.FUNCTION_COLUMNS changed?

I see, thank you very much for digging this up. Cheers, Lukas 2015-04-14 11:39 GMT+02:00 Noel Grandin noelgran...@gmail.com: Am I right assuming that somewhere in between, the return value of a stored function has now become a row in the function_columns table with position zero? I

[h2] Has the behaviour of INFORMATION_SCHEMA.FUNCTION_COLUMNS changed?

Consider the following class / method: // Java package org.jooq.test.utils.h2; public class F { public static Integer fOne() { return 1; } } -- SQL CREATE ALIAS f_one FOR org.jooq.test.utils.h2.F.fOne; Now consider the following queries: -- Version 1.3.176 SELECT pos FROM

Re: [h2] Error when adding milliseconds to timestamps

That was quick, thank you very much! 2015-03-18 9:21 GMT+01:00 Noel Grandin noelgran...@gmail.com: Thanks for reporting this, fixed in trunk. -- You received this message because you are subscribed to a topic in the Google Groups H2 Database group. To unsubscribe from this topic, visit

Re: [h2] Move from Google Code to GitHub

Finally, Tom! :) I am now much more likely to help out with 1-2 improvements, now that I no longer have to deal with SVN + E-mailing patches Cheers, Lukas Am Freitag, 13. März 2015 13:59:10 UTC+1 schrieb Thomas Mueller: Hi, Correction: the URL will probably be

[h2] Error when adding milliseconds to timestamps

According to the doc, DATEADD() takes an int for the number of minutes/seconds/milliseconds/etc to be added to a timestamp: http://h2database.com/html/functions.html?highlight=DATEADDsearch=dateadd#dateadd This blows up quite quickly when using milliseconds, of course. Here a case where I'm

Re: [h2] Re: Add support for MEDIAN aggregate function

2015-01-07 23:48 GMT+01:00 Rami Ojares rami.oja...@gmail.com: These window functions seem to me like just a shorthand notation. Here is an example from postgresql documentation. http://www.postgresql.org/docs/9.1/static/tutorial-window.html SELECT depname, empno, salary, avg(salary) OVER

[h2] Re: Add support for MEDIAN aggregate function

Hello, Much more interesting than the MEDIAN() aggregate function itself are inverse distribution functions, or ordered-set aggregate functions in general. MEDIAN(xyz) can be emulated via PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY xyz), as documented in this blog post:

Re: [h2] Recursive CTE don't work when recursion predicate uses a bind variable

what was wrong with the query logic (when in fact there was nothing wrong). Thank you, Gili On Thursday, June 5, 2014 1:13:07 PM UTC-4, Lukas Eder wrote: Hmm, wouldn't it be better to raise an error, rather than silently returning a wrong result, then? Or is this kind of experimentally

Re: [h2] Column not found when column is quoted

Thank you very much, Thomas! Am Montag, 22. September 2014 20:40:22 UTC+2 schrieb Thomas Mueller: Hi, You are right, this is a bug. I will try to fix it. Regards, Thomas On Monday, September 22, 2014, Lukas Eder lukas...@gmail.com javascript: wrote: Hello folks, I've just

[h2] Column not found when column is quoted

Hello folks, I've just analysed this jOOQ support request on Stack Overflow here and suspect that it is really a bug in H2: http://stackoverflow.com/q/25975401/521799 This query works in H2: select 1 abc, count(*) from dual group by abc order by abc asc So does this one: select 1 ABC,

[h2] Support for LATERAL join / CROSS APPLY / OUTER APPLY

Hello, This is just a suggestion for a nice-to-have feature to put on the roadmap. Some databases support the SQL standard LATERAL join, which is also known as CROSS APPLY / OUTER APPLY in T-SQL, and since recently also in Oracle 12c, which now supports both syntaxes. An example use-case can

Re: [h2] Cannot reference column aliases from expressions in ORDER BY clause

Hello, 2014-09-06 17:55 GMT+02:00 Thomas Mueller thomas.tom.muel...@gmail.com: Hi, One problem is that this would not be backward compatible for some cases, if the select list uses the same identifier names as available in the selected tables. Hmm, I can see how such a change would be a

Re: [h2] Wrong results when ORDER BY clause contains correlated scalar subquery

, Lukas Eder lukas.e...@gmail.com wrote: Hello, The following query doesn't work correctly in H2: select x from ( select 1 x union select 2 union select 3 ) t order by (select -x) asc The above should return ++ | X| ++ | 3| | 2| | 1| ++ But instead

[h2] Cannot reference column aliases from expressions in ORDER BY clause

Most SQL databases allow for referencing column aliases declared in the select list in the sort specification list of the order by clause. The following is specified to work by the SQL-1992 standard and also works in H2: select x a from (select 1 x union select 2) t order by a The SQL-1992

[h2] Wrong results when ORDER BY clause contains correlated scalar subquery

Hello, The following query doesn't work correctly in H2: select x from ( select 1 x union select 2 union select 3 ) t order by (select -x) asc The above should return ++ | X| ++ | 3| | 2| | 1| ++ But instead returns: ++ | X| ++ | 1| | 2| | 3|

[h2] Slight enhancement of NEXTVAL() and CURRVAL() functions

Hello, H2 uses a very similar syntax like PostgreSQL to fetch NEXTVAL() and CURRVAL() values from sequences. However, H2 doesn't really support explicitly specifying case-senstive literals using quotes. In PostgreSQL, I can write: nextval('foo') *operates on sequence foo* nextval('FOO')

Re: [h2] Slight enhancement of NEXTVAL() and CURRVAL() functions

This was also my first thought. However, the SQL standard specifies the following (no support for CURRVAL): next value expression ::= NEXT VALUE FOR sequence generator name For backwards-compatibility reasons (and interoperability with PostgreSQL), I guess identifiers wrapped in string

Re: [h2] Cannot parse DATE constant when using JDBC escape syntax

Thanks, Thomas! Cheers, Lukas Am Mittwoch, 23. Juli 2014 21:01:36 UTC+2 schrieb Thomas Mueller: Hi, You are right, this is unexpected. I have fixed this now (in the trunk). Regards, Thomas On Mon, Jul 21, 2014 at 6:20 PM, Lukas Eder lukas...@gmail.com javascript: wrote: Hello

[h2] Cannot parse DATE constant when using JDBC escape syntax

Hello, Please consider the following test case: try (PreparedStatement s1 = connection.prepareStatement(select 1 where date '2000-01-01' ?); PreparedStatement s2 = connection.prepareStatement(select 1 where date '2000-01-01' timestamp '2000-01-02 00:00:00'); PreparedStatement s3 =

Re: [h2] INFORMATION_SCHEMA content for user-defined aggregate functions

Hi Noel, Am Freitag, 27. Juni 2014 17:06:19 UTC+2 schrieb Noel Grandin: On Fri, Jun 27, 2014 at 4:48 PM, Lukas Eder lukas...@gmail.com javascript: wrote: - What exactly is the difference between org.h2.api.AggregateFunction and org.h2.api.Aggregate? I don't really see the point

Re: [h2] INFORMATION_SCHEMA content for user-defined aggregate functions

Am Freitag, 27. Juni 2014 17:08:34 UTC+2 schrieb Noel Grandin: On Fri, Jun 27, 2014 at 4:48 PM, Lukas Eder lukas...@gmail.com javascript: wrote: - Other databases (e.g. Oracle, HSQLDB) have more type safe ways of declaring aggregate functions. In particular, it would be useful if each

[h2] INFORMATION_SCHEMA content for user-defined aggregate functions

Hello, In order to be able to generate source code for user-defined aggregate functions in jOOQ, we would like to introspect the INFORMATIONS_SCHEMA for that matter. I can see that the aggregate function is reported in FUNCTION_ALIASES, but not in FUNCTION_COLUMNS Note, that also the

Re: [h2] ARRAY_AGG() support

2014-06-05 18:52 GMT+02:00 Thomas Mueller thomas.tom.muel...@gmail.com: Hi, H2 supports user defined aggregate functions. That's great, I wasn't aware of that! Off to our roadmap with that https://github.com/jOOQ/jOOQ/issues/3320 However, the order by syntax is not currently supported for

Re: [h2] Recursive CTE don't work when recursion predicate uses a bind variable

: Parameters are only supported within the last SELECT statement (a workaround is to use session variables like @start within the table expression).. Regards, Thomas On Tuesday, June 3, 2014, Lukas Eder lukas.e...@gmail.com wrote: In fact, there seems to be a second issue related to bind

Re: [h2] State of the recursive CTE feature

. Right now improving support is not a high priority for me, sorry. Regards, Thomas On Tuesday, June 3, 2014, Lukas Eder lukas.e...@gmail.com wrote: Hello, We're currently integrating support for CTE in jOOQ and we're wondering if we should support CTE for H2 at all. H2 has experimental CTE

[h2] ARRAY_AGG() support

Hello, I just wanted to create an example for a blog post and noticed that there is (probably?) no way to aggregate data into an array, the way PostgreSQL allows it through ARRAY_AGG(): http://www.postgresql.org/docs/9.3/interactive/functions-aggregate.html An example in PostgreSQL: select

[h2] State of the recursive CTE feature

Hello, We're currently integrating support for CTE in jOOQ and we're wondering if we should support CTE for H2 at all. H2 has experimental CTE support, if I'm not mistaken: http://www.h2database.com/html/advanced.html?highlight=recursivesearch=recursive#recursive_queries This would be one of our

[h2] Recursive CTE don't work when recursion predicate uses a bind variable

Java program to reproduce this: Connection con = getConnection(); System.out.println(Wrong result:); PreparedStatement stmt = con.prepareStatement( WITH recursive t(f) AS ( + SELECT 1 + UNION ALL+ SELECT t.f + 1 + FROM t

[h2] Re: Recursive CTE don't work when recursion predicate uses a bind variable

In fact, there seems to be a second issue related to bind variables and recursive CTE. Consider the following alternative program: Connection con = getConnection(); System.out.println(Wrong result:); PreparedStatement stmt = con.prepareStatement( WITH

Re: [h2] Excessive amount of time spent in org.h2.util.DateTimeUtils.getTimeTry()

committed a fix for this, which should cut about half of the time off. Regards, Noel On 2014-05-05 17:22, Lukas Eder wrote: I was profiling a performance issue in our own software, when I noticed an excessive amount of time being spent in org.h2.util.DateTimeUtils.getTimeTry(), even

Re: [h2] Manual is not entirely accurate about ALTER TABLE statements

Hi Thomas, 2014-05-07 7:46 GMT+02:00 Thomas Mueller thomas.tom.muel...@gmail.com: Hi, In order to simplify the documentation, currently, not all variants of the grammar are documented, only those that are recommended (that's a fuzzy description, I know). That is, if people write new code,

Re: [h2] Excessive amount of time spent in org.h2.util.DateTimeUtils.getTimeTry()

OK, so I can see that timestamps will remain the way they are in H2. That's perfectly fine. Obviuosly, Thomas has given this some deeper thought - I, myself tend to avoid wrapping my mind around that dreaded subject ;-) But going back to the original issue, what would be the optimal way to: -

Re: [h2] Manual is not entirely accurate about ALTER TABLE statements

Am Mittwoch, 7. Mai 2014 08:06:24 UTC+2 schrieb Lukas Eder: Is it optional in the ANSI SQL standard? Yes. From http://www.andrew.cmu.edu/user/shadow/sql/sql1992.txthttp://www.google.com/url?q=http%3A%2F%2Fwww.andrew.cmu.edu%2Fuser%2Fshadow%2Fsql%2Fsql1992.txtsa=Dsntz=1usg

Re: [h2] Excessive amount of time spent in org.h2.util.DateTimeUtils.getTimeTry()

Intuitively, I would agree with Noel, here. The unix timestamp (plus nano seconds) seems to be a reliable storage format. Instead of going through all the date time hassles by operating on strings, it would be possible to defer any string / calendar representation up to the moment when it is

Re: [h2] Excessive amount of time spent in org.h2.util.DateTimeUtils.getTimeTry()

timezone, when they can just get away with defaults in 98% of the time. In any case, I don't think you will be able to expect a quick answer from your users. This is a tough topic. Cheers Lukas Am Dienstag, 6. Mai 2014 10:40:40 UTC+2 schrieb Noel Grandin: On 2014-05-06 09:03, Lukas Eder wrote

[h2] Manual is not entirely accurate about ALTER TABLE statements

Hello, The manual is not entirely accurate about ALTER TABLE statements. Take this statement for instance: http://www.h2database.com/html/grammar.html#alter_table_alter_column The manual claims this syntax: - ALTER TABLE tableName ALTER COLUMN columnName When in fact it should be this syntax:

Re: [h2] Excessive amount of time spent in org.h2.util.DateTimeUtils.getTimeTry()

2014-05-06 18:11 GMT+02:00 Thomas Mueller thomas.tom.muel...@gmail.com: Hi, H2 did store timestamps as UTC. Search for timezone in this group to see what problems this caused. I'm assuming you're talking about this thread: https://groups.google.com/d/msg/h2-database/O5mfM1iSSow/7QMDoGXndCIJ

Re: [h2] Release 1.4.178 Beta

or 1? Regards, Thomas On Sunday, May 4, 2014, Lukas Eder lukas...@gmail.com javascript: wrote: Am Samstag, 3. Mai 2014 14:10:11 UTC+2 schrieb Noel Grandin: On Sat, May 3, 2014 at 12:46 PM, Lukas Eder lukas...@gmail.com wrote: What does Beta mean when upgrading to any

[h2] Excessive amount of time spent in org.h2.util.DateTimeUtils.getTimeTry()

Hello, I was profiling a performance issue in our own software, when I noticed an excessive amount of time being spent in org.h2.util.DateTimeUtils.getTimeTry(), even if the benchmark selected only one timestamp column with dozens of other columns: http://i.imgur.com/KJdXEWB.png I am a bit

[h2] Release 1.4.178 Beta

Hi there, I've noticed from the changelogs that 1.4.178 is marked as Beta, just like 1.4.177: http://www.h2database.com/html/changelog.html This is, however, not reflected in the version number released to Maven Central:

Re: [h2] Release 1.4.178 Beta

Am Samstag, 3. Mai 2014 14:10:11 UTC+2 schrieb Noel Grandin: On Sat, May 3, 2014 at 12:46 PM, Lukas Eder lukas...@gmail.comjavascript: wrote: What does Beta mean when upgrading to any of these versions? Is this because of the new MVCC store being active by default? Yes

Re: [h2] Select using ID retrieved using rand() function in a single query does not work

Maybe, support for an actual SAMPLE clause would be appropriate (in the long run)? - Oracle's SAMPLE clause: http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_10002.htm#SQLRF55293 - SQL Server's TABLESAMPLE clause:

[h2] Re: dropping JAQU?

Am Freitag, 21. März 2014 03:18:26 UTC+1 schrieb Martin Grajcar: On Friday, November 30, 2012 1:49:57 PM UTC+1, Thomas Mueller wrote: Hi, I think it's worth to revive it once Java 8 is available, using closures for conditions. So instead of: Now that Java 8 is out, is it time to

Re: [h2] Better database interoperability for BOOLEAN bind values inserted into non-BOOLEAN columns

Hello, 2014-02-19 7:55 GMT+01:00 Thomas Mueller thomas.tom.muel...@gmail.com: Hi, Hm, whatever we do, this will not be compatible with some databases. I would keep the current behavior, until somebody provides a patch to support both A simple measure might be to add a sub section to the

Re: [h2] Better database interoperability for BOOLEAN bind values inserted into non-BOOLEAN columns

true cannot be inserted into char(1)) - CUBRID - Derby - H2 - Ingres - PostgreSQL - SQLite I'll let you decide how to interpret this information :-) Cheers Lukas 2014-02-13 21:40 GMT+01:00 Lukas Eder lukas.e...@gmail.com: Yes, strict type checking is certainly desirable. I've checked the latest

Re: [h2] Bad value in denormalised INFORMATION_SCHEMA.CONSTRAINTS.COLUMN_LIST

Hi Thomas, Am Mittwoch, 12. Februar 2014 19:46:53 UTC+1 schrieb Thomas Mueller: Hi, The column_list is more a comment currently, maybe it should be removed. I wonder if the following could be used? select c.table_schema, c.table_name, c.unique_index_name, c.column_list, i.column_name

Re: [h2] Better database interoperability for BOOLEAN bind values inserted into non-BOOLEAN columns

Yes, strict type checking is certainly desirable. I've checked the latest draft documents of the the SQL:2011 standard. The PostgreSQL driver shows the expected behaviour when we use a CAST: 6.13 cast specification [...] 10) If TD is fixed-length character string, then let LTD be the length in

[h2] Bad value in denormalised INFORMATION_SCHEMA.CONSTRAINTS.COLUMN_LIST

Hello, In the extremely unlikely event of someone using a comma in their column names AND using that column in a constraint, then the INFORMATION_SCHEMA.CONSTRAINTS.COLUMN_LIST column will yield wrong values. Example: create table test1 (A,B int, A int, B int, primary key (A,B)); create table

Re: [h2] Bad value in denormalised INFORMATION_SCHEMA.CONSTRAINTS.COLUMN_LIST

2014-02-12 15:03 GMT+01:00 Noel Grandin noelgran...@gmail.com: On 2014-02-12 16:00, Lukas Eder wrote: In the extremely unlikely event of someone using a comma in their column names AND using that column in a constraint, then the INFORMATION_SCHEMA.CONSTRAINTS.COLUMN_LIST column will yield

[h2] Better database interoperability for BOOLEAN bind values inserted into non-BOOLEAN columns

Hello guys, I was wondering if it would make sense for H2 to provide better database interoperability for BOOLEAN bind values when they're inserted into non-BOOLEAN columns. Consider this test scenario: create table test(val varchar(1)); Now this program fails: Connection c =

Re: [h2] Better database interoperability for BOOLEAN bind values inserted into non-BOOLEAN columns

Hi Thomas, Thanks for your response. Some comments inline 2014-02-12 20:05 GMT+01:00 Thomas Mueller thomas.tom.muel...@gmail.com: Hi, Hm, I'm not sure. Why would you use a varchar(1) for a boolean, and not bit? In Oracle, people emulate booleans with any of number(1), char(1), varchar(1).

Re: [h2] Bad value in denormalised INFORMATION_SCHEMA.CONSTRAINTS.COLUMN_LIST

Hi Thomas, 2014-02-12 19:46 GMT+01:00 Thomas Mueller thomas.tom.muel...@gmail.com: Hi, The column_list is more a comment currently, maybe it should be removed. Or maybe deprecated ;-) On a side-note, there seems to be also an issue related to case-sensitivity in that column. A user

Re: [h2] Patch submitted for Issue 533: SYNONYM support

In my hard-core Oracle days, we've used Oracle synonyms merely for importing all objects from the de facto main schema in the public name space. Interestingly, unlike H2 (or PostgreSQL), for instance, Oracle doesn't have a default PUBLIC schema. While SQL Server allows for overriding the

Re: [h2] Re: Window functions in H2

I can see RANK() and DENSE_RANK() being emulated, but how do you emulate ROW_NUMBER() with joins? Do you remember what strategy you were looking at at the time you had a look at the implementation? Am Mittwoch, 27. November 2013 09:01:20 UTC+1 schrieb Noel Grandin: Hi I had a brief bash at

Re: [h2] How about an official H2 blog by Thomas Müller and Noel Grandin (and maybe, others)?

. But sometimes there are topics that don't belong there; in that case a blog does make a lot of sense. Unfortunately, I currently have quite little time... Regards, Thomas On Wed, Nov 13, 2013 at 12:03 PM, Lukas Eder lukas...@gmail.comjavascript: wrote: Hi guys, It just struck me like

[h2] How about an official H2 blog by Thomas Müller and Noel Grandin (and maybe, others)?

Hi guys, It just struck me like lightning. You guys don't have a blog right? It would be really awesome if there was an official H2 blog, talking about insights into storage, indexing, performance, algorithms, SQL as a language, etc. I know that writing a blog takes a lot of time, but my

[h2] Re: Some issues when binding values without casting

Hello, Just for the record and better cross-referencing of known issues, I've encountered this issue again here: http://www.petrikainulainen.net/programming/spring-framework/spring-from-the-trenches-new-like-expressions-of-spring-data-jpa/#comment-282743 This is about this particular issue

Re: [h2] DB2 Mode Support WITH UR syntax (uncommitted read)

I've been reasoning about this syntax as well for jOOQ http://www.jooq.org, recently. Here are the relevant DB2 manual pages: - http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0059221.html (isolation-clause) -

Re: [h2] PreparedStatement with a lot of indexed Parameters hangs up and takes CPU - but there is no error message

I'll tune in here to provide you with some background info from the jOOQ side. When Markus used jOOQ to *inline* bind values (e.g. WHERE foldermap.parent IN (1, 2, 3, 4)), the query ran in an acceptable time on my machine - around 1/2s. In the latter query, jOOQ renders bind values as question

Re: [h2] PreparedStatement with a lot of indexed Parameters hangs up and takes CPU - but there is no error message

Hello, 2013/10/8 Noel Grandin noelgran...@gmail.com Hi Thanks for the updated test case. I've looked into this, and the situation is thusly: We have special case optimised code for handling IN (1,2,3) queries. Unfortunately, for IN (?,?,?) queries the optimised code does not get used

Re: [h2] PreparedStatement with a lot of indexed Parameters hangs up and takes CPU - but there is no error message

2013/10/8 Thomas Mueller thomas.tom.muel...@gmail.com Hi, Would it be possible to disable generating CAST? If the bind variable is set using PreparedStatement.setLong, I don't see why CAST would be needed. This is why:

Re: [h2] Re: Error 90052, Subquery is not a single column query

Hello The odd background-info from me ;-) This is called an IN predicate on row value expressions. I have brought this up before on this user group, as I think that true row value expression support would be a good feature addition for H2:

Re: [h2] Re: Error 90052, Subquery is not a single column query

... I'm sorry, I was missing the fact that you were using Hibernate. I guess Hibernate will have to be fixed according to what I mentioned below... Am Mittwoch, 25. September 2013 07:29:36 UTC+2 schrieb Lukas Eder: Hello The odd background-info from me ;-) This is called an IN predicate

[h2] Surprising LGPL licensed JdbcConnectionPool

Hello, I've accidentally stumbled upon an LGPL license header for org.h2.jdbcx.JdbcConnectionPool: /* * Copyright 2004-2013 H2 Group. Multiple-Licensed under the H2 License, * Version 1.0, and under the Eclipse Public License, Version 1.0 * (http://h2database.com/html/license.html). *

Re: [h2] Surprising LGPL licensed JdbcConnectionPool

2013/8/30 Noel Grandin noelgran...@gmail.com On 2013-08-30 10:31, Lukas Eder wrote: Hello, I've accidentally stumbled upon an LGPL license header for org.h2.jdbcx.* *JdbcConnectionPool: It's dual-licensed, so you get to choose the the license you want to use. I understand, but given

Re: [h2] Surprising LGPL licensed JdbcConnectionPool

Aha, so the term H2 License refers to H2's dual-licensing, not to the modified MPL. I see, thanks for the clarification! 2013/8/30 Noel Grandin noelgran...@gmail.com On 2013-08-30 10:43, Lukas Eder wrote: 2013/8/30 Noel Grandin noelgran...@gmail.com On 2013-08-30 10:31, Lukas Eder wrote

Re: [h2] The OFFSET should be optimized in H2

2013/7/12 Noel Grandin noelgran...@gmail.com On 2013-07-12 09:03, Lukas Eder wrote: In exceptional cases, OFFSET clauses can be most efficiently implemented by counting and skipping the largest possible logical data sets in either a TABLE or INDEX structure, according to the specified

Re: [h2] The OFFSET should be optimized in H2

2013/7/12 Noel Grandin noelgran...@gmail.com On 2013-07-12 10:07, Lukas Eder wrote: It is a real-world use-case, in my opinion. :-) Go back and re-read the original email. OK, OK ;-) Let's go back and re-read the original email. Citing the OP: Just for write a database performance

Re: [h2] The OFFSET should be optimized in H2

Hi Noel, OK, I'm going to be a bit sarcastic in this mail. I hope you're not offended. 2013/7/12 Noel Grandin noelgran...@gmail.com On 2013-07-12 14:27, Lukas Eder wrote: Why not? Why not challenge an Open Source database with a silly stress test? Because the nice part about being

Re: [h2] Discoverability of CHECK constraints in INFORMATION_SCHEMA

2013/6/18 Noel Grandin noelgran...@gmail.com On 2013-06-09 13:43, Lukas Eder wrote: Is this the intended behaviour? In my opinion, both constraints should appear in the constraints view, though. Note, this is how I would query for CHECK constraints in the SQL standard INFORMATION_SCHEMA

Re: [h2] Discoverability of CHECK constraints in INFORMATION_SCHEMA

2013/6/19 Noel Grandin noelgran...@gmail.com On 2013-06-19 08:23, Lukas Eder wrote: Are there any plans to move towards semantic versioning ( http://semver.org) with H2? The current versioning scheme doesn't formally allow to remove such backwards-compatibility flags again. With semantic

[h2] Discoverability of CHECK constraints in INFORMATION_SCHEMA

Hello, Here's a simple script creating a table with two CHECK constraints: *create table* x ( a int *check *(a 0), b int, *constraint *x_c *check *(a b) ); The first check constraint is scoped to a single column, whereas the second one is scoped to the whole table. When trying to

Re: Predicate functions

Hello, This probably doesn't answer all of the questions in this thread, but it might answer some of those related to indexing with respect to spatial functions. First off, the useful Postgres notation WHERE st_intersects(x, y) is equivalent to WHERE st_intersects(x, y) = true. In other

Compatibility with MySQL's UNSIGNED integer types

Hello, I understand that H2 syntactically supports the UNSIGNED keyword in DDL to stay compatible with MySQL: CREATE TABLE t_unsigned ( u_byte tinyint unsigned, u_short smallint unsigned, u_int int unsigned, u_long bigint unsigned ); Obviously, this is just a

Re: Compatibility with MySQL's UNSIGNED integer types

10:29, Lukas Eder wrote: Hello, I understand that H2 syntactically supports the UNSIGNED keyword in DDL to stay compatible with MySQL: CREATE TABLE t_unsigned ( u_byte tinyint unsigned, u_short smallint unsigned, u_int int unsigned, u_long bigint unsigned

Re: Support for TRUNCATE TABLE ... [ RESTART IDENTITY | CONTINUE IDENTITY ]

generally useful than the TRUNCATE version. On 2013-05-10 12:57, Lukas Eder wrote: Hello, This is a minor feature request: Add support for the RESTART IDENTITY / CONTINUE IDENTITY options that can be passed to a TRUNCATE statement in some databases, specifically Postgres [1] and HSQLDB [2

Support for TRUNCATE TABLE ... [ RESTART IDENTITY | CONTINUE IDENTITY ]

Hello, This is a minor feature request: Add support for the RESTART IDENTITY / CONTINUE IDENTITY options that can be passed to a TRUNCATE statement in some databases, specifically Postgres [1] and HSQLDB [2]. The H2 roadmap already features Postgres' CASCADE option, but I think IDENTITY handling

Re: Doubts about TriggerAdapter's ResultSet behaviour

Hello, I understand your arguments. Yes, the well-known Row interface is missing from the JDBC API (as are many other types). It would be the correct type to pass to a trigger. I wonder what would be required to form an expert group to finally fix all those JDBC API blunders... Anyway, I will

Re: Doubts about TriggerAdapter's ResultSet behaviour

I understand that a change would break existing code. Probably, a change could wait to the next major release. I just wanted to stress the fact that I thought it was unwise to re-use an existing, well-defined contract (ResultSet), without implementing it correctly. Even if you're not supposed

Doubts about TriggerAdapter's ResultSet behaviour

Hello, On the jOOQ user group, I have recently been made aware of an issue with H2's TriggerAdapter behaviour (details can be seen here: https://groups.google.com/d/msg/jooq-user/rGyc7dq9hRs/vpAzbxYjisMJ). According to the TriggerAdapter Javadoc, the ResultSet arguments are in fact infinite,

Re: Prohibitive amount of iterations in MetaTable.generateRows() when querying the INFORMATION_SCHEMA in a large database (many tables)

schrieb Lukas Eder: Hi Thomas, I thought about changing it, by using a 'before select' trigger, so that the metadata tables are materialized. Yes, I had thought about that, too. Materialising views might be the path of least resistance here, specifically because all sorts of regular

Re: Prohibitive amount of iterations in MetaTable.generateRows() when querying the INFORMATION_SCHEMA in a large database (many tables)

improvement goes from 40s to 35s, which is still something, but maybe not worth the pain... Am Dienstag, 19. Februar 2013 17:40:07 UTC+1 schrieb Lukas Eder: Patches are welcome of course, but I would like to keep it simple if possible. I'll have a second look. There's always 1-2 things

Prohibitive amount of iterations in MetaTable.generateRows() when querying the INFORMATION_SCHEMA in a large database (many tables)

Here's a bit of a corner-case, showing a prohibitive amount of calls to MetaTable.checkIndex() and other methods within MetaTable.generateRows() 1. I've created a database schema with 15k tables (and 15k primary keys, 15k foreign keys) of this form: DROP SCHEMA IF EXISTS large; CREATE SCHEMA

Compile-time ambiguity with JDK 7

Hello, Please find attached a patch that fixes a compile-time ambiguity that is introduced in the H2 code base, when compiling H2 with Java 7. The added FileLock constructor can be seen here:

Re: How to specify a SQL standard derived column list for a table expression

I should adapt? Cheers Lukas 2013/1/3 Noel Grandin noelgran...@gmail.com On 2013-01-02 18:35, Lukas Eder wrote: Is there any way to specify a derived column list in H2 that I might have been missing? Such a syntax would be useful for various other H2 syntax constructs, such as the VALUES

  1   2   >