Re: [Firebird-devel] RETURNING clause returning NULL row when no value was updated

2017-11-29 Thread livius 

I must say that i thinked about this in the same way as you.
And i also do not know from where i got this impression ...
I really supposed that it return resultset

regards,
Karol Bieniaszewski


-Oryginalna wiadomość- 
From: Jiří Činčura

Sent: Wednesday, November 29, 2017 1:32 PM
To: firebird-devel@lists.sourceforge.net
Subject: Re: [Firebird-devel] RETURNING clause returning NULL row when no 
value was updated



   "Update returning" is the same as "execute procedure". It returns
   fixed set of values,
not a result set.


Hmm. I lived in a dream. I would argue that the i.e. "update foobar set
foo = 2 returning bar;" would return multiple rows and is allowed since
2.5. But quick test now shows that even 3.0.2 prohibits that. Not sure
where I've got that impression from.

I just can't get, at least once, helping hand from Firebird. :)

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel 



---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RETURNING clause returning NULL row when no value was updated

2017-11-29 Thread Jiří Činčura 
>"Update returning" is the same as "execute procedure". It returns
>fixed set of values, 
> not a result set.

Hmm. I lived in a dream. I would argue that the i.e. "update foobar set
foo = 2 returning bar;" would return multiple rows and is allowed since
2.5. But quick test now shows that even 3.0.2 prohibits that. Not sure
where I've got that impression from.

I just can't get, at least once, helping hand from Firebird. :)

-- 
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RETURNING clause returning NULL row when no value was updated

2017-11-29 Thread Gabor Boros 

2017. 11. 29. 11:51 keltezéssel, Jiří Činčura írta:

Hi *,

both 2.5.7 and 3.0.3 have same behavior, so it's probably expected. But
still, looks wrong to me (in such case educate me).

Hi,

"In DSQL, a statement with RETURNING always returns a single row. If the 
statement updates no records, the returned values contain NULL."


https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-update.html#fblangref25-dml-update-returningclause

Gabor

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RETURNING clause returning NULL row when no value was updated

2017-11-29 Thread Dimitry Sibiryakov 

29.11.2017 11:51, Jiří Činčura wrote:

The second update did not update any records, yet it still returns one
row in result with NULL value. Shouldn't it return empty result set?


  "Update returning" is the same as "execute procedure". It returns fixed set of values, 
not a result set.



--
  WBR, SD.

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RETURNING clause returning NULL row when no value was updated

2017-11-29 Thread Omacht András 
Hi!

"With this I don't know whether the RETURNING returns NULL as a new value or 
nothing was updated. I would have to go to PSQL and use ROW_COUNT or something 
like that."

Workaround:
update foobar set foo = 2 where id = 1 returning id, bar;

if id is null then bar null values is valid, otherwise no update were made.

András

-Original Message-
From: Jiří Činčura [mailto:j...@cincura.net] 
Sent: Wednesday, November 29, 2017 11:52 AM
To: For discussion among Firebird Developers 

Subject: [Firebird-devel] RETURNING clause returning NULL row when no value was 
updated

Hi *,

both 2.5.7 and 3.0.3 have same behavior, so it's probably expected. But still, 
looks wrong to me (in such case educate me).

SQL> create table foobar (id int primary key, foo int, bar generated
always as (foo+1));
SQL> insert into foobar values (1, 1);
SQL> update foobar set foo = 2 where id = 1 returning bar;

  BAR
=
3

SQL> update foobar set foo = 2 where id = -1 returning bar;

  BAR
=
   

SQL>

The second update did not update any records, yet it still returns one row in 
result with NULL value. Shouldn't it return empty result set?
With this I don't know whether the RETURNING returns NULL as a new value or 
nothing was updated. I would have to go to PSQL and use ROW_COUNT or something 
like that.

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

--
Check out the vibrant tech community on one of the world's most engaging tech 
sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web 
interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

[Firebird-devel] RETURNING clause returning NULL row when no value was updated

2017-11-29 Thread Jiří Činčura 
Hi *,

both 2.5.7 and 3.0.3 have same behavior, so it's probably expected. But
still, looks wrong to me (in such case educate me).

SQL> create table foobar (id int primary key, foo int, bar generated
always as (foo+1));
SQL> insert into foobar values (1, 1);
SQL> update foobar set foo = 2 where id = 1 returning bar;

  BAR
=
3

SQL> update foobar set foo = 2 where id = -1 returning bar;

  BAR
=
   

SQL>

The second update did not update any records, yet it still returns one
row in result with NULL value. Shouldn't it return empty result set?
With this I don't know whether the RETURNING returns NULL as a new value
or nothing was updated. I would have to go to PSQL and use ROW_COUNT or
something like that.

-- 
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] Unique constraint not working on first command after encryption

2017-11-29 Thread Alex Peshkoff via Firebird-devel 

On 11/29/17 10:57, Jiří Činčura wrote:

OK, the Firebird-4.0.0.816-0_Win32 behaves correctly. Will this go into
3.0.3 as well?



As you can already see it's fixed in FB3 branch too.



--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel