Re: [sqlite] JOIN works very strange [3.6.2]

2008-09-20 Thread Jay A. Kreibich 
On Sat, Sep 20, 2008 at 08:45:16AM +0400, Alexander Batyrshin scratched on the 
wall:
>  Hello everyone,
> 
> I gets strange result from this query on SQLite-3.6.2
> 
> SELECT
> town.id, town_log.new_player_id, player.name
> FROM
> town_log
> LEFT JOIN town
> LEFT JOIN player
> ON
> town.id = town_log.town_id AND town_log.new_player_id = player.id
> WHERE
> town_log.id = 5195

  "ON" is part of a JOIN operation.  You have two JOINs but only one ON,
  and it is only getting applied to the second JOIN.

  I think you want something closer to this:
  
...
FROM
town_log 
LEFT JOIN town ON town.id = town_log.town_id
LEFT JOIN player ON town_log.new_player_id = player.id
WHERE
town_log.id = 5195



  -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JOIN works very strange [3.6.2]

2008-09-19 Thread Alexander Batyrshin 
I just want to add that this SQL query works great at 3.5.4.
And this is explain:

addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 0 EXPLAIN SELECT
town.id, town_log.new_player_id, player.name
FROM
town_log
LEFT JOIN town
LEFT JOIN player
ON
town.id = town_log.town_id AND town_log.new_player_id = player.id
WHERE
town_log.id = 5195;  00
1 Integer5195  1 000
2 Goto   0 46000
3 SetNumColumns  0 5 000
4 OpenRead   0 14000
5 SetNumColumns  0 1 000
6 OpenRead   1 6 000
7 SetNumColumns  0 2 000
8 OpenRead   2 2 000
9 SetNumColumns  0 2 000
10OpenRead   3 3 0 keyinfo(1,BINARY)  00
11MustBeInt  1 41000
12NotExists  0 41100
13Integer0 3 000
14Rewind 1 38000
15Integer1 3 000
16Integer0 4 000
17Column 0 4 600
18IsNull 6 33000
19Affinity   6 1 0 db 00
20MoveGe 3 336 1  00
21IdxGE  3 336 1  01
22IdxRowid   3 10000
23MoveGe 2 0 10   00
24Column 1 0 10   00
25Column 0 1 11   00
26Ne 113210collseq(BINARY)  6b
27Integer1 4 000
28Column 1 0 12   00
29Column 0 4 13   00
30Column 2 1 14   00
31ResultRow  123 000
32Next   3 21000
33IfPos  4 37000
34NullRow2 0 000
35NullRow3 0 000
36Goto   0 27000
37Next   1 15000
38IfPos  3 41000
39NullRow1 0 000
40Goto   0 15000
41Close  0 0 000
42Close  1 0 000
43Close  2 0 000
44Close  3 0 000
45Halt   0 0 000
46Transaction0 0 000
47VerifyCookie   0 92000
48TableLock  0 140 town_log   00
49TableLock  0 6 0 town   00
50TableLock  0 2 0 player 00
51Goto   0 3 000


PS:
And the last one. I want to say that generally SQLite-3.6.3 is slowly
than 3.5.4 on my OS X 10.5.5.
My result is that 3.6.3 is slowly near 5-7%.

--
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid



On Sat, Sep 20, 2008 at 8:45 AM, Alexander Batyrshin <[EMAIL PROTECTED]> wrote:
>  Hello everyone,
>
> I gets strange result from this query on SQLite-3.6.2
>
> SELECT
>town.id, town_log.new_player_id, player.name
> FROM
>town_log
>LEFT JOIN town
>LEFT JOIN player
> ON
>town.id = town_log.town_id AND town_log.new_player_id = player.id
> WHERE
>town_log.id = 5195
>
> As you can see this query should return only 1 row, because
> town_log.id is unique key.
> But I gets rows with different town.id (1 col), with constant
> new_player.id (2 col) and JOIN do not work for player.name, couse 3
> col is empty.
>
> result:
> []
> 45512   9266
> 44544   9266
> 45229   9266
> 46376   9266
> 45927   9266
> 46645   9266
> []
>
>
> Any ideas what's going on?
>
> --
> Alexander Batyrshin aka bash
> bash = Biomechanica Artificial Sabotage Humanoid
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users