You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@spark.apache.org by Du Li <li...@yahoo-inc.com.INVALID> on 2014/09/25 02:58:34 UTC
Spark SQL use of alias in where clause
Hi,
The following query does not work in Shark nor in the new Spark SQLContext or HiveContext.
SELECT key, value, concat(key, value) as combined from src where combined like ’11%’;
The following tweak of syntax works fine although a bit ugly.
SELECT key, value, concat(key, value) as combined from src where concat(key,value) like ’11%’ order by combined;
Are you going to support alias in where clause soon?
Thanks,
Du
Re: Spark SQL use of alias in where clause
Posted by Du Li <li...@yahoo-inc.com.INVALID>.
Thanks, Yanbo and Nicholas. Now it makes more sense — query optimization is the answer. /Du
From: Nicholas Chammas <ni...@gmail.com>>
Date: Thursday, September 25, 2014 at 6:43 AM
To: Yanbo Liang <ya...@gmail.com>>
Cc: Du Li <li...@yahoo-inc.com.invalid>>, "dev@spark.apache.org<ma...@spark.apache.org>" <de...@spark.apache.org>>, "user@spark.apache.org<ma...@spark.apache.org>" <us...@spark.apache.org>>
Subject: Re: Spark SQL use of alias in where clause
That is correct. Aliases in the SELECT clause can only be referenced in the ORDER BY and HAVING clauses. Otherwise, you'll have to just repeat the statement, like concat() in this case.
A more elegant alternative, which is probably not available in Spark SQL yet, is to use Common Table Expressions<http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx>.
On Wed, Sep 24, 2014 at 11:32 PM, Yanbo Liang <ya...@gmail.com>> wrote:
Maybe it's the way SQL works.
The select part is executed after the where filter is applied, so you cannot use alias declared in select part in where clause.
Hive and Oracle behavior the same as Spark SQL.
2014-09-25 8:58 GMT+08:00 Du Li <li...@yahoo-inc.com.invalid>>:
Hi,
The following query does not work in Shark nor in the new Spark SQLContext or HiveContext.
SELECT key, value, concat(key, value) as combined from src where combined like ’11%’;
The following tweak of syntax works fine although a bit ugly.
SELECT key, value, concat(key, value) as combined from src where concat(key,value) like ’11%’ order by combined;
Are you going to support alias in where clause soon?
Thanks,
Du
Re: Spark SQL use of alias in where clause
Posted by Du Li <li...@yahoo-inc.com.INVALID>.
Thanks, Yanbo and Nicholas. Now it makes more sense — query optimization is the answer. /Du
From: Nicholas Chammas <ni...@gmail.com>>
Date: Thursday, September 25, 2014 at 6:43 AM
To: Yanbo Liang <ya...@gmail.com>>
Cc: Du Li <li...@yahoo-inc.com.invalid>>, "dev@spark.apache.org<ma...@spark.apache.org>" <de...@spark.apache.org>>, "user@spark.apache.org<ma...@spark.apache.org>" <us...@spark.apache.org>>
Subject: Re: Spark SQL use of alias in where clause
That is correct. Aliases in the SELECT clause can only be referenced in the ORDER BY and HAVING clauses. Otherwise, you'll have to just repeat the statement, like concat() in this case.
A more elegant alternative, which is probably not available in Spark SQL yet, is to use Common Table Expressions<http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx>.
On Wed, Sep 24, 2014 at 11:32 PM, Yanbo Liang <ya...@gmail.com>> wrote:
Maybe it's the way SQL works.
The select part is executed after the where filter is applied, so you cannot use alias declared in select part in where clause.
Hive and Oracle behavior the same as Spark SQL.
2014-09-25 8:58 GMT+08:00 Du Li <li...@yahoo-inc.com.invalid>>:
Hi,
The following query does not work in Shark nor in the new Spark SQLContext or HiveContext.
SELECT key, value, concat(key, value) as combined from src where combined like ’11%’;
The following tweak of syntax works fine although a bit ugly.
SELECT key, value, concat(key, value) as combined from src where concat(key,value) like ’11%’ order by combined;
Are you going to support alias in where clause soon?
Thanks,
Du
Re: Spark SQL use of alias in where clause
Posted by Nicholas Chammas <ni...@gmail.com>.
That is correct. Aliases in the SELECT clause can only be referenced in the
ORDER BY and HAVING clauses. Otherwise, you'll have to just repeat the
statement, like concat() in this case.
A more elegant alternative, which is probably not available in Spark SQL
yet, is to use Common Table Expressions
<http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx>.
On Wed, Sep 24, 2014 at 11:32 PM, Yanbo Liang <ya...@gmail.com> wrote:
> Maybe it's the way SQL works.
> The select part is executed after the where filter is applied, so you
> cannot use alias declared in select part in where clause.
> Hive and Oracle behavior the same as Spark SQL.
>
> 2014-09-25 8:58 GMT+08:00 Du Li <li...@yahoo-inc.com.invalid>:
>
>> Hi,
>>
>> The following query does not work in Shark nor in the new Spark
>> SQLContext or HiveContext.
>> SELECT key, value, concat(key, value) as combined from src where combined
>> like ’11%’;
>>
>> The following tweak of syntax works fine although a bit ugly.
>> SELECT key, value, concat(key, value) as combined from src where
>> concat(key,value) like ’11%’ order by combined;
>>
>> Are you going to support alias in where clause soon?
>>
>> Thanks,
>> Du
>>
>
>
Re: Spark SQL use of alias in where clause
Posted by Nicholas Chammas <ni...@gmail.com>.
That is correct. Aliases in the SELECT clause can only be referenced in the
ORDER BY and HAVING clauses. Otherwise, you'll have to just repeat the
statement, like concat() in this case.
A more elegant alternative, which is probably not available in Spark SQL
yet, is to use Common Table Expressions
<http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx>.
On Wed, Sep 24, 2014 at 11:32 PM, Yanbo Liang <ya...@gmail.com> wrote:
> Maybe it's the way SQL works.
> The select part is executed after the where filter is applied, so you
> cannot use alias declared in select part in where clause.
> Hive and Oracle behavior the same as Spark SQL.
>
> 2014-09-25 8:58 GMT+08:00 Du Li <li...@yahoo-inc.com.invalid>:
>
>> Hi,
>>
>> The following query does not work in Shark nor in the new Spark
>> SQLContext or HiveContext.
>> SELECT key, value, concat(key, value) as combined from src where combined
>> like ’11%’;
>>
>> The following tweak of syntax works fine although a bit ugly.
>> SELECT key, value, concat(key, value) as combined from src where
>> concat(key,value) like ’11%’ order by combined;
>>
>> Are you going to support alias in where clause soon?
>>
>> Thanks,
>> Du
>>
>
>
Re: Spark SQL use of alias in where clause
Posted by Yanbo Liang <ya...@gmail.com>.
Maybe it's the way SQL works.
The select part is executed after the where filter is applied, so you
cannot use alias declared in select part in where clause.
Hive and Oracle behavior the same as Spark SQL.
2014-09-25 8:58 GMT+08:00 Du Li <li...@yahoo-inc.com.invalid>:
> Hi,
>
> The following query does not work in Shark nor in the new Spark
> SQLContext or HiveContext.
> SELECT key, value, concat(key, value) as combined from src where combined
> like ’11%’;
>
> The following tweak of syntax works fine although a bit ugly.
> SELECT key, value, concat(key, value) as combined from src where
> concat(key,value) like ’11%’ order by combined;
>
> Are you going to support alias in where clause soon?
>
> Thanks,
> Du
>
Re: Spark SQL use of alias in where clause
Posted by Yanbo Liang <ya...@gmail.com>.
Maybe it's the way SQL works.
The select part is executed after the where filter is applied, so you
cannot use alias declared in select part in where clause.
Hive and Oracle behavior the same as Spark SQL.
2014-09-25 8:58 GMT+08:00 Du Li <li...@yahoo-inc.com.invalid>:
> Hi,
>
> The following query does not work in Shark nor in the new Spark
> SQLContext or HiveContext.
> SELECT key, value, concat(key, value) as combined from src where combined
> like ’11%’;
>
> The following tweak of syntax works fine although a bit ugly.
> SELECT key, value, concat(key, value) as combined from src where
> concat(key,value) like ’11%’ order by combined;
>
> Are you going to support alias in where clause soon?
>
> Thanks,
> Du
>