Re: [Firebird-devel] SET BIND OF for NUMERIC / DECIMAL
On 2019-12-26 18:43, Mark Rotteveel wrote: 128-bit literal - is it Numeric or Decimal? Given the lack of an int128, we should consider it an exact numeric literal of either DECIMAL or NUMERIC. The standard says (5.3 ): """ 22) The declared type of an ENL is an implementation-defined exact numeric type whose scale is the number of s to the right of the . There shall be an exact numeric type capable of representing the value of ENL exactly. """ In addition, 4.4.1 Introduction to numbers, says: """ An SQL-implementation is permitted to regard certain type>s as equivalent, if they have the same precision, scale, and radix, as permitted by the Syntax Rules of Subclause 6.1, “type>”. When two or more s are equivalent, the SQL-implementation chooses one of these equivalent type>s as the normal form representing that equivalence class of s. The normal form determines the name of the exact numeric type in the numeric type descriptor. """ See also rule 24 in 6.1 Formally, I take this to mean we should choose one type, eg DECIMAL, Done it for literals that do not fit in BIGINT. but that would probably not match people assuming that literal without decimal points are integral literals. We could consider a middle ground where literals without decimal point with 18 or less digits are BIGINT, that longer literals are DECIMAL, and that literals with decimal point are DECIMAL. I'm choosing DECIMAL here, because Firebird's NUMERIC doesn't conform to the standard requirements for NUMERIC, although literals with a decimal point are currently represented as scaled bigints **without** subtype which I believe historically are usually interpreted as NUMERIC. That's interpreted as scaled integer - any exact type in firebird is 'scaled' with scale==0 being a special case. What appears a bit strange is that running select 1, 1.5 from rdb$database; I get first column represented as INT but second as BIGINT. But as long as this did not change at least since 2.5 I will nopt touch it in order not to break applications for nothing. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] SET BIND OF for NUMERIC / DECIMAL
On 2019-12-26 19:01, Mark Rotteveel wrote: On 26/12/2019 16:43, Mark Rotteveel wrote: Formally, I take this to mean we should choose one type, eg DECIMAL, but that would probably not match people assuming that literal without decimal points are integral literals. We could consider a middle ground where literals without decimal point with 18 or less digits are BIGINT, that longer literals are DECIMAL, and that literals with decimal point are DECIMAL. On a related note, I just noticed that exact numeric literals with more than 19 digits have a subtype that is equal to the length of the literal (digits + decimal point). That's a bug :-) Right now working on it. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] SET BIND OF for NUMERIC / DECIMAL
On 26/12/2019 16:43, Mark Rotteveel wrote: Formally, I take this to mean we should choose one type, eg DECIMAL, but that would probably not match people assuming that literal without decimal points are integral literals. We could consider a middle ground where literals without decimal point with 18 or less digits are BIGINT, that longer literals are DECIMAL, and that literals with decimal point are DECIMAL. On a related note, I just noticed that exact numeric literals with more than 19 digits have a subtype that is equal to the length of the literal (digits + decimal point). Eg: """ SET SQLDA_DISPLAY ON; SQL> select 123456789012345678.90 from rdb$database; INPUT message field count: 0 OUTPUT message field count: 1 01: sqltype: 32752 NUMERIC(38) scale: -2 subtype: 21 len: 16 : name: CONSTANT alias: CONSTANT : table: owner: CONSTANT = 123456789012345678.90 SQL> select 123456789012345678.901 from rdb$database; INPUT message field count: 0 OUTPUT message field count: 1 01: sqltype: 32752 NUMERIC(38) scale: -3 subtype: 22 len: 16 : name: CONSTANT alias: CONSTANT : table: owner: CONSTANT = 123456789012345678.901 SQL> select 123456789012345678901 from rdb$database; INPUT message field count: 0 OUTPUT message field count: 1 01: sqltype: 32752 NUMERIC(38) scale: 0 subtype: 21 len: 16 : name: CONSTANT alias: CONSTANT : table: owner: CONSTANT = 123456789012345678901 """ Why is that? I would expect either subtype 0 (like literals of 19 or less digits), or otherwise subtype 1 or 2. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] SET BIND OF for NUMERIC / DECIMAL
On 26/12/2019 16:11, Alex Peshkoff via Firebird-devel wrote: On 2019-12-26 17:40, Mark Rotteveel wrote: On 25/12/2019 12:11, Alex Peshkoff via Firebird-devel wrote: On 2019-12-11 18:06, Mark Rotteveel wrote: In other words, it looks like the mapping: of INTEGER-backed types 1. ignores subtype information and Ignoring subtype is as designed here. The aim of SET BIND is to help client deal with fields values in cases when it for some reason can not work with that format directly. INTEGER-backed types have no format difference depending upon subtype. I can include subtype support provided there will be some other reasonable syntax for current behavior. I think it is necessary to include subtype information, or at minimum **exclude** the integer types from the current behaviour. I would definitely not expect that a binding for NUMERIC or DECIMAL would also map SMALLINT/INTEGER/BIGINT. However, my preference would be that the mapping should explicitly only map the subtype corresponding to the name, so NUMERIC should only map NUMERIC and DECIMAL should only DECIMAL. I don't see a need to have syntax that covers the current behaviour. 128-bit literal - is it Numeric or Decimal? Given the lack of an int128, we should consider it an exact numeric literal of either DECIMAL or NUMERIC. The standard says (5.3 ): """ 22) The declared type of an ENL is an implementation-defined exact numeric type whose scale is the number of s to the right of the . There shall be an exact numeric type capable of representing the value of ENL exactly. """ In addition, 4.4.1 Introduction to numbers, says: """ An SQL-implementation is permitted to regard certain type>s as equivalent, if they have the same precision, scale, and radix, as permitted by the Syntax Rules of Subclause 6.1, “”. When two or more s are equivalent, the SQL-implementation chooses one of these equivalent s as the normal form representing that equivalence class of s. The normal form determines the name of the exact numeric type in the numeric type descriptor. """ See also rule 24 in 6.1 Formally, I take this to mean we should choose one type, eg DECIMAL, but that would probably not match people assuming that literal without decimal points are integral literals. We could consider a middle ground where literals without decimal point with 18 or less digits are BIGINT, that longer literals are DECIMAL, and that literals with decimal point are DECIMAL. I'm choosing DECIMAL here, because Firebird's NUMERIC doesn't conform to the standard requirements for NUMERIC, although literals with a decimal point are currently represented as scaled bigints **without** subtype which I believe historically are usually interpreted as NUMERIC. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] SET BIND OF for NUMERIC / DECIMAL
On 2019-12-26 17:40, Mark Rotteveel wrote: On 25/12/2019 12:11, Alex Peshkoff via Firebird-devel wrote: On 2019-12-11 18:06, Mark Rotteveel wrote: In other words, it looks like the mapping: of INTEGER-backed types 1. ignores subtype information and Ignoring subtype is as designed here. The aim of SET BIND is to help client deal with fields values in cases when it for some reason can not work with that format directly. INTEGER-backed types have no format difference depending upon subtype. I can include subtype support provided there will be some other reasonable syntax for current behavior. I think it is necessary to include subtype information, or at minimum **exclude** the integer types from the current behaviour. I would definitely not expect that a binding for NUMERIC or DECIMAL would also map SMALLINT/INTEGER/BIGINT. However, my preference would be that the mapping should explicitly only map the subtype corresponding to the name, so NUMERIC should only map NUMERIC and DECIMAL should only DECIMAL. I don't see a need to have syntax that covers the current behaviour. 128-bit literal - is it Numeric or Decimal? Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] SET BIND OF for NUMERIC / DECIMAL
On 25/12/2019 12:11, Alex Peshkoff via Firebird-devel wrote: On 2019-12-11 18:06, Mark Rotteveel wrote: In other words, it looks like the mapping: of INTEGER-backed types 1. ignores subtype information and Ignoring subtype is as designed here. The aim of SET BIND is to help client deal with fields values in cases when it for some reason can not work with that format directly. INTEGER-backed types have no format difference depending upon subtype. I can include subtype support provided there will be some other reasonable syntax for current behavior. I think it is necessary to include subtype information, or at minimum **exclude** the integer types from the current behaviour. I would definitely not expect that a binding for NUMERIC or DECIMAL would also map SMALLINT/INTEGER/BIGINT. However, my preference would be that the mapping should explicitly only map the subtype corresponding to the name, so NUMERIC should only map NUMERIC and DECIMAL should only DECIMAL. I don't see a need to have syntax that covers the current behaviour. Mark -- Mark Rotteveel Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] SET BIND OF for NUMERIC / DECIMAL
On 2019-12-11 18:06, Mark Rotteveel wrote: In other words, it looks like the mapping: of INTEGER-backed types 1. ignores subtype information and Ignoring subtype is as designed here. The aim of SET BIND is to help client deal with fields values in cases when it for some reason can not work with that format directly. INTEGER-backed types have no format difference depending upon subtype. I can include subtype support provided there will be some other reasonable syntax for current behavior. 2. does not handle precision-less as covering all values, but instead selects the INTEGER-backed types Will do. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
