[BUG] When combining WHERE and GROUP BY results are incorrect
lyndonbauto opened this issue · 0 comments
lyndonbauto commented
Describe the bug
It seems like our COUNT operation is not being applied properly when we have filters and groups involved. It seems like results are being filtered out incorrectly and the count is being applied before the filter is complete. The two queries with results attached show the issue.
SELECT country, count(city) FROM airport WHERE (country = 'PH' OR country = 'JP') and elev > 100 group by country;| country | COUNT(city) |
|---|---|
| JP | 65 |
SELECT * FROM airport WHERE (country = 'PH' OR country = 'JP') and elev > 100 order by country;| country | route_OUT_ID | code | longest | airport_ID | city | route_IN_ID | lon | type | contains_IN_ID | elev | icao | region | runways | lat | desc |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| JP | [50957, 50958, 50959, 50960, 50961, 50962] | IBR | 8860 | 2404 | Omitama | [40006, 24317, 12151, 18632, 24447, 32455] | 140.414993286 | airport | [60308, 56806] | 105 | RJAH | JP-08 | 1 | 36.181098938 | Hyakuri Airport |
| PH | [53291] | JOL | 4144 | 3047 | Jolo | [51037] | 121.011001586914 | airport | [57449, 60951] | 118 | RPMJ | PH-SLU | 1 | 6.05366992950439 | Jolo Airport |
| JP | [40119, 40120] | MSJ | 10000 | 774 | Misawa | [16958, 17035] | 141.367996216 | airport | [58678, 55176] | 119 | RJSM | JP-02 | 1 | 40.7032012939 | Misawa Air Base |
| PH | [51046, 51047] | VRC | 5118 | 2439 | Virac | [39202, 16357] | 124.206001281738 | airport | [60343, 56841] | 121 | RPUV | PH-CAT | 1 | 13.5763998031616 | Virac Airport |
| JP | [40124] | OIM | 5905 | 778 | Izu Oshima | [17039] | 139.36000061 | airport | [58682, 55180] | 130 | RJTO | JP-13 | 1 | 34.7820014954 | Oshima Airport |
| JP | [40020, 40021, 40022] | MMB | 8202 | 751 | Ōzora | [24438, 32444, 17012] | 144.164001465 | airport | [58655, 55153] | 135 | RJCM | JP-01 | 1 | 43.8805999756 | Memanbetsu Airport |
| JP | [10300, 10301, 10302, 10303, 10304, 10305, 10306, 10307, 10308, 10309, 10310, 10311, 10312, 10313, 10314, 10315, 10316, 10317, 10318, 10319, 10431, 10320, 10321, 10322, 10323, 10324, 10325, 10326, 10327, 10328, 10329, 10330, 10331, 10332, 10333, 10334, 10335, 10336, 10337, 10338, 10339, 10340, 10341, 10342, 10343, 10432, 10344, 10345, 10346, 10347, 10348, 10349, 10350, 10351, 10352, 10353, 10354, 10355, 10433, 10434, 10356, 10357, 10358, 10359, 10360, 10361, 10362, 10363, 10364, 10365, 10366, 10367, 10368, 10369, 10370, 10371, 10435, 10436, 10437, 10438, 10440, 10372, 10373, 10374, 10375, 10376, 10377, 10384, 10385, 10378, 10386, 10387, 10388, 10389, 10390, 10391, 10392, 10393, 10394, 10395, 10396, 10397, 10398, 10399, 10400, 10401, 10402, 10403, 10404, 10405, 10406, 10407, 10408, 10409, 10410, 10411, 10439, 10379, 10380, 10381, 10412, 10413, 10414, 10415, 10416, 10417, 10418, 10419, 10420, 10421, 10422, 10423, 10424, 10425, 10426, 10427, 10428, 10429, 10430, 10382, 10383] | NRT | 13123 | 54 | Tokyo | [7455, 7540, 10715, 17060, 20298, 23468, 36258, 37858, 42674, 6022, 9150, 10803, 12394, 14026, 23645, 41159, 6146, 7657, 7746, 12508, 20495, 20543, 22060, 25319, 38111, 11043, 20581, 20631, 25376, 38168, 38218, 54232, 6308, 7880, 17497, 23916, 28730, 31925, 38287, 8002, 9600, 12769, 39969, 40008, 40034, 6471, 19282, 38505, 38518, 38524, 38534, 38541, 40047, 40054, 40062, 40075, 40097, 40138, 43300, 5466, 7106, 8237, 8715, 11352, 11896, 13474, 16236, 19852, 21031, 27888, 30547, 37532, 38549, 38555, 38566, 38581, 38622, 39066, 39140, 42253, 43349, 43376, 47049, 5141, 9896, 11993, 15205, 16290, 16767, 19485, 21127, 24279, 24332, 28030, 32303, 38671, 38708, 38720, 38727, 43521, 5692, 8400, 8859, 10447, 11561, 11643, 13690, 14809, 16376, 16939, 18491, 23281, 24404, 25999, 29704, 32361, 32409, 37188, 37234, 37655, 37670, 37742, 38769, 39269, 39295, 39308, 39341, 42519, 9038, 10553, 12204, 13274, 16530, 36174, 38933, 39411, 10182, 37369, 38969, 38991, 39046] | 140.386001587 | airport | [54456, 57958] | 141 | RJAA | JP-12 | 2 | 35.7647018433 | Tokyo Narita |
| PH | [51013, 51014] | BXU | 6450 | 2425 | Butuan City | [16345, 39024] | 125.4788 | airport | [60329, 56827] | 141 | RPME | PH-AGN | 1 | 8.9515 | Bancasi Airport |
| PH | [51042] | WNP | 4599 | 2436 | Naga | [16354] | 123.269996643066 | airport | [60340, 56838] | 142 | RPUN | PH-CAS | 1 | 13.5848999023438 | Naga Airport |
| PH | [51091, 51092, 51093] | USU | 3300 | 2451 | Coron | [39212, 16369, 39043] | 120.099998474 | airport | [60355, 56853] | 148 | RPVV | PH-PLW | 1 | 12.1215000153 | Francisco B. Reyes Airport |
| JP | [40146, 40147, 40150, 40148, 40149] | MMY | 6560 | 781 | Miyako City | [54235, 40145, 24314, 38701, 17042] | 125.294998169 | airport | [58685, 55183] | 150 | ROMY | JP-47 | 1 | 24.7828006744 | Miyako Airport |
| JP | [40008, 40009, 40010, 40015, 40011, 40012, 40013, 40014] | HKD | 9842 | 749 | Hakodate | [23990, 10423, 24386, 24436, 32443, 16949, 17010, 42566] | 140.822006226 | airport | [58653, 55151] | 151 | RJCH | JP-01 | 1 | 41.7700004578 | Hakodate Airport |
| JP | [40105] | IWJ | 6562 | 770 | Masuda | [17031] | 131.789993286 | airport | [58674, 55172] | 184 | RJOW | JP-32 | 1 | 34.676399231 | Iwami Airport |
| PH | [51011, 51012] | CBO | 6234 | 2424 | Cotabato City | [16344, 39023] | 124.209999084473 | airport | [60328, 56826] | 189 | RPMC | PH-MAG | 1 | 7.1652398109436 | Awang Airport |
| PH | [51048, 51049] | CYZ | 6890 | 2440 | Cauayan City | [39203, 16358] | 121.752998352 | airport | [60344, 56842] | 200 | RPUY | PH-ISA | 1 | 16.9298992157 | Cauayan Airport |
| JP | [50964, 50965] | TSJ | 6234 | 2406 | Tsushima | [40073, 24318] | 129.330993652 | airport | [60310, 56808] | 213 | RJDT | JP-42 | 1 | 34.2849006653 | Tsushima Airport |
| JP | [40023, 40024] | SHB | 6560 | 752 | Nakashibetsu | [24439, 17013] | 144.960006714 | airport | [58656, 55154] | 234 | RJCN | JP-01 | 1 | 43.5774993896 | Nakashibetsu Airport |
| JP | [50966, 50967] | FUJ | 6561 | 2407 | Goto | [40074, 24319] | 128.832992553711 | airport | [60311, 56809] | 273 | RJFE | JP-42 | 1 | 32.6663017272949 | Fukue Airport |
| PH | [51043, 51044] | BSO | 4101 | 2437 | Basco | [39201, 16355] | 121.980003357 | airport | [60341, 56839] | 291 | RPUO | PH-CAG | 1 | 20.4512996674 | Basco Airport |
| JP | [40121] | ONJ | 6562 | 775 | Odate | [17036] | 140.371002197 | airport | [58679, 55177] | 292 | RJSR | JP-05 | 1 | 40.1918983459 | Odate Noshiro Airport |
| JP | [50991, 50986, 50987, 50988, 50989, 50990] | HNA | 8202 | 2411 | Morioka | [50973, 24024, 24321, 12153, 16960, 24450] | 141.134994506836 | airport | [60315, 56813] | 297 | RJSI | JP-03 | 1 | 39.4286003112793 | Hanamaki Airport |
| JP | [39998] | SHM | 6560 | 746 | Shirahama | [17007] | 135.363998413 | airport | [58650, 55148] | 298 | RJBD | JP-30 | 2 | 33.6622009277 | Nanki Shirahama Airport |
| JP | [40123] | HAC | 6563 | 777 | Hachijojima | [17038] | 139.785995483 | airport | [58681, 55179] | 303 | RJTH | JP-13 | 1 | 33.1150016785 | Hachijojima Airport |
| JP | [40114, 40115, 40116, 40117, 40118] | AXT | 8200 | 773 | Akita | [24444, 16957, 17034, 18609, 32449] | 140.218994140625 | airport | [58677, 55175] | 313 | RJSK | JP-05 | 1 | 39.6156005859375 | Akita Airport |
| JP | [40016, 40017, 40018, 40019] | KUH | 8202 | 750 | Kushiro | [23991, 24387, 24437, 17011] | 144.192993164 | airport | [58654, 55152] | 327 | RJCK | JP-01 | 1 | 43.0410003662 | Kushiro Airport |
| JP | [40113, 40111, 40112] | GAJ | 6560 | 772 | Yamagata | [50972, 16956, 17033] | 140.371002197 | airport | [58676, 55174] | 353 | RJSC | JP-06 | 1 | 38.4118995667 | Yamagata Airport |
| JP | [50974, 50975, 50983, 50976, 50977, 50978, 50980, 50981, 50982, 50979] | FSZ | 8202 | 2409 | Makinohara / Shimada | [42767, 24023, 38517, 40096, 24320, 38706, 12152, 18633, 24448] | 138.18775177 | airport | [60313, 56811] | 433 | RJNS | JP-22 | 1 | 34.7960434679 | Mt. Fuji Shizuoka Airport |
| PH | [39198, 39199, 39200, 39201, 39202, 39203, 39204, 39205, 39206, 39207, 39208, 39209, 39210, 39182, 39183, 39184, 39185, 39211, 39212, 39186, 39187, 39188, 39213, 39189, 39190, 39197, 39191, 39192, 39193, 39194, 39195, 39196] | CRK | 10499 | 706 | Angeles City | [10657, 17180, 10942, 54114, 39762, 51021, 51039, 51041, 51044, 39858, 51047, 51049, 51052, 51055, 51060, 51065, 51068, 51071, 51079, 51088, 51093, 23983, 43319, 39102, 11456, 38657, 39226, 12084, 24382, 16478, 18590, 39014] | 120.559997559 | airport | [55108, 58610] | 484 | RPLC | PH-PAM | 2 | 15.1859998703 | Clark International Airport |
| JP | [40007] | OBO | 8202 | 748 | Obihiro | [17009] | 143.216995239 | airport | [58652, 55150] | 505 | RJCB | JP-01 | 1 | 42.7332992554 | Tokachi-Obihiro Airport |
| PH | [51029, 51030, 51031] | GES | 10587 | 2431 | General Santos | [39229, 16350, 39030] | 125.096000671 | airport | [60335, 56833] | 505 | RPMR | PH-SCO | 1 | 6.05800008774 | General Santos International Airport |
| PH | [51024, 51019, 51020, 51021, 51022, 51023] | CGY | 8050 | 2428 | Cagayan De Oro City | [39768, 51057, 39198, 39228, 16347, 39027] | 124.611000061 | airport | [60332, 56830] | 601 | RPML | PH-MSR | 1 | 8.41561985016 | Cagayan De Oro Airport |
| JP | [38549, 38550, 38551, 38552, 38553, 38554] | TAK | 8200 | 685 | Takamatsu | [23969, 10401, 38691, 12068, 17004, 18573] | 134.01600647 | airport | [55087, 58589] | 607 | RJOT | JP-37 | 1 | 34.2141990662 | Takamatsu Airport |
| JP | [40061, 40054, 40055, 40056, 40057, 40058, 40060, 40059] | KMJ | 9840 | 760 | Kumamoto | [50969, 10426, 11465, 38697, 16951, 17021, 18604, 32447] | 130.854995727539 | airport | [58664, 55162] | 642 | RJFT | JP-43 | 1 | 32.8372993469238 | Kumamoto Airport |
| JP | [40110, 40106, 40107, 40108, 40109] | AOJ | 9846 | 771 | Aomori | [50971, 24443, 16955, 17032, 18608] | 140.690994262695 | airport | [58675, 55173] | 664 | RJSA | JP-02 | 1 | 40.7346992492676 | Aomori Airport |
| JP | [40085] | NTQ | 6562 | 764 | Wajima | [17025] | 136.962005615 | airport | [58668, 55166] | 718 | RJNW | JP-17 | 1 | 37.2930984497 | Noto Airport |
| JP | [40030, 40031, 40032, 40033] | AKJ | 8200 | 756 | Asahikawa / Hokkaidō | [23992, 32445, 17017, 18600] | 142.447006225586 | airport | [58660, 55158] | 721 | RJEC | JP-01 | 1 | 43.6707992553711 | Asahikawa Airport |
| JP | [40086, 40087, 40088, 40089, 40090, 40091, 40092, 40093] | OKJ | 9843 | 765 | Okayama City | [23483, 23996, 39106, 38699, 12094, 24442, 17026, 18607] | 133.854995728 | airport | [58669, 55167] | 806 | RJOB | JP-33 | 1 | 34.7569007874 | Okayama Airport |
| JP | [38517, 38505, 38506, 38507, 38508, 38509, 38510, 38511, 38512, 38516, 38514, 38513, 38515] | KOJ | 9840 | 680 | Kagoshima | [50980, 23967, 40001, 43312, 11438, 10396, 38687, 12065, 16943, 24370, 32431, 16999, 18568] | 130.718994140625 | airport | [55082, 58584] | 906 | RJFK | JP-46 | 1 | 31.8034000396729 | Kagoshima Airport |
| JP | [37655, 37656, 37657, 37658, 37659, 37660, 37661, 37662, 37663, 37664] | HIJ | 9842 | 666 | Hiroshima | [23954, 38576, 10388, 38686, 12053, 24423, 10629, 16998, 18558, 39356] | 132.919006348 | airport | [55068, 58570] | 1088 | RJOA | JP-34 | 1 | 34.4361000061 | Hiroshima Airport |
| JP | [50984, 50985] | FKS | 8202 | 2410 | Sukagawa | [16959, 24449] | 140.430999755859 | airport | [60314, 56812] | 1221 | RJSF | JP-07 | 1 | 37.2274017333984 | Fukushima Airport |
| JP | [50955, 50956] | MMJ | 6560 | 2403 | Matsumoto | [24316, 24446] | 137.923004150391 | airport | [60307, 56805] | 2182 | RJAF | JP-20 | 1 | 36.1668014526367 | Matsumoto Airport |