>>---------------------------------------------------------------------
>>-- Component: NonStop SQL Regression Test Suite
>>-- Description:
>>--   This test unit is a positive and negative local test for arithmetic
>>--expressions as they appear in the select statement.  Precedence,  type
>>--propagation, and expression overflow in SELECT statement expressions
>>--are all tested. Truncation on assignment is also tested here, in the
>>--context of INSERT and UPDATE. Truncation on assignment in SELECT INTO
>>--statements is performed.  This is a 'select statement test unit' only
>>--in the sense that the bulk of the tests are done in the context of select
>>--statements. This test unit is really a test of arithmetic expression
>>--handling in SQL.
>>--   This test unit uses tables btsel26, btsel27 and various
>>--other tables from the select global database.  The assignment
>>--truncation tests also create, use, and drop temporary tables. The
>>--testcases in this testunit are as follows:
>>
>>--     A0: precedence tests
>>--     A1: addition/subtraction overflow (column values only)
>>--     A2: addition/subtraction overflow (columns, literals)
>>--     A3: multiplication overflow
>>--     A4: division overflow
>>--     A5: aggregate overflow
>>--     A6: type propagation
>>--     A7: insert statement assignment truncation
>>--     A8: update statement assignment truncation
>>--     A9: select into truncation tests
>>
>>--All testcases are documented further below.
>>
>>--****************** end test unit comments  ***************************
>>
>>--<testunit-summary>
>>--select #12 pos/neg/loc ufi/prep
>>
>>--<testunit-specs>
>>--mode-type ufi/prep
>>--test-type functional
>>--form-type pos/neg/loc
>>-- select-test is not specified because INSERT and UPDATE tests
>>-- are performed in the arithmetic truncation testcases.
>>
>>
>>--<testcase A0>
>>
>>--   <testcase-summary>
>>--       select expression precedence test-
>>--       this tests the accuracy of the
>>--       various precedence rules in the
>>--       processing of SQL expressions.
>>
>>--   <detail>
>>--       various SQL expressions are tested in SELECT statements.  Most
>>--       of the expressions contain no parenthesis to check the accuracy
>>--       of the default precedence rules. Some expressions with
>>--       parenthesis are used to verify the ability of parens to override
>>--       the default precedence(s).
>>
>>
>>--   <templates>
>>--       US00
>>
>>--  Boolean/comp op precedence
>>
>>--  check precedence of AND over OR
>>--  correct answer: first 2 records (insertion order, based on SYSKEY)
>>-- <ufi-input>
>>       select *
+>       from btsel06
+>       where col_42 <> col_43 AND
+>             col_44 < col_45 OR
+>             col_66 = col_67 AND
+>             col_68 > col_69;

PIC_X_A  PIC_X_B  PIC_X_C  COL_1  COL_2  COL_3  COL_4  COL_5  COL_6  COL_7  COL_8  COL_9  COL_10  COL_21       COL_22       COL_23       COL_24       COL_25       COL_26       COL_27      COL_28      COL_29      COL_30      COL_41       COL_42       COL_43       COL_44       COL_45      COL_46      COL_47      COL_48      COL_61                COL_62                COL_63                COL_64                COL_65                COL_66                COL_67               COL_68               COL_69               COL_70
-------  -------  -------  -----  -----  -----  -----  -----  -----  -----  -----  -----  ------  -----------  -----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  -------------------  -------------------  -------------------  -------------------

joe      Z        in         100    200    100    200    200    100    100    100     50     100          100          200          100          200          200          100         100         100          50         100       100.00       200.00       100.00       200.00      200.00      100.00      100.00      100.00                   100                   200                   100                   200                   200                   100                  100                  100                   50                  100
sue      Q        on         200    400    100    100    400    100    100     50    100     200          200          400          100          100          400          100         100          50         100         200       200.00       400.00       100.00       100.00      400.00      100.00      100.00       50.00                   200                   400                   100                   100                   400                   100                  100                   50                  100                  200

--- 2 row(s) selected.
>>
>>--  check precedence of NOT over OR
>>--  correct answer: all records
>>-- <ufi-input>
>>       select *
+>       from btsel08
+>       where not pic_252 > pic_1 OR
+>                 pic_252 > pic_1;

LARGE_INT             PIC_252                                                                                                                                                                                                                                                       PIC_1
--------------------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----

                 300  Q                                                                                                                                                                                                                                                             X    
                2000  alexander                                                                                                                                                                                                                                                     B    
                 100  carltons                                                                                                                                                                                                                                                      E    
                3000  george                                                                                                                                                                                                                                                        D    
                1000  harveys                                                                                                                                                                                                                                                       B    
                 400  joseph                                                                                                                                                                                                                                                        X    
                 200  squaw                                                                                                                                                                                                                                                         X    
                4000  valley                                                                                                                                                                                                                                                        D    

--- 8 row(s) selected.
>>
>>--  check precedence of NOT over AND
>>--  correct answer: all records except last 2
>>-- <ufi-input>
>>       select *
+>       from btsel06
+>       where col_2 > col_3 AND
+>         NOT col_26 = col_27 AND
+>             col_69 = col_70;

--- 0 row(s) selected.
>>
>>--  check precedence of BETWEEN predicate and NOT BETWEEN
>>-- <ufi-input>
>>       select *
+>       from btsel06
+>       where NOT col_28 = 50 AND
+>             NOT col_1 NOT BETWEEN 100 AND 800 AND
+>             NOT col_70 <= 200 OR
+>                 col_69 NOT BETWEEN 100 AND 300;

PIC_X_A  PIC_X_B  PIC_X_C  COL_1  COL_2  COL_3  COL_4  COL_5  COL_6  COL_7  COL_8  COL_9  COL_10  COL_21       COL_22       COL_23       COL_24       COL_25       COL_26       COL_27      COL_28      COL_29      COL_30      COL_41       COL_42       COL_43       COL_44       COL_45      COL_46      COL_47      COL_48      COL_61                COL_62                COL_63                COL_64                COL_65                COL_66                COL_67               COL_68               COL_69               COL_70
-------  -------  -------  -----  -----  -----  -----  -----  -----  -----  -----  -----  ------  -----------  -----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  -------------------  -------------------  -------------------  -------------------

joe      A        jo         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      B        to         300   1000    400   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      C        go         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      Z        in         100    200    100    200    200    100    100    100     50     100          100          200          100          200          200          100         100         100          50         100       100.00       200.00       100.00       200.00      200.00      100.00      100.00      100.00                   100                   200                   100                   200                   200                   100                  100                  100                   50                  100
pam      D        al         900   1000    900   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
sue      C        by         300   1000    500   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
sue      D        so         200   1000    300   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000

--- 7 row(s) selected.
>>
>>--  check precedence of IN and NOT IN
>>-- <ufi-input>
>>       select *
+>       from btsel06
+>       where NOT col_6 NOT IN (50,150,250)
+>             AND col_1 IN (100,200,300)
+>             OR  col_3 IN (900,1900);

PIC_X_A  PIC_X_B  PIC_X_C  COL_1  COL_2  COL_3  COL_4  COL_5  COL_6  COL_7  COL_8  COL_9  COL_10  COL_21       COL_22       COL_23       COL_24       COL_25       COL_26       COL_27      COL_28      COL_29      COL_30      COL_41       COL_42       COL_43       COL_44       COL_45      COL_46      COL_47      COL_48      COL_61                COL_62                COL_63                COL_64                COL_65                COL_66                COL_67               COL_68               COL_69               COL_70
-------  -------  -------  -----  -----  -----  -----  -----  -----  -----  -----  -----  ------  -----------  -----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  -------------------  -------------------  -------------------  -------------------

joe      A        jo         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      B        to         300   1000    400   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      C        go         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
pam      D        al         900   1000    900   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
sue      C        by         300   1000    500   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
sue      D        so         200   1000    300   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000

--- 6 row(s) selected.
>>
>>--  check precedence of LIKE and NOT LIKE
>>-- <ufi-input>
>>       select *
+>       from btsel06
+>       where NOT pic_x_a NOT LIKE '__e'
+>             AND pic_x_c NOT LIKE 'in'
+>             AND pic_x_b NOT LIKE 'Q'
+>             OR  pic_x_b LIKE 'D';

PIC_X_A  PIC_X_B  PIC_X_C  COL_1  COL_2  COL_3  COL_4  COL_5  COL_6  COL_7  COL_8  COL_9  COL_10  COL_21       COL_22       COL_23       COL_24       COL_25       COL_26       COL_27      COL_28      COL_29      COL_30      COL_41       COL_42       COL_43       COL_44       COL_45      COL_46      COL_47      COL_48      COL_61                COL_62                COL_63                COL_64                COL_65                COL_66                COL_67               COL_68               COL_69               COL_70
-------  -------  -------  -----  -----  -----  -----  -----  -----  -----  -----  -----  ------  -----------  -----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  -----------  -----------  -----------  -----------  ----------  ----------  ----------  ----------  --------------------  --------------------  --------------------  --------------------  --------------------  --------------------  -------------------  -------------------  -------------------  -------------------

joe      A        jo         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      B        to         300   1000    400   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
joe      C        go         100   1000    200   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
pam      D        al         900   1000    900   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
sue      C        by         300   1000    500   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000
sue      D        so         200   1000    300   1000   1000     50   1000   1000   1000    1000         1000         1000         1000         1000         1000         1000        1000        1000        1000        1000      1000.00      1000.00      1000.00      1000.00     1000.00     1000.00     1000.00     1000.00                  1000                  1000                  1000                  1000                  1000                  1000                 1000                 1000                 1000                 1000

--- 6 row(s) selected.
>>
>>--  check precedence of NOT NOT NOT
>>-- <ufi-input>
>>       select *
+>       from btsel06
+>       where NOT NOT NOT col_6 < 100;

*** ERROR[15001] A syntax error occurred at or before: 
select *        from btsel06        where NOT NOT NOT col_6 < 100;
                                                    ^ (53 characters from start of SQL statement)

*** ERROR[8822] The statement was not prepared.

>>
>>--  check precedence of NOT (NOT (NOT
>>-- <ufi-input>
>>--       select *
>>--       from btsel06
>>--       where NOT (NOT (NOT col_6 < 100));
>>
>>
>>
>>--  arithmetic precedence
>>
>>--  check pri of monadic -
>>-- <ufi-input>
>>       select -large_int * medium_int + -small_int
+>       from btsel01;

(EXPR)              
--------------------

            -1600090
     -10000000000010
             7999000
            -8991080
          7999998000
            -2009000
               -8000
           -10000090

--- 8 row(s) selected.
>>
>>--  check pri of monadic +
>>-- <ufi-input>
>>       select +large_int * medium_int + +large_int
+>       from btsel01;

(EXPR)              
--------------------

             1600200
      10001000000000
            -8001000
             8991999
         -8001000000
             2002000
                   0
            10001000

--- 8 row(s) selected.
>>
>>--  check pri of *, / vs. +, -
>>-- <ufi-input>
>>       select binary_signed + binary_32_u - pic_comp_1
+>              * decimal_1 / pic_decimal_3 + decimal_3_unsigned
+>       from btsel01;

(EXPR)               
---------------------

             -5353.30
               -10.00
                70.00
               575.00
              1570.00
              2658.60
              4093.80
              7013.40

--- 8 row(s) selected.
>>
>>--  check pri of * vs. / and + vs. - (should be same)
>>-- <ufi-input>
>>       select binary_signed - binary_32_u + pic_comp_1
+>              / decimal_1 * pic_decimal_3 + decimal_3_unsigned
+>       from btsel01;

(EXPR)               
---------------------

             -4521.60
               102.50
               124.00
              1440.00
             16990.00
              3507.50
              4114.00
              8254.20

--- 8 row(s) selected.
>>
>>--  check pri of monadic -, preceded by subtraction operator
>>-- <ufi-input>
>>       select -large_int * medium_int - -small_int
+>       from btsel01;

(EXPR)              
--------------------

            -1599910
      -9999999999990
             8001000
            -8990920
          8000002000
            -1991000
                8000
            -9999910

--- 8 row(s) selected.
>>
>>--  check pri of aggregates
>>-- <ufi-input>
>>       select avg(small_int) + -max(large_int) *
+>              sum(medium_int)
+>       from btsel01;

(EXPR)              
--------------------

     -58999999997466

--- 1 row(s) selected.
>>
>>--  check pri of expr inside aggr.
>>-- <ufi-input>
>>       select avg(binary_signed + binary_32_u - pic_comp_1
+>                  * decimal_1 / pic_decimal_3 + decimal_3_unsigned)
+>       from btsel01;

(EXPR)               
---------------------

              1327.18

--- 1 row(s) selected.
>>
>>
>>
>>--  arithmetic/boolean/comp op
>>
>>--  complex query
>>--  correct answer: all records except first 2.
>>-- <ufi-input>
>>       select *
+>       from btsel01
+>       where NOT small_int + medium_int - medium_int + medium_int
+>                 * small_int / pic_decimal_1 * pic_decimal_1 < large_int
+>             AND binary_signed BETWEEN 1000 and 10000
+>             AND pic_decimal_1 + pic_decimal_2 * 2 <= 8
+>             OR  pic_x_7 NOT LIKE '__%'
+>             OR  pic_decimal_3 <> 8
+>             AND pic_decimal_3 >= 6;

CHAR_1  CHAR_10     PIC_X_1  PIC_X_7  PIC_X_LONG                                                                                                                                                                                                VAR_CHAR                                                                                                                                                                                                                                                       BINARY_SIGNED  BINARY_32_U  BINARY_64_S            PIC_COMP_1            PIC_COMP_2  PIC_COMP_3    SMALL_INT  MEDIUM_INT  LARGE_INT             DECIMAL_1  DECIMAL_2_SIGNED  DECIMAL_3_UNSIGNED  PIC_DECIMAL_1  PIC_DECIMAL_2  PIC_DECIMAL_3
------  ----------  -------  -------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------  -----------  ---------------------  --------------------  ----------  ------------  ---------  ----------  --------------------  ---------  ----------------  ------------------  -------------  -------------  -------------

D       michelle    D        michael  rat                                                                                                                                                                                                       thomas                                                                                                                                                                                                                                                                 -5000        90.00               2000.000                   500         .40     100.80000         90        8000                   200          7               .93                 140            6.1           .600              6
D       melissa     C        7        pop                                                                                                                                                                                                       jimmy                                                                                                                                                                                                                                                                   1000        80.00               1500.000                   500         .20     100.99990         80        9000                   999          5               .80                 120            4.1           .400              4
E       monica      Q        sue      pat                                                                                                                                                                                                       christopher                                                                                                                                                                                                                                                             2000        90.00               1200.000                  3000         .30     100.99999       2000        8000              -1000000          1               .90                  80            5.1           .500              5
C       maureen     E        jimmy    rum                                                                                                                                                                                                       marilyn                                                                                                                                                                                                                                                                 3000        80.00               2000.000                   500         .50     100.70000       9000        1000                  2000          8               .97                 150            7.1           .700              7
D       steven      B        9        bat                                                                                                                                                                                                       thomas                                                                                                                                                                                                                                                                  8000        70.00               2000.000                   500         .10     100.99900         90       10000                  1000          7               .70                 110            3.1           .300              3

--- 5 row(s) selected.
>>
>>--  same as above, with parens that change the evaluation order
>>-- <ufi-input>
>>       select *
+>       from btsel01
+>       where NOT small_int + medium_int - medium_int + medium_int
+>                 * small_int / pic_decimal_1 * pic_decimal_1 < large_int
+>             AND binary_signed BETWEEN 1000 and 10000
+>             AND pic_decimal_1 + pic_decimal_2 * 2 <= 8
+>             OR ( pic_x_7 NOT LIKE '__%'
+>                  OR  pic_decimal_3 <> 8)
+>             AND pic_decimal_3 >= 6;

CHAR_1  CHAR_10     PIC_X_1  PIC_X_7  PIC_X_LONG                                                                                                                                                                                                VAR_CHAR                                                                                                                                                                                                                                                       BINARY_SIGNED  BINARY_32_U  BINARY_64_S            PIC_COMP_1            PIC_COMP_2  PIC_COMP_3    SMALL_INT  MEDIUM_INT  LARGE_INT             DECIMAL_1  DECIMAL_2_SIGNED  DECIMAL_3_UNSIGNED  PIC_DECIMAL_1  PIC_DECIMAL_2  PIC_DECIMAL_3
------  ----------  -------  -------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------  -----------  ---------------------  --------------------  ----------  ------------  ---------  ----------  --------------------  ---------  ----------------  ------------------  -------------  -------------  -------------

D       michelle    D        michael  rat                                                                                                                                                                                                       thomas                                                                                                                                                                                                                                                                 -5000        90.00               2000.000                   500         .40     100.80000         90        8000                   200          7               .93                 140            6.1           .600              6
D       melissa     C        7        pop                                                                                                                                                                                                       jimmy                                                                                                                                                                                                                                                                   1000        80.00               1500.000                   500         .20     100.99990         80        9000                   999          5               .80                 120            4.1           .400              4
E       monica      Q        sue      pat                                                                                                                                                                                                       christopher                                                                                                                                                                                                                                                             2000        90.00               1200.000                  3000         .30     100.99999       2000        8000              -1000000          1               .90                  80            5.1           .500              5
C       maureen     E        jimmy    rum                                                                                                                                                                                                       marilyn                                                                                                                                                                                                                                                                 3000        80.00               2000.000                   500         .50     100.70000       9000        1000                  2000          8               .97                 150            7.1           .700              7
D       steven      B        9        bat                                                                                                                                                                                                       thomas                                                                                                                                                                                                                                                                  8000        70.00               2000.000                   500         .10     100.99900         90       10000                  1000          7               .70                 110            3.1           .300              3

--- 5 row(s) selected.
>>
>>--  same as above, with meaningless parens
>>-- <ufi-input>
>>       select *
+>       from btsel01
+>       where NOT small_int + medium_int - medium_int + medium_int
+>                 * small_int / pic_decimal_1 * pic_decimal_1 < large_int
+>             AND binary_signed BETWEEN 1000 and 10000
+>             AND pic_decimal_1 + pic_decimal_2 * 2 <= 8
+>             OR  pic_x_7 NOT LIKE '__%'
+>             OR  (pic_decimal_3 <> 8
+>                  AND pic_decimal_3 >= 6) ;

CHAR_1  CHAR_10     PIC_X_1  PIC_X_7  PIC_X_LONG                                                                                                                                                                                                VAR_CHAR                                                                                                                                                                                                                                                       BINARY_SIGNED  BINARY_32_U  BINARY_64_S            PIC_COMP_1            PIC_COMP_2  PIC_COMP_3    SMALL_INT  MEDIUM_INT  LARGE_INT             DECIMAL_1  DECIMAL_2_SIGNED  DECIMAL_3_UNSIGNED  PIC_DECIMAL_1  PIC_DECIMAL_2  PIC_DECIMAL_3
------  ----------  -------  -------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------  -----------  ---------------------  --------------------  ----------  ------------  ---------  ----------  --------------------  ---------  ----------------  ------------------  -------------  -------------  -------------

D       michelle    D        michael  rat                                                                                                                                                                                                       thomas                                                                                                                                                                                                                                                                 -5000        90.00               2000.000                   500         .40     100.80000         90        8000                   200          7               .93                 140            6.1           .600              6
D       melissa     C        7        pop                                                                                                                                                                                                       jimmy                                                                                                                                                                                                                                                                   1000        80.00               1500.000                   500         .20     100.99990         80        9000                   999          5               .80                 120            4.1           .400              4
E       monica      Q        sue      pat                                                                                                                                                                                                       christopher                                                                                                                                                                                                                                                             2000        90.00               1200.000                  3000         .30     100.99999       2000        8000              -1000000          1               .90                  80            5.1           .500              5
C       maureen     E        jimmy    rum                                                                                                                                                                                                       marilyn                                                                                                                                                                                                                                                                 3000        80.00               2000.000                   500         .50     100.70000       9000        1000                  2000          8               .97                 150            7.1           .700              7
D       steven      B        9        bat                                                                                                                                                                                                       thomas                                                                                                                                                                                                                                                                  8000        70.00               2000.000                   500         .10     100.99900         90       10000                  1000          7               .70                 110            3.1           .300              3

--- 5 row(s) selected.
>>
>>--  select a huge arithmetic expression with parens
>>-- <ufi-input>
>>       select ((binary_signed * (binary_64_s + pic_comp_1) -
+>                (pic_comp_2 + pic_comp_3) * small_int
+>               ) * medium_int - large_int / decimal_1
+>              ) + decimal_2_signed * decimal_3_unsigned / pic_decimal_3
+>       from btsel01;

(EXPR)              
--------------------

       -100072864006
          -135101955
          -209439770
         17927135896
         65580200174
          6589199770
         36952000013
        199909010782

--- 8 row(s) selected.
>>
>>-- <end-input>
>>
>>
>>-- <testcase A1>
>>
>>-- <testcase-summary>
>>--     select expression arithmetic
>>--     overflow test - this tests
>>--     overflow from addition and
>>--     subtraction in select expressions
>>--     of column values only (no literals).
>>
>>-- <detail>
>>--       Testing of overflow from the operations of addition
>>--       and subtraction is tested.  There are four different
>>--       levels of overflow: no overflow, possible overflow,
>>--       precision overflow, and actual overflow. Possible
>>--       overflow means the computed precision of the result
>>--       exceeds 18. Precision overflow means that the precision of the
>>--       actual runtime result exceeded 18, but did not exceed the
>>--       maximum value that can be stored in a binary 64 variable.
>>--       Actual overflow means the actual runtime result exceeded
>>--       the maximum value that can be stored in a binary 64 variable.
>>--         Expressions involving only columns are tested. Signed and
>>--       unsigned column values are tested, as are binary and decimal
>>--       column values.
>>
>>-- <templates>
>>--     US00
>>
>>--  column/column
>>--  signed
>>
>>--  possible overflow, binary columns, actual values small -> no overflow
>>-- <ufi-input>
>>      select large_bin_1 + large_bin_2
+>      from btsel26
+>      where selector = 'A';

(EXPR)              
--------------------

                   4

--- 1 row(s) selected.
>>
>>--  possible overflow, decimal columns, actual values small ->
>>--  no overflow
>>-- <ufi-input>
>>      select large_dec_1 + large_dec_2
+>      from btsel26
+>      where selector = 'A';

(EXPR)              
--------------------

                   4

--- 1 row(s) selected.
>>
>>--  possible overflow, binary columns, actual values large
>>--  (result should be one less than precision overflow)
>>-- <ufi-input>
>>      select large_bin_1 + large_bin_2
+>      from btsel26
+>      where selector = 'D';

(EXPR)              
--------------------

  999999999999999999

--- 1 row(s) selected.
>>
>>--  same as above, with decimal columns
>>-- <ufi-input>
>>      select large_dec_1 + large_dec_2
+>      from btsel26
+>      where selector = 'D';

(EXPR)              
--------------------

  999999999999999999

--- 1 row(s) selected.
>>
>>--  precision overflow, binary columns, result positive
>>-- <ufi-input>
>>      select large_bin_1 + large_bin_2
+>      from btsel26
+>      where selector = 'B';

(EXPR)              
--------------------

 1000000000000000000

--- 1 row(s) selected.
>>
>>--  precision overflow, decimal columns, result positive
>>-- <ufi-input>
>>      select large_dec_1 + large_dec_2
+>      from btsel26
+>      where selector = 'B';

(EXPR)              
--------------------

 1000000000000000000

--- 1 row(s) selected.
>>
>>--  precision overflow, binary columns, result negative
>>--  (underflow)
>>-- <ufi-input>
>>      select large_bin_1 - large_bin_2
+>      from btsel26
+>      where selector = 'C';

(EXPR)              
--------------------

-1000000000000000000

--- 1 row(s) selected.
>>
>>--  precision overflow, dec cols, result negative
>>--  (underflow)
>>-- <ufi-input>
>>      select large_dec_1 - large_dec_2
+>      from btsel26
+>      where selector = 'C';

(EXPR)              
--------------------

-1000000000000000000

--- 1 row(s) selected.
>>
>>--  precision overflow, binary columns, scale 18
>>--  (add .9 + .2, scales of both = .18)
>>-- <ufi-input>
>>      select small_bin_1 + small_bin_2
+>      from btsel26
+>      where selector = 'A';

(EXPR)               
---------------------

  1.10000000000000000

--- 1 row(s) selected.
>>
>>--  same as above, dec columns
>>-- <ufi-input>
>>      select small_dec_1 + small_dec_2
+>      from btsel26
+>      where selector = 'A';

(EXPR)               
---------------------

  1.10000000000000000

--- 1 row(s) selected.
>>
>>--  precision overflow, binary columns
>>-- <ufi-input>
>>      select small_bin_1 + small_bin_2
+>      from btsel26
+>      where selector = 'B';

(EXPR)               
---------------------

  1.00000000000000000

--- 1 row(s) selected.
>>
>>--  same, dec columns
>>-- <ufi-input>
>>      select small_dec_1 + small_dec_2
+>      from btsel26
+>      where selector = 'B';

(EXPR)               
---------------------

  1.00000000000000999

--- 1 row(s) selected.
>>
>>--  precision overflow, bin columns
>>-- <ufi-input>
>>      select small_bin_1 + small_bin_2
+>      from btsel26
+>      where selector = 'C';

(EXPR)               
---------------------

  1.00000000000000000

--- 1 row(s) selected.
>>
>>--  same , dec columns
>>-- <ufi-input>
>>      select small_dec_1 + small_dec_2
+>      from btsel26
+>      where selector = 'C';

(EXPR)               
---------------------

  1.00000000000001999

--- 1 row(s) selected.
>>
>>--  no overflow possible, bin cols, result positive
>>-- <ufi-input>
>>      select semi_large_bin_1 + semi_large_bin_2
+>      from btsel26
+>      where selector = 'A';

(EXPR)              
--------------------

  199999999999999998

--- 1 row(s) selected.
>>
>>--  no overflow possible, bin cols, result negative
>>-- <ufi-input>
>>      select semi_large_bin_1 - semi_large_bin_2
+>      from btsel26
+>      where selector = 'B';

(EXPR)              
--------------------

 -199999999999999998

--- 1 row(s) selected.
>>
>>--  precision overflow, binary columns
>>-- <ufi-input>
>>      select one_nine_bin + nine_one_bin
+>      from btsel26
+>      where selector = 'A';

(EXPR)               
---------------------

  1000000000.20000000

--- 1 row(s) selected.
>>
>>--  overflow possible, result largest value possible with no
>>--  precision overflow
>>-- <ufi-input>
>>      select one_nine_bin + nine_one_bin
+>      from btsel26
+>      where selector = 'B';

(EXPR)               
---------------------

   999999999.99999999

--- 1 row(s) selected.
>>
>>--  actual overflow, cols bin, result positive
>>-- <ufi-input>
>>      select large_bin_1 + small_bin_1
+>      from btsel26
+>      where selector = 'B';

(EXPR)              
--------------------

  999999999999999999

--- 1 row(s) selected.
>>
>>--  same as above, dec cols
>>-- <ufi-input>
>>      select large_dec_1 + small_dec_1
+>      from btsel26
+>      where selector = 'B';

(EXPR)              
--------------------

  999999999999999999

--- 1 row(s) selected.
>>
>>--  actual overflow, cols bin, result negative
>>--  (underflow)
>>-- <ufi-input>
>>      select large_bin_1 - small_bin_1
+>      from btsel26
+>      where selector = 'C';

(EXPR)              
--------------------

 -999999999999999999

--- 1 row(s) selected.
>>
>>--  actual overflow by 1
>>-- <ufi-input>
>>      select large_bin_1 + small_bin_2
+>      from btsel26
+>      where selector = 'G';

(EXPR)              
--------------------

   92233720368547758

--- 1 row(s) selected.
>>
>>--  one less than actual overflow
>>-- <ufi-input>
>>      select large_bin_1 + small_bin_1
+>      from btsel26
+>      where selector = 'G';

(EXPR)              
--------------------

   92233720368547758

--- 1 row(s) selected.
>>
>>--  overflow possible (technically but not really), binary cols,
>>--  actual values small - no overflow
>>-- <ufi-input>
>>      select nine_zero_bin_u + zero_nine_bin_u
+>      from btsel26
+>      where selector  = 'A';

(EXPR)               
---------------------

          2.200000000

--- 1 row(s) selected.
>>
>>--  same as above, dec cols
>>-- <ufi-input>
>>      select nine_zero_dec_u + zero_nine_dec_u
+>      from btsel26
+>      where selector  = 'A';

(EXPR)               
---------------------

          2.200000000

--- 1 row(s) selected.
>>
>>--  possible overflow, bin cols, actual values large
>>--  result should be .000 000 001 less than overflow
>>-- <ufi-input>
>>      select nine_zero_bin_u + zero_nine_bin_u
+>      from btsel26
+>      where selector = 'B';

(EXPR)               
---------------------

  999999999.999999999

--- 1 row(s) selected.
>>
>>--  same as above, dec cols
>>-- <ufi-input>
>>      select nine_zero_dec_u + zero_nine_dec_u
+>      from btsel26
+>      where selector = 'B';

(EXPR)               
---------------------

  999999999.999999999

--- 1 row(s) selected.
>>
>>--  actual overflow, binary cols
>>-- <ufi-input>
>>      select nine_zero_bin_u + zero_nine_bin_u
+>             + zero_nine_bin_u
+>      from btsel26
+>      where selector = 'B';

(EXPR)                
----------------------

  1000000000.999999998

--- 1 row(s) selected.
>>
>>--  same as above, dec cols
>>-- <ufi-input>
>>      select nine_zero_dec_u + zero_nine_dec_u
+>             + zero_nine_dec_u
+>      from btsel26
+>      where selector = 'B';

(EXPR)                
----------------------

  1000000000.999999998

--- 1 row(s) selected.
>>
>>--  actual underflow, bin cols
>>-- <ufi-input>
>>      select -nine_zero_bin_u - zero_nine_bin_u
+>             - zero_nine_bin_u
+>      from btsel26
+>      where selector = 'B';

(EXPR)               
---------------------

   -1000000000.999999

--- 1 row(s) selected.
>>-- <end-input>
>>
>>
>>-- <testcase A2>
>>
>>-- <testcase-summary>
>>--     Select expression arithmetic
>>--     overflow test - this tests
>>--       overflow from addition and
>>--       subtraction in select expressions
>>--       of column values and literals, and
>>--       select expressions of literals and
>>--       literals.
>>
>>-- <detail>
>>--         Testing of overflow from the operations of addition
>>--       and subtraction is tested.
>>--         Expressions involving a column with a literal and
>>--       literal/literal expressions are tested.
>>
>>-- <templates>
>>--     US00
>>
>>--  column/literal
>>
>>--  precision overflow, binary column
>>--  add .9 + .2, scale of column = .18
>>-- <ufi-input>
>>      select small_bin_1 + .200000000000000000
+>      from btsel26
+>      where selector = 'A';

(EXPR)               
---------------------

  1.10000000000000000

--- 1 row(s) selected.
>>
>>--  precision overflow, binary column
>>--  add .9 + .2, scale of column = .18, scale of literal = 1
>>-- <ufi-input>
>>      select small_bin_1 + .2
+>      from btsel26
+>      where selector = 'A';

(EXPR)               
---------------------

  1.10000000000000000

--- 1 row(s) selected.
>>
>>--  precision overflow, dec column
>>-- <ufi-input>
>>      select small_dec_1 + .000000000000000001
+>      from btsel26
+>      where selector = 'B';

(EXPR)               
---------------------

  1.00000000000000000

--- 1 row(s) selected.
>>
>>--  precision overflow, dec column
>>-- <ufi-input>
>>      select small_dec_1 + .000000000000000002
+>      from btsel26
+>      where selector = 'C';

(EXPR)               
---------------------

  1.00000000000000000

--- 1 row(s) selected.
>>
>>--  literal/literal
>>
>>--  precision overflow ?
>>-- <ufi-input>
>>      select .900000000000000000 + .200000000000000000
+>      from btsel26;

(EXPR)               
---------------------

  1.10000000000000000
  1.10000000000000000
  1.10000000000000000
  1.10000000000000000
  1.10000000000000000
  1.10000000000000000
  1.10000000000000000
  1.10000000000000000
  1.10000000000000000
  1.10000000000000000
  1.10000000000000000
  1.10000000000000000
  1.10000000000000000
  1.10000000000000000
  1.10000000000000000
  1.10000000000000000

--- 16 row(s) selected.
>>
>>--  precision overflow
>>-- <ufi-input>
>>      select .999999999999999999 + .000000000000000001
+>      from btsel26;

(EXPR)               
---------------------

  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000

--- 16 row(s) selected.
>>
>>--  precision overflow
>>-- <ufi-input>
>>      select .999999999999999999 + .000000000000000002
+>      from btsel26 ;

(EXPR)               
---------------------

  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000
  1.00000000000000000

--- 16 row(s) selected.
>>
>>--  actual overflow ?
>>-- <ufi-input>
>>      select 10.0 + .900000000000000000
+>      from btsel26;

(EXPR)               
---------------------

   10.900000000000000
   10.900000000000000
   10.900000000000000
   10.900000000000000
   10.900000000000000
   10.900000000000000
   10.900000000000000
   10.900000000000000
   10.900000000000000
   10.900000000000000
   10.900000000000000
   10.900000000000000
   10.900000000000000
   10.900000000000000
   10.900000000000000
   10.900000000000000

--- 16 row(s) selected.
>>
>>--  actual overflow ?
>>-- <ufi-input>
>>      select .90000000000000000000
+>      from btsel26;

(EXPR)                
----------------------

 .90000000000000000000
 .90000000000000000000
 .90000000000000000000
 .90000000000000000000
 .90000000000000000000
 .90000000000000000000
 .90000000000000000000
 .90000000000000000000
 .90000000000000000000
 .90000000000000000000
 .90000000000000000000
 .90000000000000000000
 .90000000000000000000
 .90000000000000000000
 .90000000000000000000
 .90000000000000000000

--- 16 row(s) selected.
>>
>>--  actual overflow ?
>>-- <ufi-input>
>>      select 10.999999999999999999
+>      from btsel26;

(EXPR)                
----------------------

 10.999999999999999999
 10.999999999999999999
 10.999999999999999999
 10.999999999999999999
 10.999999999999999999
 10.999999999999999999
 10.999999999999999999
 10.999999999999999999
 10.999999999999999999
 10.999999999999999999
 10.999999999999999999
 10.999999999999999999
 10.999999999999999999
 10.999999999999999999
 10.999999999999999999
 10.999999999999999999

--- 16 row(s) selected.
>>
>>-- <end-input>
>>
>>-- <testcase A3>
>>
>>-- <testcase-summary>
>>--       select expression arithmetic
>>--       overflow test - this tests
>>--       overflow from multiplication
>>--       in select expressions  of
>>--       column values only (no literals).
>>
>>-- <detail>
>>--         Testing of overflow from multiplication is tested.
>>--       There are four different levels of overflow: no overflow,
>>--       possible overflow, precision overflow, and actual overflow.
>>--       Possible overflow means the computed precision of the result
>>--       exceeds 18. Precision overflow means that the precision of the
>>--       actual runtime result exceeded 18, but did not exceed the
>>--       maximum value that can be stored in a binary 64 variable.
>>--       Actual overflow means the actual runtime result exceeded
>>--       the maximum value that can be stored in a binary 64 variable.
>>--         Expressions involving only columns are tested. Signed and
>>--       unsigned column values are tested, as are binary and decimal
>>--       column values.
>>
>>-- <templates>
>>--     US00
>>
>>--  signed
>>
>>--  no overflow possible, result positive
>>-- <ufi-input>
>>      select eight_one_bin * one_eight_bin
+>      from btsel26
+>      where selector = 'A';

(EXPR)               
---------------------

  999999998.000000001

--- 1 row(s) selected.
>>
>>--  no overflow possible, result negative
>>-- <ufi-input>
>>      select eight_one_bin * one_eight_bin
+>      from btsel26
+>      where selector = 'B';

(EXPR)               
---------------------

 -999999998.000000001

--- 1 row(s) selected.
>>
>>--  overflow possible, result positive
>>-- <ufi-input>
>>      select eight_one_bin * one_nine_bin
+>      from btsel26
+>      where selector = 'D';

(EXPR)               
---------------------

   43999999.560000000

--- 1 row(s) selected.
>>
>>--  actual overflow, result positive
>>-- <ufi-input>
>>      select nine_one_bin * one_eight_bin
+>      from btsel26
+>      where selector = 'A';

(EXPR)               
---------------------

  9999999981.00000000

--- 1 row(s) selected.
>>
>>--  actual overflow, result negative (underflow)
>>-- <ufi-input>
>>      select nine_one_bin * one_eight_bin
+>      from btsel26
+>      where selector = 'C';

(EXPR)               
---------------------

 -9999999989.00000000

--- 1 row(s) selected.
>>
>>--  actual overflow, scale 18; bin cols.
>>-- <ufi-input>
>>      select one_nine_bin * one_nine_bin
+>      from btsel26
+>      where selector = 'D';

(EXPR)               
---------------------

  19.3600000000000000

--- 1 row(s) selected.
>>
>>--  possible overflow only, scale 18; bin cols.
>>-- <ufi-input>
>>      select one_nine_bin * one_nine_bin
+>      from btsel26
+>      where selector = 'D';

(EXPR)               
---------------------

  19.3600000000000000

--- 1 row(s) selected.
>>
>>--  actual overflow (right end only), result positive
>>--  scale = 36
>>-- <ufi-input>
>>      select small_bin_1 * small_bin_2
+>      from btsel26
+>      where selector = 'D';

(EXPR)               
---------------------

  .099999999999999999

--- 1 row(s) selected.
>>
>>--  actual overflow (right end only), result negative
>>--  scale = 36
>>-- <ufi-input>
>>      select small_bin_1 * -small_bin_2
+>      from btsel26
+>      where selector = 'D';

(EXPR)               
---------------------

   -.0999999999999999

--- 1 row(s) selected.
>>
>>--  actual overflow (left end only) result positive
>>-- <ufi-input>
>>      select large_bin_1 * large_bin_2
+>      from btsel26
+>      where selector = 'D';

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x000076A20BBC96F2C1C519B457551100 to Target Type:LARGEINT(REC_BIN64_SIGNED).

--- 0 row(s) selected.
>>
>>--  actual overflow (left end only) result negative
>>-- <ufi-input>
>>      select large_bin_1 * -large_bin_2
+>      from btsel26
+>      where selector = 'D';

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x000076A20BBC96F2C1C519B457551180 to Target Type:LARGEINT(REC_BIN64_SIGNED).

--- 0 row(s) selected.
>>
>>--  actual overflow (scale > 18), precision = 19
>>-- <ufi-input>
>>      select small_bin_1 * zero_one_bin
+>      from btsel26
+>      where selector = 'D';

(EXPR)               
---------------------

  .099999999999999999

--- 1 row(s) selected.
>>
>>--  actual overflow (scale > 18) precision = 20
>>-- <ufi-input>
>>      select small_bin_1 * zero_one_bin
+>      from btsel26
+>      where selector = 'C';

(EXPR)               
---------------------

  .899999999999999999

--- 1 row(s) selected.
>>
>>
>>--  unsigned
>>
>>--  actual overflow, bin cols
>>-- <ufi-input>
>>      select nine_zero_bin_u * zero_nine_bin_u
+>             * nine_zero_bin_u
+>      from btsel26
+>      where selector = 'B';

(EXPR)              
--------------------

  999999997000000002

--- 1 row(s) selected.
>>
>>--  actual underflow, bin cols
>>-- <ufi-input>
>>      select nine_zero_bin_u * -zero_nine_bin_u
+>             * nine_zero_bin_u
+>      from btsel26
+>      where selector = 'B';

(EXPR)              
--------------------

 -999999997000000002

--- 1 row(s) selected.
>>
>>--  actual overflow, dec cols
>>-- <ufi-input>
>>      select nine_zero_dec_u * zero_nine_dec_u
+>             * nine_zero_dec_u
+>      from btsel26
+>      where selector = 'B';

(EXPR)              
--------------------

  999999997000000002

--- 1 row(s) selected.
>>
>>--  actual underflow, dec cols
>>-- <ufi-input>
>>      select nine_zero_dec_u * -zero_nine_dec_u
+>             * nine_zero_dec_u
+>      from btsel26
+>      where selector = 'B';

(EXPR)              
--------------------

 -999999997000000002

--- 1 row(s) selected.
>>
>>-- <end-input>
>>
>>
>>-- <testcase A4>
>>
>>-- <testcase-summary>
>>--       select expression arithmetic
>>--       overflow test - this tests
>>--       overflow from division
>>--       in select expressions of
>>--       column values only (no literals).
>>
>>-- <detail>
>>--         Testing of overflow from division is tested.
>>--       There are three different levels of overflow: no overflow,
>>--       precision overflow, and actual overflow.
>>--       Precision overflow means that the precision of the
>>--       actual runtime result exceeded 18, but did not exceed the
>>--       maximum value that can be stored in a binary 64 variable.
>>--       Actual overflow means the actual runtime result exceeded
>>--       the maximum value that can be stored in a binary 64 variable.
>>--         Expressions involving only binary columns are tested.
>>
>>-- <templates>
>>--     US00
>>
>>
>>--  no left overflow or right truncation, scale positive
>>-- <ufi-input>
>>      select large_bin_1 / large_bin_2
+>      from btsel26
+>      where selector = 'E';

(EXPR)              
--------------------

  333333333333333333

--- 1 row(s) selected.
>>
>>--  right truncation, scale 0
>>-- <ufi-input>
>>      select large_bin_1 / large_bin_2
+>      from btsel26
+>      where selector = 'F' ;

(EXPR)              
--------------------

  499999999999999999

--- 1 row(s) selected.
>>
>>--  precision overflow (precision > 18)
>>-- <ufi-input>
>>      select large_bin_1 / small_bin_2
+>      from btsel26
+>      where selector = 'D';

(EXPR)              
--------------------

 1000000000000000000

--- 1 row(s) selected.
>>
>>--  actual left overflow, result positive , scale negative
>>-- <ufi-input>
>>      select large_bin_1 / small_bin_1
+>      from btsel26
+>      where selector = 'F';

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0xF6FFE7890423C78A0000000000000000 to Target Type:LARGEINT(REC_BIN64_SIGNED).

--- 0 row(s) selected.
>>
>>--  actual left overflow, result negative, scale negative
>>-- <ufi-input>
>>      select -large_bin_1 / small_bin_1
+>      from btsel26
+>      where selector = 'F';

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0xF6FFE7890423C78A0000000000000080 to Target Type:LARGEINT(REC_BIN64_SIGNED).

--- 0 row(s) selected.
>>
>>--  right truncation, result positive
>>--  scale positive
>>-- <ufi-input>
>>      select small_bin_1 / semi_large_bin_1
+>      from btsel26
+>      where selector = 'E';

(EXPR)               
---------------------

  .000000000000000000

--- 1 row(s) selected.
>>
>>--  right truncation, result negative
>>--  scale positive
>>-- <ufi-input>
>>      select -small_bin_1 / semi_large_bin_1
+>      from btsel26
+>      where selector = 'E';

(EXPR)               
---------------------

    .0000000000000000

--- 1 row(s) selected.
>>
>>--  no left overflow, scale negative
>>-- <ufi-input>
>>      select semi_large_bin_1 / small_bin_1
+>      from btsel26
+>      where selector = 'F';

(EXPR)              
--------------------

  999999999999999990

--- 1 row(s) selected.
>>
>>--  right truncation, scale negative
>>-- <ufi-input>
>>      select large_bin_1 / one_nine_bin
+>      from btsel26
+>      where selector = 'F';

(EXPR)              
--------------------

  399999999999999999

--- 1 row(s) selected.
>>
>>--  no left overflow or right truncation, decimal places in result
>>-- <ufi-input>
>>      select semi_large_bin_2 / one_eight_bin
+>      from btsel26
+>      where selector = 'D';

(EXPR)              
--------------------

                  12

--- 1 row(s) selected.
>>
>>-- <end-input>
>>
>>-- <testcase A5>
>>
>>-- <testcase-summary>
>>--     this testcase is an arithmetic
>>--     overflow test for aggregates
>>
>>-- <detail>
>>--      min, max, count are tested to ensure the proper precision
>>--      and scale of the result. Note no overflow is possible for
>>--      these aggregates.  Most of the tests here are for SUM and
>>--      AVG. For SUM - there are two kinds of overflow - overflow
>>--      in the computation and overflow in the result. Note that
>>--      there can never be overflow in the result for AVG.
>>
>>-- <templates>
>>--     US00
>>
>>--  MAX, MIN
>>
>>--  max, scale = 0
>>-- <ufi-input>
>>       select max(large_bin_1)
+>       from btsel26;

(EXPR)              
--------------------

  999999999999999999

--- 1 row(s) selected.
>>
>>--  max, scale = 9
>>-- <ufi-input>
>>       select max(one_nine_bin)
+>       from btsel26;

(EXPR)               
---------------------

          9.999999999

--- 1 row(s) selected.
>>
>>--  min, scale = 0
>>-- <ufi-input>
>>       select min(large_bin_1)
+>       from btsel26;

(EXPR)              
--------------------

 -999999999999999999

--- 1 row(s) selected.
>>
>>--  min, scale = 9
>>-- <ufi-input>
>>       select min(one_nine_bin)
+>       from btsel26;

(EXPR)               
---------------------

           .000000000

--- 1 row(s) selected.
>>
>>--  COUNT
>>
>>--  count, original scale = 0
>>-- <ufi-input>
>>       select count(distinct large_bin_1)
+>       from btsel26;

(EXPR)              
--------------------

                   5

--- 1 row(s) selected.
>>
>>--  count, original scale = 9
>>-- <ufi-input>
>>       select count(distinct one_nine_bin)
+>       from btsel26;

(EXPR)              
--------------------

                   7

--- 1 row(s) selected.
>>
>>--  count(*)
>>-- <ufi-input>
>>       select count(*)
+>       from btsel26;

(EXPR)              
--------------------

                  16

--- 1 row(s) selected.
>>
>>--  SUM
>>
>>--  sum, no overflow, scale = 0
>>-- <ufi-input>
>>       select sum(semi_large_bin_1)
+>       from btsel26;

(EXPR)              
--------------------

  100000000000000009

--- 1 row(s) selected.
>>
>>--  sum, overflow in calculation and result
>>-- <ufi-input>
>>       select sum(large_bin_1)
+>       from btsel26
+>       where (selector <> 'O') and (selector <> 'P');

(EXPR)              
--------------------

 9192233720368547751

--- 1 row(s) selected.
>>
>>--  sum, overflow in calculation only
>>-- <ufi-input>
>>       select sum(large_bin_1)
+>       from btsel26;

(EXPR)              
--------------------

 7192233720368547753

--- 1 row(s) selected.
>>
>>--  sum, underflow in calculation and result
>>-- <ufi-input>
>>       select sum(-large_bin_1)
+>       from btsel26
+>       where (selector <> 'O') and (selector <> 'P');

(EXPR)              
--------------------

-9192233720368547751

--- 1 row(s) selected.
>>
>>--  sum, no overflow, scale > 0
>>-- <ufi-input>
>>       select sum(small_dec_2)
+>       from btsel26;

(EXPR)               
---------------------

  .200000000000030002

--- 1 row(s) selected.
>>
>>--  sum, overflow in calculation and result, scale > 0
>>-- <ufi-input>
>>       select sum(small_dec_1)
+>       from btsel26;

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Operand1 Type:LARGEINT(MBIN64S) Operand1 Value:8899999999999999992 Operand2 Type:LARGEINT(MBIN64S) Operand2 Value:999999999999999999. Instruction:SUM_MBIN64S_MBIN64S Operation:SUM.

--- 0 row(s) selected.
>>
>>--  AVG
>>
>>--  avg, no overflow, scale = 0
>>-- <ufi-input>
>>       select avg(semi_large_bin_1)
+>       from btsel26;

(EXPR)              
--------------------

    6250000000000000

--- 1 row(s) selected.
>>
>>--  avg, no overflow in result, overflow in calculation
>>--  (impossible to get overflow in result)
>>-- <ufi-input>
>>       select avg(large_bin_1)
+>       from btsel26
+>       where (selector <> 'O') and (selector <> 'P');

(EXPR)              
--------------------

  656588122883467696

--- 1 row(s) selected.
>>
>>--  avg, underflow in calculation
>>-- <ufi-input>
>>       select avg(-large_bin_1)
+>       from btsel26;

(EXPR)              
--------------------

 -449514607523034234

--- 1 row(s) selected.
>>
>>--  avg, no overflow, scale > 0
>>-- <ufi-input>
>>       select avg(small_dec_2)
+>       from btsel26;

(EXPR)               
---------------------

  .012500000000001875

--- 1 row(s) selected.
>>
>>--  avg, overflow, scale > 0
>>-- <ufi-input>
>>       select avg(small_dec_1)
+>       from btsel26;

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Operand1 Type:LARGEINT(MBIN64S) Operand1 Value:8899999999999999992 Operand2 Type:LARGEINT(MBIN64S) Operand2 Value:999999999999999999. Instruction:SUM_MBIN64S_MBIN64S Operation:SUM.

--- 0 row(s) selected.
>>
>>--  SUM, overflow based on order records are evaluated
>>
>>--  sum, no overflow in calculation or result IF the calculation
>>--  is done in 'insertion' order (file with SYSKEY)
>>-- <ufi-input>
>>       select sum(large_dec_1)
+>       from btsel26;

(EXPR)              
--------------------

 9099999999999999993

--- 1 row(s) selected.
>>
>>--  sum, no overflow in result, overflow in calculation
>>--  IF the calculation done based on alternate index on large_dec_1
>>-- <ufi-input>
>>       select sum(large_dec_1)
+>       from btsel26
+>       where large_dec_1 <> 2;

(EXPR)              
--------------------

 9099999999999999991

--- 1 row(s) selected.
>>
>>-- <end-input>
>>
>>
>>-- <testcase A6>
>>
>>-- <testcase-summary>
>>--       this testcase tests type
>>--       propagation of expressions.
>>
>>-- <detail>
>>--       type propagation tests are conducted for various data
>>--       types and resultant data types
>>
>>-- <templates>
>>--       US00
>>
>>--  sbin data
>>
>>--  result bin 16
>>--  p = 3, s = 2
>>-- <ufi-input>
>>         select pic_comp_2 + pic_comp_2
+>         from btsel01
+>         where binary_signed = 60;

(EXPR) 
-------

   1.58

--- 1 row(s) selected.
>>
>>--  result bin 16
>>--  p = 4, s = 4
>>-- <ufi-input>
>>        select pic_comp_2 * pic_comp_2
+>        from btsel01
+>        where binary_signed = 60;

(EXPR) 
-------

  .6241

--- 1 row(s) selected.
>>
>>--  result bin 32
>>--  p = 9, s = 0
>>-- <ufi-input>
>>        select (binary_signed + small_int) * small_int
+>        from btsel01
+>        where binary_signed = 60;

(EXPR)              
--------------------

             1060000

--- 1 row(s) selected.
>>
>>--  result bin 64
>>--  p = 18, s = 8
>>-- <ufi-input>
>>        select (binary_signed + small_int) / binary_64_s
+>        from btsel01
+>        where binary_signed = 60;

(EXPR)               
---------------------

           .883333333

--- 1 row(s) selected.
>>
>>--  unsigned data
>>
>>--  result bin 16
>>--  p = 4, s = 0
>>-- <ufi-input>
>>        select decimal_3_unsigned + decimal_3_unsigned
+>        from btsel01
+>        where binary_signed = 60;

(EXPR)
------

   200

--- 1 row(s) selected.
>>
>>--  result bin 32
>>--  p = 9, s = 0
>>-- <ufi-input>
>>        select (col_2 + col_7) * col_6
+>        from btsel06
+>        where pic_x_b = 'Q';

(EXPR)              
--------------------

               50000

--- 1 row(s) selected.
>>
>>--  result bin 64
>>--  p = 12, s = 2
>>-- <ufi-input>
>>        select binary_32_u * decimal_3_unsigned
+>        from btsel01
+>        where binary_signed = 60;

(EXPR)               
---------------------

              6000.00

--- 1 row(s) selected.
>>
>>--  sdec data
>>
>>--  result bin 16
>>--  p =   3, s = 1
>>-- <ufi-input>
>>        select pic_decimal_1 - pic_decimal_3
+>        from btsel01
+>        where pic_decimal_1 = 1.1;

(EXPR) 
-------

     .1

--- 1 row(s) selected.
>>
>>--  result bin 32
>>--  p = 9, s = 0
>>-- <ufi-input>
>>        select col_9 * col_10 + col_9
+>        from btsel06
+>        where pic_x_b = 'Q';

(EXPR)    
----------

     20100

--- 1 row(s) selected.
>>
>>--  result bin 64
>>--  p = 18, s = 9
>>-- <ufi-input>
>>        select col_47 / col_48 * col_70
+>        from btsel06
+>        where pic_x_b = 'Q';

(EXPR)              
--------------------

                 400

--- 1 row(s) selected.
>>
>>--  constant data
>>
>>--  result bin 16
>>--  p = 4, s = 2
>>-- <ufi-input>
>>        select decimal_2_signed + 4.7
+>        from btsel01
+>        where binary_32_u = 60;

(EXPR) 
-------

   5.30

--- 1 row(s) selected.
>>
>>--  result bin 32
>>--  p = 8, s = 2
>>-- <ufi-input>
>>        select decimal_3_unsigned * 9.08 + 10000
+>        from btsel01
+>        where binary_32_u = 60;

(EXPR)      
------------

    10908.00

--- 1 row(s) selected.
>>
>>--  result bin 64
>>--  p = 18, s = 16
>>-- <ufi-input>
>>        select (17.496 / 85) + (63 * medium_int) - small_int
+>        from btsel01
+>        where binary_32_u = 60;

(EXPR)               
---------------------

          503000.2058

--- 1 row(s) selected.
>>
>>--  mixed/complex/aggregates
>>
>>--  result bin 16
>>-- <ufi-input>
>>        select pic_comp_2 - decimal_2_signed * pic_decimal_3
+>        from btsel01
+>        where binary_32_u = 60;

(EXPR) 
-------

   -.41

--- 1 row(s) selected.
>>
>>--  result bin 16
>>-- <ufi-input>
>>        select max(pic_comp_2) - min(decimal_2_signed) *
+>               max(pic_decimal_3)
+>        from btsel01
+>        where binary_32_u = 60;

(EXPR) 
-------

   -.41

--- 1 row(s) selected.
>>
>>--  result bin 32
>>-- <ufi-input>
>>        select decimal_3_unsigned + small_int - decimal_1
+>        from btsel01
+>        where binary_32_u = 60;

(EXPR)     
-----------

       1095

--- 1 row(s) selected.
>>
>>--  result bin 64
>>-- <ufi-input>
>>        select ((decimal_1 + decimal_3_unsigned) *
+>                binary_32_u - pic_comp_2
+>               ) / pic_comp_1 + pic_comp_3 * decimal_2_signed
+>        from btsel01;

(EXPR)               
---------------------

            120.20320
            144.44760
            165.58083
            100.79952
             93.32989
            122.95800
            194.78200
             87.07910

--- 8 row(s) selected.
>>
>>--  result bin 64
>>-- <ufi-input>
>>        select sum(pic_comp_3) + sum(large_int) + avg(binary_32_u)
+>        from btsel01;

(EXPR)              
--------------------

           999004075

--- 1 row(s) selected.
>>
>>-- <end-input>
>>
>>
>>-- <testcase A7>
>>
>>-- <testcase-summary>
>>--       this testcase tests assignment
>>--       truncation using INSERT stmts.
>>
>>-- <detail>
>>--       truncation assignment is tested by first creating
>>--       a temporary table, trunctmp, using the
>>--       sqlt041d catalog.  Test statements that insert
>>--       various values into the various columns are run. Only
>>--       one column is tested per test statement - all other
>>--       column values are set to 0.
>>
>>
>>--  create table trunctmp
>>-- <ufi-input>
>>set parserflags 1024;

--- SQL operation complete.
>> -- allow no audit
>>         create table trunctmp (
+>              two_three_bin      numeric(5,3) signed 
+>            , smallest_bin       numeric (18,18) signed
+>            , smallest_dec       decimal (18,18) signed
+>            , largest_bin        numeric (18,0) signed
+>            , largest_dec        decimal (18,0) signed
+>            , semi_large_bin     numeric (17,0) signed
+>            , semi_large_dec     decimal (17,0) signed
+>            )
+>--           CATALOG sqlt041d
+>?ifMX
+>            --ATTRIBUTE 
+>?ifMX
+>            --NO AUDIT
+>         ;

--- SQL operation complete.
>>reset parserflags 1024;

--- SQL operation complete.
>>
>>--  test first field :  +- 2.3 bin
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (1.0001,0,0,0,0,0,0);

--- 1 row(s) inserted.
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (1.1001,0,0,0,0,0,0);

--- 1 row(s) inserted.
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (1.0016,0,0,0,0,0,0);

--- 1 row(s) inserted.
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (.0001,0,0,0,0,0,0);

--- 1 row(s) inserted.
>>
>>--  second field : +- .18 bin
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,.000000000000000001,0,0,0,0,0);

--- 1 row(s) inserted.
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,.0000000000000000001,0,0,0,0,0);

--- 1 row(s) inserted.
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,.1000000000000000001,0,0,0,0,0);

--- 1 row(s) inserted.
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,.1000000000000000016,0,0,0,0,0);

--- 1 row(s) inserted.
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,1.1,0,0,0,0,0);

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x0000EE042CFC430F to Target Type:LARGEINT(REC_BIN64_SIGNED).

--- 0 row(s) inserted.
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,1.100000000000000001,0,0,0,0,0);

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:1100000000000000001 Target Type:LARGEINT(IBIN64S) Max Target Value:999999999999999999. Instruction:RANGE_HIGH_S64S64 Operation:RANGE_HIGH.

--- 0 row(s) inserted.
>>
>>-- <ufi-input>
>>-- for some reason the backpatching of 9.9 into smallest_bin numeric(18,18)
>>-- fails on NT & MIPS as expected but succeeds unexpectedly on IPF.
>>        Insert into trunctmp
+>           values (0,9.9,0,0,0,0,0);

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x00005E2C8CDD63890000 to Target Type:LARGEINT(REC_BIN64_SIGNED).

--- 0 row(s) inserted.
>>
>>--  third field : +- .18 dec
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,0,.000000000000000001,0,0,0,0);

--- 1 row(s) inserted.
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,0,.0000000000000000001,0,0,0,0);

--- 1 row(s) inserted.
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,0,.1000000000000000001,0,0,0,0);

--- 1 row(s) inserted.
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,0,.1000000000000000016,0,0,0,0);

--- 1 row(s) inserted.
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,0,1.1,0,0,0,0);

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x0000EE042CFC430F0000 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).

--- 0 row(s) inserted.
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,0,1.100000000000000001,0,0,0,0);

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:1100000000000000001 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).

--- 0 row(s) inserted.
>>
>>--  fourth field : +- 18.0 bin
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,0,0,1000000000000000000,0,0,0);

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:1000000000000000000 Target Type:LARGEINT(IBIN64S) Max Target Value:999999999999999999. Instruction:RANGE_HIGH_S64S64 Operation:RANGE_HIGH.

--- 0 row(s) inserted.
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,0,0,9900000000000000000,0,0,0);

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x00005E2C8CDD63890000 to Target Type:LARGEINT(REC_BIN64_SIGNED).

--- 0 row(s) inserted.
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,0,0,1.1,0,0,0);

--- 1 row(s) inserted.
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,0,0,.1,0,0,0);

--- 1 row(s) inserted.
>>
>>--  fifth field +- 18.0 (dec)
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,0,0,0,1000000000000000000,0,0);

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:1000000000000000000 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).

--- 0 row(s) inserted.
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,0,0,0,1.1,0,0);

--- 1 row(s) inserted.
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,0,0,0,.1,0,0);

--- 1 row(s) inserted.
>>
>>--  sixth field : +- 17.0 bin
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,0,0,0,0,100000000000000000,0);

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:100000000000000000 Target Type:LARGEINT(IBIN64S) Max Target Value:99999999999999999. Instruction:RANGE_HIGH_S64S64 Operation:RANGE_HIGH.

--- 0 row(s) inserted.
>>
>>--  seventh field : +- 17.0 dec
>>
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values (0,0,0,0,0,0,100000000000000000,0);

*** ERROR[4023] The degree of each row value constructor (8) must equal the degree of the target table column list (7).

*** ERROR[8822] The statement was not prepared.

>>
>>-- <end-input>
>>
>>-- <display>
>>         select *
+>         from trunctmp;

TWO_THREE_BIN  SMALLEST_BIN           SMALLEST_DEC          LARGEST_BIN           LARGEST_DEC          SEMI_LARGE_BIN        SEMI_LARGE_DEC
-------------  ---------------------  --------------------  --------------------  -------------------  --------------------  ------------------

        1.000    .000000000000000000   .000000000000000000                     0                    0                     0                   0
        1.100    .000000000000000000   .000000000000000000                     0                    0                     0                   0
        1.001    .000000000000000000   .000000000000000000                     0                    0                     0                   0
         .000    .000000000000000000   .000000000000000000                     0                    0                     0                   0
         .000    .000000000000000001   .000000000000000000                     0                    0                     0                   0
         .000    .000000000000000000   .000000000000000000                     0                    0                     0                   0
         .000    .100000000000000000   .000000000000000000                     0                    0                     0                   0
         .000    .100000000000000001   .000000000000000000                     0                    0                     0                   0
         .000    .000000000000000000   .000000000000000001                     0                    0                     0                   0
         .000    .000000000000000000   .000000000000000000                     0                    0                     0                   0
         .000    .000000000000000000   .100000000000000000                     0                    0                     0                   0
         .000    .000000000000000000   .100000000000000001                     0                    0                     0                   0
         .000    .000000000000000000   .000000000000000000                     1                    0                     0                   0
         .000    .000000000000000000   .000000000000000000                     0                    0                     0                   0
         .000    .000000000000000000   .000000000000000000                     0                    1                     0                   0
         .000    .000000000000000000   .000000000000000000                     0                    0                     0                   0

--- 16 row(s) selected.
>>
>>-- <testcase A8>
>>
>>-- <testcase-summary>
>>--       this testcase tests assignment
>>--       truncation using UPDATE stmts.
>>
>>-- <detail>
>>--       truncation assignment is tested by first creating
>>--       a temporary table, trunctmp, using the
>>--       Test statements that update
>>--       various values of the various columns are run. Only
>>--       one column is tested per test statement - all other
>>--       column values are set to 0.
>>
>>
>>--  create table trunctmp
>>-- <ufi-input>
>>	 drop table trunctmp;

--- SQL operation complete.
>>set parserflags 1024;

--- SQL operation complete.
>> -- allow no audit
>>         create table trunctmp (
+>              selector           char(1)
+>            , two_three_bin      numeric(5,3) signed 
+>            , smallest_bin       numeric (18,18) signed
+>            , smallest_dec       decimal (18,18) signed
+>            , largest_bin        numeric (18,0) signed
+>            , largest_dec        decimal (18,0) signed
+>            , semi_large_bin     numeric (17,0) signed
+>            , semi_large_dec     decimal (17,0) signed
+>            )
+>--           CATALOG sqlt041d
+>?ifMX
+>            --ATTRIBUTE 
+>?ifMX
+>            --NO AUDIT
+>
+>         ;

--- SQL operation complete.
>>reset parserflags 1024;

--- SQL operation complete.
>>
>>--  insert blank records into table
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values ('A',0,0,0,0,0,0,0);

--- 1 row(s) inserted.
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values ('B',0,0,0,0,0,0,0);

--- 1 row(s) inserted.
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values ('C',0,0,0,0,0,0,0);

--- 1 row(s) inserted.
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values ('D',0,0,0,0,0,0,0);

--- 1 row(s) inserted.
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values ('E',0,0,0,0,0,0,0);

--- 1 row(s) inserted.
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values ('F',0,0,0,0,0,0,0);

--- 1 row(s) inserted.
>>-- <ufi-input>
>>        Insert into trunctmp
+>           values ('G',0,0,0,0,0,0,0);

--- 1 row(s) inserted.
>>
>>
>>
>>--  test first field :  +- 2.3 bin
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set two_three_bin = 1.0001
+>        where selector = 'A';

--- 1 row(s) updated.
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set two_three_bin = 1.1001
+>        where selector = 'B';

--- 1 row(s) updated.
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set two_three_bin = 1.0016
+>        where selector = 'C';

--- 1 row(s) updated.
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set two_three_bin = .0001
+>        where selector = 'D';

--- 1 row(s) updated.
>>
>>--  second field : +- .18 bin
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set smallest_bin = .000000000000000001
+>        where selector = 'A';

--- 1 row(s) updated.
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set smallest_bin = .000000000000000001
+>        where selector = 'B';

--- 1 row(s) updated.
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set smallest_bin = .100000000000000001
+>        where selector = 'C';

--- 1 row(s) updated.
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set smallest_bin = .100000000000000016
+>        where selector = 'D';

--- 1 row(s) updated.
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set smallest_bin = 1.1
+>        where selector = 'E';

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x0000EE042CFC430F to Target Type:LARGEINT(REC_BIN64_SIGNED).

--- 0 row(s) updated.
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set smallest_bin = 1.100000000000000001
+>        where selector = 'F';

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:1100000000000000001 Target Type:LARGEINT(IBIN64S) Max Target Value:999999999999999999. Instruction:RANGE_HIGH_S64S64 Operation:RANGE_HIGH.

--- 0 row(s) updated.
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set smallest_bin = 9.9
+>        where selector = 'G';

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x00005E2C8CDD63890000 to Target Type:LARGEINT(REC_BIN64_SIGNED).

--- 0 row(s) updated.
>>
>>--  third field : +- .18 dec
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set smallest_dec = .000000000000000001
+>        where selector = 'A';

--- 1 row(s) updated.
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set smallest_dec = .000000000000000001
+>        where selector = 'B';

--- 1 row(s) updated.
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set smallest_dec = .100000000000000001
+>        where selector = 'C';

--- 1 row(s) updated.
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set smallest_dec = .100000000000000016
+>        where selector = 'D';

--- 1 row(s) updated.
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set smallest_dec = 1.1
+>        where selector = 'E';

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:NUMERIC(REC_NUM_BIG_SIGNED) Source Value:0x0000EE042CFC430F0000 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).

--- 0 row(s) updated.
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set smallest_dec = 1.100000000000000001
+>        where selector = 'F';

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:1100000000000000001 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).

--- 0 row(s) updated.
>>
>>--  fourth field : +- 18.0 bin
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set largest_bin = 100000000000000000
+>        where selector = 'A';

--- 1 row(s) updated.
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set largest_bin = 990000000000000000
+>        where selector = 'B';

--- 1 row(s) updated.
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set largest_bin = 1.1
+>        where selector = 'C';

--- 1 row(s) updated.
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set largest_bin = .1
+>        where selector = 'D';

--- 1 row(s) updated.
>>
>>--  fifth field +- 18.0 (dec)
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set largest_dec = 100000000000000000
+>        where selector = 'A';

--- 1 row(s) updated.
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set largest_dec = 1.1
+>        where selector = 'B';

--- 1 row(s) updated.
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set largest_dec = .1
+>        where selector = 'C';

--- 1 row(s) updated.
>>
>>--  sixth field : +- 17.0 bin
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set semi_large_bin = 100000000000000000
+>        where selector = 'A';

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:LARGEINT(MBIN64S) Source Value:100000000000000000 Target Type:LARGEINT(IBIN64S) Max Target Value:99999999999999999. Instruction:RANGE_HIGH_S64S64 Operation:RANGE_HIGH.

--- 0 row(s) updated.
>>
>>--  seventh field : +- 17.0 dec
>>
>>-- <ufi-input>
>>        Update trunctmp
+>           set semi_large_dec = 100000000000000000
+>        where selector = 'A';

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:100000000000000000 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).

--- 0 row(s) updated.
>>
>>-- <end-input>
>>
>>-- <display>
>>         select *
+>         from trunctmp;

SELECTOR  TWO_THREE_BIN  SMALLEST_BIN           SMALLEST_DEC          LARGEST_BIN           LARGEST_DEC          SEMI_LARGE_BIN        SEMI_LARGE_DEC
--------  -------------  ---------------------  --------------------  --------------------  -------------------  --------------------  ------------------

A                 1.000    .000000000000000001   .000000000000000001    100000000000000000   100000000000000000                     0                   0
B                 1.100    .000000000000000001   .000000000000000001    990000000000000000                    1                     0                   0
C                 1.001    .100000000000000001   .100000000000000001                     1                    0                     0                   0
D                  .000    .100000000000000016   .100000000000000016                     0                    0                     0                   0
E                  .000    .000000000000000000   .000000000000000000                     0                    0                     0                   0
F                  .000    .000000000000000000   .000000000000000000                     0                    0                     0                   0
G                  .000    .000000000000000000   .000000000000000000                     0                    0                     0                   0

--- 7 row(s) selected.
>>         drop table trunctmp;

--- SQL operation complete.
>>
>>-- <testcase A9>
>>
>>-- <testcase-summary>
>>--     select into assignment truncation
>>--     test - this tests the accuracy
>>--     of assignment truncation when
>>--     values are selected from a SQL
>>--     table and assigned to a host var.
>>
>>-- <detail>
>>--     select into statements are executed for various SQL types
>>--     and host variable types. The host variables are then inserted
>>--     into a SQL dummy table which is displayed in the display code.
>>
>>-- <templates>
>>--    BS05
>>
>>-- <host-vars>
>>--       ?section declare
>>--          01 two-three-bin-var       PIC S9(2)V9(3) COMP.
>>--          01 zero-eighteen-bin-var   PIC SV9(18) COMP.
>>--          01 zero-eighteen-dec-var   PIC SV9(18) .
>>--          01 eighteen-zero-bin-var   PIC S9(18) COMP.
>>--          01 eighteen-zero-dec-var   PIC S9(18) .
>>--       ?section init
>>--          move zeros to two-three-bin-var.
>>--          move zeros to zero-eighteen-bin-var.
>>--          move zeros to zero-eighteen-dec-var.
>>--          move zeros to eighteen-zero-bin-var.
>>--          move zeros to eighteen-zero-dec-var.
>>
>>--  create dummy table seltemp
>>-- <prep-input>
>>set parserflags 1024;

--- SQL operation complete.
>> -- allow no audit
>>         create table seltemp (
+>              two_four_bin      numeric(6,4)
+>            , zero_eighteen_bin numeric(18,18)
+>            , zero_eighteen_dec decimal(18,18)
+>            , eighteen_zero_bin numeric (18,0)
+>            , eighteen_zero_dec decimal(18,0)
+>            )
+>?ifMX
+>            --ATTRIBUTE 
+>?ifMX
+>            --NO AUDIT
+>         ;

--- SQL operation complete.
>>reset parserflags 1024;

--- SQL operation complete.
>>
>>-- <prep-input>  intentional INTO cluase
>>        select two_four_bin
+>--       into :two-three-bin-var
+>        from btsel27
+>        where selector = 'A';

TWO_FOUR_BIN
------------

      1.1001

--- 1 row(s) selected.
>>
>>-- <prep-input>
>>        select two_four_bin
+>--       into :two-three-bin-var
+>        from btsel27
+>        where selector = 'A';

TWO_FOUR_BIN
------------

      1.1001

--- 1 row(s) selected.
>>
>>set param ?twoThreeBinVar 1.1001;
>>
>>-- <prep-input>
>>        insert into seltemp
+>           values (?twoThreeBinVar,0,0,0,0);

--- 1 row(s) inserted.
>>
>>-- <prep-input>
>>        select two_four_bin
+>--       into :two-three-bin-var
+>        from btsel27
+>        where selector = 'B';

TWO_FOUR_BIN
------------

      1.0016

--- 1 row(s) selected.
>>
>>set param ?twoThreeBinVar 1.0016;
>>
>>-- <prep-input>
>>        insert into seltemp
+>           values (?twoThreeBinVar,0,0,0,0);

--- 1 row(s) inserted.
>>
>>-- <prep-input>
>>        select two_four_bin
+>--       into :two-three-bin-var
+>        from btsel27
+>        where selector = 'C' ;

TWO_FOUR_BIN
------------

       .0001

--- 1 row(s) selected.
>>
>>set param ?twoThreeBinVar .0001;
>>-- <prep-input>
>>        insert into seltemp
+>           values (?twoThreeBinVar,0,0,0,0);

--- 1 row(s) inserted.
>>
>>-- <prep-input>
>>        select one_one_bin
+>--       into :eighteenZeroBinVar
+>        from btsel27
+>        where selector = 'A';

ONE_ONE_BIN
-----------

        1.1

--- 1 row(s) selected.
>>
>>set param ?eighteenZeroBinVar 1.1;
>>
>>-- <prep-input>
>>        insert into seltemp
+>           values (0,0,0,?eighteenZeroBinVar,0);

--- 1 row(s) inserted.
>>
>>-- <prep-input>
>>        select one_one_bin
+>--       into :eighteen-zero-bin-var
+>        from btsel27
+>        where selector = 'B';

ONE_ONE_BIN
-----------

         .1

--- 1 row(s) selected.
>>
>>set param ?eighteenZeroBinVar .1;
>>
>>-- <prep-input>
>>        insert into seltemp
+>           values (0,0,0,?eighteenZeroBinVar,0);

--- 1 row(s) inserted.
>>
>>-- <prep-input>
>>        select one_one_bin
+>--       into :eighteen-zero-dec-var
+>        from btsel27
+>        where selector = 'A';

ONE_ONE_BIN
-----------

        1.1

--- 1 row(s) selected.
>>
>>set param ?eighteenZeroDecVar 1.1;
>>
>>-- <prep-input>
>>        insert into seltemp
+>           values (0,0,0,0,?eighteenZeroDecVar);

*** WARNING[8411] A numeric overflow occurred during an arithmetic computation or data conversion.

--- 1 row(s) inserted.
>>
>>-- <prep-input>
>>        select one_one_bin
+>--       into :eighteen-zero-dec-var
+>        from btsel27
+>        where selector = 'B';

ONE_ONE_BIN
-----------

         .1

--- 1 row(s) selected.
>>
>>set param ?eighteenZeroDecVar .1;
>>
>>-- <prep-input>
>>        insert into seltemp
+>           values (0,0,0,0,?eighteenZeroDecVar);

*** WARNING[8411] A numeric overflow occurred during an arithmetic computation or data conversion.

--- 1 row(s) inserted.
>>
>>--  precision overflow
>>-- <prep-input>
>>        select one_one_bin
+>--       into :zero-eighteen-bin-var
+>        from btsel27
+>        where selector = 'A';

ONE_ONE_BIN
-----------

        1.1

--- 1 row(s) selected.
>>
>>set param ?zeroEighteenBinVar 1.1;
>>
>>-- <prep-input>
>>        insert into seltemp
+>           values (0,?zeroEighteenBinVar,0,0,0);

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:1.100000000000000000 to Target Type:LARGEINT(REC_BIN64_SIGNED).

*** ERROR[15015] PARAM ?zeroEighteenBinVar (value 1.1) cannot be converted to type NUMERIC(18, 18).

--- 0 row(s) inserted.
>>
>>--  actual overflow
>>-- <prep-input>
>>        select one_one_bin
+>--       into :zero-eighteen-bin-var
+>        from btsel27
+>        where selector = 'C';

ONE_ONE_BIN
-----------

        9.9

--- 1 row(s) selected.
>>
>>set param ?zeroEighteenBinVar 9.9;
>>
>>-- <prep-input>
>>        insert into seltemp
+>           values (0,?zeroEighteenBinVar,0,0,0);

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:CHAR(REC_BYTE_F_ASCII,3 BYTES,ISO88591) Source Value:9.9 to Target Type:LARGEINT(REC_BIN64_SIGNED).

*** ERROR[15015] PARAM ?zeroEighteenBinVar (value 9.9) cannot be converted to type NUMERIC(18, 18).

--- 0 row(s) inserted.
>>
>>--  actual overflow
>>-- <prep-input>
>>        select one_one_bin
+>--       into :zero-eighteen-dec-var
+>        from btsel27
+>        where selector = 'A';

ONE_ONE_BIN
-----------

        1.1

--- 1 row(s) selected.
>>
>>set param ?zeroEighteenDecVar 1.1;
>>
>>-- <prep-input>
>>        insert into seltemp
+>           values (0,?zeroEighteenDecVar,0,0,0);

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:1.100000000000000000 to Target Type:LARGEINT(REC_BIN64_SIGNED).

*** ERROR[15015] PARAM ?zeroEighteenDecVar (value 1.1) cannot be converted to type NUMERIC(18, 18).

--- 0 row(s) inserted.
>>
>>--  actual overflow
>>-- <prep-input>
>>        select one_one_bin
+>--       into :zero-eighteen-dec-var
+>        from btsel27
+>        where selector = 'C';

ONE_ONE_BIN
-----------

        9.9

--- 1 row(s) selected.
>>
>>set param ?zeroEighteenDecVar 9.9;
>>
>>-- <prep-input>
>>        insert into seltemp
+>           values (0,?zeroEighteenDecVar,0,0,0);

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:CHAR(REC_BYTE_F_ASCII,3 BYTES,ISO88591) Source Value:9.9 to Target Type:LARGEINT(REC_BIN64_SIGNED).

*** ERROR[15015] PARAM ?zeroEighteenDecVar (value 9.9) cannot be converted to type NUMERIC(18, 18).

--- 0 row(s) inserted.
>>
>>--  precision overflow
>>-- <prep-input>
>>        select large_bin_1 * 10
+>--       into :eighteen-zero-bin-var
+>        from btsel26
+>        where selector = 'D';

(EXPR)              
--------------------

 1000000000000000000

--- 1 row(s) selected.
>>
>>set param ?eighteenZeroBinVar 1000000000000000000;
>>
>>-- <prep-input>
>>        insert into seltemp
+>           values (0,0,0,?eighteenZeroBinVar,0);

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:LARGEINT(REC_BIN64_SIGNED) Source Value:1000000000000000000 to Target Type:LARGEINT(REC_BIN64_SIGNED).

*** ERROR[15015] PARAM ?eighteenZeroBinVar (value 1000000000000000000) cannot be converted to type NUMERIC(18, 0).

--- 0 row(s) inserted.
>>
>>--  actual overflow
>>-- <prep-input>
>>        select large_bin_1 * 10
+>--       into :eighteen-zero-dec-var
+>        from btsel26
+>        where selector = 'D';

(EXPR)              
--------------------

 1000000000000000000

--- 1 row(s) selected.
>>
>>set param ?eighteenZeroDecVar 1000000000000000000;
>>
>>-- <prep-input>
>>        insert into seltemp
+>           values (0,0,0,0,?eighteenZeroDecVar);

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Conversion of Source Type:CHAR(REC_BYTE_F_ASCII,19 BYTES,ISO88591) Source Value:1000000000000000000 to Target Type:DECIMAL SIGNED(REC_DECIMAL_LSE).

*** ERROR[15015] PARAM ?eighteenZeroDecVar (value 1000000000000000000) cannot be converted to type DECIMAL(18, 0).

--- 0 row(s) inserted.
>>
>>-- <end-input>
>>
>>-- <display>
>>          select *
+>          from seltemp;

TWO_FOUR_BIN  ZERO_EIGHTEEN_BIN      ZERO_EIGHTEEN_DEC     EIGHTEEN_ZERO_BIN     EIGHTEEN_ZERO_DEC
------------  ---------------------  --------------------  --------------------  -------------------

      1.1001    .000000000000000000   .000000000000000000                     0                    0
      1.0016    .000000000000000000   .000000000000000000                     0                    0
       .0001    .000000000000000000   .000000000000000000                     0                    0
       .0000    .000000000000000000   .000000000000000000                     1                    0
       .0000    .000000000000000000   .000000000000000000                     0                    0
       .0000    .000000000000000000   .000000000000000000                     0                    1
       .0000    .000000000000000000   .000000000000000000                     0                    0

--- 7 row(s) selected.
>>          drop table seltemp;

--- SQL operation complete.
>>
>>show param;
PARAM ?twoThreeBinVar .0001
PARAM ?eighteenZeroBinVar 1000000000000000000
PARAM ?eighteenZeroDecVar 1000000000000000000
PARAM ?zeroEighteenBinVar 9.9
PARAM ?zeroEighteenDecVar 9.9
>>
>>-- <end-test>
>>LOG;
