System.Data.SQLite

Login
This project makes use of Eagle, provided by Mistachkin Systems.
Eagle: Secure Software Automation

Artifact 97b7fb2dd71c0fb77d28647bd4181e1b0e83f90a:

Ticket change [97b7fb2dd7] - New ticket [03cb660e3b] Incorrect query results return in left join when join clause references other table. by anonymous 2018-11-01 14:30:42.
D 2018-11-01T14:30:42.602
J foundin 1.0.109.0
J icomment If\sthe\sleft\sjoin\sclause,\sif\sother\stable\sis\sreferenced,\squery\sreturns\scompletely\swrong\sdata\sset.\sIts\sdifficult\sto\sexplain\sin\swords,\sso\slet\sme\swrite\sthe\ssql\swhich\sreproduces\sthe\sproblem:\r\n\r\n--\screate\stable\swith\san\sindex\r\ncreate\stable\st\s(n\sint,\sm\sint,\ss\svarchar);\r\ncreate\sindex\stx\son\st(m);\r\n\r\n--add\ssome\sdata\sto\stable\r\nWITH\sRECURSIVE\r\n\s\scnt(x)\sAS\s(\r\n\s\s\s\s\sSELECT\s1\r\n\s\s\s\s\sUNION\sALL\r\n\s\s\s\s\sSELECT\sx+1\sFROM\scnt\s\r\n\s\s\s\s\sLIMIT\s100000\r\n\s\s)\r\ninsert\sinto\st(n,\ss)\sSELECT\sx,\scast(x\sas\stext)\sFROM\scnt;\r\nupdate\st\sset\sm\s=\sn\swhere\s(n\s%\s1000)=0;\r\n\r\n--problematic\squery\r\nselect\scount(*)\r\nfrom\st\st1\r\nleft\sjoin\st\st2\son\s(t1.m\s=\st2.n)\sand\s(t1.m\sis\snot\snull);\r\n--displays\s100\swhich\sis\sincorrect.\r\n\r\n--Lets\stry\sthe\ssame\squery\safter\sdropping\sthe\sindex\r\ndrop\sindex\stx;\r\n\r\nselect\scount(*)\r\nfrom\st\st1\r\nleft\sjoin\st\st2\son\s(t1.m\s=\st2.n)\sand\s(t1.m\sis\snot\snull);\r\n--displays\s100,000\swhich\sis\scorrect.
J login anonymous
J mimetype text/x-fossil-plain
J private_contact 411f3ab164b4071cc732af82c07ab861c870b9d3
J severity Severe
J status Open
J title Incorrect\squery\sresults\sreturn\sin\sleft\sjoin\swhen\sjoin\sclause\sreferences\sother\stable
J type Code_Defect
K 03cb660e3b18105a66e449309b1901c259f38432
U anonymous
Z 40a5c70fbf527e966efdb57aedada953