Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

AVG + GROUP BY returns NULL for some records that should have results #6178

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

AVG + GROUP BY returns NULL for some records that should have results #6178

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2016-12-25 23:12:12 +0100
From: Anthony Damico <>
To: SQL devs <>
Version: 11.25.3 (Dec2016)
CC: ajdamico, @hannesmuehleisen

Last updated: 2017-01-26 14:56:43 +0100

Comment 24856

Date: 2016-12-25 23:12:12 +0100
From: Anthony Damico <>

User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:50.0) Gecko/20100101 Firefox/50.0
Build Identifier:

tested on dec2016 release

Reproducible: Always

Steps to Reproduce:

you can download the two cols csv file here: http://filebin.ca/36dXIRbOlBmY/twocols.csv

it will load directly into R without the external download

four sql commands to recreate so long as you have "two cols.csv" loaded at the correct filepath

CREATE TABLE x (tailnum STRING, arr_delay INTEGER)
COPY OFFSET 2 INTO x FROM 'c:\\Users\\anthonyd\\Desktop\\two cols.csv' USING DELIMITERS ',','\n','"' NULL as ''
SELECT tailnum , AVG( arr_delay ) FROM x WHERE tailnum = 'N907MQ' GROUP BY tailnum
SELECT * FROM ( SELECT tailnum , AVG( arr_delay ) FROM x GROUP BY tailnum ) AS xxx WHERE tailnum = 'N907MQ'

minimal reproducible example R code

 load the latest versions of everything

 devtools::install_github("hannesmuehleisen/MonetDBLite",ref="Dec2016Lite-R")
 install.packages( c( 'DBI' , 'nycflights13' ) )

library(nycflights13)
library(DBI)

db <- dbConnect( MonetDBLite::MonetDBLite() )

 two columns
x <- flights[ c( 'tailnum' , 'arr_delay' ) ]

dbWriteTable( db , 'x' , x )

dbGetQuery( db , "SELECT tailnum , AVG( arr_delay ) FROM x WHERE tailnum = 'N907MQ' GROUP BY tailnum" )
   tailnum   L3
 1  N907MQ 91.5

dbGetQuery( db , "SELECT * FROM ( SELECT tailnum , AVG( arr_delay ) FROM x GROUP BY tailnum ) AS xxx WHERE tailnum = 'N907MQ'" )
   tailnum L3
 1  N907MQ NA

Actual Results:

neither of these queries should return missing values?

dbGetQuery( db , "SELECT tailnum , AVG( arr_delay ) FROM x WHERE tailnum = 'N907MQ' GROUP BY tailnum" )
   tailnum   L3
 1  N907MQ 91.5

dbGetQuery( db , "SELECT * FROM ( SELECT tailnum , AVG( arr_delay ) FROM x GROUP BY tailnum ) AS xxx WHERE tailnum = 'N907MQ'" )
   tailnum L3
 1  N907MQ NA

sorry if i'm doing something dumb. thanks!

Comment 24866

Date: 2017-01-02 18:23:52 +0100
From: MonetDB Mercurial Repository <>

Changeset 41e0ace65aed made by Hannes Muehleisen hannes@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=41e0ace65aed

Changeset description:

Smaller test for Bug #6178

Comment 24867

Date: 2017-01-03 09:31:06 +0100
From: @hannesmuehleisen

Bug only appears when running mserver with --forcemito

Comment 24868

Date: 2017-01-03 09:31:08 +0100
From: MonetDB Mercurial Repository <>

Changeset a9ebbd5b2e53 made by Hannes Muehleisen hannes@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=a9ebbd5b2e53

Changeset description:

Update for Bug #6178, issue only appears when using AVG

Comment 24869

Date: 2017-01-03 12:25:09 +0100
From: MonetDB Mercurial Repository <>

Changeset c10ab2216eec made by Hannes Muehleisen hannes@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=c10ab2216eec

Changeset description:

Fix for Bug #6178

Comment 24919

Date: 2017-01-26 14:56:43 +0100
From: @kutsurak

Fixed in version Dec2016-SP1.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant