[Feature Request]: Mathematical Built-in function power(x,y)

[dengn]

Is there an existing issue for the same bug?

• I have checked the existing issues.

Is your feature request related to a problem?

No response

Describe the solution you'd like

Returns the value of X raised to the power of Y.

mysql> SELECT POW(2,2);
        -> 4
mysql> SELECT POW(2,-2);
        -> 0.25

Describe alternatives you've considered

No response

Documentation, Adoption, Use Case, Migration Strategy

Please refer to the tutorial about how to develop a builtin function.

Additional information

No response

[SleepingPirate7]

I have some questions about this issue.Function power takes two numberic type as its parameter and there are ten number types (u8 u16 u32 u64 i8 i16 i32 i64 f32 f64).So there will be a total of 90 permutations.Do we need to write all of there permutations manually?

[SleepingPirate7]

Or is there any method to convert all parameters to float64? Then we only need to write one function with type func(float64,float64)float64.

[SleepingPirate7]

// BinOp is a binary operator.
type BinOp struct {
	LeftType   types.T
	RightType  types.T
	ReturnType types.T

	Fn func(*vector.Vector, *vector.Vector, *process.Process, bool, bool) (*vector.Vector, error)
}

What do there bool parameters mean?

[broccoliSpicy]

90 permutations, gee...
factorial numbers are monsters that haunt my dreams.

In situations like this, I think we could do this instead:

1. register only one power function like this

func init() {
	extend.FunctionRegistry["power"] = builtin.Power
	extend.BinaryReturnTypes[builtin.Power] = func(e0, e1 extend.Extend) types.T {
		return types.T_float64
	}

	extend.BinaryStrings[builtin.Power] = func(es []extend.Extend) string {
		return fmt.Sprintf("power(%s, %s)", es[0], es[1])
	}
	overload.OpTypes[builtin.Power] = overload.Binary
	overload.BinOps[builtin.Power] = []*overload.BinOp{
		{
			LeftType:   types.T_float64,
			RightType:  types.T_float64,
			ReturnType: types.T_float64,
			Fn: func(vector0, vector1 *vector.Vector, proc *process.Process, constant1, constant2 bool) (*vector.Vector, error) {
				// bla bla...
			},
		},
	}
}

2. In the pkg/sql/colexec/extend/overload/init.go file, there are maps that store operation casting rules, add casting rule for power function like this:

	targetType := []types.Type{
		{Oid: types.T_float64, Size: 8},
		{Oid: types.T_float64, Size: 8},
	}
	for _, left := range ints {
		for _, right := range ints {
			OperatorCastRules[builtin.Power] = append(OperatorCastRules[builtin.Power], []castRule{
					{NumArgs: 2, sourceTypes: []types.T{left, right}, targetTypes: targetType},
					{NumArgs: 2, sourceTypes: []types.T{right, left}, targetTypes: targetType},
			}...)
		}
	}
	// ... other casting rules for builtin.Power
       // ...

I think the above two step will perhaps help, sorry I didn't write any example binary functions as I promised in the "little primer" as I got bogged down with various other tasks, hope binary function doesn't confuse you much.

[broccoliSpicy]

```go
// BinOp is a binary operator.
type BinOp struct {
	LeftType   types.T
	RightType  types.T
	ReturnType types.T

	Fn func(*vector.Vector, *vector.Vector, *process.Process, bool, bool) (*vector.Vector, error)
}

What do there bool parameters mean?

the bool parameters indicates whether the vector passed in the function is actually just contain one value (hence the constant), and if so, we can modify our function to cope with that, pkg/sql/colexec/extend/overload/plus.go has some nice example regarding how to milk this situation for our function implementations.

[SleepingPirate7]

In theory, this idea could work. But in practice, if we do this, we will get an import cycle.

We need to import package builtin into package overload to use builtin.Power.In this case,it will generate an import cycle.
pkg/builtin/types.go:

import (
	"github.com/matrixorigin/matrixone/pkg/sql/colexec/extend/overload"
)
...

pkg/sql/colexec/extend/overload/init.go:

import (
	"github.com/matrixorigin/matrixone/pkg/builtin"
)

@broccoliSpicy

[broccoliSpicy]

stupid me... I completely forgot the fact that we include overload in the builtin package.

In this case, perhaps move the above "inserting rules to OperatorCastRules map" code to the builtin package could untangle this cycle.
I think Golang allows writing more than one init function in a single file, so we could write another init function in builtin/binary/power.go or simply put our "inserting to a map" code in the original init function.
or perhaps there are more elegant approaches to untangle this issue... @WenhaoKong2001

[dengn]

Already implemented. Thanks! @WenhaoKong2001