Re: [firebird-support] Fwd: Firebird MON$STATEMENTS table MON$TRANSACTION_ID field [null]
On 26-3-2015 14:33, Resul Demirok resul.demi...@gmail.com [firebird-support] wrote: > I have try to get sql text from active transaction. I had got some sql > text but some sql text is empty. I searched reason and i undertastand > mon$statements table mon$transaction_id is null and some transaction has > id in this table but as has null. why transactions id cannot add to this > table? The MON$STATEMENTS contains the statement handles in Firebird. The lifetime of a statement handle is separate from the transaction. Therefor if there is currently no transaction associated with the statement (eg: the statement handle has been created but not yet prepared or executed), then the transaction id is null. Also if a statement has been executed with a transaction, it will be disassociated once the transaction has been committed or rolled back. I am not entirely sure, but I believe the transaction is also disassociated if the statement has completed (update, delete, etc), or - for select - the cursor has been explicitly closed/dropped or if all records have been read. Also: sql text can be null/empty if the statement handle has been created, but hasn't yet been prepared with a statement. I also believe that for DDL statements the statement is automatically unprepared after execute (and the statement handle can then be reused). Mark -- Mark Rotteveel
Re: [firebird-support] Cast as Numeric without parenthesis
On 26-3-2015 09:26, liviuslivius liviusliv...@poczta.onet.pl
[firebird-support] wrote:
>
>
> Hi,
> i know this kind of cast
> SELECT CAST('1.23' AS NUMERIC(10, 2)) FROM RDB$DATABASE
> but what are the rules for this (numeric without parenthesis)
> [1]
> SELECT CAST('1.23' AS NUMERIC) FROM RDB$DATABASE
> result is "1"
> [2]
> SELECT CAST('12345678.23' as numeric) FROM RDB$DATABASE
> result is "12345678"
> [3]
> SELECT CAST('123456789.23' as numeric) FROM RDB$DATABASE
> SQL Message : -802
> Arithmetic overflow or division by zero has occurred.
> Engine Code: 335544321
> Engine Message :
> arithmetic exception, numeric overflow, or string truncation
> numeric value is out of range
> [4]
> SELECT CAST('123456789' as numeric) FROM RDB$DATABASE
> result is "123456789"
> [5]
> SELECT CAST('12345678.23' as numeric) FROM RDB$DATABASE
> result is "12345678"
> [6]
> SELECT CAST('1234567890' as numeric) FROM RDB$DATABASE
> result is "1234567890"
> [7]
> SELECT CAST('12345678901' as numeric) FROM RDB$DATABASE
> SQL Message : -802
> Arithmetic overflow or division by zero has occurred.
> Engine Code: 335544321
> Engine Message :
> arithmetic exception, numeric overflow, or string truncation
> numeric value is out of range
> what are the rules and why
> as you can see [3] raise error [4][6] not
NUMERIC without scale and precision is INTEGER. See Interbase 6.0 Data
Definition Guide page 65. This allows 1234567890 to fit, even though it
has precision 10.
According to that same page, NUMERIC(9) is stored as INTEGER.
CAST('1234567890' AS NUMERIC(9)) also works even though it is precision
10, but this is caused by Firebird treating it as a normal INTEGER, and
1234567890 < 2147483647. Technically this is a bug.
This is also the reason why [7] fails: 12345678901 doesn't fit in a 32
bit integer.
Now as to why [4] fails: 123456789.23 is a NUMERIC(12,2) which is stored
as a BIGINT 12345678923. When casting to NUMERIC (or: INTEGER), the
value is first converted to a NUMERIC(9,2): as 12345678923 and scale -2,
and only then is the scale removed to convert to NUMERIC(9) (or
INTEGER): as 123456789. But 12345678923 does not fit in an INTEGER, so
the conversion is rejected at the first step.
The reason that [5] succeeds is that 1234567823 fits in an INTEGER, if
you would have used 12345678.235 it would have failed as well.
On the other hand forcing Firebird to first truncate will allow it to
pass, the following casts both work:
CAST(CAST('12345678.235' AS NUMERIC(10)) as NUMERIC)
CAST(CAST('123456789.23' AS NUMERIC(10)) as NUMERIC)
Now if this is correct behavior from the perspective of the SQL standard
I am not sure (and I currently don't have the energy to study them to
find out), but I'd guess it isn't.
Mark
--
Mark Rotteveel
[firebird-support] Fwd: Firebird MON$STATEMENTS table MON$TRANSACTION_ID field [null]
-- Forwarded message -- From: Resul Demirok Date: 2015-03-26 15:07 GMT+02:00 Subject: Firebird MON$STATEMENTS table MON$TRANSACTION_ID field [null] To: firebird-support-subscr...@yahoogroups.com Hi, I have try to get sql text from active transaction. I had got some sql text but some sql text is empty. I searched reason and i undertastand mon$statements table mon$transaction_id is null and some transaction has id in this table but as has null. why transactions id cannot add to this table? Help please. Thanks.
Re: [firebird-support] Cast as Numeric without parenthesis
On 26/03/2015 12:35, Svein Erling Tysvær
svein.erling.tysv...@kreftregisteret.no [firebird-support] wrote:
Here, I find that
SELECT CAST(123456789.12345678 as decimal) FROM RDB$DATABASE
succeeds, whereas
SELECT CAST(123456789.123456789 as decimal) FROM RDB$DATABASE
fails. I don't know why.
Once Upon A Time there was a language called Algol68 whose designers
appeared to be keen to get all that sort of thing absolutely right,
according to a careful reading of the "transput" specs ("I/O" was
considered an old-fashioned term).
The conclusion one had to come to was that the only way to get the
conversion of numbers between binary and decimal digit representation
right was to do all the work (not terribly quickly, to be sure) as
arbitrary length string manipulation, with only a very /very/ carefully
designed conversion to binary form, from a carefully designed canonical
string representation, as the last stage of a potentially long and
complex sequence of operations.
I've never seen anyone go to remotely as much trouble to get this stuff
right since then. I can't, however, see such an approach having a
problem with the example above.
--
Tim Ward
RE: [firebird-support] Cast as Numeric without parenthesis
>then why here is an error?
>
>SELECT CAST('123456789.23' as numeric) FROM RDB$DATABASE
Logically speaking I don't understand why
SELECT CAST('123456789.23' as numeric(9,0)) FROM RDB$DATABASE
is failing when
SELECT CAST('1.23' as numeric(1,0)) FROM RDB$DATABASE
succeeds.
Practically, I guess it means that Firebird first converts from a (var)char to
an intermediate integer field before trying to round to nearest whole number,
but I don't know why.
Normally, I don't store numbers in strings, and
SELECT CAST(123456789.23 as integer) FROM RDB$DATABASE
succeeds.
Here, I find that
SELECT CAST(123456789.12345678 as decimal) FROM RDB$DATABASE
succeeds, whereas
SELECT CAST(123456789.123456789 as decimal) FROM RDB$DATABASE
fails. I don't know why.
Sorry for not being able to shed any more light,
Set
Re: RE: [firebird-support] Cast as Numeric without parenthesis
then why here is an error?
SELECT CAST('123456789.23' as numeric) FROM RDB$DATABASE
result should be 123 456 789 is less then 2 147 483 647
i assume this, because this working:
SELECT CAST('12345678.23' as numeric) FROM RDB$DATABASE
SELECT CAST('123456789' as numeric) FROM RDB$DATABASE
PS. good to know that this is Numeric(9, 0)
regards,
Karol Bieniaszewski
W dniu 2015-03-26 11:03:59 użytkownik Rand Random rand.ran...@outlook.com
[firebird-support] napisał:
Only the negative range goes to 2147483648 and you used positive value which is
ranged till 2147483647,
so this should work
SELECT CAST('-2147483648' as numeric) FROM RDB$DATABASE
SELECT CAST('-2147483648' as numeric(9,0)) FROM RDB$DATABASE
To: firebird-support@yahoogroups.com
From: firebird-support@yahoogroups.com
Date: Thu, 26 Mar 2015 10:05:17 +0100
Subject: RE: [firebird-support] Cast as Numeric without parenthesis
I would expect numbers in the range -2147483648 to +2147483647 to be accepted
(the size of an integer), and then there’s a separate field for storing whether
things are shifted to the left or right. I’ve no experience or theoretical
knowledge confirming this, but your examples would fit such a description.
Hence, I would guess NUMERIC to default to NUMERIC(9, 0).
I've confirmed that
SELECT CAST('2147483647' as numeric) FROM RDB$DATABASE
SELECT CAST('2147483647' as numeric(9,0) FROM RDB$DATABASE
both are accepted, whereas
SELECT CAST('2147483648' as numeric) FROM RDB$DATABASE
SELECT CAST('2147483648' as numeric(9,0) FROM RDB$DATABASE
are rejected.
HTH,
Set
~-|**|PrettyHtmlStart|**|-~
RE: [firebird-support] Cast as Numeric without parenthesis
Only the negative range goes to 2147483648 and you used positive value which is
ranged till 2147483647,
so this should work
SELECT CAST('-2147483648' as numeric) FROM RDB$DATABASE
SELECT CAST('-2147483648' as numeric(9,0)) FROM RDB$DATABASE
To: firebird-support@yahoogroups.com
From: firebird-support@yahoogroups.com
Date: Thu, 26 Mar 2015 10:05:17 +0100
Subject: RE: [firebird-support] Cast as Numeric without parenthesis
I would expect numbers in the range -2147483648 to +2147483647 to be
accepted (the size of an integer), and then there’s a separate field for
storing whether things are shifted to the left or right. I’ve no experience or
theoretical knowledge confirming this, but your examples would fit such a
description. Hence, I would guess NUMERIC to default to NUMERIC(9, 0).
I've confirmed that
SELECT CAST('2147483647' as numeric) FROM RDB$DATABASE
SELECT CAST('2147483647' as numeric(9,0) FROM RDB$DATABASE
both are accepted, whereas
SELECT CAST('2147483648' as numeric) FROM RDB$DATABASE
SELECT CAST('2147483648' as numeric(9,0) FROM RDB$DATABASE
are rejected.
HTH,
Set
RE: [firebird-support] Cast as Numeric without parenthesis
I would expect numbers in the range -2147483648 to +2147483647 to be accepted
(the size of an integer), and then there’s a separate field for storing whether
things are shifted to the left or right. I’ve no experience or theoretical
knowledge confirming this, but your examples would fit such a description.
Hence, I would guess NUMERIC to default to NUMERIC(9, 0).
I've confirmed that
SELECT CAST('2147483647' as numeric) FROM RDB$DATABASE
SELECT CAST('2147483647' as numeric(9,0) FROM RDB$DATABASE
both are accepted, whereas
SELECT CAST('2147483648' as numeric) FROM RDB$DATABASE
SELECT CAST('2147483648' as numeric(9,0) FROM RDB$DATABASE
are rejected.
HTH,
Set
[firebird-support] Cast as Numeric without parenthesis
Hi,
i know this kind of cast
SELECT CAST('1.23' AS NUMERIC(10, 2)) FROM RDB$DATABASE
but what are the rules for this (numeric without parenthesis)
[1]
SELECT CAST('1.23' AS NUMERIC) FROM RDB$DATABASE
result is "1"
[2]
SELECT CAST('12345678.23' as numeric) FROM RDB$DATABASE
result is "12345678"
[3]
SELECT CAST('123456789.23' as numeric) FROM RDB$DATABASE
SQL Message : -802
Arithmetic overflow or division by zero has occurred.
Engine Code : 335544321
Engine Message :
arithmetic exception, numeric overflow, or string truncation
numeric value is out of range
[4]
SELECT CAST('123456789' as numeric) FROM RDB$DATABASE
result is "123456789"
[5]
SELECT CAST('12345678.23' as numeric) FROM RDB$DATABASE
result is "12345678"
[6]
SELECT CAST('1234567890' as numeric) FROM RDB$DATABASE
result is "1234567890"
[7]
SELECT CAST('12345678901' as numeric) FROM RDB$DATABASE
SQL Message : -802
Arithmetic overflow or division by zero has occurred.
Engine Code : 335544321
Engine Message :
arithmetic exception, numeric overflow, or string truncation
numeric value is out of range
what are the rules and why
as you can see [3] raise error [4][6] not
regards,
Karol Bieniaszewski
