You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@spark.apache.org by Mohammed Guller <mo...@glassbeam.com> on 2014/10/09 04:26:13 UTC
Spark SQL parser bug?
Hi -
When I run the following Spark SQL query in Spark-shell ( version 1.1.0) :
val rdd = sqlContext.sql("SELECT a FROM x WHERE ts >= '2012-01-01T00:00:00' AND ts <= '2012-03-31T23:59:59' ")
it gives the following error:
rdd: org.apache.spark.sql.SchemaRDD =
SchemaRDD[294] at RDD at SchemaRDD.scala:103
== Query Plan ==
== Physical Plan ==
java.util.NoSuchElementException: head of empty list
The ts column in the where clause has timestamp data and is of type timestamp. If I replace the string '2012-01-01T00:00:00' in the where clause with its epoch value, then the query works fine.
It looks I have run into an issue described in this pull request: https://github.com/apache/spark/pull/2084
Is that PR not merged in Spark version 1.1.0? Or am I missing something?
Thanks,
Mohammed
RE: Spark SQL parser bug?
Posted by Mohammed Guller <mo...@glassbeam.com>.
Leon,
I solved the problem by creating a work around for it, so didn't have a need to upgrade to 1.1.2-SNAPSHOT.
Mohammed
-----Original Message-----
From: Leon [mailto:pachkulja@gmail.com]
Sent: Tuesday, November 25, 2014 11:36 AM
To: user@spark.incubator.apache.org
Subject: RE: Spark SQL parser bug?
Hello
I just stumbled on exactly the same issue as you are discussing in this thread. Here are my dependencies:
<dependencies>
<dependency>
<groupId>com.datastax.spark</groupId>
<artifactId>spark-cassandra-connector_2.10</artifactId>
<version>1.1.0</version>
</dependency>
<dependency>
<groupId>com.datastax.spark</groupId>
<artifactId>spark-cassandra-connector-java_2.10</artifactId>
<version>1.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-core_2.10</artifactId>
<version>1.1.2-SNAPSHOT</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-streaming_2.10</artifactId>
<version>1.1.2-SNAPSHOT</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-sql_2.10</artifactId>
<version>1.1.2-SNAPSHOT</version>
<scope>provided</scope>
</dependency>
</dependencies>
As you can see I am using the latest of Spark and Spark Cassandra Connector and I still get the same error message:
Exception in thread "main" java.util.NoSuchElementException: head of empty list
So, I don't believe this bug was really fixed in Spark 1.1.1 release as reported above.
Did you problem get fixed with the latest Spark update?
Thanks,
Leon
--
View this message in context: http://apache-spark-user-list.1001560.n3.nabble.com/Spark-SQL-parser-bug-tp15999p19793.html
Sent from the Apache Spark User List mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@spark.apache.org For additional commands, e-mail: user-help@spark.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@spark.apache.org
For additional commands, e-mail: user-help@spark.apache.org
RE: Spark SQL parser bug?
Posted by Leon <pa...@gmail.com>.
Hello
I just stumbled on exactly the same issue as you are discussing in this
thread. Here are my dependencies:
<dependencies>
<dependency>
<groupId>com.datastax.spark</groupId>
<artifactId>spark-cassandra-connector_2.10</artifactId>
<version>1.1.0</version>
</dependency>
<dependency>
<groupId>com.datastax.spark</groupId>
<artifactId>spark-cassandra-connector-java_2.10</artifactId>
<version>1.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-core_2.10</artifactId>
<version>1.1.2-SNAPSHOT</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-streaming_2.10</artifactId>
<version>1.1.2-SNAPSHOT</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-sql_2.10</artifactId>
<version>1.1.2-SNAPSHOT</version>
<scope>provided</scope>
</dependency>
</dependencies>
As you can see I am using the latest of Spark and Spark Cassandra Connector
and I still get the same error message:
Exception in thread "main" java.util.NoSuchElementException: head of empty
list
So, I don't believe this bug was really fixed in Spark 1.1.1 release as
reported above.
Did you problem get fixed with the latest Spark update?
Thanks,
Leon
--
View this message in context: http://apache-spark-user-list.1001560.n3.nabble.com/Spark-SQL-parser-bug-tp15999p19793.html
Sent from the Apache Spark User List mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@spark.apache.org
For additional commands, e-mail: user-help@spark.apache.org
RE: Spark SQL parser bug?
Posted by Mohammed Guller <mo...@glassbeam.com>.
That explains it. Thanks!
Mohammed
From: Yin Huai [mailto:huaiyin.thu@gmail.com]
Sent: Monday, October 13, 2014 8:47 AM
To: Mohammed Guller
Cc: Cheng, Hao; Cheng Lian; user@spark.apache.org
Subject: Re: Spark SQL parser bug?
Yeah, it is not related to timezone. I think you hit this issue<https://issues.apache.org/jira/browse/SPARK-3173> and it was fixed after 1.1 release.
On Mon, Oct 13, 2014 at 11:24 AM, Mohammed Guller <mo...@glassbeam.com>> wrote:
Good guess, but that is not the reason. Look at this code:
scala> val data = sc.parallelize(1325548800000L::1335548800000L::Nil).map(i=> T(i.toString, new java.sql.Timestamp(i)))
data: org.apache.spark.rdd.RDD[T] = MappedRDD[17] at map at <console>:17
scala> data.collect
res3: Array[T] = Array(T(1325548800000,2012-01-02 16:00:00.0), T(1335548800000,2012-04-27 10:46:40.0))
scala> data.registerTempTable("x")
scala> val s = sqlContext.sql("select a from x where ts>='1970-01-01 00:00:00';")
s: org.apache.spark.sql.SchemaRDD =
SchemaRDD[20] at RDD at SchemaRDD.scala:103
== Query Plan ==
== Physical Plan ==
Project [a#2]
ExistingRdd [a#2,ts#3], MapPartitionsRDD[22] at mapPartitions at basicOperators.scala:208
scala> s.collect
res5: Array[org.apache.spark.sql.Row] = Array()
Mohammed
From: Yin Huai [mailto:huaiyin.thu@gmail.com<ma...@gmail.com>]
Sent: Monday, October 13, 2014 7:19 AM
To: Mohammed Guller
Cc: Cheng, Hao; Cheng Lian; user@spark.apache.org<ma...@spark.apache.org>
Subject: Re: Spark SQL parser bug?
Seems the reason that you got "wrong" results was caused by timezone.
The time in java.sql.Timestamp(long time) means "milliseconds since January 1, 1970, 00:00:00 GMT. A negative number is the number of milliseconds before January 1, 1970, 00:00:00 GMT."
However, in ts>='1970-01-01 00:00:00', '1970-01-01 00:00:00' is using your local timezone.
Thanks,
Yin
On Mon, Oct 13, 2014 at 9:58 AM, Mohammed Guller <mo...@glassbeam.com>> wrote:
Hi Cheng,
I am using version 1.1.0.
Looks like that bug was fixed sometime after 1.1.0 was released. Interestingly, I tried your code on 1.1.0 and it gives me a different (incorrect) result:
case class T(a:String, ts:java.sql.Timestamp)
val sqlContext = new org.apache.spark.sql.SQLContext(sc)
import sqlContext.createSchemaRDD
val data = sc.parallelize(10000::20000::Nil).map(i=> T(i.toString, new java.sql.Timestamp(i)))
data.registerTempTable("x")
val s = sqlContext.sql("select a from x where ts>='1970-01-01 00:00:00';")
scala> s.collect
res1: Array[org.apache.spark.sql.Row] = Array()
Mohammed
From: Cheng, Hao [mailto:hao.cheng@intel.com<ma...@intel.com>]
Sent: Sunday, October 12, 2014 1:35 AM
To: Mohammed Guller; Cheng Lian; user@spark.apache.org<ma...@spark.apache.org>
Subject: RE: Spark SQL parser bug?
Hi, I couldn’t reproduce the bug with the latest master branch. Which version are you using? Can you also list data in the table “x”?
case class T(a:String, ts:java.sql.Timestamp)
val sqlContext = new org.apache.spark.sql.SQLContext(sc)
import sqlContext.createSchemaRDD
val data = sc.parallelize(10000::20000::Nil).map(i=> T(i.toString, new java.sql.Timestamp(i)))
data.registerTempTable("x")
val s = sqlContext.sql("select a from x where ts>='1970-01-01 00:00:00';")
s.collect
output:
res1: Array[org.apache.spark.sql.Row] = Array([10000], [20000])
Cheng Hao
From: Mohammed Guller [mailto:mohammed@glassbeam.com]
Sent: Sunday, October 12, 2014 12:06 AM
To: Cheng Lian; user@spark.apache.org<ma...@spark.apache.org>
Subject: RE: Spark SQL parser bug?
I tried even without the “T” and it still returns an empty result:
scala> val sRdd = sqlContext.sql("select a from x where ts >= '2012-01-01 00:00:00';")
sRdd: org.apache.spark.sql.SchemaRDD =
SchemaRDD[35] at RDD at SchemaRDD.scala:103
== Query Plan ==
== Physical Plan ==
Project [a#0]
ExistingRdd [a#0,ts#1], MapPartitionsRDD[37] at mapPartitions at basicOperators.scala:208
scala> sRdd.collect
res10: Array[org.apache.spark.sql.Row] = Array()
Mohammed
From: Cheng Lian [mailto:lian.cs.zju@gmail.com]
Sent: Friday, October 10, 2014 10:14 PM
To: Mohammed Guller; user@spark.apache.org<ma...@spark.apache.org>
Subject: Re: Spark SQL parser bug?
Hmm, there is a “T” in the timestamp string, which makes the string not a valid timestamp string representation. Internally Spark SQL uses java.sql.Timestamp.valueOf to cast a string to a timestamp.
On 10/11/14 2:08 AM, Mohammed Guller wrote:
scala> rdd.registerTempTable("x")
scala> val sRdd = sqlContext.sql("select a from x where ts >= '2012-01-01T00:00:00';")
sRdd: org.apache.spark.sql.SchemaRDD =
SchemaRDD[4] at RDD at SchemaRDD.scala:103
== Query Plan ==
== Physical Plan ==
Project [a#0]
ExistingRdd [a#0,ts#1], MapPartitionsRDD[6] at mapPartitions at basicOperators.scala:208
scala> sRdd.collect
res2: Array[org.apache.spark.sql.Row] = Array()
Re: Spark SQL parser bug?
Posted by Yin Huai <hu...@gmail.com>.
Yeah, it is not related to timezone. I think you hit this issue
<https://issues.apache.org/jira/browse/SPARK-3173> and it was fixed after
1.1 release.
On Mon, Oct 13, 2014 at 11:24 AM, Mohammed Guller <mo...@glassbeam.com>
wrote:
> Good guess, but that is not the reason. Look at this code:
>
>
>
> scala> val data =
> sc.parallelize(1325548800000L::1335548800000L::Nil).map(i=> T(i.toString,
> new java.sql.Timestamp(i)))
>
> data: org.apache.spark.rdd.RDD[T] = MappedRDD[17] at map at <console>:17
>
>
>
> scala> data.collect
>
> res3: Array[T] = *Array(T(1325548800000,2012-01-02 16:00:00.0),
> T(1335548800000,2012-04-27 10:46:40.0))*
>
>
>
> scala> data.registerTempTable("x")
>
>
>
> scala> val s = sqlContext.sql("select a from x where ts>='1970-01-01
> 00:00:00';")
>
> s: org.apache.spark.sql.SchemaRDD =
>
> SchemaRDD[20] at RDD at SchemaRDD.scala:103
>
> == Query Plan ==
>
> == Physical Plan ==
>
> Project [a#2]
>
> ExistingRdd [a#2,ts#3], MapPartitionsRDD[22] at mapPartitions at
> basicOperators.scala:208
>
>
>
> scala> s.collect
>
> res5: Array[org.apache.spark.sql.Row] = Array()
>
>
>
>
>
>
>
> Mohammed
>
>
>
> *From:* Yin Huai [mailto:huaiyin.thu@gmail.com]
> *Sent:* Monday, October 13, 2014 7:19 AM
> *To:* Mohammed Guller
> *Cc:* Cheng, Hao; Cheng Lian; user@spark.apache.org
>
> *Subject:* Re: Spark SQL parser bug?
>
>
>
> Seems the reason that you got "wrong" results was caused by timezone.
>
>
>
> The time in java.sql.Timestamp(long time) means "milliseconds since
> January 1, 1970, 00:00:00 *GMT*. A negative number is the number of
> milliseconds before January 1, 1970, 00:00:00 *GMT*."
>
>
>
> However, in ts>='1970-01-01 00:00:00', '1970-01-01 00:00:00' is using
> your local timezone.
>
>
>
> Thanks,
>
>
>
> Yin
>
>
>
>
>
> On Mon, Oct 13, 2014 at 9:58 AM, Mohammed Guller <mo...@glassbeam.com>
> wrote:
>
> Hi Cheng,
>
> I am using version 1.1.0.
>
>
>
> Looks like that bug was fixed sometime after 1.1.0 was released.
> Interestingly, I tried your code on 1.1.0 and it gives me a different
> (incorrect) result:
>
>
>
> case class T(a:String, ts:java.sql.Timestamp)
>
> val sqlContext = new org.apache.spark.sql.SQLContext(sc)
>
> import sqlContext.createSchemaRDD
>
> val data = sc.parallelize(10000::20000::Nil).map(i=> T(i.toString, new
> java.sql.Timestamp(i)))
>
> data.registerTempTable("x")
>
> val s = sqlContext.sql("select a from x where ts>='1970-01-01 00:00:00';")
>
>
>
> scala> s.collect
>
> res1: Array[org.apache.spark.sql.Row] = Array()
>
>
>
> Mohammed
>
>
>
> *From:* Cheng, Hao [mailto:hao.cheng@intel.com]
> *Sent:* Sunday, October 12, 2014 1:35 AM
> *To:* Mohammed Guller; Cheng Lian; user@spark.apache.org
>
>
> *Subject:* RE: Spark SQL parser bug?
>
>
>
> Hi, I couldn’t reproduce the bug with the latest master branch. Which
> version are you using? Can you also list data in the table “x”?
>
>
>
> case class T(a:String, ts:java.sql.Timestamp)
>
> val sqlContext = new org.apache.spark.sql.SQLContext(sc)
>
> import sqlContext.createSchemaRDD
>
> val data = sc.parallelize(10000::20000::Nil).map(i=> T(i.toString, new
> java.sql.Timestamp(i)))
>
> data.registerTempTable("x")
>
> val s = sqlContext.sql("select a from x where ts>='1970-01-01 00:00:00';")
>
> s.collect
>
>
>
> output:
>
> res1: Array[org.apache.spark.sql.Row] = Array([10000], [20000])
>
>
>
> Cheng Hao
>
>
>
> *From:* Mohammed Guller [mailto:mohammed@glassbeam.com
> <mo...@glassbeam.com>]
> *Sent:* Sunday, October 12, 2014 12:06 AM
> *To:* Cheng Lian; user@spark.apache.org
> *Subject:* RE: Spark SQL parser bug?
>
>
>
> I tried even without the “T” and it still returns an empty result:
>
>
>
> scala> val sRdd = sqlContext.sql("select a from x where ts >= '2012-01-01
> 00:00:00';")
>
> sRdd: org.apache.spark.sql.SchemaRDD =
>
> SchemaRDD[35] at RDD at SchemaRDD.scala:103
>
> == Query Plan ==
>
> == Physical Plan ==
>
> Project [a#0]
>
> ExistingRdd [a#0,ts#1], MapPartitionsRDD[37] at mapPartitions at
> basicOperators.scala:208
>
>
>
> scala> sRdd.collect
>
> res10: Array[org.apache.spark.sql.Row] = Array()
>
>
>
>
>
> Mohammed
>
>
>
> *From:* Cheng Lian [mailto:lian.cs.zju@gmail.com <li...@gmail.com>]
> *Sent:* Friday, October 10, 2014 10:14 PM
> *To:* Mohammed Guller; user@spark.apache.org
> *Subject:* Re: Spark SQL parser bug?
>
>
>
> Hmm, there is a “T” in the timestamp string, which makes the string not a
> valid timestamp string representation. Internally Spark SQL uses
> java.sql.Timestamp.valueOf to cast a string to a timestamp.
>
> On 10/11/14 2:08 AM, Mohammed Guller wrote:
>
> scala> rdd.registerTempTable("x")
>
>
>
> scala> val sRdd = sqlContext.sql("select a from x where ts >= '2012-01-01
> *T*00:00:00';")
>
> sRdd: org.apache.spark.sql.SchemaRDD =
>
> SchemaRDD[4] at RDD at SchemaRDD.scala:103
>
> == Query Plan ==
>
> == Physical Plan ==
>
> Project [a#0]
>
> ExistingRdd [a#0,ts#1], MapPartitionsRDD[6] at mapPartitions at
> basicOperators.scala:208
>
>
>
> scala> sRdd.collect
>
> res2: Array[org.apache.spark.sql.Row] = Array()
>
>
>
>
>
RE: Spark SQL parser bug?
Posted by Mohammed Guller <mo...@glassbeam.com>.
Good guess, but that is not the reason. Look at this code:
scala> val data = sc.parallelize(1325548800000L::1335548800000L::Nil).map(i=> T(i.toString, new java.sql.Timestamp(i)))
data: org.apache.spark.rdd.RDD[T] = MappedRDD[17] at map at <console>:17
scala> data.collect
res3: Array[T] = Array(T(1325548800000,2012-01-02 16:00:00.0), T(1335548800000,2012-04-27 10:46:40.0))
scala> data.registerTempTable("x")
scala> val s = sqlContext.sql("select a from x where ts>='1970-01-01 00:00:00';")
s: org.apache.spark.sql.SchemaRDD =
SchemaRDD[20] at RDD at SchemaRDD.scala:103
== Query Plan ==
== Physical Plan ==
Project [a#2]
ExistingRdd [a#2,ts#3], MapPartitionsRDD[22] at mapPartitions at basicOperators.scala:208
scala> s.collect
res5: Array[org.apache.spark.sql.Row] = Array()
Mohammed
From: Yin Huai [mailto:huaiyin.thu@gmail.com]
Sent: Monday, October 13, 2014 7:19 AM
To: Mohammed Guller
Cc: Cheng, Hao; Cheng Lian; user@spark.apache.org
Subject: Re: Spark SQL parser bug?
Seems the reason that you got "wrong" results was caused by timezone.
The time in java.sql.Timestamp(long time) means "milliseconds since January 1, 1970, 00:00:00 GMT. A negative number is the number of milliseconds before January 1, 1970, 00:00:00 GMT."
However, in ts>='1970-01-01 00:00:00', '1970-01-01 00:00:00' is using your local timezone.
Thanks,
Yin
On Mon, Oct 13, 2014 at 9:58 AM, Mohammed Guller <mo...@glassbeam.com>> wrote:
Hi Cheng,
I am using version 1.1.0.
Looks like that bug was fixed sometime after 1.1.0 was released. Interestingly, I tried your code on 1.1.0 and it gives me a different (incorrect) result:
case class T(a:String, ts:java.sql.Timestamp)
val sqlContext = new org.apache.spark.sql.SQLContext(sc)
import sqlContext.createSchemaRDD
val data = sc.parallelize(10000::20000::Nil).map(i=> T(i.toString, new java.sql.Timestamp(i)))
data.registerTempTable("x")
val s = sqlContext.sql("select a from x where ts>='1970-01-01 00:00:00';")
scala> s.collect
res1: Array[org.apache.spark.sql.Row] = Array()
Mohammed
From: Cheng, Hao [mailto:hao.cheng@intel.com<ma...@intel.com>]
Sent: Sunday, October 12, 2014 1:35 AM
To: Mohammed Guller; Cheng Lian; user@spark.apache.org<ma...@spark.apache.org>
Subject: RE: Spark SQL parser bug?
Hi, I couldn’t reproduce the bug with the latest master branch. Which version are you using? Can you also list data in the table “x”?
case class T(a:String, ts:java.sql.Timestamp)
val sqlContext = new org.apache.spark.sql.SQLContext(sc)
import sqlContext.createSchemaRDD
val data = sc.parallelize(10000::20000::Nil).map(i=> T(i.toString, new java.sql.Timestamp(i)))
data.registerTempTable("x")
val s = sqlContext.sql("select a from x where ts>='1970-01-01 00:00:00';")
s.collect
output:
res1: Array[org.apache.spark.sql.Row] = Array([10000], [20000])
Cheng Hao
From: Mohammed Guller [mailto:mohammed@glassbeam.com]
Sent: Sunday, October 12, 2014 12:06 AM
To: Cheng Lian; user@spark.apache.org<ma...@spark.apache.org>
Subject: RE: Spark SQL parser bug?
I tried even without the “T” and it still returns an empty result:
scala> val sRdd = sqlContext.sql("select a from x where ts >= '2012-01-01 00:00:00';")
sRdd: org.apache.spark.sql.SchemaRDD =
SchemaRDD[35] at RDD at SchemaRDD.scala:103
== Query Plan ==
== Physical Plan ==
Project [a#0]
ExistingRdd [a#0,ts#1], MapPartitionsRDD[37] at mapPartitions at basicOperators.scala:208
scala> sRdd.collect
res10: Array[org.apache.spark.sql.Row] = Array()
Mohammed
From: Cheng Lian [mailto:lian.cs.zju@gmail.com]
Sent: Friday, October 10, 2014 10:14 PM
To: Mohammed Guller; user@spark.apache.org<ma...@spark.apache.org>
Subject: Re: Spark SQL parser bug?
Hmm, there is a “T” in the timestamp string, which makes the string not a valid timestamp string representation. Internally Spark SQL uses java.sql.Timestamp.valueOf to cast a string to a timestamp.
On 10/11/14 2:08 AM, Mohammed Guller wrote:
scala> rdd.registerTempTable("x")
scala> val sRdd = sqlContext.sql("select a from x where ts >= '2012-01-01T00:00:00';")
sRdd: org.apache.spark.sql.SchemaRDD =
SchemaRDD[4] at RDD at SchemaRDD.scala:103
== Query Plan ==
== Physical Plan ==
Project [a#0]
ExistingRdd [a#0,ts#1], MapPartitionsRDD[6] at mapPartitions at basicOperators.scala:208
scala> sRdd.collect
res2: Array[org.apache.spark.sql.Row] = Array()
Re: Spark SQL parser bug?
Posted by Yin Huai <hu...@gmail.com>.
Seems the reason that you got "wrong" results was caused by timezone.
The time in java.sql.Timestamp(long time) means "milliseconds since January
1, 1970, 00:00:00 *GMT*. A negative number is the number of milliseconds
before January 1, 1970, 00:00:00 *GMT*."
However, in ts>='1970-01-01 00:00:00', '1970-01-01 00:00:00' is using your
local timezone.
Thanks,
Yin
On Mon, Oct 13, 2014 at 9:58 AM, Mohammed Guller <mo...@glassbeam.com>
wrote:
> Hi Cheng,
>
> I am using version 1.1.0.
>
>
>
> Looks like that bug was fixed sometime after 1.1.0 was released.
> Interestingly, I tried your code on 1.1.0 and it gives me a different
> (incorrect) result:
>
>
>
> case class T(a:String, ts:java.sql.Timestamp)
>
> val sqlContext = new org.apache.spark.sql.SQLContext(sc)
>
> import sqlContext.createSchemaRDD
>
> val data = sc.parallelize(10000::20000::Nil).map(i=> T(i.toString, new
> java.sql.Timestamp(i)))
>
> data.registerTempTable("x")
>
> val s = sqlContext.sql("select a from x where ts>='1970-01-01 00:00:00';")
>
>
>
> scala> s.collect
>
> res1: Array[org.apache.spark.sql.Row] = Array()
>
>
>
> Mohammed
>
>
>
> *From:* Cheng, Hao [mailto:hao.cheng@intel.com]
> *Sent:* Sunday, October 12, 2014 1:35 AM
> *To:* Mohammed Guller; Cheng Lian; user@spark.apache.org
>
> *Subject:* RE: Spark SQL parser bug?
>
>
>
> Hi, I couldn’t reproduce the bug with the latest master branch. Which
> version are you using? Can you also list data in the table “x”?
>
>
>
> case class T(a:String, ts:java.sql.Timestamp)
>
> val sqlContext = new org.apache.spark.sql.SQLContext(sc)
>
> import sqlContext.createSchemaRDD
>
> val data = sc.parallelize(10000::20000::Nil).map(i=> T(i.toString, new
> java.sql.Timestamp(i)))
>
> data.registerTempTable("x")
>
> val s = sqlContext.sql("select a from x where ts>='1970-01-01 00:00:00';")
>
> s.collect
>
>
>
> output:
>
> res1: Array[org.apache.spark.sql.Row] = Array([10000], [20000])
>
>
>
> Cheng Hao
>
>
>
> *From:* Mohammed Guller [mailto:mohammed@glassbeam.com
> <mo...@glassbeam.com>]
> *Sent:* Sunday, October 12, 2014 12:06 AM
> *To:* Cheng Lian; user@spark.apache.org
> *Subject:* RE: Spark SQL parser bug?
>
>
>
> I tried even without the “T” and it still returns an empty result:
>
>
>
> scala> val sRdd = sqlContext.sql("select a from x where ts >= '2012-01-01
> 00:00:00';")
>
> sRdd: org.apache.spark.sql.SchemaRDD =
>
> SchemaRDD[35] at RDD at SchemaRDD.scala:103
>
> == Query Plan ==
>
> == Physical Plan ==
>
> Project [a#0]
>
> ExistingRdd [a#0,ts#1], MapPartitionsRDD[37] at mapPartitions at
> basicOperators.scala:208
>
>
>
> scala> sRdd.collect
>
> res10: Array[org.apache.spark.sql.Row] = Array()
>
>
>
>
>
> Mohammed
>
>
>
> *From:* Cheng Lian [mailto:lian.cs.zju@gmail.com <li...@gmail.com>]
> *Sent:* Friday, October 10, 2014 10:14 PM
> *To:* Mohammed Guller; user@spark.apache.org
> *Subject:* Re: Spark SQL parser bug?
>
>
>
> Hmm, there is a “T” in the timestamp string, which makes the string not a
> valid timestamp string representation. Internally Spark SQL uses
> java.sql.Timestamp.valueOf to cast a string to a timestamp.
>
> On 10/11/14 2:08 AM, Mohammed Guller wrote:
>
> scala> rdd.registerTempTable("x")
>
>
>
> scala> val sRdd = sqlContext.sql("select a from x where ts >= '2012-01-01
> *T*00:00:00';")
>
> sRdd: org.apache.spark.sql.SchemaRDD =
>
> SchemaRDD[4] at RDD at SchemaRDD.scala:103
>
> == Query Plan ==
>
> == Physical Plan ==
>
> Project [a#0]
>
> ExistingRdd [a#0,ts#1], MapPartitionsRDD[6] at mapPartitions at
> basicOperators.scala:208
>
>
>
> scala> sRdd.collect
>
> res2: Array[org.apache.spark.sql.Row] = Array()
>
>
>
RE: Spark SQL parser bug?
Posted by Mohammed Guller <mo...@glassbeam.com>.
Hi Cheng,
I am using version 1.1.0.
Looks like that bug was fixed sometime after 1.1.0 was released. Interestingly, I tried your code on 1.1.0 and it gives me a different (incorrect) result:
case class T(a:String, ts:java.sql.Timestamp)
val sqlContext = new org.apache.spark.sql.SQLContext(sc)
import sqlContext.createSchemaRDD
val data = sc.parallelize(10000::20000::Nil).map(i=> T(i.toString, new java.sql.Timestamp(i)))
data.registerTempTable("x")
val s = sqlContext.sql("select a from x where ts>='1970-01-01 00:00:00';")
scala> s.collect
res1: Array[org.apache.spark.sql.Row] = Array()
Mohammed
From: Cheng, Hao [mailto:hao.cheng@intel.com]
Sent: Sunday, October 12, 2014 1:35 AM
To: Mohammed Guller; Cheng Lian; user@spark.apache.org
Subject: RE: Spark SQL parser bug?
Hi, I couldn’t reproduce the bug with the latest master branch. Which version are you using? Can you also list data in the table “x”?
case class T(a:String, ts:java.sql.Timestamp)
val sqlContext = new org.apache.spark.sql.SQLContext(sc)
import sqlContext.createSchemaRDD
val data = sc.parallelize(10000::20000::Nil).map(i=> T(i.toString, new java.sql.Timestamp(i)))
data.registerTempTable("x")
val s = sqlContext.sql("select a from x where ts>='1970-01-01 00:00:00';")
s.collect
output:
res1: Array[org.apache.spark.sql.Row] = Array([10000], [20000])
Cheng Hao
From: Mohammed Guller [mailto:mohammed@glassbeam.com]
Sent: Sunday, October 12, 2014 12:06 AM
To: Cheng Lian; user@spark.apache.org<ma...@spark.apache.org>
Subject: RE: Spark SQL parser bug?
I tried even without the “T” and it still returns an empty result:
scala> val sRdd = sqlContext.sql("select a from x where ts >= '2012-01-01 00:00:00';")
sRdd: org.apache.spark.sql.SchemaRDD =
SchemaRDD[35] at RDD at SchemaRDD.scala:103
== Query Plan ==
== Physical Plan ==
Project [a#0]
ExistingRdd [a#0,ts#1], MapPartitionsRDD[37] at mapPartitions at basicOperators.scala:208
scala> sRdd.collect
res10: Array[org.apache.spark.sql.Row] = Array()
Mohammed
From: Cheng Lian [mailto:lian.cs.zju@gmail.com]
Sent: Friday, October 10, 2014 10:14 PM
To: Mohammed Guller; user@spark.apache.org<ma...@spark.apache.org>
Subject: Re: Spark SQL parser bug?
Hmm, there is a “T” in the timestamp string, which makes the string not a valid timestamp string representation. Internally Spark SQL uses java.sql.Timestamp.valueOf to cast a string to a timestamp.
On 10/11/14 2:08 AM, Mohammed Guller wrote:
scala> rdd.registerTempTable("x")
scala> val sRdd = sqlContext.sql("select a from x where ts >= '2012-01-01T00:00:00';")
sRdd: org.apache.spark.sql.SchemaRDD =
SchemaRDD[4] at RDD at SchemaRDD.scala:103
== Query Plan ==
== Physical Plan ==
Project [a#0]
ExistingRdd [a#0,ts#1], MapPartitionsRDD[6] at mapPartitions at basicOperators.scala:208
scala> sRdd.collect
res2: Array[org.apache.spark.sql.Row] = Array()
RE: Spark SQL parser bug?
Posted by "Cheng, Hao" <ha...@intel.com>.
Hi, I couldn’t reproduce the bug with the latest master branch. Which version are you using? Can you also list data in the table “x”?
case class T(a:String, ts:java.sql.Timestamp)
val sqlContext = new org.apache.spark.sql.SQLContext(sc)
import sqlContext.createSchemaRDD
val data = sc.parallelize(10000::20000::Nil).map(i=> T(i.toString, new java.sql.Timestamp(i)))
data.registerTempTable("x")
val s = sqlContext.sql("select a from x where ts>='1970-01-01 00:00:00';")
s.collect
output:
res1: Array[org.apache.spark.sql.Row] = Array([10000], [20000])
Cheng Hao
From: Mohammed Guller [mailto:mohammed@glassbeam.com]
Sent: Sunday, October 12, 2014 12:06 AM
To: Cheng Lian; user@spark.apache.org
Subject: RE: Spark SQL parser bug?
I tried even without the “T” and it still returns an empty result:
scala> val sRdd = sqlContext.sql("select a from x where ts >= '2012-01-01 00:00:00';")
sRdd: org.apache.spark.sql.SchemaRDD =
SchemaRDD[35] at RDD at SchemaRDD.scala:103
== Query Plan ==
== Physical Plan ==
Project [a#0]
ExistingRdd [a#0,ts#1], MapPartitionsRDD[37] at mapPartitions at basicOperators.scala:208
scala> sRdd.collect
res10: Array[org.apache.spark.sql.Row] = Array()
Mohammed
From: Cheng Lian [mailto:lian.cs.zju@gmail.com]
Sent: Friday, October 10, 2014 10:14 PM
To: Mohammed Guller; user@spark.apache.org<ma...@spark.apache.org>
Subject: Re: Spark SQL parser bug?
Hmm, there is a “T” in the timestamp string, which makes the string not a valid timestamp string representation. Internally Spark SQL uses java.sql.Timestamp.valueOf to cast a string to a timestamp.
On 10/11/14 2:08 AM, Mohammed Guller wrote:
scala> rdd.registerTempTable("x")
scala> val sRdd = sqlContext.sql("select a from x where ts >= '2012-01-01T00:00:00';")
sRdd: org.apache.spark.sql.SchemaRDD =
SchemaRDD[4] at RDD at SchemaRDD.scala:103
== Query Plan ==
== Physical Plan ==
Project [a#0]
ExistingRdd [a#0,ts#1], MapPartitionsRDD[6] at mapPartitions at basicOperators.scala:208
scala> sRdd.collect
res2: Array[org.apache.spark.sql.Row] = Array()
RE: Spark SQL parser bug?
Posted by Mohammed Guller <mo...@glassbeam.com>.
I tried even without the “T” and it still returns an empty result:
scala> val sRdd = sqlContext.sql("select a from x where ts >= '2012-01-01 00:00:00';")
sRdd: org.apache.spark.sql.SchemaRDD =
SchemaRDD[35] at RDD at SchemaRDD.scala:103
== Query Plan ==
== Physical Plan ==
Project [a#0]
ExistingRdd [a#0,ts#1], MapPartitionsRDD[37] at mapPartitions at basicOperators.scala:208
scala> sRdd.collect
res10: Array[org.apache.spark.sql.Row] = Array()
Mohammed
From: Cheng Lian [mailto:lian.cs.zju@gmail.com]
Sent: Friday, October 10, 2014 10:14 PM
To: Mohammed Guller; user@spark.apache.org
Subject: Re: Spark SQL parser bug?
Hmm, there is a “T” in the timestamp string, which makes the string not a valid timestamp string representation. Internally Spark SQL uses java.sql.Timestamp.valueOf to cast a string to a timestamp.
On 10/11/14 2:08 AM, Mohammed Guller wrote:
scala> rdd.registerTempTable("x")
scala> val sRdd = sqlContext.sql("select a from x where ts >= '2012-01-01T00:00:00';")
sRdd: org.apache.spark.sql.SchemaRDD =
SchemaRDD[4] at RDD at SchemaRDD.scala:103
== Query Plan ==
== Physical Plan ==
Project [a#0]
ExistingRdd [a#0,ts#1], MapPartitionsRDD[6] at mapPartitions at basicOperators.scala:208
scala> sRdd.collect
res2: Array[org.apache.spark.sql.Row] = Array()
Re: Spark SQL parser bug?
Posted by Cheng Lian <li...@gmail.com>.
Hmm, there is a “T” in the timestamp string, which makes the string not
a valid timestamp string representation. Internally Spark SQL uses
|java.sql.Timestamp.valueOf| to cast a string to a timestamp.
On 10/11/14 2:08 AM, Mohammed Guller wrote:
> scala> rdd.registerTempTable("x")
>
> scala> val sRdd = sqlContext.sql("select a from x where ts >=
> '2012-01-01*T*00:00:00';")
>
> sRdd: org.apache.spark.sql.SchemaRDD =
>
> SchemaRDD[4] at RDD at SchemaRDD.scala:103
>
> == Query Plan ==
>
> == Physical Plan ==
>
> Project [a#0]
>
> ExistingRdd [a#0,ts#1], MapPartitionsRDD[6] at mapPartitions at
> basicOperators.scala:208
>
> scala> sRdd.collect
>
> res2: Array[org.apache.spark.sql.Row] = Array()
>
RE: Spark SQL parser bug?
Posted by Mohammed Guller <mo...@glassbeam.com>.
Hi Chen,
Thanks for looking into this.
It looks like the bug may be in the Spark Cassandra connector code. Table x is a table in Cassandra.
However, while trying to troubleshoot this issue, I noticed another issue. This time I did not use Cassandra; instead created a table on the fly. I am not seeing the same issue, but the results do not like right. Here is a my complete Spark-shell session:
Spark assembly has been built with Hive, including Datanucleus jars on classpath
Welcome to
____ __
/ __/__ ___ _____/ /__
_\ \/ _ \/ _ `/ __/ '_/
/___/ .__/\_,_/_/ /_/\_\ version 1.1.0
/_/
Using Scala version 2.10.4 (Java HotSpot(TM) 64-Bit Server VM, Java 1.7.0_67)
Type in expressions to have them evaluated.
Type :help for more information.
14/10/10 11:05:11 WARN Utils: Your hostname, ubuntu resolves to a loopback address: 127.0.1.1; using 192.168.59.135 instead (on interface eth0)
14/10/10 11:05:11 WARN Utils: Set SPARK_LOCAL_IP if you need to bind to another address
14/10/10 11:05:14 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Spark context available as sc.
scala> import org.apache.spark.sql._
import org.apache.spark.sql._
scala> val sqlContext = new SQLContext(sc)
sqlContext: org.apache.spark.sql.SQLContext = org.apache.spark.sql.SQLContext@2be5c74d
scala> import sqlContext.createSchemaRDD
import sqlContext.createSchemaRDD
scala> case class X(a: Int, ts: java.sql.Timestamp)
defined class X
scala> val rdd = sc.parallelize( 1 to 5).map{ n => X(n, new java.sql.Timestamp(1325548800000L + n*86400000))}
rdd: org.apache.spark.rdd.RDD[X] = MappedRDD[1] at map at <console>:20
scala> rdd.collect
res0: Array[X] = Array(X(1,2012-01-03 16:00:00.0), X(2,2012-01-04 16:00:00.0), X(3,2012-01-05 16:00:00.0), X(4,2012-01-06 16:00:00.0), X(5,2012-01-07 16:00:00.0))
scala> rdd.registerTempTable("x")
scala> val sRdd = sqlContext.sql("select a from x where ts >= '2012-01-01T00:00:00';")
sRdd: org.apache.spark.sql.SchemaRDD =
SchemaRDD[4] at RDD at SchemaRDD.scala:103
== Query Plan ==
== Physical Plan ==
Project [a#0]
ExistingRdd [a#0,ts#1], MapPartitionsRDD[6] at mapPartitions at basicOperators.scala:208
scala> sRdd.collect
res2: Array[org.apache.spark.sql.Row] = Array()
Mohammed
From: Cheng Lian [mailto:lian.cs.zju@gmail.com]
Sent: Friday, October 10, 2014 4:37 AM
To: Mohammed Guller; user@spark.apache.org
Subject: Re: Spark SQL parser bug?
Hi Mohammed,
Would you mind to share the DDL of the table x and the complete stacktrace of the exception you got? A full Spark shell session history would be more than helpful. PR #2084 had been merged in master in Aug, and timestamp type is supported in 1.1.
I tried the following snippets in Spark shell (v1.1), and didn’t observe this issue:
scala> import org.apache.spark.sql._
import org.apache.spark.sql._
scala> import sc._
import sc._
scala> val sqlContext = new SQLContext(sc)
sqlContext: org.apache.spark.sql.SQLContext = org.apache.spark.sql.SQLContext@6c3441c5<ma...@6c3441c5>
scala> import sqlContext._
import sqlContext._
scala> case class Record(a: Int, ts: java.sql.Timestamp)
defined class Record
scala> makeRDD(Seq.empty[Record], 1).registerTempTable("x")
scala> sql("SELECT a FROM x WHERE ts >= '2012-01-01T00:00:00' AND ts <= '2012-03-31T23:59:59'")
res1: org.apache.spark.sql.SchemaRDD =
SchemaRDD[3] at RDD at SchemaRDD.scala:103
== Query Plan ==
== Physical Plan ==
Project [a#0]
ExistingRdd [a#0,ts#1], MapPartitionsRDD[5] at mapPartitions at basicOperators.scala:208
scala> res1.collect()
...
res2: Array[org.apache.spark.sql.Row] = Array()
Cheng
On 10/9/14 10:26 AM, Mohammed Guller wrote:
Hi –
When I run the following Spark SQL query in Spark-shell ( version 1.1.0) :
val rdd = sqlContext.sql("SELECT a FROM x WHERE ts >= '2012-01-01T00:00:00' AND ts <= '2012-03-31T23:59:59' ")
it gives the following error:
rdd: org.apache.spark.sql.SchemaRDD =
SchemaRDD[294] at RDD at SchemaRDD.scala:103
== Query Plan ==
== Physical Plan ==
java.util.NoSuchElementException: head of empty list
The ts column in the where clause has timestamp data and is of type timestamp. If I replace the string '2012-01-01T00:00:00' in the where clause with its epoch value, then the query works fine.
It looks I have run into an issue described in this pull request: https://github.com/apache/spark/pull/2084
Is that PR not merged in Spark version 1.1.0? Or am I missing something?
Thanks,
Mohammed
Re: Spark SQL parser bug?
Posted by Cheng Lian <li...@gmail.com>.
Hi Mohammed,
Would you mind to share the DDL of the table |x| and the complete
stacktrace of the exception you got? A full Spark shell session history
would be more than helpful. PR #2084 had been merged in master in Aug,
and timestamp type is supported in 1.1.
I tried the following snippets in Spark shell (v1.1), and didn’t observe
this issue:
|scala> import org.apache.spark.sql._
import org.apache.spark.sql._
scala> import sc._
import sc._
scala> val sqlContext = new SQLContext(sc)
sqlContext: org.apache.spark.sql.SQLContext = org.apache.spark.sql.SQLContext@6c3441c5
scala> import sqlContext._
import sqlContext._
scala> case class Record(a: Int, ts: java.sql.Timestamp)
defined class Record
scala> makeRDD(Seq.empty[Record], 1).registerTempTable("x")
scala> sql("SELECT a FROM x WHERE ts >= '2012-01-01T00:00:00' AND ts <= '2012-03-31T23:59:59'")
res1: org.apache.spark.sql.SchemaRDD =
SchemaRDD[3] at RDD at SchemaRDD.scala:103
== Query Plan ==
== Physical Plan ==
Project [a#0]
ExistingRdd [a#0,ts#1], MapPartitionsRDD[5] at mapPartitions at basicOperators.scala:208
scala> res1.collect()
...
res2: Array[org.apache.spark.sql.Row] = Array()
|
Cheng
On 10/9/14 10:26 AM, Mohammed Guller wrote:
> Hi –
>
> When I run the following Spark SQL query in Spark-shell ( version 1.1.0) :
>
> val rdd = sqlContext.sql("SELECT a FROM x WHERE ts >=
> '2012-01-01T00:00:00' AND ts <= '2012-03-31T23:59:59' ")
>
> it gives the following error:
>
> rdd: org.apache.spark.sql.SchemaRDD =
>
> SchemaRDD[294] at RDD at SchemaRDD.scala:103
>
> == Query Plan ==
>
> == Physical Plan ==
>
> java.util.NoSuchElementException: head of empty list
>
> The ts column in the where clause has timestamp data and is of type
> timestamp. If I replace the string '2012-01-01T00:00:00' in the where
> clause with its epoch value, then the query works fine.
>
> It looks I have run into an issue described in this pull request:
> https://github.com/apache/spark/pull/2084
>
> Is that PR not merged in Spark version 1.1.0? Or am I missing something?
>
> Thanks,
>
> Mohammed
>