Page 1 of 2
Column must appear in the GROUP BY
Posted: Mon Aug 26, 2013 5:48 pm
by amine
Hi,
I have been using Vertica for the past couple of months, and I am running into an issue that only occurs from the app, but not from the shell (I am suspecting something with the driver maybe?).
I am using vertica jdk5-6.1.2
java.lang.RuntimeException: Exception while executing statement : [Vertica][VJDBC](2640) ERROR: Column "name" must appear in the GROUP BY clause or be used in an aggregate function
errorCode: 2640, sqlState: 42803
Select
SUBSTRING(name,1,20) as g0,
subject as g1,
count(*) as c0,
avg(score) as c1,
From
my_table
Group By
SUBSTRING(name,1,20),
subject
Having
(count(*) > 5);
Re: Column must appear in the GROUP BY
Posted: Mon Aug 26, 2013 6:08 pm
by JimKnicely
Maybe try using the column aliases?
- Select
SUBSTRING(name,1,20) as g0,
subject as g1,
count(*) as c0,
avg(score) as c1
From
my_table
Group By
g0,
g1
Having
(count(*) > 5);
Or you can list the column positions in the GROUP BY clause:
- Select
SUBSTRING(name,1,20) as g0,
subject as g1,
count(*) as c0,
avg(score) as c1,
From
my_table
Group By
1,
2
Having
(count(*) > 5);
Re: Column must appear in the GROUP BY
Posted: Mon Aug 26, 2013 6:23 pm
by amine
It didn't help.
But I did some research, and I found that in postgres, the doc says:
When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions, since there would be more than one possible value to return for an ungrouped column.
And I am wondering if the the Vertica driver is following the same thought.
Again, this query works fine from the shell, it's just failing through the driver
Link:
http://www.postgresql.org/docs/current/ ... QL-GROUPBY
Re: Column must appear in the GROUP BY
Posted: Mon Aug 26, 2013 9:37 pm
by JimKnicely
Hmm.
What about this?
SELECT g0, g1, count(*) as c0, avg(score) as c1
FROM
(Select
SUBSTRING(name,1,20) as g0,
subject as g1,
score
From my_table
) foo
Group By
g0, g1
Having
(count(*) > 5);
Re: Column must appear in the GROUP BY
Posted: Tue Aug 27, 2013 4:10 pm
by amine
I unfortunately can't change the query structure (due to internal dependencies/reasons).
But I would like to know if this confirms that there's an issue with the driver or not. I can't see any reason why this would work from the shell, but not from the provided driver...
And if so, is there an ETA to fix this?
Re: Column must appear in the GROUP BY
Posted: Tue Aug 27, 2013 4:27 pm
by amine
Also, trying the same query without the substring section works fine both sides.
Re: Column must appear in the GROUP BY
Posted: Tue Aug 27, 2013 4:49 pm
by JimKnicely
Hmm. I created a table name my_table with the following attributes and data:
Code: Select all
dbadmin=> \d my_table;
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+----------+---------+--------------+------+---------+----------+-------------+-------------
public | my_table | name | varchar(100) | 100 | | f | f |
public | my_table | subject | varchar(100) | 100 | | f | f |
public | my_table | score | int | 8 | | f | f |
(3 rows)
dbadmin=> SELECT * FROM my_table;
name | subject | score
------+---------+-------
jim | english | 96
jim | english | 97
jim | english | 100
jim | english | 100
jim | english | 100
jim | english | 95
(6 rows)
Next, I ran your query in SQuirreLSQL which is using the Vertica JDBC Driver. It ran without error:
- SQuirrelQuery.png (74.28 KiB) Viewed 25939 times
Maybe it's not a JDBC Driver issue? What version of Vertica are you running?