postgresql - Implicit JOIN returns unexpected results -


i have 2 tables:

fccuser=# select count(*) public.fine_collection user_id = 5000;  count -------   2500 (1 row)  fccuser=# select count(*) public.police_notice user_id = 5000;  count -------   1011 (1 row)

and when issue 

fccuser=# select count(*)  public.fine_collection, public.police_notice  fine_collection.user_id = 5000    , fine_collection.user_id = police_notice.user_id;

i expecting 2500 got

count

2527500 (1 row)

i.e., cartesian product of two. , analyze is:

fccuser=# explain analyze verbose select count(*) public.fine_collection, public.police_notice fine_collection.user_id = 5000 , fine_collection.user_id = police_notice.user_id;                                                                            query plan -----------------------------------------------------------------------------------------------------------------------------------------------------------------  aggregate  (cost=47657.20..47657.21 rows=1 width=0) (actual time=1991.552..1991.552 rows=1 loops=1)    output: count(*)    ->  nested loop  (cost=0.86..39760.60 rows=3158640 width=0) (actual time=0.448..1462.155 rows=2527500 loops=1)          ->  index scan using idx_user_id on public.fine_collection  (cost=0.43..265.98 rows=8774 width=8) (actual time=0.213..2.448 rows=2500 loops=1)                output: fine_collection.user_id                index cond: (fine_collection.user_id = 5000)                heap fetches: 1771          ->  materialize  (cost=0.42..12.52 rows=360 width=2) (actual time=0.000..0.205 rows=1011 loops=2500)                output: police_notice.user_id                ->  index scan using idx_pn_userid on public.police_notice  (cost=0.42..10.72 rows=360 width=2) (actual time=0.217..1.101 rows=1011 loops=1)                      output: police_notice.user_id                      index cond: (police_notice.user_id = 5000)                      heap fetches: 751  planning time: 2.126 ms  execution time: 1991.697 ms (15 rows)

and postgres documentation states when join performed on non-primary columns first creates cartesian product (cross join) , applies filter. think cartesian product have rows same user_id in case, not sure how filter can applied

same happens left join, inner join etc., subquery seems give correct result of 2500.

i reasonably sure not work way in mysql though. thoughts?

thank you

the result of join correct. join every collection user_id 5000 every police notice same user_id. have 2500 , 1011 rows joined together, , produces 2527500 new rows.


Comments

Post a Comment

Popular posts from this blog

node.js - Node js - Trying to send POST request, but it is not loading javascript content -

i have been trying send post request website, , has not been loading full site. i guessing problem node js not loading javascript content, executing it. can see javascript in response, though never loads content javascript files producing in html. what mean is, doesn't execute javascript. how make node js code overcome this, , wait the full website load? have tried native code, , "request' module, both producing same errors. if helps, don't need native javascript. modules fine. some code have tried (request module): var options = { method: 'post', uri: 'https://url', formdata: { 'email': 'email', 'prize': '0', 'transactional': 'on' }, headers: { /* 'content-type': 'application/x-www-form-urlencoded' */ // set automatically } }; rp(options) .then(function (body) { console.log(body) }) .catch(function (err) { console.log(err) }); when fetch content...
Read more

javascript - Replicate keyboard event with html button -

i have tried few ways make happen. have javascript pinball game uses keyboard keys controls. converting application touch screen display. workaround, hoping overlay buttons when clicked simulate keyboard strokes. have done research , sounds should work. using jquery v3.2.1 here things have tried far... html element: <input type="button" class="coil"></input> $('.coil').trigger({type: 'keydown', which: 40, keycode: 40}); with this, not errors, nothing happens. var coilkey = $('body').keydown(function(e) { switch (e.keycode) { case 40: break;} }) $('.coil').click(coilkey); this 1 returns error in console... ((jquery.event.special[handleobj.origtype] || (intermediate value)).handle || handleobj.handler).apply not function below javascript game engine handles key listeners... addkeylistener: function (keyandlistener) { this.keylisteners.push(keyandlistener); }, // gi...
Read more

javascript - Web audio api 5.1 surround example not working in firefox -

i've been playing lott lately web audio api on both firefox , chrome. few days ago started experiment surround set. when trying surround audio in web audio api came notice example works fine in chrome v59 not in firefox v54. // create web audio api context var audioctx = new (window.audiocontext || window.webkitaudiocontext)(); audioctx.destination.channelinterpretation = 'discrete'; audioctx.destination.channelcountmode = 'explicit'; audioctx.destination.channelcount = 6; var oscillators = []; var merger = audioctx.createchannelmerger(6); console.log(audioctx.destination, merger); //merger.channelinterpretation = 'discrete'; //merger.channelcountmode = 'explicit'; //merger.channelcount = 6; var addoscilator = function(channel, frequency) { var oscillator = audioctx.createoscillator(); oscillator.frequency.value = frequency; // value in hertz oscillator.connect(merger,0,channel); oscillator.start(); oscillators.push(o...
Read more