MONEY(32) without scale as result of a numeric expression involving two MONEY(P,S) values?

Hello, Normally, an Informix MONEY(P) always gets a scale and cannot be a floating point type like DECIMAL(P). We have discovered that the data type resulting from a numeric expression using MONEY(P,S) produces a MONEY(32) as floating point MONEY. Is this a feature or a bug? The collength value in syscolumns contains 8447 = ((32 * 256) + 255) Try this: create database test_money_32; create table table1( m1 money(16,2), m2 money(16,2) ); create view view1 (sm) as select m1 * m2 from table1; SELECT T.TABNAME, C.COLNAME, C.COLTYPE, C.COLLENGTH, C.COLNO, C.EXTENDED_ID FROM "informix".SYSCOLUMNS C, "informix".SYSTABLES T WHERE C.TABID = T.TABID AND T.TABNAME = 'view1';Result: tabname view1 colname sm coltype 8 collength 8447 colno 1 extended_id 0 Tested with IDS 14.10.FC10DE. My instinct is that this is a bug and should produce a coltype of 5 (DECIMAL), or a valid MONEY(P,S) with precision and fixed scale. Seb ------------------------------ Sebastien FLAESCH ------------------------------

Sebastien: I think that is just an artifact of you multiplying two money(16,2) columns to form the view's field. Witness: I created your test table then ran my sqlstruct utility to see what data types it sees in a query: art@Gandolf:~$ sqlstruct -d testdb -n 'test_money' -S > select m1 * m2 from table1; CREATE TABLE test_money ( (expression) DECIMAL(32,255) ); > select m1, m2 from table1; CREATE TABLE test_money ( m1 DECIMAL(16,2), m2 DECIMAL(16,2) );Notice that the two columns are seen as DECIMAL(16, 2) while the product of m1 * m2 is seen as DECIMAL(32, 255). Why 32, 255 and not 32, 4 as I would expect, and that may be a bug. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hello Art, Thanks for you answer. I known a bit about syscolumns.collength encoding for Informix types. We use this information to build our .sch schema file to compile Genero code: https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_DatabaseSchema_018.html I guess your tool produces DECIMAL(32,255) because of the collength 8447, which in fact corresponds to DECIMAL(32) without a scale. The collength value is the result of the formula based on (P)recision and (S)cale: ( (P * 256) + S ) The trick: When (S)cale is not specified in a DECIMAL(P), S = 255. For ex: With DECIMAL(32,2), you get ((32 * 256) + 2) = 8194 With DECIMAL(32), you get ((32 * 256) + 255) = 8447 Your tool should produce DECIMAL(P), if (collength % 256) = 255, and where P = (collength / 256) To me, it is normal that a numeric expression involving DECIMAL(P,S) values produces a result of type DECIMAL(32): The floating point DECIMAL(32) can hold any potential real number with the maximum precision supported by Informix. For ex: > select 1 / 33333; (constant) 3.0000300003e-05 0.0000300003... would become 0.0000 in a DECIMAL(32,4) Seb ------------------------------ Sebastien FLAESCH ------------------------------

Yea, I agree. After I posted that response I realized that I have to revisit that app's handling of DECIMAL sizing. Thanks for clarifying for the group and pointing up the error to me. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hello! Anyone from IBM or HCL to comment on this? I believe producing a floating-point MONEY(32) without scale is a bug. Otherwise, this should be explained and documented. https://help.hcl-software.com/hclinformix/15.0.0/sqr/ids_sqr_126.html Says: Unlike the DECIMAL data type, the MONEY data type is always treated as a fixed-point decimal number. Seb ------------------------------ Sebastien FLAESCH ------------------------------

I can reproduce your result. On the face of it, I think it could be a bug, albeit a rather obscure one. However, the case for a bug is not as clear as you might think. SELECT m1, m2, m1 * m2 AS product FROM table1;creates a result with a MONEY(32) - floating point - value, too. So, the problem is not in the CREATE VIEW code or the view processing; it is in the general calculation code. Using the view was a clever way to get the type information. Presumably, the argument is that the result type could be (should be?) MONEY(32,4). If the MONEY values are divided by another MONEY, the result type is DECIMAL(32) - floating point. That's reasonable; it is a ratio of two money values, and that's a pure number. More interestingly, if you divide a MONEY value by a DECIMAL value, the result is not a MONEY type but a DECIMAL(32). So, if you divide the cost of lunch between the 4 people eating, the result is not a monetary value. A counter-argument to "the result should be MONEY(32,4)" would be "What units does a MONEY * MONEY value have"? In terms of "dimensional analysis", the units (type) of multiplying two MONEY values a MONEY-squared type, which does not make a lot of sense. So, a result type of DECIMAL(32) would not be pushing the boundaries. If you multiply or divide one MONEY with a numeric value (any type other than MONEY), there's a reasonable case for "that should still be a MONEY result", but what should the precision be? That is unclear. Does it depend on whether the numeric type is an integer or a floating-point type? If I reduce the scale on the types to MONEY(10,2), I get more floating point results but with interesting lengths for divisions. However, the result of multiplying the two money types has type MONEY(20,4). + DROP TABLE IF EXISTS table1; + CREATE TABLE table1(m1 MONEY(10,2), m2 MONEY(10,2)); + DROP VIEW IF EXISTS view1; + CREATE VIEW view1(sm) AS SELECT m1 * m2 FROM table1; + SELECT T.tabname, C.colname, C.coltype, C.collength, HEX(C.collength) hex_collength, C.colno, C.extended_id FROM "informix".SysColumns C JOIN "informix".SysTables T ON C.tabid = T.tabid WHERE T.tabname = 'view1'; tabname|colname|coltype|collength|hex_collength|colno|extended_id VARCHAR(128)|VARCHAR(128)|SMALLINT|SMALLINT|CHAR(10)|SMALLINT|INTEGER view1|sm|8|5124|0x00001404|1|0 + INSERT INTO table1 VALUES(12345678.56, 98765432.54); + SELECT * FROM view1; sm MONEY(20,4) 1219326282978204.3424 + SELECT m1, m2, m1 * m2 AS product FROM table1; m1|m2|product MONEY(10,2)|MONEY(10,2)|MONEY(20,4) 12345678.56|98765432.54|1219326282978204.3424 + SELECT m1, m2, m1 / m2 AS ratio FROM table1; m1|m2|ratio MONEY(10,2)|MONEY(10,2)|DECIMAL(20) 12345678.56|98765432.54|0.12499999486156252296 + SELECT m1, m2, m1 / 3.14159 AS ratio1, m2 / 4 AS ratio2 FROM table1; m1|m2|ratio1|ratio2 MONEY(10,2)|MONEY(10,2)|DECIMAL(26)|DECIMAL(30) 12345678.56|98765432.54|3929754.8566171906582335696|24691358.135 Similarly, if one column has the type MONEY(16,2) and the other MONEY(15,2), the result of the multiplication is MONEY(31,4). This was tested on RHEL 8.8 against IDS 14.10.FC10W2DE. In short, there's a quagmire here. It will need to be discussed internally to decide what to do, if anything. I think a reasonable case can be made MONEY(32,4) in this case - where the sum of the scales is exactly 32. Another (huge) set of cases would be "what is the result of multiplying MONEY(20,4) by MONEY(20,4). We don't have a MONEY(40,8) type, so what type should that give. DECIMAL(32) suddenly looks attractive, but should the fundamental type of the result (DECIMAL vs MONEY) depend on the size of the input operands? (A MODE ANSI database might cause some problems, too, though casual testing showed floating-point decimals.) -- Jonathan Leffler < jonathan.leffler@gmail.com > #include <disclaimer.h> Guardian of DBD::Informix - v2018.1031 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to b e amused."