Add TRUNC as an alias for TRUNCATE

[Lukas Eder]

Hello,

This is a minor issue. I've found that many other databases support TRUNC and TRUNCATE equally. H2 only has TRUNCATE:
http://www.h2database.com/html/functions.html#truncate

Adding TRUNC() as an alias/synonym might be useful for increased compatibility with

- CUBRID
- DB2
- HSQLDB
- Ingres
- Oracle
- Postgres

Cheers
Lukas

[Noel Grandin]

TRUNC() is a scalar function that operates on values like dates.
TRUNCATE is table modification method.
They have nothing to do with each other besides the naming similarity.

But yes, it would be nice to support it.
Patches are welcome.

> --
> You received this message because you are subscribed to the Google
> Groups "H2 Database" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/h2-database/-/FOkozo4knkAJ.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.

[Lukas Eder]

Hmm, not sure what you mean by "table modification method". You mean
the TRUNCATE statement?

I didn't find any TRUNC() function in H2:
http://www.h2database.com/html/functions.html#trunc

Only TRUNCATE() (on numbers, not dates):
http://www.h2database.com/html/functions.html#truncate

So actually, yes, it would be lovely if TRUNC() / TRUNCATE() also
supported truncating dates, the way Oracle does, for instance :-)

2012/4/20 Noel Grandin <noelg...@gmail.com>:

[Noel Grandin]

My apologies, you are correct.

Care to have a bash at implementing this?
The parsing code is really easy to work with, straightforward
recursive-descent parser.
Just search for "TRUNCATE" and and start with making "TRUNC" an alias.

[Lukas Eder]

Yes, I can give it a shot. The code looks understandable enough for me
to add an alias - not the TRUNC(date, datepart) function, though :-)

Cheers
Lukas

2012/4/21 Noel Grandin <noelg...@gmail.com>:

[Lukas Eder]

It was fairly easy, indeed. Just added another function with the same
function ID. Here's the patch. I didn't find any test case for
TRUNCATE, which I could've enhanced. I successfully checked this query
in the H2 Console, though.

select trunc(23.45, -1), truncate(23.45, -1), trunc(23.45, 1),
truncate(23.45, 1)

Cheers
Lukas

2012/4/21 Noel Grandin <noelg...@gmail.com>:

[Noel Grandin]

Hi

Patch is fine. Do you think you could update the documention as well?

Search for truncate in the whole project, it's in an HTML file.

Thanks, Noel Grandin.

[Lukas Eder]

OK, let's try again :-)

New patch contains:
- changelog.html (should I do that?)
- help.csv (I followed the example from CHAR_LENGTH vs. CHARACTER_LENGTH)
- Function.java
- test-1.3.txt (did find the unit test. I suspect that test-1.2.txt is
for H2 1.2, as opposed to H2 1.3 and needs not be touched...?)

Cheers
Lukas

N.B: How do I get the tests to run? I get this exception:

C:\Users\lukas\workspace\h2>build.bat test
Target: test
Deleting temp
Deleting docs
Compiling 541 classes
Copying 1 files to temp
Compiling 487 classes
Copying 15 files to temp
Running org.h2.build.doc.GenerateHelp
Javadoc
java.lang.NoClassDefFoundError: RootDoc
at java.lang.Class.getDeclaredMethods0(Native Method)
at java.lang.Class.privateGetDeclaredMethods(Class.java:2427)
at java.lang.Class.getMethod0(Class.java:2670)
at java.lang.Class.getMethod(Class.java:1603)
at com.sun.tools.javadoc.DocletInvoker.invoke(DocletInvoker.java:246)
at com.sun.tools.javadoc.DocletInvoker.validOptions(DocletInvoker.java:198)
at com.sun.tools.javadoc.Start.parseAndExecute(Start.java:317)
at com.sun.tools.javadoc.Start.begin(Start.java:128)
at com.sun.tools.javadoc.Main.execute(Main.java:41)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.h2.build.BuildBase.invoke(BuildBase.java:241)
at org.h2.build.BuildBase.javadoc(BuildBase.java:483)
at org.h2.build.Build.resources(Build.java:639)
at org.h2.build.Build.compile(Build.java:195)
at org.h2.build.Build.compile(Build.java:90)
at org.h2.build.Build.test(Build.java:670)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.h2.build.BuildBase.invoke(BuildBase.java:241)
at org.h2.build.BuildBase.runTarget(BuildBase.java:207)
at org.h2.build.BuildBase.run(BuildBase.java:188)
at org.h2.build.Build.main(Build.java:35)
Caused by: java.lang.ClassNotFoundException: RootDoc
at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:248)
... 27 more

2012/4/21 Noel Grandin <noelg...@gmail.com>:

[Noel Grandin]

Sweet! Nice patch, committed to trunk.

Just for completeness sake, please confirm that you are contributing
this under the terms of the H2 license.
http://h2database.com/html/license.html

Thanks.

PS. that exception was probably because you need the java tools jar in
your classpath.

[Lukas Eder]

> Just for completeness sake, please confirm that you are contributing

Confirmed.

Cheers
Lukas

2012/4/22 Noel Grandin <noelg...@gmail.com>:

[Thomas Mueller]

Hi,

Thanks for the patch! Just to answer your questions:

New patch contains:
- changelog.html (should I do that?)

Yes.

 

- help.csv (I followed the example from CHAR_LENGTH vs. CHARACTER_LENGTH)

Perfect.

 

- Function.java

Yes.

 

- test-1.3.txt (did find the unit test. I suspect that test-1.2.txt is
for H2 1.2, as opposed to H2 1.3 and needs not be touched...?)

Just 1.3 is fine. test-1.2.txt will go away soon (probably on the first 1.4 beta). 

N.B: How do I get the tests to run? I get this exception:

I guess it didn't use the *correct* tools.jar (maybe the tools.jar from the wrong JDK?) but I'm not sure.

Regards,

Thomas

[Lukas Eder]

Hello

Thanks for the patch! Just to answer your questions:

You're welcome. Thanks for the feedback. Next time, I'm sure I'll get the tests running with the appropriate tools.jar.

Cheers
Lukas