[ https://issues.apache.org/jira/browse/SPARK-35504?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17350555#comment-17350555 ]
Nikolay Sokolov commented on SPARK-35504: ----------------------------------------- Added execution plans for two SparkSQL queries: * [^SPARK-35504_first_query_plan.log] * [^SPARK-35504_second_query_plan.log] > count distinct asterisk > ------------------------ > > Key: SPARK-35504 > URL: https://issues.apache.org/jira/browse/SPARK-35504 > Project: Spark > Issue Type: Bug > Components: SQL > Affects Versions: 3.0.0 > Environment: {code:java} > uname -a > Linux 5.4.0-1038-aws #40~18.04.1-Ubuntu SMP Sat Feb 6 01:56:56 UTC 2021 > x86_64 x86_64 x86_64 GNU/Linux > {code} > > {code:java} > lsb_release -a > No LSB modules are available. > Distributor ID: Ubuntu > Description: Ubuntu 18.04.4 LTS > Release: 18.04 > Codename: bionic > {code} > > {code:java} > /opt/spark/bin/spark-submit --version > Welcome to > ____ __ > / __/__ ___ _____/ /__ > _\ \/ _ \/ _ `/ __/ '_/ > /___/ .__/\_,_/_/ /_/\_\ version 3.0.0 > /_/ > Using Scala version 2.12.10, OpenJDK 64-Bit Server VM, 1.8.0_292 > Branch HEAD > Compiled by user ubuntu on 2020-06-06T13:05:28Z > Revision 3fdfce3120f307147244e5eaf46d61419a723d50 > Url https://gitbox.apache.org/repos/asf/spark.git > Type --help for more information. > {code} > {code:java} > lscpu > Architecture: x86_64 > CPU op-mode(s): 32-bit, 64-bit > Byte Order: Little Endian > CPU(s): 4 > On-line CPU(s) list: 0-3 > Thread(s) per core: 2 > Core(s) per socket: 2 > Socket(s): 1 > NUMA node(s): 1 > Vendor ID: GenuineIntel > CPU family: 6 > Model: 85 > Model name: Intel(R) Xeon(R) Platinum 8275CL CPU @ 3.00GHz > Stepping: 7 > CPU MHz: 3602.011 > BogoMIPS: 6000.01 > Hypervisor vendor: KVM > Virtualization type: full > L1d cache: 32K > L1i cache: 32K > L2 cache: 1024K > L3 cache: 36608K > NUMA node0 CPU(s): 0-3 > Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca > cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm > constant_tsc rep_good nopl xtopology nonstop_tsc cpuid aperfmperf > tsc_known_freq pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe > popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm > 3dnowprefetch invpcid_single pti fsgsbase tsc_adjust bmi1 avx2 smep bmi2 erms > invpcid mpx avx512f avx512dq rdseed adx smap clflushopt clwb avx512cd > avx512bw avx512vl xsaveopt xsavec xgetbv1 xsaves ida arat pku ospke > {code} > > Reporter: Nikolay Sokolov > Priority: Minor > Attachments: SPARK-35504_first_query_plan.log, > SPARK-35504_second_query_plan.log > > > Hi everyone, > I hope you're well! > > Today I came across a very interesting case when the result of the execution > of the algorithm for counting unique rows differs depending on the form > (count(distinct *) vs count( * ) from derived table) of the Spark SQL queries. > I still can't figure out on my own if this is a bug or a feature and I would > like to share what I found. > > I run Spark SQL queries through the Thrift (and not only) connecting to the > Spark cluster. I use the DBeaver app to execute Spark SQL queries. > > So, I have two identical Spark SQL queries from an algorithmic point of view > that return different results. > > The first query: > {code:sql} > select count(distinct *) unique_amt from storage_datamart.olympiads > ; -- Rows: 13437678 > {code} > > The second query: > {code:sql} > select count(*) from (select distinct * from storage_datamart.olympiads) > ; -- Rows: 36901430 > {code} > > The result of the two queries is different. (But it must be the same, right!?) > {code:sql} > select 'The first query' description, count(distinct *) unique_amt from > storage_datamart.olympiads > union all > select 'The second query', count(*) from (select distinct * from > storage_datamart.olympiads) > ; > {code} > > The result of the above query is the following: > {code:java} > The first query 13437678 > The second query 36901430 > {code} > > I can easily calculate the unique number of rows in the table: > {code:sql} > select count(*) from ( > select student_id, olympiad_id, tour, grade > from storage_datamart.olympiads > group by student_id, olympiad_id, tour, grade > having count(*) = 1 > ) > ; -- Rows: 36901365 > {code} > > The table DDL is the following: > {code:sql} > CREATE TABLE `storage_datamart`.`olympiads` ( > `ptn_date` DATE, > `student_id` BIGINT, > `olympiad_id` STRING, > `grade` BIGINT, > `grade_type` STRING, > `tour` STRING, > `created_at` TIMESTAMP, > `created_at_local` TIMESTAMP, > `olympiad_num` BIGINT, > `olympiad_name` STRING, > `subject` STRING, > `started_at` TIMESTAMP, > `ended_at` TIMESTAMP, > `region_id` BIGINT, > `region_name` STRING, > `municipality_name` STRING, > `school_id` BIGINT, > `school_name` STRING, > `school_status` BOOLEAN, > `oly_n_common` INT, > `num_day` INT, > `award_type` STRING, > `new_student_legacy` INT, > `segment` STRING, > `total_start` TIMESTAMP, > `total_end` TIMESTAMP, > `year_learn` STRING, > `parent_id` BIGINT, > `teacher_id` BIGINT, > `parallel` BIGINT, > `olympiad_type` STRING) > USING parquet > LOCATION 's3a://uchiru-bi-dwh/storage/datamart/olympiads.parquet' > ; > {code} > > Could you please tell me why in the first Spark SQL query counting the unique > number of rows using the construction `count(distinct *)` does not count > correctly and why the result of the two Spark SQL queries is different?? > Thanks in advance. > > p.s. I could not find a description of such behaviour of the function > `count(distinct *)` in the [official Spark > documentation|https://spark.apache.org/docs/latest/sql-ref-functions-builtin.html#aggregate-functions]: > {quote}count(DISTINCT expr[, expr...]) -> Returns the number of rows for > which the supplied expression(s) are unique and non-null. > {quote} > -- This message was sent by Atlassian Jira (v8.3.4#803005) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org