Ming,
I agree that a better error message would always be better. The parser
essentially stops when it encounters an error / unexpected syntax. One could
imagine a parser that keeps going and realigns itself to valid syntax that it
finds again. Then it could potentially call out that the ORDER BY is at the
wrong place, or there are three columns with invalid data types, listing the
columns and data types specified, etc. But I don't know if any parser does
that. It seems that would be hard to do.
We have to live with many imperfections in life. Maybe we will just have to
live with this one too until we have an artificial intelligent parser that can
figure out exactly what you want. In that case migrating SQL from one database
to another will not be necessary anymore. In search of parser nirvana :-)
Rohit
-------- Original message --------
From: "Liu, Ming (Ming)" <[email protected]>
Date: 03/24/2016 8:42 AM (GMT-06:00)
To: Rohit Jain <[email protected]>, [email protected]
Subject: 答复: Does Trafodion support to use 'order by' in a subquery
Thanks Rohit,
That is good to know, after think it again, yes, this is wrong in the first
place. I tested in Hive, it doesn’t support that syntax either, but seems
some RDBMS (Oracle, DB2) allow this syntax but ignore it.
However, It will be more user friendly that optimizer or parser simply give a
warning or ignore it, because other database allows this, but since
itself is wrong at first, so this ‘enhancement’ is not necessary, just if
Trafodion could do that, that seems more flexible.
Thanks,
Ming
发件人: Rohit [mailto:[email protected]]
发送时间: 2016年3月24日
18:12
收件人: Liu, Ming (Ming) <[email protected]>; [email protected]
主题: RE: Does Trafodion support to use 'order by' in a subquery
Order by is allowed only on the outermost select in ANSI. It is meant to define
the order of the final results of the query returned to the user. It is not
allowed in sub queries.
Rohit
-------- Original message --------
From: "Liu, Ming (Ming)" <[email protected]>
Date: 03/24/2016 2:15 AM (GMT-06:00)
To: [email protected]
Subject: Does Trafodion support to use 'order by' in a subquery
Hi, all,
I don’t know if this is same in other database, but in Trafodion, if I want to
have a subquery which have a ‘order by’, it seems not allowed. Is this normal or
a limitation in Trafodion?
For example:
create table t1 ( c1 int, c2 int);
select * from (
Select * from t1 order by c1);
*** ERROR[15001] A syntax error occurred at or before:
select * from (select * from t1 order by c1);
^ (44 characters from start of SQL
statement)
Thanks in advance,
Ming
