SQLA9 and SQLA11 Differences in Numeric Aggregates on Character Columns

[Former Member]

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 (9.0.2.3924) to SQL Anywhere 11 (11.0.1.2538). 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!

Notes:

1. I can't change the schema 🙂
2. The topic should probably be modified to something more sensible.

[MarkCulp · Accepted solution]

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:

SELECT SUM( if isnumeric( amount ) = 1 then cast( amount as int ) else 0 endif ) as total
  FROM mytable 
 WHERE ( case 
         when ISNUMERIC( amount ) = 1 then
            ( if amount > 0 then 1 else 0 endif )
         else 0
         end ) > 0;

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.

[Former Member · Accepted solution]

That explains it, thanks Mark!

[VolkerBarth]

Based on Mark's example, the following (somewhat shorter) query works with SA 12.0.1, too:

:::SQL
SELECT SUM(if isnumeric(amount) = 1
           then if cast(amount as int) > 0 then cast(amount as int) else 0 end if
           else 0 end if) as total
FROM mytable

It doesn't need a particular WHERE clause as for the if expression, since the following is guaranteed (as to the docs😞

IF condition THEN expression1 [ ELSE expression2 ] { ENDIF | END IF }

expression1 is evaluated only if condition is TRUE. Similarly, expression2 is evaluated only if condition is FALSE.

As such, an IF expression (or an CASE expression) can be used when "short-circuited" evaluation (as in C/C++) is needed.

[Former Member]

This works great too, thanks!

[former_SQLA_member1694868]

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:

:::SQL

SELECT SUM( amount ) FROM mytable 
WHERE ISNUMERIC( amount ) = 1 AND amount not like '-%'

Just a guess.

[Former Member]

That's a pretty clever solution. It looks like it works for the examples I tested it with.