[ 
https://issues.apache.org/jira/browse/SPARK-35504?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Nikolay Sokolov resolved SPARK-35504.
-------------------------------------
    Resolution: Fixed

I could not fully comprehend what was written in the documentation.

Helped to figure it out.

> 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

Reply via email to