[
https://issues.apache.org/jira/browse/PHOENIX-2443?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Nick Hryhoriev updated PHOENIX-2443:
------------------------------------
Description:
I have table
CREATE TABLE ec.auto_data_year(
cf.date timestamp,
cf.state varchar,
cf.county_code bigint,
cf.income_bracket varchar,
cf.sale_date bigint,
ship_date varchar NOT NULL,
cf.sales decimal(10,0),
cf.zipcode bigint,
cf.product_category varchar,
cf.city varchar,
cf.product_group varchar,
cf.satisfaction bigint,
sku varchar NOT NULL,
cf.planned_sales decimal(10,0),
cf.gender varchar,
cf.county varchar,
cf.review_text varchar,
cf.datewithouttime varchar,
cf.date_millis bigint,
cf.dateseconds bigint,
cf.datestringpattern1 varchar,
cf.datestringpattern2 varchar,
cf.datenumberpattern bigint,
cf.year bigint
CONSTRAINT pk PRIMARY KEY (ship_date, sku))
DEFAULT_COLUMN_FAMILY='cf'
;
and have three query:
1) select min(DATE) as "DATE_min", max(DATE) as "DATE_max", min(PLANNED_SALES)
as "PLANNED_SALES_min", max(PLANNED_SALES) as "PLANNED_SALES_max",
min(DATESECONDS) as "DATESECONDS_min", max(DATESECONDS) as "DATESECONDS_max",
min(YEAR) as "YEAR_min", max(YEAR) as "YEAR_max", min(SALES) as "SALES_min",
max(SALES) as "SALES_max", min(DATE_MILLIS) as "DATE_MILLIS_min",
max(DATE_MILLIS) as "DATE_MILLIS_max" from (select
DATE,STATE,COUNTY_CODE,INCOME_BRACKET,SALE_DATE,SHIP_DATE,SALES,ZIPCODE,PRODUCT_CATEGORY,CITY,PRODUCT_GROUP,SATISFACTION,SKU,PLANNED_SALES,GENDER,COUNTY,REVIEW_TEXT,DATEWITHOUTTIME,DATE_MILLIS,DATESECONDS,DATESTRINGPATTERN1,DATESTRINGPATTERN2,DATENUMBERPATTERN,YEAR
from ec. auto_data_year a where city in ('Albion', 'Jamaica', 'Apulia
Station') order by county DESC limit 100) as ds
WOKR GOOD
2) select min(DATE) as "DATE_min", max(DATE) as "DATE_max", min(PLANNED_SALES)
as "PLANNED_SALES_min", max(PLANNED_SALES) as "PLANNED_SALES_max",
min(DATESECONDS) as "DATESECONDS_min", max(DATESECONDS) as "DATESECONDS_max",
min(YEAR) as "YEAR_min", max(YEAR) as "YEAR_max", min(SALES) as "SALES_min",
max(SALES) as "SALES_max", min(DATE_MILLIS) as "DATE_MILLIS_min",
max(DATE_MILLIS) as "DATE_MILLIS_max" from (select * from ec. auto_data_year a
where city in ('Albion', 'Jamaica', 'Apulia Station') order by county DESC
limit 100) as ds
HAVE AN ERROR java.sql.SQLFeatureNotSupportedException: Wildcard in subqueries
not supported.
3)select min(DATE) as "DATE_min", max(DATE) as "DATE_max", min(PLANNED_SALES)
as "PLANNED_SALES_min", max(PLANNED_SALES) as "PLANNED_SALES_max",
min(DATESECONDS) as "DATESECONDS_min", max(DATESECONDS) as "DATESECONDS_max",
min(YEAR) as "YEAR_min", max(YEAR) as "YEAR_max", min(SALES) as "SALES_min",
max(SALES) as "SALES_max", min(DATE_MILLIS) as "DATE_MILLIS_min",
max(DATE_MILLIS) as "DATE_MILLIS_max" from (select * from ec. auto_data_year a
where city in ('Albion', 'Jamaica', 'Apulia Station') order by county DESC) as
ds
HAVE AN ERROR java.sql.SQLException: ERROR 1018 (42Y27): Aggregate may not
contain columns not in GROUP BY. CFY.COUNTY
SO i don't understand what the difference between this 3 query. Thats a bug, or
that my fault?
was:
I have table
CREATE TABLE ec.auto_data_year(
cf.date timestamp,
cf.state varchar,
cf.county_code bigint,
cf.income_bracket varchar,
cf.sale_date bigint,
ship_date varchar NOT NULL,
cf.sales decimal(10,0),
cf.zipcode bigint,
cf.product_category varchar,
cf.city varchar,
cf.product_group varchar,
cf.satisfaction bigint,
sku varchar NOT NULL,
cf.planned_sales decimal(10,0),
cf.gender varchar,
cf.county varchar,
cf.review_text varchar,
cf.datewithouttime varchar,
cf.date_millis bigint,
cf.dateseconds bigint,
cf.datestringpattern1 varchar,
cf.datestringpattern2 varchar,
cf.datenumberpattern bigint,
cf.year bigint
CONSTRAINT pk PRIMARY KEY (ship_date, sku))
DEFAULT_COLUMN_FAMILY='cf'
;
and have three query:
1) select min(DATE) as "DATE_min", max(DATE) as "DATE_max", min(PLANNED_SALES)
as "PLANNED_SALES_min", max(PLANNED_SALES) as "PLANNED_SALES_max",
min(DATESECONDS) as "DATESECONDS_min", max(DATESECONDS) as "DATESECONDS_max",
min(YEAR) as "YEAR_min", max(YEAR) as "YEAR_max", min(SALES) as "SALES_min",
max(SALES) as "SALES_max", min(DATE_MILLIS) as "DATE_MILLIS_min",
max(DATE_MILLIS) as "DATE_MILLIS_max" from (select
DATE,STATE,COUNTY_CODE,INCOME_BRACKET,SALE_DATE,SHIP_DATE,SALES,ZIPCODE,PRODUCT_CATEGORY,CITY,PRODUCT_GROUP,SATISFACTION,SKU,PLANNED_SALES,GENDER,COUNTY,REVIEW_TEXT,DATEWITHOUTTIME,DATE_MILLIS,DATESECONDS,DATESTRINGPATTERN1,DATESTRINGPATTERN2,DATENUMBERPATTERN,YEAR
from ec.AUTO_DATA_YEAR_ITEST a where city in ('Albion', 'Jamaica', 'Apulia
Station') order by county DESC limit 100) as ds
WOKR GOOD
2) select min(DATE) as "DATE_min", max(DATE) as "DATE_max", min(PLANNED_SALES)
as "PLANNED_SALES_min", max(PLANNED_SALES) as "PLANNED_SALES_max",
min(DATESECONDS) as "DATESECONDS_min", max(DATESECONDS) as "DATESECONDS_max",
min(YEAR) as "YEAR_min", max(YEAR) as "YEAR_max", min(SALES) as "SALES_min",
max(SALES) as "SALES_max", min(DATE_MILLIS) as "DATE_MILLIS_min",
max(DATE_MILLIS) as "DATE_MILLIS_max" from (select * from
ec.AUTO_DATA_YEAR_ITEST a where city in ('Albion', 'Jamaica', 'Apulia Station')
order by county DESC limit 100) as ds
HAVE AN ERROR java.sql.SQLFeatureNotSupportedException: Wildcard in subqueries
not supported.
3)select min(DATE) as "DATE_min", max(DATE) as "DATE_max", min(PLANNED_SALES)
as "PLANNED_SALES_min", max(PLANNED_SALES) as "PLANNED_SALES_max",
min(DATESECONDS) as "DATESECONDS_min", max(DATESECONDS) as "DATESECONDS_max",
min(YEAR) as "YEAR_min", max(YEAR) as "YEAR_max", min(SALES) as "SALES_min",
max(SALES) as "SALES_max", min(DATE_MILLIS) as "DATE_MILLIS_min",
max(DATE_MILLIS) as "DATE_MILLIS_max" from (select * from
ec.AUTO_DATA_YEAR_ITEST a where city in ('Albion', 'Jamaica', 'Apulia Station')
order by county DESC) as ds
HAVE AN ERROR java.sql.SQLException: ERROR 1018 (42Y27): Aggregate may not
contain columns not in GROUP BY. CFY.COUNTY
SO i don't understand what the difference between this 3 query. Thats a bug, or
that my fault?
> Different error on same structure min/max query
> ------------------------------------------------
>
> Key: PHOENIX-2443
> URL: https://issues.apache.org/jira/browse/PHOENIX-2443
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.5.0
> Environment: Phoenix 4.5.0 run on HDP last version
> Reporter: Nick Hryhoriev
> Labels: SQL
>
> I have table
> CREATE TABLE ec.auto_data_year(
> cf.date timestamp,
> cf.state varchar,
> cf.county_code bigint,
> cf.income_bracket varchar,
> cf.sale_date bigint,
> ship_date varchar NOT NULL,
> cf.sales decimal(10,0),
> cf.zipcode bigint,
> cf.product_category varchar,
> cf.city varchar,
> cf.product_group varchar,
> cf.satisfaction bigint,
> sku varchar NOT NULL,
> cf.planned_sales decimal(10,0),
> cf.gender varchar,
> cf.county varchar,
> cf.review_text varchar,
> cf.datewithouttime varchar,
> cf.date_millis bigint,
> cf.dateseconds bigint,
> cf.datestringpattern1 varchar,
> cf.datestringpattern2 varchar,
> cf.datenumberpattern bigint,
> cf.year bigint
> CONSTRAINT pk PRIMARY KEY (ship_date, sku))
> DEFAULT_COLUMN_FAMILY='cf'
> ;
> and have three query:
> 1) select min(DATE) as "DATE_min", max(DATE) as "DATE_max",
> min(PLANNED_SALES) as "PLANNED_SALES_min", max(PLANNED_SALES) as
> "PLANNED_SALES_max", min(DATESECONDS) as "DATESECONDS_min", max(DATESECONDS)
> as "DATESECONDS_max", min(YEAR) as "YEAR_min", max(YEAR) as "YEAR_max",
> min(SALES) as "SALES_min", max(SALES) as "SALES_max", min(DATE_MILLIS) as
> "DATE_MILLIS_min", max(DATE_MILLIS) as "DATE_MILLIS_max" from (select
> DATE,STATE,COUNTY_CODE,INCOME_BRACKET,SALE_DATE,SHIP_DATE,SALES,ZIPCODE,PRODUCT_CATEGORY,CITY,PRODUCT_GROUP,SATISFACTION,SKU,PLANNED_SALES,GENDER,COUNTY,REVIEW_TEXT,DATEWITHOUTTIME,DATE_MILLIS,DATESECONDS,DATESTRINGPATTERN1,DATESTRINGPATTERN2,DATENUMBERPATTERN,YEAR
> from ec. auto_data_year a where city in ('Albion', 'Jamaica', 'Apulia
> Station') order by county DESC limit 100) as ds
> WOKR GOOD
> 2) select min(DATE) as "DATE_min", max(DATE) as "DATE_max",
> min(PLANNED_SALES) as "PLANNED_SALES_min", max(PLANNED_SALES) as
> "PLANNED_SALES_max", min(DATESECONDS) as "DATESECONDS_min", max(DATESECONDS)
> as "DATESECONDS_max", min(YEAR) as "YEAR_min", max(YEAR) as "YEAR_max",
> min(SALES) as "SALES_min", max(SALES) as "SALES_max", min(DATE_MILLIS) as
> "DATE_MILLIS_min", max(DATE_MILLIS) as "DATE_MILLIS_max" from (select * from
> ec. auto_data_year a where city in ('Albion', 'Jamaica', 'Apulia Station')
> order by county DESC limit 100) as ds
> HAVE AN ERROR java.sql.SQLFeatureNotSupportedException: Wildcard in
> subqueries not supported.
> 3)select min(DATE) as "DATE_min", max(DATE) as "DATE_max", min(PLANNED_SALES)
> as "PLANNED_SALES_min", max(PLANNED_SALES) as "PLANNED_SALES_max",
> min(DATESECONDS) as "DATESECONDS_min", max(DATESECONDS) as "DATESECONDS_max",
> min(YEAR) as "YEAR_min", max(YEAR) as "YEAR_max", min(SALES) as "SALES_min",
> max(SALES) as "SALES_max", min(DATE_MILLIS) as "DATE_MILLIS_min",
> max(DATE_MILLIS) as "DATE_MILLIS_max" from (select * from ec. auto_data_year
> a where city in ('Albion', 'Jamaica', 'Apulia Station') order by county DESC)
> as ds
> HAVE AN ERROR java.sql.SQLException: ERROR 1018 (42Y27): Aggregate may not
> contain columns not in GROUP BY. CFY.COUNTY
> SO i don't understand what the difference between this 3 query. Thats a bug,
> or that my fault?
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
