Re: [sqlite] What is wrong with this trigger?
On 16 Nov 2013, at 3:14pm, James K. Lowden wrote: > On Sun, 10 Nov 2013 14:36:06 -0800 > Igor Korot wrote: > >> Well from strictly mathematical point of view maximum or minimum of >> nothing is nothing. And since nothing is 0, than it is zero. > > Who is the oldest female US president? And, to extend that, what is the age of the youngest female US president ? There's a huge difference between 'no answer' and zero. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this trigger?
On Sun, 10 Nov 2013 14:36:06 -0800
Igor Korot wrote:
> Well from strictly mathematical point of view maximum or minimum of
> nothing is nothing. And since nothing is 0, than it is zero.
Who is the oldest female US president?
You largest of a set must be a member of that set. Actually, I suspect
that all set operations yield a set, so max{T} yields a set that is a
subset of {T}. When {T} is the empty set, max{T} yields the empty
set.
--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this trigger?
On 11/11/2013 12:36 AM, Igor Korot wrote: Well from strictly mathematical point of view maximum or minimum of nothing is nothing. And since nothing is 0, than it is zero. Thank you. max() can't simply pull 0 out of the air, for it 0 is the same random number as 1e-129 which might be also considered 0 if only 128 decimal digits are significant to you. SELECT produce empty set which is nothing in algebra of sets, max() produce scalar value where nothing is undefined if you're looking at numbers. Hence max() on empty set produce NULL, which is nothing in SQL. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this trigger?
On 10 Nov 2013, at 10:36pm, Igor Korot wrote: > On Sun, Nov 10, 2013 at 8:44 AM, Simon Slavin wrote: > >> On 10 Nov 2013, at 4:26pm, Igor Korot wrote: >> >>> I'm updating the table when the program exit. >> >> There is not need to update the table. In fact there's no need to store the >> rank values at all. > > There is. > Consider the fact that the user changes the rank and exit the program > with the saving. Then he select the option "Reset the league". > Where can I get the original rank from? What does 'original' rank depend on ? The number of points the player had when they were added to the league ? Whatever it is, store that value in the table and use that to figure out what rank the player is by counting how many players have the same or more points than they do. It'll be something like SELECT count(*) FROM league WHERE pointsWhenJoined >= [this player's pointsWhenJoined] You only need to do this when you actually need to know the number. You don't need to keep updating a lot of player's points every time something happens, and this saves you a lot of programming and time. >>> Now, I would expect for the max() function in this case to be >>> evaluated to 0 and not NULL. >>> This is plain mathematics: max value of nothing is nothing which >>> mathematically 0. >> >> No. Max of nothing is nothing. And the way you express 'nothing' in SQL is >> NULL. NULL is not 0 or 0.0. NULL is not the empty string "". NULL is not >> the BLOB x''. NULL means 'value missing'. > > Well from strictly mathematical point of view maximum or minimum of > nothing is nothing. And since nothing is 0, than it is zero. No, nothing is not zero. Read what I wrote again. Try this: if I want you to be very very quiet do I say to you "I want you to say zero." ? If you go to a bank you don't have an account with and ask "How much money do I have in my account ?" do they answer "You have zero dollars in your account, Sir." ? Google difference between nothing and zero and if it's still not clear, ask someone who teaches maths. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this trigger?
Simon, On Sun, Nov 10, 2013 at 8:44 AM, Simon Slavin wrote: > > On 10 Nov 2013, at 4:26pm, Igor Korot wrote: > >> I'm updating the table when the program exit. > > There is not need to update the table. In fact there's no need to store the > rank values at all. There is. Consider the fact that the user changes the rank and exit the program with the saving. Then he select the option "Reset the league". Where can I get the original rank from? > >> Now, I would expect for the max() function in this case to be >> evaluated to 0 and not NULL. >> This is plain mathematics: max value of nothing is nothing which >> mathematically 0. > > No. Max of nothing is nothing. And the way you express 'nothing' in SQL is > NULL. NULL is not 0 or 0.0. NULL is not the empty string "". NULL is not > the BLOB x''. NULL means 'value missing'. > > As another contributor to the thread noted, suppose you have a list of any > number of values. You remove them one by one. At each stage, the maximum of > the remaining list can only remain the same or get smaller. Why should > removing the last number somehow /increase/ the maximum of a list ? Now > suppose your list is -18, -41, -23 and you remove them in any order. Well from strictly mathematical point of view maximum or minimum of nothing is nothing. And since nothing is 0, than it is zero. Thank you. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this trigger?
If Igor "must have a number" for the "max", even when there are no rows, then I'm certain that the coalesce() function is exactly what he needs to use. coalesce(max(id),0)) will give the maximum "id" value of the result set or 0 if there are no results in the result set (NULL). On Sun, Nov 10, 2013 at 10:44 AM, Simon Slavin wrote: > > On 10 Nov 2013, at 4:26pm, Igor Korot wrote: > > > I'm updating the table when the program exit. > > There is not need to update the table. In fact there's no need to store > the rank values at all. > > > Now, I would expect for the max() function in this case to be > > evaluated to 0 and not NULL. > > This is plain mathematics: max value of nothing is nothing which > > mathematically 0. > > No. Max of nothing is nothing. And the way you express 'nothing' in SQL > is NULL. NULL is not 0 or 0.0. NULL is not the empty string "". NULL is > not the BLOB x''. NULL means 'value missing'. > > As another contributor to the thread noted, suppose you have a list of any > number of values. You remove them one by one. At each stage, the maximum > of the remaining list can only remain the same or get smaller. Why should > removing the last number somehow /increase/ the maximum of a list ? Now > suppose your list is -18, -41, -23 and you remove them in any order. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- This is clearly another case of too many mad scientists, and not enough hunchbacks. Maranatha! <>< John McKown ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this trigger?
On 10 Nov 2013, at 4:26pm, Igor Korot wrote: > I'm updating the table when the program exit. There is not need to update the table. In fact there's no need to store the rank values at all. > Now, I would expect for the max() function in this case to be > evaluated to 0 and not NULL. > This is plain mathematics: max value of nothing is nothing which > mathematically 0. No. Max of nothing is nothing. And the way you express 'nothing' in SQL is NULL. NULL is not 0 or 0.0. NULL is not the empty string "". NULL is not the BLOB x''. NULL means 'value missing'. As another contributor to the thread noted, suppose you have a list of any number of values. You remove them one by one. At each stage, the maximum of the remaining list can only remain the same or get smaller. Why should removing the last number somehow /increase/ the maximum of a list ? Now suppose your list is -18, -41, -23 and you remove them in any order. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this trigger?
Simon et al, On Sun, Nov 10, 2013 at 7:18 AM, Simon Slavin wrote: > > On 10 Nov 2013, at 3:05pm, Igor Tandetnik wrote: > >> That rather depends on what value you deem to be the correct one. You've >> never explained the desired outcome of all this choreography. > > He's trying to keep each player's rank in his league table. And he wants the > rank column for all players to be updated each time he inserts a new player > in the table, so if he inserts a new player who is fourth, all players that > were fourth or after fourth move down one rank. This is what I'm looking for. Consider the players table data: id player_name rank 1PlayerA 1 2PlayerB 2 3PlayerC 3 4PlayerD 4 Now when the new league is created I specify that only PlayerB and PlayerC should be in there. And so I'm writing the following query: INSERT INTO playersinleague() SELECT * FROM players ORDER BY rank; Now in playersinleague table I'd like to see following: idcurrent_rank original_rank 2 1 1 3 2 2 So that rank in playersinleague table should start from 1 and be incremented by 1. > > Once again I suggest using a TRIGGER is the wrong way to do it: > > A) You have to write a trigger for when a player joins a league. > B) You have to write an equivalent trigger for when a player leaves a league. > C) You have to write an equivalent trigger for when a player moves position. > D) That's a lot of checking and operations any time any player does anything. Not really. I'm updating the table when the program exit. And I already have this logic. It does not require to write any triggers. But because the ranks in the main pool (players table) are not ordered the same way as they could be added to the league (playersinleague table) it can be updated with the trigger. I hope I explained everything and it's now clearer. Now when the league is created there is no records and, therefore, max(current_rank) is NULL and so "1 + max(current_rank)" does evaluate to NULL. Now, I would expect for the max() function in this case to be evaluated to 0 and not NULL. This is plain mathematics: max value of nothing is nothing which mathematically 0. Which means that I can use this value in mathematical expressions. Thank you. > > Instead I think it makes more sense just to deduce the ranks from the results > of your SELECT, when you select the players in points order. The player who > comes off first is ranked 1. Not only is it simple but it requires no extra > operations, so it speeds up all INSERT/UPDATE/DELETE. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this trigger?
On 10 Nov 2013, at 3:05pm, Igor Tandetnik wrote: > That rather depends on what value you deem to be the correct one. You've > never explained the desired outcome of all this choreography. He's trying to keep each player's rank in his league table. And he wants the rank column for all players to be updated each time he inserts a new player in the table, so if he inserts a new player who is fourth, all players that were fourth or after fourth move down one rank. Once again I suggest using a TRIGGER is the wrong way to do it: A) You have to write a trigger for when a player joins a league. B) You have to write an equivalent trigger for when a player leaves a league. C) You have to write an equivalent trigger for when a player moves position. D) That's a lot of checking and operations any time any player does anything. Instead I think it makes more sense just to deduce the ranks from the results of your SELECT, when you select the players in points order. The player who comes off first is ranked 1. Not only is it simple but it requires no extra operations, so it speeds up all INSERT/UPDATE/DELETE. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this trigger?
On 11/10/2013 12:54 AM, Igor Korot wrote: CREATE TRIGGER playersinleague_insert AFTER INSERT on playersinleague BEGIN UPDATE playersinleague SET current_rank = 1+ (select max( current_rank ) from playersinleague WHERE id = new.id), original_rank = current_rank WHERE id = new.id AND current_rank IS NULL; END; This trigger makes no sense at all. It says: when a row is inserted with current_rank of NULL, take the value of current_rank *from that row* (which is, of course, NULL), add 1 to it (which produces NULL), and write it back to current_rank (which thus remains NULL). The whole thing is a very elaborate no-op. I suspect you want to drop "WHERE id = new.id" part from the subquery. Also, "original_rank = current_rank" uses the current value of current_rank before the UPDATE (that is, NULL), not the new value. SELECT * FROM playersinleague WHERE id=1 AND playerid=1; 1|1|1|27|42.0|42.0|0|0|||0 I didn't specify current_rank on the INSERT query which means the field shuold be NULL. Yes it is. Which is what the SELECT statement shows. Which part is surprising? Also will the UPDATE in trigger do the right thing and correctly update the original_rank? That rather depends on what value you deem to be the correct one. You've never explained the desired outcome of all this choreography. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this trigger?
On 10 Nov 2013, at 12:34pm, John McKown wrote: > If you need a particular "default" value instead of a NULL, use the > coalesce() function. > > select coalesce(max(current_rank),0) FROM playersinleague WHERE id = 1; Purely for clarity's sake, and not to say coalesce() doesn't work, I suggest you use ifnull() instead. Also, I agree with John's analysis: max() of zero items is NULL. I'm not sure what stdev() of zero items is, but fortunately SQLite doesn't have that function. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this trigger?
I would expect NULL (empty string) as the result from a max (or any other summary) query which has "no rows". Any particular value wouldn't make too much sense to me. Why any particular value? 0 doesn't really make sense. Well no more or less sense than -1 or even -10372 (picked "at random"). If you need a particular "default" value instead of a NULL, use the coalesce() function. select coalesce(max(current_rank),0) FROM playersinleague WHERE id = 1; On Sun, Nov 10, 2013 at 2:39 AM, Igor Korot wrote: > I just tried to do: > > SELECT max(current_rank) FROM playersinleague WHERE id = 1; > > and I got an empty string and not 0. > > Is this a bug? Should max(field) return 0 if there is no records that > satisfy criteria? > > Thank you. > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this trigger?
I just tried to do: SELECT max(current_rank) FROM playersinleague WHERE id = 1; and I got an empty string and not 0. Is this a bug? Should max(field) return 0 if there is no records that satisfy criteria? Thank you. On Sat, Nov 9, 2013 at 9:54 PM, Igor Korot wrote: > Hi, ALL, > > CREATE TABLE playersinleague( > id integer > playerid integer > ishitter char > age integer > value double > currvalue double > draft boolean > isnew char(1) > current_rank integer > original_rank integer > deleted integer > foreign key(id) references leagues(id) > foreign key(playerid) references players(playerid) > ); > > CREATE TRIGGER playersinleague_insert AFTER INSERT on playersinleague > BEGIN > UPDATE playersinleague SET current_rank = 1+ (select max( > current_rank ) from playersinleague WHERE id = new.id), original_rank > = current_rank WHERE id = new.id AND current_rank IS NULL; > END; > > or do it this way: > > DROP TRIGGER playersinleague_insert; > CREATE TRIGGER playersinleague_insert AFTER INSERT ON playersinleague > BEGIN > UPDATE playersinleague SET current_rank = 1 + (select max( > current_rank ) FROM playersinleague WHERE id = new.id), original_rank > = current_rank WHERE rowid = new.rowid; > END; > > After that: > > INSERT INTO playersinleague(id,playerid,ishitter,age,value,currvalue,dra > ft,isnew,deleted) SELECT 1, players.playerid, players.ishitter, players.age, > pla > yers.value, players.value, 0, "0", 0 FROM players WHERE (teamid >= 1 AND > teamid > <= 15) OR (teamid >= 16 AND teamid <= 30) ORDER BY rank; > > SELECT * FROM playersinleague WHERE id=1 AND playerid=1; > 1|1|1|27|42.0|42.0|0|0|||0 > > I didn't specify current_rank on the INSERT query which means the > field shuold be NULL. > > I think I am doing something wrong but I don't see what. > > Could someone please help? > Also will the UPDATE in trigger do the right thing and correctly > update the original_rank? > > Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What is wrong with this trigger?
Hi, ALL, CREATE TABLE playersinleague( id integer playerid integer ishitter char age integer value double currvalue double draft boolean isnew char(1) current_rank integer original_rank integer deleted integer foreign key(id) references leagues(id) foreign key(playerid) references players(playerid) ); CREATE TRIGGER playersinleague_insert AFTER INSERT on playersinleague BEGIN UPDATE playersinleague SET current_rank = 1+ (select max( current_rank ) from playersinleague WHERE id = new.id), original_rank = current_rank WHERE id = new.id AND current_rank IS NULL; END; or do it this way: DROP TRIGGER playersinleague_insert; CREATE TRIGGER playersinleague_insert AFTER INSERT ON playersinleague BEGIN UPDATE playersinleague SET current_rank = 1 + (select max( current_rank ) FROM playersinleague WHERE id = new.id), original_rank = current_rank WHERE rowid = new.rowid; END; After that: INSERT INTO playersinleague(id,playerid,ishitter,age,value,currvalue,dra ft,isnew,deleted) SELECT 1, players.playerid, players.ishitter, players.age, pla yers.value, players.value, 0, "0", 0 FROM players WHERE (teamid >= 1 AND teamid <= 15) OR (teamid >= 16 AND teamid <= 30) ORDER BY rank; SELECT * FROM playersinleague WHERE id=1 AND playerid=1; 1|1|1|27|42.0|42.0|0|0|||0 I didn't specify current_rank on the INSERT query which means the field shuold be NULL. I think I am doing something wrong but I don't see what. Could someone please help? Also will the UPDATE in trigger do the right thing and correctly update the original_rank? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
