Regression for SUM() operator?

[Bill Oliver-2]

Hello all!

I have this test - the last select fails against HEAD with "expression
evaluation not supported". It seems to me that it should work - it used to
work in Vulcan, but perhaps the rules were tightened up?

SET NAMES ASCII;
CREATE DATABASE 'test.fdb' DEFAULT CHARACTER SET ISO8859_1;

 CREATE TABLE USER_INPUT ( USER_ID INT, USER_TYPED CHAR (10), CASH_BALANCE
NUMERIC (5, 2));
 INSERT INTO USER_INPUT VALUES (0, '999.99', 999.99);
 INSERT INTO USER_INPUT VALUES (1, '-999.99', -999.99);
 INSERT INTO USER_INPUT VALUES (2, '  54.', 54);
 INSERT INTO USER_INPUT VALUES (CAST ('3' AS INT), CAST (-7.02 AS CHAR
(10)), CAST (' -.702E+1' AS NUMERIC (5, 2)));
 SELECT CAST (AVG (CAST (USER_TYPED AS INT)) AS INT) FROM USER_INPUT;
--  PASS:0647 If 1 row selected and value is 11 or 12?

 SELECT AVG (cast (USER_TYPED as numeric (5,2))) FROM USER_INPUT;
--  PASS:0647 If 1 row selected and value is 11.745 +- 0.01?

 UPDATE USER_INPUT SET USER_TYPED = CAST (0 AS CHAR (10)), CASH_BALANCE =
CASH_BALANCE - CAST ('500' AS NUMERIC (5, 2)) WHERE USER_ID = CAST ('-0' AS
INT);
 SELECT SUM (USER_TYPED) * 100, SUM (cast (CASH_BALANCE as real)) FROM
USER_INPUT;
--  PASS:0647 If 1 row selected and first value is -95301 +- 4?
--  PASS:0647 AND second value is -453.02 +- 0.04?



With Vulcan, I get:

 SELECT SUM (USER_TYPED) * 100, SUM (cast (CASH_BALANCE as real)) FROM
USER_INPUT;

                                            SUM
======================= =======================
     -95301.00000000000      -453.0199999809265



If this is a bug, I suspect the fix is dsql/make.cpp, to allow text for
nod_agg_total2, as below

  case nod_agg_total2:
   MAKE_desc(threadData, desc, node->nod_arg[0], null_replacement);
   dtype = desc->dsc_dtype;
   if (!DTYPE_IS_NUMERIC(desc->dsc_dtype) &&
    !DTYPE_IS_TEXT(desc->dsc_dtype))
   {
    ERRD_post(isc_expression_eval_err, 0);
   }

As usual, if this is confirmed bug, I can enter a tracker.

-bill



-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[asfernandes]

Bill Oliver escreveu:
> With Vulcan, I get:
>
>  SELECT SUM (USER_TYPED) * 100, SUM (cast (CASH_BALANCE as real)) FROM
> USER_INPUT;
>
>                                             SUM
> ======================= =======================
>      -95301.00000000000      -453.0199999809265
Vulcan is wrong. CHAR + CHAR is error, so SUM(CHAR) is error too.


Adriano


-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Dmitry Yemanov]

Adriano dos Santos Fernandes wrote:

>
>> With Vulcan, I get:
>>
>>  SELECT SUM (USER_TYPED) * 100, SUM (cast (CASH_BALANCE as real)) FROM
>> USER_INPUT;
>>
>>                                             SUM
>> ======================= =======================
>>      -95301.00000000000      -453.0199999809265
>
> Vulcan is wrong. CHAR + CHAR is error, so SUM(CHAR) is error too.

To be more precise, this is true for Dialect 3 only which tries to
enforce type safety in arithmetics. The legacy Dialect 1 allows addition
of numbers represented via strings, but AFAIK this is against the SQL
specification.


Dmitry

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Dmitry Yemanov]

Bill Oliver wrote:
>
> I have this test - the last select fails against HEAD with "expression
> evaluation not supported". It seems to me that it should work - it used to
> work in Vulcan, but perhaps the rules were tightened up?

In both FB v1.5 and Vulcan, <string> + <string> is [intentionally]
prohibited in Dialect 3, but sum(<string>) was allowed. This was
considered a bug and hence fixed in FB v2.0.


Dmitry

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel