Re: Optimize group by on simple nested queries
Hey, Ok, I'm gonna try to find a work around in order to rewrite the sql query. Thanks anyway ! LP Le 6 févr. 2012 à 08:41, Thomas Mueller a écrit : > Hi, > > I'm afraid it would be quite hard to implement this optimization. Currently I > wouldn't know to do it I'm afraid. > > Regards, > Thomas > > -- > 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 > h2-database+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. -- 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 h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
MySQL date functions
Hello,
I need to be able to execute MySQL statements on H2 for my unit tests.
And I'm blocked by the DATE_ADD(...) function.
I tried to define my own function in Java like this:
public class H2Functions {
public static Date dateAdd(Date date, String expr) {
...
}
}
CREATE ALIAS DATE_ADD FOR "H2Functions.dateAdd";
SELECT DATE_ADD(now(), INTERVAL 1 DAY);
Syntax error in SQL statement "SELECT DATE_ADD(NOW(), INTERVAL 1[*]
DAY) "; expected "., (, [, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE,
REGEXP, IS, IN, BETWEEN, AND, OR, ), ,"; SQL statement:
SELECT DATE_ADD(now(), INTERVAL 1 DAY) [42001-164] 42001/42001
I understand that INTERVAL 1 DAY is not a string, and so my function
is not called.
Is there a mean to have a SQL request with DATE_ADD which works on
both H2 and MySQL?
Thanks.
--
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
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
Re: MySQL date functions
We don't have such a facility at the moment, but it sounds like a
reasonable feature request.
We could allow you to mark a function alias as having "free-form
parameters". Then it would be up to the function code to parse the
arguments.
Something like
CREATE ALIAS DATE_ADD FREEFORM FOR "H2Functions.dateAdd";
Where the method would have to be defined like this:
public static void dateAdd(String[] params) {}
Thomas, what do you think of my proposed syntax?
On Mon, Feb 6, 2012 at 17:55, Boris Granveaud wrote:
> Hello,
>
> I need to be able to execute MySQL statements on H2 for my unit tests.
> And I'm blocked by the DATE_ADD(...) function.
>
> I tried to define my own function in Java like this:
>
> public class H2Functions {
> public static Date dateAdd(Date date, String expr) {
> ...
> }
> }
>
> CREATE ALIAS DATE_ADD FOR "H2Functions.dateAdd";
> SELECT DATE_ADD(now(), INTERVAL 1 DAY);
>
> Syntax error in SQL statement "SELECT DATE_ADD(NOW(), INTERVAL 1[*]
> DAY) "; expected "., (, [, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE,
> REGEXP, IS, IN, BETWEEN, AND, OR, ), ,"; SQL statement:
> SELECT DATE_ADD(now(), INTERVAL 1 DAY) [42001-164] 42001/42001
>
> I understand that INTERVAL 1 DAY is not a string, and so my function
> is not called.
>
> Is there a mean to have a SQL request with DATE_ADD which works on
> both H2 and MySQL?
>
> Thanks.
>
> --
> 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
> h2-database+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.
>
--
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
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
Re: Sporadic error : 90067-149
Hi, I don't know what the problem could be. Do you use different client and server versions? If yes, then possibly it's a bug in H2 (incompatibility with old versions). If the same version is used, then I don't know what it could be. In both cases, I suggest to upgrade to a newer version of H2. Regards, Thomas -- 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 h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Re: MySQL date functions
Hi, The H2 parser doesn't currently understand INTERVAL. What you could do is use TIMESTAMPADD, which is supported by both H2 and MySQL: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampadd http://h2database.com/html/functions.html#dateadd I guess I will have to add the INTERVAL syntax. > "free-form parameters" I don't know... it would mean for some of the parameters the function is responsible for parsing, but that would complicate things quite a lot. For example: SELECT DATE_ADD(now(), INTERVAL (X+Y+?) DAY) from ... Who would be responsible to parse X+Y+? (a parameter). It would require quite a lot of new API. If possible, I would try to avoid that. I guess it would be easier to support INTERVAL ... as a new data type. Anyway it seems this is required at some point. Regards, Thomas -- 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 h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
Re: MySQL date functions
I didn't notice TIMESTAMPADD, thanks! Boris. On Feb 7, 6:36 am, Thomas Mueller wrote: > Hi, > > The H2 parser doesn't currently understand INTERVAL. What you could do > is use TIMESTAMPADD, which is supported by both H2 and MySQL: > > http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#f... > > http://h2database.com/html/functions.html#dateadd > > I guess I will have to add the INTERVAL syntax. > > > "free-form parameters" > > I don't know... it would mean for some of the parameters the function > is responsible for parsing, but that would complicate things quite a > lot. For example: > > SELECT DATE_ADD(now(), INTERVAL (X+Y+?) DAY) from ... > > Who would be responsible to parse X+Y+? (a parameter). It would > require quite a lot of new API. If possible, I would try to avoid > that. > > I guess it would be easier to support INTERVAL ... as a new > data type. Anyway it seems this is required at some point. > > Regards, > Thomas -- 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 h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
