You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Jeffrey Lichtman <sw...@rcn.com> on 2005/09/29 07:13:41 UTC
Re: [PATCH] (DERBY-582) Dynamic parameter should be allowed to
be the operand of unary operator "-". Derby throws exception 42X36:
The '-' operator is not allowed to take a ? parameter as an operand."
>I have a simple patch to allow dynamic parameters for unary minus
>and plus. The parameter will be bound to DOUBLE. The patch is
>attached to JIRA. Can a commiter please commit it if everything
>looks good? I have run all the tests and no new failures. Have also
>added a test for this.
I'm not convinced this is a good idea.
The SQL standard limits the use of parameters to those places where
their types can be determined unambiguously, for example, in an
insert or update list, or as an argument to the CAST function. I
don't know of any general way to figure out what the type of a
parameter "should" be in other places, especially for overloaded
functions and operators like - (both unary and binary).
What will happen if Cloudscape binds a parameter to a double, and the
user tries to use it with a fixed-point type like decimal? It's
likely there will be a loss of precision, and the user won't get what
he expects.
If the user wants to use a parameter with unary minus, I suggest the
use of the cast function to make the type unambiguous:
- (cast ? as <type>)
- Jeff Lichtman
swazoo@rcn.com
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/
Re: [PATCH] (DERBY-582) Dynamic parameter should be allowed to be the operand of unary operator "-". Derby throws exception 42X36: The '-' operator is not allowed to take a ? parameter as an operand."
Posted by Mamta Satoor <ms...@gmail.com>.
My logic is trying to do what you explained in the 2nd paragraph. As for the
cases, where the parameters are excluded, parameters under a unary minus
will be excluded automatically too because I have defined UnaryOperatorNode
to extend ParameterNode. The existing code (where parameters are disallowed)
checks for isParameterNode() and throws exception if isParameterNode()
returns true. I have added the method isParameterNode() to UnaryOperatorNode
which returns true if it is unary minus/unary plus and has a parameter for
the operand. Because of this implementation scheme, I didn't have to go look
at the existing parameter exclusion code to also check for unary minus/unary
plus parameters.
I should have the code for review within a day or two, need to write
comments and more tests.
Mamta
On 10/2/05, Jeffrey Lichtman <sw...@rcn.com> wrote:
>
> I've been thinking about where "- ?" should be allowed in queries.
> Currently, parameters are allowed only in certain situations - the
> documentation lists seventeen different places where parameters can
> be used. These places were chosen because it's possible to figure out
> the type of the parameter from the context. For example, a parameter
> can be used as the first operand of BETWEEN as long as the one of the
> second and third operands is not a parameter.
>
> It seems to me that a parameter could be used with a unary minus
> anywhere a "bare" parameter is allowed. Derby could use the same
> rules to determine the type of the parameter as if the minus weren't
> there. Obviously, there would have to be an error if the type isn't
> numeric.
>
> I don't think we should allow unary minus on parameters in any other
> cases. This seems obvious, but it means poring over the code and
> looking for all the places where parameters are excluded, and making
> it look for parameters under a unary minus.
>
>
> - Jeff Lichtman
> swazoo@rcn.com
> Check out Swazoo Koolak's Web Jukebox at
> http://swazoo.com/
>
>
Re: [PATCH] (DERBY-582) Dynamic parameter should be allowed to
be the operand of unary operator "-". Derby throws exception 42X36:
The '-' operator is not allowed to take a ? parameter as an operand."
Posted by Jeffrey Lichtman <sw...@rcn.com>.
I've been thinking about where "- ?" should be allowed in queries.
Currently, parameters are allowed only in certain situations - the
documentation lists seventeen different places where parameters can
be used. These places were chosen because it's possible to figure out
the type of the parameter from the context. For example, a parameter
can be used as the first operand of BETWEEN as long as the one of the
second and third operands is not a parameter.
It seems to me that a parameter could be used with a unary minus
anywhere a "bare" parameter is allowed. Derby could use the same
rules to determine the type of the parameter as if the minus weren't
there. Obviously, there would have to be an error if the type isn't numeric.
I don't think we should allow unary minus on parameters in any other
cases. This seems obvious, but it means poring over the code and
looking for all the places where parameters are excluded, and making
it look for parameters under a unary minus.
- Jeff Lichtman
swazoo@rcn.com
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/
Re: [PATCH] (DERBY-582) Dynamic parameter should be allowed to be the operand of unary operator "-". Derby throws exception 42X36: The '-' operator is not allowed to take a ? parameter as an operand."
Posted by Mamta Satoor <ms...@gmail.com>.
Same as what happens for where t1.int_column + ? = -? ie the type of
parameter in -? will be int no matter which side of equal operator it
appears in the 2 ways of writing this where clause.
On 9/29/05, Jeffrey Lichtman <sw...@rcn.com> wrote:
>
>
> > where t1.int_column + ? = - ?
> >The first parameter gets bound to the type of t1.int_column and then
> >second parameter gets bound to the same type too.
>
> OK, then how about:
>
> where - ? = t1.int_column + ?
>
>
> - Jeff Lichtman
> swazoo@rcn.com
> Check out Swazoo Koolak's Web Jukebox at
> http://swazoo.com/
>
>
Re: [PATCH] (DERBY-582) Dynamic parameter should be allowed to
be the operand of unary operator "-". Derby throws exception 42X36:
The '-' operator is not allowed to take a ? parameter as an operand."
Posted by Jeffrey Lichtman <sw...@rcn.com>.
> where t1.int_column + ? = - ?
>The first parameter gets bound to the type of t1.int_column and then
>second parameter gets bound to the same type too.
OK, then how about:
where - ? = t1.int_column + ?
- Jeff Lichtman
swazoo@rcn.com
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/
Re: [PATCH] (DERBY-582) Dynamic parameter should be allowed to be the operand of unary operator "-". Derby throws exception 42X36: The '-' operator is not allowed to take a ? parameter as an operand."
Posted by Mamta Satoor <ms...@gmail.com>.
Hi Jeff,
I ran the test cases that you brought up with my very rough code changes
and the code seems to handle them all correctly.
Here is what happens in each of the cases
where t1.int_column + ? = - ?
The first parameter gets bound to the type of t1.int_column and then second
parameter gets bound to the same type too.
where t1.int_column = ? / - ?
This will not be accepted by Derby because both the operators for / are
parameters. In fact, if you tried the same query but without the unary
operator ie where t1.int_column = ? / ?, Derby will throw an exception that
both parameters around / can't be parameters.
where - ? not in (select t1.int_column + ? from . . .)
The second parameter gets the type of t1.int_column and then same type gets
assigned to the first parameter.
thanks,
Mamta
On 9/28/05, Jeffrey Lichtman <sw...@rcn.com> wrote:
>
>
> >I agree with your and Dan's point about possible precision loss. I
> >am trying to find a way where rather than hard coding the -?/+? to
> >Double, try to get it's datatype from the context where it is used.
>
> There are circumstances where you can figure this out, but it's
> ambiguous in the general case. For example:
>
> where t1.int_column + ? = - ?
>
> Since both sides of the "=" operator have parameters, you can't use
> the type of one side to figure out the type of the other side.
>
> Here are some more examples of ambiguity:
>
> where t1.int_column = ? / - ?
>
> where - ? not in (select t1.int_column + ? from . . .)
>
> I don't think it's a good idea to try to solve this problem at all.
> There are a few cases where it's obvious what the type should be, and
> many more where it's not. There's also an easy workaround (use the
> CAST function). Even if we were able to figure out an algorithm to
> determine the type of a parameter in all cases, would it be possible
> to explain the behavior to an average user?
>
>
> - Jeff Lichtman
> swazoo@rcn.com
> Check out Swazoo Koolak's Web Jukebox at
> http://swazoo.com/
>
>
Re: [PATCH] (DERBY-582) Dynamic parameter should be allowed to
be the operand of unary operator "-". Derby throws exception 42X36:
The '-' operator is not allowed to take a ? parameter as an operand."
Posted by Jeffrey Lichtman <sw...@rcn.com>.
>I agree with your and Dan's point about possible precision loss. I
>am trying to find a way where rather than hard coding the -?/+? to
>Double, try to get it's datatype from the context where it is used.
There are circumstances where you can figure this out, but it's
ambiguous in the general case. For example:
where t1.int_column + ? = - ?
Since both sides of the "=" operator have parameters, you can't use
the type of one side to figure out the type of the other side.
Here are some more examples of ambiguity:
where t1.int_column = ? / - ?
where - ? not in (select t1.int_column + ? from . . .)
I don't think it's a good idea to try to solve this problem at all.
There are a few cases where it's obvious what the type should be, and
many more where it's not. There's also an easy workaround (use the
CAST function). Even if we were able to figure out an algorithm to
determine the type of a parameter in all cases, would it be possible
to explain the behavior to an average user?
- Jeff Lichtman
swazoo@rcn.com
Check out Swazoo Koolak's Web Jukebox at
http://swazoo.com/
Re: [PATCH] (DERBY-582) Dynamic parameter should be allowed to be the operand of unary operator "-". Derby throws exception 42X36: The '-' operator is not allowed to take a ? parameter as an operand."
Posted by Mamta Satoor <ms...@gmail.com>.
Hi Jeff,
I agree with your and Dan's point about possible precision loss. I am
trying to find a way where rather than hard coding the -?/+? to Double, try
to get it's datatype from the context where it is used.
For instance, say there is a table t1 with columns c11 as decimal(2,1) and
c12 as int, Then for following sql
select * from t1 where c11 = -? and c12 = +?
The first parameter should be bound to decimal and the second parameter
should be bound to int.
Looking at the code for UnaryOperatorNode/UnaryArithmeticOperatorNode, it
seems like it is going to be tricky to pass on the datatype of the left
operand in above example to UnaryArithmeticOperator.
Some info about current code flow for the specific example above and one
possible generic solution
BinaryOperatorNode.bindExpression calls bindExpression on the left operand
(line 307 in the code) and then on the right operand
(UnaryArithmeticOperatorNode). The right operand at this point doesn't know
what type it should be bound to. In my preliminary changes, I am making the
bindExpression no-op for UnaryArithmeticOperatorNode if it has a parameter
operand which is not bound to any type yet. Later in
BinaryOperatorNode.bindExpression (line 335), the right operand gets bound
to the same type as the left operand through the setDescriptor call, which
is what we want. Once we have the correct type, we now want to run
bindExpression on the UnaryArithmeticOperator. In my changes, I am
overriding the setDescriptor method in UnaryArithmeticOperatorNode and
having it call the bindExpression after it sets the type of the parameter to
the left operand's type. Calling bindExpression will run all the bind time
rules that should be run on a UnaryArithmeticOperatorNode. I haven't run
enough tests to know if this is full proof but looking from outside, does
anyone think there is a better solution than this? Even if my changes work
fine, they seem more like a hack because during the actual bindExpression
call, I am treating it as a no-op and then forcing the bindExpression later
when UnaryArithmeticOperator does get the correct type. Any feedback from
the community on this will be great.
thanks,
Mamta
On 9/28/05, Jeffrey Lichtman <sw...@rcn.com> wrote:
>
>
> >I have a simple patch to allow dynamic parameters for unary minus
> >and plus. The parameter will be bound to DOUBLE. The patch is
> >attached to JIRA. Can a commiter please commit it if everything
> >looks good? I have run all the tests and no new failures. Have also
> >added a test for this.
>
> I'm not convinced this is a good idea.
>
> The SQL standard limits the use of parameters to those places where
> their types can be determined unambiguously, for example, in an
> insert or update list, or as an argument to the CAST function. I
> don't know of any general way to figure out what the type of a
> parameter "should" be in other places, especially for overloaded
> functions and operators like - (both unary and binary).
>
> What will happen if Cloudscape binds a parameter to a double, and the
> user tries to use it with a fixed-point type like decimal? It's
> likely there will be a loss of precision, and the user won't get what
> he expects.
>
> If the user wants to use a parameter with unary minus, I suggest the
> use of the cast function to make the type unambiguous:
>
> - (cast ? as <type>)
>
>
> - Jeff Lichtman
> swazoo@rcn.com
> Check out Swazoo Koolak's Web Jukebox at
> http://swazoo.com/
>
>