[jira] [Created] (KYLIN-1090) Cannot query a column whose name is one of the following: year, month, day, hour, minute, second

["Francesco Palmiotto (JIRA)" <ji...@apache.org>]

Francesco Palmiotto created KYLIN-1090:
------------------------------------------

             Summary: Cannot query a column whose name is one of the following: year, month, day, hour, minute, second
                 Key: KYLIN-1090
                 URL: https://issues.apache.org/jira/browse/KYLIN-1090
             Project: Kylin
          Issue Type: Bug
          Components: Query Engine
    Affects Versions: v1.1
            Reporter: Francesco Palmiotto
            Assignee: liyang


Suppose we have a query like:

"select f.year, avg(f.my_measure)
from fact_table f
group by f.year"

This will fail and throw the following error:

Encountered ". year" at line 1, column 9. Was expecting one of: "FROM" ... "," ... "AS" ... <IDENTIFIER> ... <QUOTED_IDENTIFIER> ... <BACK_QUOTED_IDENTIFIER> ... <BRACKET_QUOTED_IDENTIFIER> ... <UNICODE_QUOTED_IDENTIFIER> ... "." ... "NOT" ... "IN" ... "BETWEEN" ... "LIKE" ... "SIMILAR" ... "=" ... ">" ... "<" ... "<=" ... ">=" ... "<>" ... "+" ... "-" ... "*" ... "/" ... "||" ... "AND" ... "OR" ... "IS" ... "MEMBER" ... "SUBMULTISET" ... "MULTISET" ... "[" ... "." <IDENTIFIER> ... "." <QUOTED_IDENTIFIER> ... "." <BACK_QUOTED_IDENTIFIER> ... "." <BRACKET_QUOTED_IDENTIFIER> ... "." <UNICODE_QUOTED_IDENTIFIER> ... "." "*" ... "(" ...


I think this is due to the fact that year, month, day, hour, minute and second are all reserved keywords in ANSI SQL.




--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Re: [jira] [Created] (KYLIN-1090) Cannot query a column whose name is one of the following: year, month, day, hour, minute, second

[Patrick McAnneny <pa...@leadkarma.com>]

This is due to hive reserved keywords. Perhaps Kylin should auto-escape
columns when querying hive? The full list of keywords is:

ADD,ADMIN,AFTER,ALL,ALTER,ANALYZE,AND,ARCHIVE,ARRAY,AS,ASC,AUTHORIZATION,BEFORE,BETWEEN,
BIGINT,BINARY,BOOLEAN,BOTH,BUCKET,BUCKETS,BY,CASCADE,CASE,CAST,CHANGE,CHAR,CLUSTER,
CLUSTERED,CLUSTERSTATUS,COLLECTION,COLUMN,COLUMNS,COMMENT,COMPACT,COMPACTIONS,COMPUTE,
CONCATENATE,CONF,CONTINUE,CREATE,CROSS,CUBE,CURRENT,CURRENT_DATE,CURRENT_TIMESTAMP,CURSOR,
DATA,DATABASE,DATABASES,DATE,DATETIME,DAY,DBPROPERTIES,DECIMAL,DEFERRED,DEFINED,DELETE,
DELIMITED,DEPENDENCY,DESC,DESCRIBE,DIRECTORIES,DIRECTORY,DISABLE,DISTINCT,DISTRIBUTE,
DOUBLE,DROP,ELEM_TYPE,ELSE,ENABLE,END,ESCAPED,EXCHANGE,EXCLUSIVE,EXISTS,EXPLAIN,EXPORT,
EXTENDED,EXTERNAL,FALSE,FETCH,FIELDS,FILE,FILEFORMAT,FIRST,FLOAT,FOLLOWING,FOR,FORMAT,
FORMATTED,FROM,FULL,FUNCTION,FUNCTIONS,GRANT,GROUP,GROUPING,HAVING,HOLD_DDLTIME,HOUR,
IDXPROPERTIES,IF,IGNORE,IMPORT,IN,INDEX,INDEXES,INNER,INPATH,INPUTDRIVER,INPUTFORMAT,
INSERT,INT,INTERSECT,INTERVAL,INTO,IS,ITEMS,JAR,JOIN,KEYS,KEY_TYPE,LATERAL,LEFT,LESS,
LIKE,LIMIT,LINES,LOAD,LOCAL,LOCATION,LOCK,LOCKS,LOGICAL,LONG,MACRO,MAP,MAPJOIN,
MATERIALIZED,MINUS,MINUTE,MONTH,MORE,MSCK,NONE,NOSCAN,NOT,NO_DROP,NULL,OF,OFFLINE,ON,
OPTION,OR,ORDER,OUT,OUTER,OUTPUTDRIVER,OUTPUTFORMAT,OVER,OVERWRITE,OWNER,PARTIALSCAN,
PARTITION,PARTITIONED,PARTITIONS,PERCENT,PLUS,PRECEDING,PRESERVE,PRETTY,PRINCIPALS,
PROCEDURE,PROTECTION,PURGE,RANGE,READ,READONLY,READS,REBUILD,RECORDREADER,RECORDWRITER,
REDUCE,REGEXP,RELOAD,RENAME,REPAIR,REPLACE,RESTRICT,REVOKE,REWRITE,RIGHT,RLIKE,ROLE,ROLES,
ROLLUP,ROW,ROWS,SCHEMA,SCHEMAS,SECOND,SELECT,SEMI,SERDE,SERDEPROPERTIES,SERVER,SET,SETS,
SHARED,SHOW,SHOW_DATABASE,SKEWED,SMALLINT,SORT,SORTED,SSL,STATISTICS,STORED,STREAMTABLE,
STRING,STRUCT,TABLE,TABLES,TABLESAMPLE,TBLPROPERTIES,TEMPORARY,TERMINATED,THEN,TIMESTAMP,
TINYINT,TO,TOUCH,TRANSACTIONS,TRANSFORM,TRIGGER,TRUE,TRUNCATE,UNARCHIVE,UNBOUNDED,UNDO,
UNION,UNIONTYPE,UNIQUEJOIN,UNLOCK,UNSET,UNSIGNED,UPDATE,URI,USE,USER,USING,UTC,
UTCTIMESTAMP,VALUES,VALUE_TYPE,VARCHAR,VIEW,WHEN,WHERE,WHILE,WINDOW,WITH,YEAR

(taken from
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL)

On Tue, Oct 20, 2015 at 5:26 AM, Francesco Palmiotto (JIRA) <jira@apache.org
> wrote:

> Francesco Palmiotto created KYLIN-1090:
> ------------------------------------------
>
>              Summary: Cannot query a column whose name is one of the
> following: year, month, day, hour, minute, second
>                  Key: KYLIN-1090
>                  URL: https://issues.apache.org/jira/browse/KYLIN-1090
>              Project: Kylin
>           Issue Type: Bug
>           Components: Query Engine
>     Affects Versions: v1.1
>             Reporter: Francesco Palmiotto
>             Assignee: liyang
>
>
> Suppose we have a query like:
>
> "select f.year, avg(f.my_measure)
> from fact_table f
> group by f.year"
>
> This will fail and throw the following error:
>
> Encountered ". year" at line 1, column 9. Was expecting one of: "FROM" ...
> "," ... "AS" ... <IDENTIFIER> ... <QUOTED_IDENTIFIER> ...
> <BACK_QUOTED_IDENTIFIER> ... <BRACKET_QUOTED_IDENTIFIER> ...
> <UNICODE_QUOTED_IDENTIFIER> ... "." ... "NOT" ... "IN" ... "BETWEEN" ...
> "LIKE" ... "SIMILAR" ... "=" ... ">" ... "<" ... "<=" ... ">=" ... "<>" ...
> "+" ... "-" ... "*" ... "/" ... "||" ... "AND" ... "OR" ... "IS" ...
> "MEMBER" ... "SUBMULTISET" ... "MULTISET" ... "[" ... "." <IDENTIFIER> ...
> "." <QUOTED_IDENTIFIER> ... "." <BACK_QUOTED_IDENTIFIER> ... "."
> <BRACKET_QUOTED_IDENTIFIER> ... "." <UNICODE_QUOTED_IDENTIFIER> ... "." "*"
> ... "(" ...
>
>
> I think this is due to the fact that year, month, day, hour, minute and
> second are all reserved keywords in ANSI SQL.
>
>
>
>
> --
> This message was sent by Atlassian JIRA
> (v6.3.4#6332)
>