My organization is currently testing a new version of a vendor's software. The most significant change as far as the database goes is the upgrade from SQL Anywhere 9 (126.96.36.19924) to SQL Anywhere 11 (188.8.131.528). For the most part, everything works great, but there is an issue I am having trouble diagnosing.
SQLA9 and SQLA11 treat this scenario differently and I am wondering if someone could explain why or point me to documentation to help. It's easiest explained by a stripped-down example:
-- Create table CREATE TABLE mytable ( amount CHAR(10) NOT NULL ); -- Insert test data INSERT INTO mytable ( amount ) VALUES ( 20000 ); INSERT INTO mytable ( amount ) VALUES ( 10000 ); INSERT INTO mytable ( amount ) VALUES ( 0 ); INSERT INTO mytable ( amount ) VALUES ( 'Included' ); INSERT INTO mytable ( amount ) VALUES ( -1000 );
At this point, both databases return the same data, of course:
-- Lookie at all the data SELECT * FROM mytable; SQLA9 SQLA11 amount amount '20000' '20000' '10000' '10000' '0' '0' 'Included' 'Included' '-1000' '-1000'
I need to do numeric operations on the data that is actually numeric, so I use ISNUMERIC():
-- Select numeric entries SELECT * FROM mytable WHERE ISNUMERIC( amount ) = 1; SQLA9 SQLA11 amount amount '20000' '20000' '10000' '10000' '0' '0' '-1000' '-1000'
One last requirement- The numbers I'm working with have to be positive numbers and non-zero:
-- Select numeric entries greater than zero SELECT * FROM mytable WHERE ISNUMERIC( amount ) = 1 AND amount > 0; SQLA9 SQLA11 amount amount '20000' '20000' '10000' '10000'
This is the data I want, so I will attempt to use SUM() to get the total:
-- Get the sum of non-zero numeric entries SELECT SUM( amount ) FROM mytable WHERE ISNUMERIC( amount ) = 1 AND amount > 0 SQLA9 SQLA11 SUM( amount ) Cannot convert 'Included' to a numeric '30000' SQLCODE=-157, ODBC 3 State="07006"
The error is being thrown by "amount > 0" in SQLA11. Without it, the SUM() works. That predicate only appears to be an issue when using numeric aggregates (SUM, AVG, etc). The database must be applying the WHERE clause to the data first, in either scenario, so I am confused as to why this is happening. The SQLA9 behaviour seems correct, but maybe I'm missing something obvious.
Thanks in advance!
SQL Anywhere's query execution engine was completely rewritten for version 10 and this is the reason why you are seeing the difference. SQL does not give you any guarentees on the order in which the predicates are computed. In your example, the executing engine has chosen to evaluate "amount > 0" prior to evaluating "isnumeric(amount) = 1".
This works in my simple tests:
I needed to "hide" the amount value in the SUM operator inside the IF clause so that the execution engine did not attempt to (prematurely) evaluate the column as numeric while reading the column from the row.
Based on Mark's example, the following (somewhat shorter) query works with SA 12.0.1, too:
It doesn't need a particular WHERE clause as for the if expression, since the following is guaranteed (as to the docs):
As such, an IF expression (or an CASE expression) can be used when "short-circuited" evaluation (as in C/C++) is needed.
I asked something along these lines wrt the order of results. The response I received indicated that the order of things that are equal is decided when the statement is processed, not necessarily by the order you type it.
I would guess that just because the isnumeric is first, it does not remove the rows it excludes from evaluation from the second condition's evaluation. 'Included' is not numeric, so it will return an error for > 0.
I've also seen it stated that storing numbers as text is generally a bad idea, but as you said you can't change the schema.
Since they are strings, this may work:
Just a guess.