[jira] [Comment Edited] (SPARK-11788) Using java.sql.Timestamp and java.sql.Date in where clauses on JDBC dataframes causes SQLServerException
[ https://issues.apache.org/jira/browse/SPARK-11788?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15964438#comment-15964438 ] Björn-Elmar Macek edited comment on SPARK-11788 at 4/11/17 2:39 PM: I have an issue which maybe related and occurs in 2.1.0. I created a table which contains a Timestamp column. An describe results in: user_id int null userCreatedAt timestamp null country_iso string null When i query as follows ... select date_format(userCreatedAt, ""), userCreatedAt, date_format(userCreatedAt, "") = "2017" from result where date_format(userCreatedAt, "") = "2017" order by userCreatedAt ... the third column contains only true values as it should be due to the corresponding expression being in the where clause. When i execute the same query on the same table in which i replaced userCreatedAt by a java.sql.Timestamp created from the userCreatedAt column, the result contains lots of false values. This is correct, since the year of those timestamp is not 2017. But i would expect them to not be included in the result. Also, date_format(userCreatedAt, "") returns the correct year. Can anybody reproduce this issue? was (Author: macek): I have an issue which maybe related and occurs in 2.1.0. I created a table which contains a Timestamp column. An describe results in: user_id int null userCreatedAt timestamp null country_iso string null When i query as follows ... select date_format(userCreatedAt, ""), userCreatedAt, date_format(userCreatedAt, "") = "2017" from result where date_format(userCreatedAt, "") = "2017" order by userCreatedAt ... the third column contains only true values as it should be due to the corresponding expression being in the where clause. When i execute the same query on the same table in which i replaced userCreatedAt by a java.sql.Timestamp created from the userCreatedAt column, the result contains lots of false values. This is correct, since the year of those timestamp is not 2017. But i would expect them to not be included in the result. Also, date_format(userCreatedAt, "") returns the correct year. Can anybody reproduce this issue and will it be fixed? > Using java.sql.Timestamp and java.sql.Date in where clauses on JDBC > dataframes causes SQLServerException > > > Key: SPARK-11788 > URL: https://issues.apache.org/jira/browse/SPARK-11788 > Project: Spark > Issue Type: Bug > Components: SQL >Affects Versions: 1.5.2 >Reporter: Martin Tapp >Assignee: Huaxin Gao > Fix For: 1.5.3, 1.6.0 > > > I have a MSSQL table that has a timestamp column and am reading it using > DataFrameReader.jdbc. Adding a where clause which compares a timestamp range > causes a SQLServerException. > The problem is in > https://github.com/apache/spark/blob/master/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JDBCRDD.scala#L264 > (compileValue) which should surround timestamps/dates with quotes (only does > it for strings). > Sample pseudo-code: > val beg = new java.sql.Timestamp(...) > val end = new java.sql.Timestamp(...) > val filtered = jdbcdf.where($"TIMESTAMP_COLUMN" >= beg && $"TIMESTAMP_COLUMN" > < end) > Generated SQL query: "TIMESTAMP_COLUMN >= 2015-01-01 00:00:00.0" > Query should use quotes around timestamp: "TIMESTAMP_COLUMN >= '2015-01-01 > 00:00:00.0'" > Fallback is to filter client-side which is extremely inefficient as the whole > table needs to be downloaded to each Spark executor. > Thanks -- This message was sent by Atlassian JIRA (v6.3.15#6346) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Comment Edited] (SPARK-11788) Using java.sql.Timestamp and java.sql.Date in where clauses on JDBC dataframes causes SQLServerException
[ https://issues.apache.org/jira/browse/SPARK-11788?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15964438#comment-15964438 ] Björn-Elmar Macek edited comment on SPARK-11788 at 4/11/17 2:34 PM: I have an issue which maybe related and occurs in 2.1.0. I created a table which contains a Timestamp column. An describe results in: user_id int null userCreatedAt timestamp null country_iso string null When i query as follows ... select date_format(userCreatedAt, ""), userCreatedAt, date_format(userCreatedAt, "") = "2017" from result where date_format(userCreatedAt, "") = "2017" order by userCreatedAt ... the third column contains only true values as it should be due to the corresponding expression being in the where clause. When i execute the same query on the same table in which i replaced userCreatedAt by a java.sql.Timestamp created from the userCreatedAt column, the result contains lots of false values. This is correct, since the year of those timestamp is not 2017. But i would expect them to not be included in the result. Also, date_format(userCreatedAt, "") returns the correct year. Can anybody reproduce this issue and will it be fixed? was (Author: macek): I have an issue which maybe related and occurs in 2.1.0. I created a table which contains a Timestamp column. An describe results in: user_id int null userCreatedAt timestamp null country_iso string null When i query as follows ... select date_format(userCreatedAt, ""), userCreatedAt, date_format(userCreatedAt, "") = "2017" from result where date_format(userCreatedAt, "") = "2017" order by userCreatedAt ... the third column contains only true values as it should be due to the expression being in the where clause. When i execute the same query on the same table in which i replaced userCreatedAt by a java.sql.Timestamp created from the userCreatedAt column, the result contains lots of false values. This is correct, since the year of those timestamp is not 2017. But i would expect them to not be included in the result. Also, date_format(userCreatedAt, "") returns the correct year. Can anybody reproduce this issue and will it be fixed? > Using java.sql.Timestamp and java.sql.Date in where clauses on JDBC > dataframes causes SQLServerException > > > Key: SPARK-11788 > URL: https://issues.apache.org/jira/browse/SPARK-11788 > Project: Spark > Issue Type: Bug > Components: SQL >Affects Versions: 1.5.2 >Reporter: Martin Tapp >Assignee: Huaxin Gao > Fix For: 1.5.3, 1.6.0 > > > I have a MSSQL table that has a timestamp column and am reading it using > DataFrameReader.jdbc. Adding a where clause which compares a timestamp range > causes a SQLServerException. > The problem is in > https://github.com/apache/spark/blob/master/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JDBCRDD.scala#L264 > (compileValue) which should surround timestamps/dates with quotes (only does > it for strings). > Sample pseudo-code: > val beg = new java.sql.Timestamp(...) > val end = new java.sql.Timestamp(...) > val filtered = jdbcdf.where($"TIMESTAMP_COLUMN" >= beg && $"TIMESTAMP_COLUMN" > < end) > Generated SQL query: "TIMESTAMP_COLUMN >= 2015-01-01 00:00:00.0" > Query should use quotes around timestamp: "TIMESTAMP_COLUMN >= '2015-01-01 > 00:00:00.0'" > Fallback is to filter client-side which is extremely inefficient as the whole > table needs to be downloaded to each Spark executor. > Thanks -- This message was sent by Atlassian JIRA (v6.3.15#6346) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Comment Edited] (SPARK-11788) Using java.sql.Timestamp and java.sql.Date in where clauses on JDBC dataframes causes SQLServerException
[ https://issues.apache.org/jira/browse/SPARK-11788?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15964438#comment-15964438 ] Björn-Elmar Macek edited comment on SPARK-11788 at 4/11/17 2:28 PM: I have an issue which maybe related and occurs in 2.1.0. I created a table which contains a Timestamp column. An describe results in: user_id int null userCreatedAt timestamp null country_iso string null When i query as follows ... select date_format(userCreatedAt, ""), userCreatedAt, date_format(userCreatedAt, "") = "2017" from result where date_format(userCreatedAt, "") = "2017" order by userCreatedAt ... the third column contains only true values as it should be due to the expression being in the where clause. When i execute the same query on the same table in which i replaced userCreatedAt by a java.sql.Timestamp created from the userCreatedAt column, the result contains lots of false values. This is correct, since the year of those timestamp is not 2017. But i would expect them to not be included in the result. Also, date_format(userCreatedAt, "") returns the correct year. Can anybody reproduce this issue and will it be fixed? was (Author: macek): I have an issue which maybe related and occurs in 2.1.0. I created a table which contains a Timestamp column. An describe results in: user_id int null userCreatedAt timestamp null country_iso string null When i query as follows ... select date_format(userCreatedAt, ""), userCreatedAt, date_format(userCreatedAt, "") = "2017" from result where date_format(userCreatedAt, "") = "2017" order by userCreatedAt ... the third column contains only true values as it should be due to the expression being in the where clause. When i execute the same query on the same table in which i replaced userCreatedAt by a java.sql.Timestamp created from the userCreatedAt column, the result contains lots of false values. Also, date_format(userCreatedAt, "") returns the correct year. Can anybody reproduce this issue and will it be fixed? > Using java.sql.Timestamp and java.sql.Date in where clauses on JDBC > dataframes causes SQLServerException > > > Key: SPARK-11788 > URL: https://issues.apache.org/jira/browse/SPARK-11788 > Project: Spark > Issue Type: Bug > Components: SQL >Affects Versions: 1.5.2 >Reporter: Martin Tapp >Assignee: Huaxin Gao > Fix For: 1.5.3, 1.6.0 > > > I have a MSSQL table that has a timestamp column and am reading it using > DataFrameReader.jdbc. Adding a where clause which compares a timestamp range > causes a SQLServerException. > The problem is in > https://github.com/apache/spark/blob/master/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JDBCRDD.scala#L264 > (compileValue) which should surround timestamps/dates with quotes (only does > it for strings). > Sample pseudo-code: > val beg = new java.sql.Timestamp(...) > val end = new java.sql.Timestamp(...) > val filtered = jdbcdf.where($"TIMESTAMP_COLUMN" >= beg && $"TIMESTAMP_COLUMN" > < end) > Generated SQL query: "TIMESTAMP_COLUMN >= 2015-01-01 00:00:00.0" > Query should use quotes around timestamp: "TIMESTAMP_COLUMN >= '2015-01-01 > 00:00:00.0'" > Fallback is to filter client-side which is extremely inefficient as the whole > table needs to be downloaded to each Spark executor. > Thanks -- This message was sent by Atlassian JIRA (v6.3.15#6346) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Comment Edited] (SPARK-11788) Using java.sql.Timestamp and java.sql.Date in where clauses on JDBC dataframes causes SQLServerException
[ https://issues.apache.org/jira/browse/SPARK-11788?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15022845#comment-15022845 ] Huaxin Gao edited comment on SPARK-11788 at 11/23/15 7:49 PM: -- Sorry, I just realized that actually I checked in the $"B" === date && $"C" === timestamp instead of the $"B" > date && $"C" > timestamp I will change. Never mind. It is $"B" > date && $"C" > timestamp I have multiple test case and I confused myself. was (Author: huaxing): Sorry, I just realized that actually I checked in the $"B" === date && $"C" === timestamp instead of the $"B" > date && $"C" > timestamp I will change. > Using java.sql.Timestamp and java.sql.Date in where clauses on JDBC > dataframes causes SQLServerException > > > Key: SPARK-11788 > URL: https://issues.apache.org/jira/browse/SPARK-11788 > Project: Spark > Issue Type: Bug > Components: SQL >Affects Versions: 1.5.2 >Reporter: Martin Tapp > > I have a MSSQL table that has a timestamp column and am reading it using > DataFrameReader.jdbc. Adding a where clause which compares a timestamp range > causes a SQLServerException. > The problem is in > https://github.com/apache/spark/blob/master/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JDBCRDD.scala#L264 > (compileValue) which should surround timestamps/dates with quotes (only does > it for strings). > Sample pseudo-code: > val beg = new java.sql.Timestamp(...) > val end = new java.sql.Timestamp(...) > val filtered = jdbcdf.where($"TIMESTAMP_COLUMN" >= beg && $"TIMESTAMP_COLUMN" > < end) > Generated SQL query: "TIMESTAMP_COLUMN >= 2015-01-01 00:00:00.0" > Query should use quotes around timestamp: "TIMESTAMP_COLUMN >= '2015-01-01 > 00:00:00.0'" > Fallback is to filter client-side which is extremely inefficient as the whole > table needs to be downloaded to each Spark executor. > Thanks -- This message was sent by Atlassian JIRA (v6.3.4#6332) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Comment Edited] (SPARK-11788) Using java.sql.Timestamp and java.sql.Date in where clauses on JDBC dataframes causes SQLServerException
[ https://issues.apache.org/jira/browse/SPARK-11788?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15018511#comment-15018511 ] Martin Tapp edited comment on SPARK-11788 at 11/20/15 6:53 PM: --- Any update, this seems like an easy fix? was (Author: doctapp): Any udpate, this seems like an easy fix? > Using java.sql.Timestamp and java.sql.Date in where clauses on JDBC > dataframes causes SQLServerException > > > Key: SPARK-11788 > URL: https://issues.apache.org/jira/browse/SPARK-11788 > Project: Spark > Issue Type: Bug > Components: SQL >Affects Versions: 1.5.2 >Reporter: Martin Tapp > > I have a MSSQL table that has a timestamp column and am reading it using > DataFrameReader.jdbc. Adding a where clause which compares a timestamp range > causes a SQLServerException. > The problem is in > https://github.com/apache/spark/blob/master/sql/core/src/main/scala/org/apache/spark/sql/execution/datasources/jdbc/JDBCRDD.scala#L264 > (compileValue) which should surround timestamps/dates with quotes (only does > it for strings). > Sample pseudo-code: > val beg = new java.sql.Timestamp(...) > val end = new java.sql.Timestamp(...) > val filtered = jdbcdf.where($"TIMESTAMP_COLUMN" >= beg && $"TIMESTAMP_COLUMN" > < end) > Generated SQL query: "TIMESTAMP_COLUMN >= 2015-01-01 00:00:00.0" > Query should use quotes around timestamp: "TIMESTAMP_COLUMN >= '2015-01-01 > 00:00:00.0'" > Fallback is to filter client-side which is extremely inefficient as the whole > table needs to be downloaded to each Spark executor. > Thanks -- This message was sent by Atlassian JIRA (v6.3.4#6332) - To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org
