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);
Column must appear in the GROUP BY
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Column must appear in the GROUP BY
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);
- 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);
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Column must appear in the GROUP BY
It didn't help.
But I did some research, and I found that in postgres, the doc says:
Again, this query works fine from the shell, it's just failing through the driver
Link:
http://www.postgresql.org/docs/current/ ... QL-GROUPBY
But I did some research, and I found that in postgres, the doc says:
And I am wondering if the the Vertica driver is following the same thought.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.
Again, this query works fine from the shell, it's just failing through the driver
Link:
http://www.postgresql.org/docs/current/ ... QL-GROUPBY
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Column must appear in the GROUP BY
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);
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);
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Column must appear in the GROUP BY
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?
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
Also, trying the same query without the substring section works fine both sides.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Column must appear in the GROUP BY
Hmm. I created a table name my_table with the following attributes and data:
Next, I ran your query in SQuirreLSQL which is using the Vertica JDBC Driver. It ran without error:
Maybe it's not a JDBC Driver issue? What version of Vertica are you running?
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)
Maybe it's not a JDBC Driver issue? What version of Vertica are you running?
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.