![(please configure the [header_logo] section in trac.ini)](/chrome/site/your_project_logo.png){kind=logo}
Numeric functions and operators
Numeric constants
| Function | HTSQL | SQL | Javascript | Python |
| The Pi constant | pi() | pi() | Math.PI | math.pi |
| The E constant | e() | exp(1.0) | Math.E | math.e |
| | nan() ??? | NUMERIC 'NaN' | -- | -- |
| | infinity() ??? | DOUBLE PRECISION 'Infinity' | Infinity | -- |
| | -infinity() (doesn't really work) | DOUBLE PRECISION '-Infinity' | -Infinity | -- |
| | nan() (difference between float and numeric?) | DOUBLE PRECISION 'NaN' | NaN | -- |
Signatures:
- htsql:pi() -> Number
- htsql:e() -> Number
???
PostgreSQL supports NaN, Infinity and -Infinity float values. However there seems no mean to check for these values. There's also a NUMERIC NaN value.
PostgreSQL produces the error value out of range: overflow for the expression (- DOUBLE PRECISION 'Infinity') so it could not be used for producing the (DOUBLE PRECISION '-Infinity') value.
On the other hand, the following expression are true:
- NUMERIC 'NaN' = NUMERIC 'NaN'
- DOUBLE PRECISION 'NaN' = DOUBLE PRECISION 'NaN'
- DOUBLE PRECISION 'Infinity' = DOUBLE PRECISION 'Infinity'
- DOUBLE PRECISION '-Infinity' = DOUBLE PRECISION '-Infinity'
- DOUBLE PRECISION 'NaN' > DOUBLE PRECISION 'Infinity'
- DOUBLE PRECISION 'Infinity' > DOUBLE PRECISION '-Infinity'
It's not clear if adding NaN, Infinity, and -Infinity constants will add any value to HTSQL?
Supporting special IEEE 754 values is a tough problem. It is better to drop them totally if we cannot support them properly.
Numeric cast
| Function | HTSQL | SQL | Javascript | Python |
| Cast to Number | number(value) | CAST(value AS NUMERIC) | Number(value) | int(value); float(value) |
| Cast to Integer | integer(value) | CAST(value AS INTEGER) | -- | int(value) |
Signatures:
- htsql:number(Number value) -> Number
- htsql:number(String value) -> Number
- htsql:integer(Number value) -> Number(scale=0)
- htsql:integer(String value) -> Number(scale=0)
- htsql:integer(Boolean value) -> Number(scale=0)
Type resolution: htsql:number(Untyped => Number), htsql:integer(Untyped => Number).
If the argument of the function htsql:number() is a Number, it is no-op. A String argument is converted to a number using the SQL CAST(... AS NUMERIC) operator.
FIXME: htsql:number(Boolean | BitString | TimeInterval value) -> Number ???
A possible alternative form:
- htsql.number(Number | String value[, Number precision[, Number scale]]) -> Number
The expression number(value, precision, scale) is converted to the SQL expression CAST(value AS NUMERIC(precision, scale)). Therefore the parameters precision and scale must be literal values. The question is: do we really need this form?
Arithmetic operators
| Function | HTSQL | SQL | Javascript | Python |
| Unary + (no-op) | +x | + x | + x | + x |
| Unary - (negation) | -x | - x | - x | - x |
| Binary + (addition) | x+y | x + y | x + y | x + y |
| Binary - (substraction) | x-y | x - y | x - y | x - y |
| Binary * (multiplication) | x*y | x * y | x * y | x * y |
| Binary / (division) | x div y | x / y | x / y | x / y; x // y |
| Binary % (remainder) | x mod y | x % y | x % y | x % y |
| Binary ^ (power) | x ^ y | x ^ y | Math.pow(x, y) | x ** y |
Signatures:
- htsql:operator:"+()"(Number x) -> Number
- htsql:operator:"-()"(Number x) -> Number
- htsql:operator:"+"(Number x, Number y) -> Number
- htsql:operator:"-"(Number x, Number y) -> Number
- htsql:operator:"*"(Number x, Number y) -> Number
- htsql:operator:"div"(Number x, Number y) -> Number
- htsql:operator:"mod"(Number x, Number y) -> Number
- htsql:operator:"^"(Number x, Number y) -> Number
Type resolution:
- htsql:operator:"+()"(Untyped => Number)
- htsql:operator:"-()"(Untyped => Number)
- htsql:operator:"+"(Untyped => Number, Number), htsql:operator:"+"(Number, Untyped => Number)
- htsql:operator:"-"(Untyped => Number, Number), htsql:operator:"-"(Number, Untyped => Number)
- htsql:operator:"*"(Untyped => Number, Number), htsql:operator:"*"(Number, Untyped => Number)
- htsql:operator:"div"(Untyped => Number, Untyped => Number)
- htsql:operator:"mod"(Untyped => Number, Untyped => Number)
- htsql:operator:"^"(Untyped => Number, Untyped => Number)
Notice: integer division truncates results.
Bitwise operations
| Function | HTSQL | SQL | Javascript | Python |
| Bitwise AND | bitwise_and(x,y) | x & y | x & y | x & y |
| Bitwise OR | bitwise_or(x,y) | x | y | x | y | x | y |
| Bitwise XOR | bitwise_xor(x,y) | x # y | x ^ y | x ^ y |
| Bitwise NOT | bitwise_not(x) | ~ x | ~ x | ~ x |
| Bitwise shift left | bitwise_lshift(x,y) | x << y | x << y | x << y |
| Bitwise shift right | bitwise_rshift(x,y) | x >> y | x >> y | x >> y |
Signatures:
- htsql:bitwise_and(Number(is_integer=True) x, Number(is_integer=True) y) -> Number(is_integer=True)
- htsql:bitwise_or(Number(is_integer=True) x, Number(is_integer=True) y) -> Number(is_integer=True)
- htsql:bitwise_xor(Number(is_integer=True) x, Number(is_integer=True) y) -> Number(is_integer=True)
- htsql:bitwise_not(Number(is_integer=True) x) -> Number(is_integer=True)
- htsql:bitwise_lshift(Number(is_integer=True) x, Number(is_integer=True) y) -> Number(is_integer=True)
- htsql:bitwise_rshift(Number(is_integer=True) x, Number(is_integer=True) y) -> Number(is_integer=True)
Type resolution: Untyped => Number for all arguments
Mathematical functions
| Function | HTSQL | SQL | Javascript | Python |
| Absolute value of the argument | abs(x) | abs(x) | Math.abs(x) | abs(x) |
| Sign of the argument | sign(x) | sign(x) | -- | -- |
| Ceiling (smallest integer >= argument) | ceil(x) | ceil(x) | Math.ceil(x) | math.ceil(x) |
| Floor (largest integer <= argument) | floor(x) | floor(x) | Math.floor(x) | math.floor(x) |
| Truncate toward zero | trunc(x[,n=0]) | trunc(x[, n]) | -- | -- |
| Round to nearest integer | round(x[,n=0]) | round(x[, n]) | -- | round(x[, n=0]) |
| Square root | sqrt(x) | sqrt(x) | Math.sqrt(x) | math.sqrt(x) |
| | cbrt(x) (?) | cbrt(x) | -- | -- |
| Exponential | exp(x) | exp(x) | Math.exp(x) | math.exp(x) |
| Natural logarithm | ln(x) | ln(x) | Math.log(x) | math.log(x) |
| Base 10 logarithm | log10(x) | log(x) | -- | math.log10(x) |
| Logarithm | log(x,b) | log(x, b) | -- | math.log(x, b) |
| Radians to degrees | degrees(x) | degress(x) | -- | degrees(x) |
| Degrees to radians | radians(x) | radians(x) | -- | radians(x) |
| Set seed for random() calls | setseed(x) (?) | setseed(x) | -- | random.seed([x]) |
| Random value between 0.0 and 1.0 | random() | random() | Math.random() | random.random() |
Signatures:
- htsql:abs(Number x) -> Number
- htsql:sign(Number x) -> Number
- htsql:ceil(Number x) -> Number
- htsql:floor(Number x) -> Number
- htsql:trunc(Number x[, Number(is_integer=True) n]) -> Number
- htsql:round(Number x[, Number(is_integer=True) n]) -> Number
- htsql:sqrt(Number x) -> Number
- htsql:cbrt(Number x) -> Number
- htsql:exp(Number x) -> Number
- htsql:ln(Number x) -> Number
- htsql:log10(Number x) -> Number
- htsql:log(Number x, Number b) -> Number
- htsql:degrees(Number x) -> Number
- htsql:radians(Number x) -> Number
- htsql:setseed(Number x) -> Number
- htsql:random() -> Number
Type resolution: Untyped => Number for all arguments
Notes:
- The default base for the function log() is 10 in SQL and 2 in Python. So we would better not allow using log() without the explicit base.
- The function cbrt() should be dropped. Could be easily replaced with x ^ (1.0/3.0).
- The function setseed() should be dropped or, at least, renamed.
Trigonometric functions
| Function | HTSQL | SQL | Javascript | Python |
| Sine | sin(t) | sin(t) | Math.sin(t) | math.sin(t) |
| Cosine | cos(t) | cos(t) | Math.cos(t) | math.cos(t) |
| Tangent | tan(t) | tan(t) | Math.tan(t) | math.tan(t) |
| Cotangent | cot(t) | cot(t) | -- | -- |
| Inverse sine | asin(t) | asin(t) | Math.asin(t) | math.asin(t) |
| Inverse cosine | acos(t) | acos(t) | Math.acos(t) | math.acos(t) |
| Inverse tangent | atan(t) | atan(t) | Math.atan(t) | math.atan(t) |
| Inverse tangent of y/x | atan2(y,x) | atan2(y, x) | Math.atan2(y, x) | math.atan2(y, x) |
Signatures:
- htsql:sin(Number t) -> Number
- htsql:cos(Number t) -> Number
- htsql:tan(Number t) -> Number
- htsql:cot(Number t) -> Number
- htsql:asin(Number t) -> Number
- htsql:acos(Number t) -> Number
- htsql:atan(Number t) -> Number
- htsql:atan2(Number y, Number x) -> Number
Type resolution: Untyped => Number for all arguments
