{"action":"opened","issue":{"url":"https://api.github.com/repos/yandex/ClickHouse/issues/3233","repository_url":"https://api.github.com/repos/yandex/ClickHouse","labels_url":"https://api.github.com/repos/yandex/ClickHouse/issues/3233/labels{/name}","comments_url":"https://api.github.com/repos/yandex/ClickHouse/issues/3233/comments","events_url":"https://api.github.com/repos/yandex/ClickHouse/issues/3233/events","html_url":"https://github.com/yandex/ClickHouse/issues/3233","id":364383921,"node_id":"MDU6SXNzdWUzNjQzODM5MjE=","number":3233,"title":"SummingMergeTree data - select returns values for partition field that don't exist in partitions","user":{"login":"josepowera","id":41049275,"node_id":"MDQ6VXNlcjQxMDQ5Mjc1","avatar_url":"https://avatars1.githubusercontent.com/u/41049275?v=4","gravatar_id":"","url":"https://api.github.com/users/josepowera","html_url":"https://github.com/josepowera","followers_url":"https://api.github.com/users/josepowera/followers","following_url":"https://api.github.com/users/josepowera/following{/other_user}","gists_url":"https://api.github.com/users/josepowera/gists{/gist_id}","starred_url":"https://api.github.com/users/josepowera/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/josepowera/subscriptions","organizations_url":"https://api.github.com/users/josepowera/orgs","repos_url":"https://api.github.com/users/josepowera/repos","events_url":"https://api.github.com/users/josepowera/events{/privacy}","received_events_url":"https://api.github.com/users/josepowera/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-09-27T09:29:10Z","updated_at":"2018-09-27T09:29:10Z","closed_at":null,"author_association":"NONE","body":"Short: table is partitioned with 3 fields (dspid, tenantid, toYYYYMMDD(eventDate))\r\n\r\nWhen we insert data we have only value 1 for tenantid (2nd field of partition).\r\n\r\nIf we list partitions using system parts (select partition from system.parts where database='test5ssp' and table='sspimps_raw_sum1h' and active=1) we have all parts only with tenantid=1 (full output):\r\n(0, 1, 20180926)\r\n(0, 1, 20180926)\r\n(0, 1, 20180927)\r\n(2, 1, 20180926)\r\n(2, 1, 20180926)\r\n(2, 1, 20180927)\r\n(2, 1, 20180927)\r\n(2, 1, 20180927)\r\n(2, 1, 20180927)\r\n(3, 1, 20180926)\r\n(3, 1, 20180926)\r\n(3, 1, 20180927)\r\n\r\nHowever if we check \r\nselect distinct tenantid from test5ssp.sspimps_raw_sum1h  \r\nwe get 226 distinct numbers. (output is shortened for clarity)\r\n`80\r\n234\r\n2\r\n187\r\n235\r\n215\r\n76\r\n233\r\n......\r\n......\r\n1\r\n......\r\n107\r\n146\r\n181\r\n143\r\n123\r\n`\r\n\r\n```\r\nselect uniq(tenantid) from test5ssp.sspimps_raw_sum1h\r\n228\r\n\r\n```\r\n\r\nWhat are we doing wrong, that server returns values, that are never inserted and not even in partitions list?\r\n\r\n\r\n\r\n\r\n\r\nDDL:\r\n\r\n`CREATE TABLE test5ssp.sspimps_raw_sum1h ( tenantid Int64,  dspid Int64,  dspseatid Int64,  adprocesstype Int64,  publisherid String,  siteid String,  adzoneid String,  adomain String,  campaignid String,  creativeid String,  dealid String,  geocountry3 String,  region String,  city String,  eventtype Int64,  eventsubtype Int64,  currency String,  dlvtaken Int64,  reason Int64,  screenwidth Int64,  screenheight Int64,  measurablevisibility Int64,  eventDate Date,  eventTimestamp DateTime,  mediacost Int64,  floorprice Int64,  bidprice Int64,  totalbidscount Int64,  quantity Int64,  datacost Int64,  sspfee Int64,  publisherrevenue Int64,  segmentslist String,  impressions Int64,  vimpressions Int64,  clicks Int64) ENGINE = SummingMergeTree() PARTITION BY (dspid, tenantid, toYYYYMMDD(eventDate)) ORDER BY (publisherid, siteid, dspseatid, campaignid, creativeid, currency, reason, dealid, dlvtaken, adomain, adzoneid, geocountry3, eventtype, segmentslist, eventsubtype, screenwidth, screenheight, adprocesstype, city, region, eventDate) SETTINGS index_granularity = 8192`\r\n\r\nselect partition,name,active,rows,partition_id,database,table,engine,path from system.parts where database='test5ssp' and table='sspimps_raw_sum1h' and active=1  (full output below)\r\n\r\nuporabnik@ubuntu:~$ echo \"select partition,name,active,rows,partition_id,database,table,engine,path from system.parts where database='test5ssp' and table='sspimps_raw_sum1h' and active=1 FORMAT PrettyCompact\" | curl 'http://chserver.local:8123/' --data-binary @-\r\n┌─partition────────┬─name───────────────────┬─active─┬─rows─┬─partition_id─┬─database─┬─table─────────────┬─engine───────────┬─path────────────────────────────────────────────────────────────────────────┐\r\n│ (0, 1, 20180926) │ 0-1-20180926_2_41_3    │      1 │ 1229 │ 0-1-20180926 │ test5ssp │ sspimps_raw_sum1h │ SummingMergeTree │ /var/lib/clickhouse/data/test5ssp/sspimps_raw_sum1h/0-1-20180926_2_41_3/    │\r\n│ (0, 1, 20180926) │ 0-1-20180926_45_45_0   │      1 │ 1183 │ 0-1-20180926 │ test5ssp │ sspimps_raw_sum1h │ SummingMergeTree │ /var/lib/clickhouse/data/test5ssp/sspimps_raw_sum1h/0-1-20180926_45_45_0/   │\r\n│ (0, 1, 20180927) │ 0-1-20180927_46_734_57 │      1 │ 1412 │ 0-1-20180927 │ test5ssp │ sspimps_raw_sum1h │ SummingMergeTree │ /var/lib/clickhouse/data/test5ssp/sspimps_raw_sum1h/0-1-20180927_46_734_57/ │\r\n│ (0, 1, 20180927) │ 0-1-20180927_737_737_0 │      1 │ 1198 │ 0-1-20180927 │ test5ssp │ sspimps_raw_sum1h │ SummingMergeTree │ /var/lib/clickhouse/data/test5ssp/sspimps_raw_sum1h/0-1-20180927_737_737_0/ │\r\n│ (2, 1, 20180926) │ 2-1-20180926_1_40_3    │      1 │ 4233 │ 2-1-20180926 │ test5ssp │ sspimps_raw_sum1h │ SummingMergeTree │ /var/lib/clickhouse/data/test5ssp/sspimps_raw_sum1h/2-1-20180926_1_40_3/    │\r\n│ (2, 1, 20180926) │ 2-1-20180926_43_43_0   │      1 │ 3017 │ 2-1-20180926 │ test5ssp │ sspimps_raw_sum1h │ SummingMergeTree │ /var/lib/clickhouse/data/test5ssp/sspimps_raw_sum1h/2-1-20180926_43_43_0/   │\r\n│ (2, 1, 20180927) │ 2-1-20180927_44_736_57 │      1 │ 4639 │ 2-1-20180927 │ test5ssp │ sspimps_raw_sum1h │ SummingMergeTree │ /var/lib/clickhouse/data/test5ssp/sspimps_raw_sum1h/2-1-20180927_44_736_57/ │\r\n│ (3, 1, 20180926) │ 3-1-20180926_3_42_3    │      1 │   20 │ 3-1-20180926 │ test5ssp │ sspimps_raw_sum1h │ SummingMergeTree │ /var/lib/clickhouse/data/test5ssp/sspimps_raw_sum1h/3-1-20180926_3_42_3/    │\r\n│ (3, 1, 20180926) │ 3-1-20180926_47_47_0   │      1 │   17 │ 3-1-20180926 │ test5ssp │ sspimps_raw_sum1h │ SummingMergeTree │ /var/lib/clickhouse/data/test5ssp/sspimps_raw_sum1h/3-1-20180926_47_47_0/   │\r\n│ (3, 1, 20180927) │ 3-1-20180927_48_735_57 │      1 │   24 │ 3-1-20180927 │ test5ssp │ sspimps_raw_sum1h │ SummingMergeTree │ /var/lib/clickhouse/data/test5ssp/sspimps_raw_sum1h/3-1-20180927_48_735_57/ │\r\n│ (3, 1, 20180927) │ 3-1-20180927_738_738_0 │      1 │   16 │ 3-1-20180927 │ test5ssp │ sspimps_raw_sum1h │ SummingMergeTree │ /var/lib/clickhouse/data/test5ssp/sspimps_raw_sum1h/3-1-20180927_738_738_0/ │\r\n└──────────────────┴────────────────────────┴────────┴──────┴──────────────┴──────────┴───────────────────┴──────────────────┴─────────────────────────────────────────────────────────────────────────────┘select distinct tenantid from test5ssp.sspimps_raw_sum1h (full output)\r\n`79\r\n230\r\n2\r\n184\r\n231\r\n212\r\n75\r\n229\r\n6\r\n182\r\n204\r\n43\r\n228\r\n1\r\n168\r\n205\r\n30\r\n171\r\n4\r\n14\r\n9\r\n11\r\n12\r\n13\r\n3\r\n10\r\n7\r\n8\r\n5\r\n221\r\n186\r\n200\r\n128\r\n225\r\n209\r\n224\r\n202\r\n222\r\n226\r\n211\r\n219\r\n170\r\n223\r\n213\r\n214\r\n175\r\n220\r\n227\r\n194\r\n217\r\n207\r\n159\r\n215\r\n198\r\n154\r\n201\r\n203\r\n193\r\n101\r\n178\r\n216\r\n146\r\n206\r\n176\r\n210\r\n172\r\n173\r\n183\r\n115\r\n138\r\n189\r\n218\r\n147\r\n169\r\n188\r\n124\r\n152\r\n197\r\n158\r\n190\r\n130\r\n196\r\n102\r\n174\r\n162\r\n208\r\n137\r\n192\r\n131\r\n143\r\n157\r\n118\r\n145\r\n163\r\n165\r\n134\r\n153\r\n195\r\n181\r\n125\r\n199\r\n180\r\n109\r\n136\r\n191\r\n123\r\n177\r\n150\r\n22\r\n160\r\n151\r\n144\r\n185\r\n129\r\n187\r\n111\r\n58\r\n28\r\n23\r\n46\r\n55\r\n57\r\n48\r\n89\r\n47\r\n34\r\n87\r\n82\r\n69\r\n15\r\n19\r\n83\r\n90\r\n86\r\n122\r\n119\r\n97\r\n59\r\n16\r\n232\r\n40\r\n92\r\n52\r\n84\r\n36\r\n78\r\n81\r\n60\r\n35\r\n106\r\n112\r\n100\r\n50\r\n31\r\n29\r\n27\r\n94\r\n98\r\n88\r\n135\r\n91\r\n62\r\n25\r\n65\r\n70\r\n33\r\n72\r\n53\r\n113\r\n105\r\n41\r\n95\r\n99\r\n61\r\n132\r\n133\r\n66\r\n39\r\n44\r\n51\r\n93\r\n17\r\n42\r\n45\r\n37\r\n49\r\n18\r\n71\r\n67\r\n80\r\n38\r\n26\r\n120\r\n110\r\n126\r\n108\r\n54\r\n68\r\n64\r\n20\r\n85\r\n121\r\n127\r\n76\r\n116\r\n32\r\n74\r\n24\r\n117\r\n21\r\n56\r\n63\r\n148\r\n179\r\n139\r\n104\r\n73\r\n96\r\n140\r\n142\r\n77\r\n114\r\n103\r\n107\r\n149\r\n141\r\n`\r\n\r\n\r\nClickhouse version:\r\nselect version()\r\n18.12.17\r\n\r\n"}}
{"actor":{"display_login":"josepowera"}}