Forum: Building VoltDB Applications

Post: SQL Functions

SQL Functions
skashikar
Mar 23, 2010
Hi,
Is there a doc listing built-in functions callable from SQL- aggregates or regular?
Also, are user-defined functions callable from SQL planned for future release?
Thanks,
Swapnil
SQL Functions
bheath
Mar 23, 2010
Hi Swapnil,
The Using VoltDB Guide describes supported SQL for VoltDB in Appendix B. The docs can be found at:
https://community.voltdb.com/sites/default/files/UsingVoltDB.pdf
User defined functions are not currently supported, but may be in a future release. If you can give more detail about the functions you are interested in, we may be able to help you include that logic within a stored procedure.
Regards,
Bobbi
SQL Functions
skashikar
Apr 2, 2010
Hi Swapnil,
The Using VoltDB Guide describes supported SQL for VoltDB in Appendix B. The docs can be found at:
https://community.voltdb.com/sites/default/files/UsingVoltDB.pdf
User defined functions are not currently supported, but may be in a future release. If you can give more detail about the functions you are interested in, we may be able to help you include that logic within a stored procedure.
Regards,
Bobbi


Hi Bobbi,
I just meant it as a general query about features, but thanks for the offer.
I have one more general query - in a new thread.
Thanks,
Swapnil
SQL Functions
tcallaghan
Apr 3, 2010
Hi Bobbi,
I just meant it as a general query about features, but thanks for the offer.
I have one more general query - in a new thread.
Thanks,
Swapnil


Swapnil,
Just to follow up on Bobbi's response.
We currently support SUM(), MIN(), MAX(), COUNT(), and AVG() aggregate functions. We do not have any single-row SQL functions at this point. VoltDB stored procedures are extremely efficient and you can easily implement TRUNC(), ROUND(), NVL(), DECODE(), etc. functionality within Java. We do plan on adding single-row SQL functions to our supported SQL as we get feedback from the community.
Are there any SQL functions that you find invaluable?
-Tim
make sticky maybe
henning
Apr 16, 2010
Swapnil,
Just to follow up on Bobbi's response.
We currently support SUM(), MIN(), MAX(), COUNT(), and AVG() aggregate functions. We do not have any single-row SQL functions at this point. VoltDB stored procedures are extremely efficient and you can easily implement TRUNC(), ROUND(), NVL(), DECODE(), etc. functionality within Java. We do plan on adding single-row SQL functions to our supported SQL as we get feedback from the community.
Are there any SQL functions that you find invaluable?
-Tim


You might want to make this question a new, sticky thread exclusively for requests of built-in functions.
Performance
henning
Apr 3, 2010
Is a Java implemented round() in a stored procedure always going to be as fast as a (coming) VoltDB built-in function? Because VoltDB is written in Java anyway?
Or will there be performance advantages of built-in functions in the future?
Java round() vs. SQL round()
tcallaghan
Apr 5, 2010
Is a Java implemented round() in a stored procedure always going to be as fast as a (coming) VoltDB built-in function? Because VoltDB is written in Java anyway?
Or will there be performance advantages of built-in functions in the future?


Henning,
There will be a minor performance advantage to having single-row SQL functions, as in the following:

select round(a.quantity * a.price,2) as value from a where a.order_id = 5; To implement this in Java I'll have to bring back the value of a.quantity as well as a.price, so I'll have some additional overhead. However, this is already very fast in VoltDB.
As users build VoltDB applications we will prioritise the additional functionality according to need.
-Tim
Bit operators
David Best
Jan 4, 2011
Henning,
There will be a minor performance advantage to having single-row SQL functions, as in the following:

select round(a.quantity * a.price,2) as value from a where a.order_id = 5; To implement this in Java I'll have to bring back the value of a.quantity as well as a.price, so I'll have some additional overhead. However, this is already very fast in VoltDB.
As users build VoltDB applications we will prioritise the additional functionality according to need.
-Tim


Where should we post enhancement requests?
I would like some bit operators:
select * from <table> where <col> band 0x0011 > 0;
This would give us an OR-clause functionality suitable for our current needs.
bitwise and
tcallaghan
Jan 6, 2011
Where should we post enhancement requests?
I would like some bit operators:
select * from <table> where <col> band 0x0011 > 0;
This would give us an OR-clause functionality suitable for our current needs.


David,
I've created a community request ticket in our Jira system at http://issues.voltdb.com/browse/ENG-955.
You can vote on the item at that link.
-Tim