[
https://issues.apache.org/jira/browse/PHOENIX-2443?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15022442#comment-15022442
]
Maryann Xue commented on PHOENIX-2443:
--------------------------------------
I have file PHOENIX-2444 for (3), which is basically expanding (rewriting) the
derived table without discarding the "order by".
> 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
> I use JDBC Client.
> 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)
