phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "James Taylor (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-2443) Different error on same structure min/max query
Date Sun, 22 Nov 2015 05:08:11 GMT

    [ https://issues.apache.org/jira/browse/PHOENIX-2443?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15020839#comment-15020839
] 

James Taylor commented on PHOENIX-2443:
---------------------------------------

For (2), as the error messages says, we don't support usage of wildcards in derived tables
(see PHOENIX-2041). For (3), that might be another limitation or maybe a bug, but I can't
seem to find an open JIRA. WDYT, [~maryannxue]?



> 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)

Mime
View raw message