[jira] [Commented] (SPARK-20700) InferFiltersFromConstraints stackoverflows for query (v2)
[
https://issues.apache.org/jira/browse/SPARK-20700?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16014458#comment-16014458
]
Apache Spark commented on SPARK-20700:
--
User 'jiangxb1987' has created a pull request for this issue:
https://github.com/apache/spark/pull/18020
> InferFiltersFromConstraints stackoverflows for query (v2)
> -
>
> Key: SPARK-20700
> URL: https://issues.apache.org/jira/browse/SPARK-20700
> Project: Spark
> Issue Type: Bug
> Components: Optimizer, SQL
>Affects Versions: 2.2.0
>Reporter: Josh Rosen
>Assignee: Jiang Xingbo
>
> The following (complicated) query eventually fails with a stack overflow
> during optimization:
> {code}
> CREATE TEMPORARY VIEW table_5(varchar0002_col_1, smallint_col_2, float_col_3,
> int_col_4, string_col_5, timestamp_col_6, string_col_7) AS VALUES
> ('68', CAST(NULL AS SMALLINT), CAST(244.90413 AS FLOAT), -137, '571',
> TIMESTAMP('2015-01-14 00:00:00.0'), '947'),
> ('82', CAST(213 AS SMALLINT), CAST(53.184647 AS FLOAT), -724, '-278',
> TIMESTAMP('1999-08-15 00:00:00.0'), '437'),
> ('-7', CAST(-15 AS SMALLINT), CAST(NULL AS FLOAT), -890, '778',
> TIMESTAMP('1991-05-23 00:00:00.0'), '630'),
> ('22', CAST(676 AS SMALLINT), CAST(385.27386 AS FLOAT), CAST(NULL AS INT),
> '-10', TIMESTAMP('1996-09-29 00:00:00.0'), '641'),
> ('16', CAST(430 AS SMALLINT), CAST(187.23717 AS FLOAT), 989, CAST(NULL AS
> STRING), TIMESTAMP('2024-04-21 00:00:00.0'), '-234'),
> ('83', CAST(760 AS SMALLINT), CAST(-695.45386 AS FLOAT), -970, '330',
> CAST(NULL AS TIMESTAMP), '-740'),
> ('68', CAST(-930 AS SMALLINT), CAST(NULL AS FLOAT), -915, '-766', CAST(NULL
> AS TIMESTAMP), CAST(NULL AS STRING)),
> ('48', CAST(692 AS SMALLINT), CAST(-220.59615 AS FLOAT), 940, '-514',
> CAST(NULL AS TIMESTAMP), '181'),
> ('21', CAST(44 AS SMALLINT), CAST(NULL AS FLOAT), -175, '761',
> TIMESTAMP('2016-06-30 00:00:00.0'), '487'),
> ('50', CAST(953 AS SMALLINT), CAST(837.2948 AS FLOAT), 705, CAST(NULL AS
> STRING), CAST(NULL AS TIMESTAMP), '-62');
> CREATE VIEW bools(a, b) as values (1, true), (1, true), (1, null);
> SELECT
> AVG(-13) OVER (ORDER BY COUNT(t1.smallint_col_2) DESC ROWS 27 PRECEDING ) AS
> float_col,
> COUNT(t1.smallint_col_2) AS int_col
> FROM table_5 t1
> INNER JOIN (
> SELECT
> (MIN(-83) OVER (PARTITION BY t2.a ORDER BY t2.a, (t1.int_col_4) *
> (t1.int_col_4) ROWS BETWEEN CURRENT ROW AND 15 FOLLOWING)) NOT IN (-222, 928)
> AS boolean_col,
> t2.a,
> (t1.int_col_4) * (t1.int_col_4) AS int_col
> FROM table_5 t1
> LEFT JOIN bools t2 ON (t2.a) = (t1.int_col_4)
> WHERE
> (t1.smallint_col_2) > (t1.smallint_col_2)
> GROUP BY
> t2.a,
> (t1.int_col_4) * (t1.int_col_4)
> HAVING
> ((t1.int_col_4) * (t1.int_col_4)) IN ((t1.int_col_4) * (t1.int_col_4),
> SUM(t1.int_col_4))
> ) t2 ON (((t2.int_col) = (t1.int_col_4)) AND ((t2.a) = (t1.int_col_4))) AND
> ((t2.a) = (t1.smallint_col_2));
> {code}
> (I haven't tried to minimize this failing case yet).
> Based on sampled jstacks from the driver, it looks like the query might be
> repeatedly inferring filters from constraints and then pruning those filters.
> Here's part of the stack at the point where it stackoverflows:
> {code}
> [... repeats ...]
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$.org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative(Canonicalize.scala:50)
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$$anonfun$org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative$1.apply(Canonicalize.scala:50)
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$$anonfun$org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative$1.apply(Canonicalize.scala:50)
> at
> scala.collection.TraversableLike$$anonfun$flatMap$1.apply(TraversableLike.scala:241)
> at
> scala.collection.TraversableLike$$anonfun$flatMap$1.apply(TraversableLike.scala:241)
> at scala.collection.immutable.List.foreach(List.scala:381)
> at
> scala.collection.TraversableLike$class.flatMap(TraversableLike.scala:241)
> at scala.collection.immutable.List.flatMap(List.scala:344)
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$.org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative(Canonicalize.scala:50)
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$$anonfun$org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative$1.apply(Canonicalize.scala:50)
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$$anonfun$org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative$1.apply(Canonicalize.scala:50)
> at
>
[jira] [Commented] (SPARK-20700) InferFiltersFromConstraints stackoverflows for query (v2)
[
https://issues.apache.org/jira/browse/SPARK-20700?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16013199#comment-16013199
]
Jiang Xingbo commented on SPARK-20700:
--
In the previous approach we used `aliasMap` to link an `Attribute` to the
expression with potentially the form `f(a, b)`, but we only searched the
`expressions` and `children.expressions` for this, which is not enough when an
`Alias` may lies deep in the logical plan. In that case, we can't generate the
valid equivalent constraint classes and thus we fail to prevent the recursive
deductions.
I'll send a PR to fix this later today.
> InferFiltersFromConstraints stackoverflows for query (v2)
> -
>
> Key: SPARK-20700
> URL: https://issues.apache.org/jira/browse/SPARK-20700
> Project: Spark
> Issue Type: Bug
> Components: Optimizer, SQL
>Affects Versions: 2.2.0
>Reporter: Josh Rosen
>Assignee: Jiang Xingbo
>
> The following (complicated) query eventually fails with a stack overflow
> during optimization:
> {code}
> CREATE TEMPORARY VIEW table_5(varchar0002_col_1, smallint_col_2, float_col_3,
> int_col_4, string_col_5, timestamp_col_6, string_col_7) AS VALUES
> ('68', CAST(NULL AS SMALLINT), CAST(244.90413 AS FLOAT), -137, '571',
> TIMESTAMP('2015-01-14 00:00:00.0'), '947'),
> ('82', CAST(213 AS SMALLINT), CAST(53.184647 AS FLOAT), -724, '-278',
> TIMESTAMP('1999-08-15 00:00:00.0'), '437'),
> ('-7', CAST(-15 AS SMALLINT), CAST(NULL AS FLOAT), -890, '778',
> TIMESTAMP('1991-05-23 00:00:00.0'), '630'),
> ('22', CAST(676 AS SMALLINT), CAST(385.27386 AS FLOAT), CAST(NULL AS INT),
> '-10', TIMESTAMP('1996-09-29 00:00:00.0'), '641'),
> ('16', CAST(430 AS SMALLINT), CAST(187.23717 AS FLOAT), 989, CAST(NULL AS
> STRING), TIMESTAMP('2024-04-21 00:00:00.0'), '-234'),
> ('83', CAST(760 AS SMALLINT), CAST(-695.45386 AS FLOAT), -970, '330',
> CAST(NULL AS TIMESTAMP), '-740'),
> ('68', CAST(-930 AS SMALLINT), CAST(NULL AS FLOAT), -915, '-766', CAST(NULL
> AS TIMESTAMP), CAST(NULL AS STRING)),
> ('48', CAST(692 AS SMALLINT), CAST(-220.59615 AS FLOAT), 940, '-514',
> CAST(NULL AS TIMESTAMP), '181'),
> ('21', CAST(44 AS SMALLINT), CAST(NULL AS FLOAT), -175, '761',
> TIMESTAMP('2016-06-30 00:00:00.0'), '487'),
> ('50', CAST(953 AS SMALLINT), CAST(837.2948 AS FLOAT), 705, CAST(NULL AS
> STRING), CAST(NULL AS TIMESTAMP), '-62');
> CREATE VIEW bools(a, b) as values (1, true), (1, true), (1, null);
> SELECT
> AVG(-13) OVER (ORDER BY COUNT(t1.smallint_col_2) DESC ROWS 27 PRECEDING ) AS
> float_col,
> COUNT(t1.smallint_col_2) AS int_col
> FROM table_5 t1
> INNER JOIN (
> SELECT
> (MIN(-83) OVER (PARTITION BY t2.a ORDER BY t2.a, (t1.int_col_4) *
> (t1.int_col_4) ROWS BETWEEN CURRENT ROW AND 15 FOLLOWING)) NOT IN (-222, 928)
> AS boolean_col,
> t2.a,
> (t1.int_col_4) * (t1.int_col_4) AS int_col
> FROM table_5 t1
> LEFT JOIN bools t2 ON (t2.a) = (t1.int_col_4)
> WHERE
> (t1.smallint_col_2) > (t1.smallint_col_2)
> GROUP BY
> t2.a,
> (t1.int_col_4) * (t1.int_col_4)
> HAVING
> ((t1.int_col_4) * (t1.int_col_4)) IN ((t1.int_col_4) * (t1.int_col_4),
> SUM(t1.int_col_4))
> ) t2 ON (((t2.int_col) = (t1.int_col_4)) AND ((t2.a) = (t1.int_col_4))) AND
> ((t2.a) = (t1.smallint_col_2));
> {code}
> (I haven't tried to minimize this failing case yet).
> Based on sampled jstacks from the driver, it looks like the query might be
> repeatedly inferring filters from constraints and then pruning those filters.
> Here's part of the stack at the point where it stackoverflows:
> {code}
> [... repeats ...]
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$.org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative(Canonicalize.scala:50)
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$$anonfun$org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative$1.apply(Canonicalize.scala:50)
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$$anonfun$org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative$1.apply(Canonicalize.scala:50)
> at
> scala.collection.TraversableLike$$anonfun$flatMap$1.apply(TraversableLike.scala:241)
> at
> scala.collection.TraversableLike$$anonfun$flatMap$1.apply(TraversableLike.scala:241)
> at scala.collection.immutable.List.foreach(List.scala:381)
> at
> scala.collection.TraversableLike$class.flatMap(TraversableLike.scala:241)
> at scala.collection.immutable.List.flatMap(List.scala:344)
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$.org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative(Canonicalize.scala:50)
> at
>
[jira] [Commented] (SPARK-20700) InferFiltersFromConstraints stackoverflows for query (v2)
[
https://issues.apache.org/jira/browse/SPARK-20700?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16008286#comment-16008286
]
Jiang Xingbo commented on SPARK-20700:
--
I've reproduced this case, will dive further into it this weekend.
> InferFiltersFromConstraints stackoverflows for query (v2)
> -
>
> Key: SPARK-20700
> URL: https://issues.apache.org/jira/browse/SPARK-20700
> Project: Spark
> Issue Type: Bug
> Components: Optimizer, SQL
>Affects Versions: 2.2.0
>Reporter: Josh Rosen
>
> The following (complicated) query eventually fails with a stack overflow
> during optimization:
> {code}
> CREATE TEMPORARY VIEW table_5(varchar0002_col_1, smallint_col_2, float_col_3,
> int_col_4, string_col_5, timestamp_col_6, string_col_7) AS VALUES
> ('68', CAST(NULL AS SMALLINT), CAST(244.90413 AS FLOAT), -137, '571',
> TIMESTAMP('2015-01-14 00:00:00.0'), '947'),
> ('82', CAST(213 AS SMALLINT), CAST(53.184647 AS FLOAT), -724, '-278',
> TIMESTAMP('1999-08-15 00:00:00.0'), '437'),
> ('-7', CAST(-15 AS SMALLINT), CAST(NULL AS FLOAT), -890, '778',
> TIMESTAMP('1991-05-23 00:00:00.0'), '630'),
> ('22', CAST(676 AS SMALLINT), CAST(385.27386 AS FLOAT), CAST(NULL AS INT),
> '-10', TIMESTAMP('1996-09-29 00:00:00.0'), '641'),
> ('16', CAST(430 AS SMALLINT), CAST(187.23717 AS FLOAT), 989, CAST(NULL AS
> STRING), TIMESTAMP('2024-04-21 00:00:00.0'), '-234'),
> ('83', CAST(760 AS SMALLINT), CAST(-695.45386 AS FLOAT), -970, '330',
> CAST(NULL AS TIMESTAMP), '-740'),
> ('68', CAST(-930 AS SMALLINT), CAST(NULL AS FLOAT), -915, '-766', CAST(NULL
> AS TIMESTAMP), CAST(NULL AS STRING)),
> ('48', CAST(692 AS SMALLINT), CAST(-220.59615 AS FLOAT), 940, '-514',
> CAST(NULL AS TIMESTAMP), '181'),
> ('21', CAST(44 AS SMALLINT), CAST(NULL AS FLOAT), -175, '761',
> TIMESTAMP('2016-06-30 00:00:00.0'), '487'),
> ('50', CAST(953 AS SMALLINT), CAST(837.2948 AS FLOAT), 705, CAST(NULL AS
> STRING), CAST(NULL AS TIMESTAMP), '-62');
> CREATE VIEW bools(a, b) as values (1, true), (1, true), (1, null);
> SELECT
> AVG(-13) OVER (ORDER BY COUNT(t1.smallint_col_2) DESC ROWS 27 PRECEDING ) AS
> float_col,
> COUNT(t1.smallint_col_2) AS int_col
> FROM table_5 t1
> INNER JOIN (
> SELECT
> (MIN(-83) OVER (PARTITION BY t2.a ORDER BY t2.a, (t1.int_col_4) *
> (t1.int_col_4) ROWS BETWEEN CURRENT ROW AND 15 FOLLOWING)) NOT IN (-222, 928)
> AS boolean_col,
> t2.a,
> (t1.int_col_4) * (t1.int_col_4) AS int_col
> FROM table_5 t1
> LEFT JOIN bools t2 ON (t2.a) = (t1.int_col_4)
> WHERE
> (t1.smallint_col_2) > (t1.smallint_col_2)
> GROUP BY
> t2.a,
> (t1.int_col_4) * (t1.int_col_4)
> HAVING
> ((t1.int_col_4) * (t1.int_col_4)) IN ((t1.int_col_4) * (t1.int_col_4),
> SUM(t1.int_col_4))
> ) t2 ON (((t2.int_col) = (t1.int_col_4)) AND ((t2.a) = (t1.int_col_4))) AND
> ((t2.a) = (t1.smallint_col_2));
> {code}
> (I haven't tried to minimize this failing case yet).
> Based on sampled jstacks from the driver, it looks like the query might be
> repeatedly inferring filters from constraints and then pruning those filters.
> Here's part of the stack at the point where it stackoverflows:
> {code}
> [... repeats ...]
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$.org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative(Canonicalize.scala:50)
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$$anonfun$org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative$1.apply(Canonicalize.scala:50)
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$$anonfun$org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative$1.apply(Canonicalize.scala:50)
> at
> scala.collection.TraversableLike$$anonfun$flatMap$1.apply(TraversableLike.scala:241)
> at
> scala.collection.TraversableLike$$anonfun$flatMap$1.apply(TraversableLike.scala:241)
> at scala.collection.immutable.List.foreach(List.scala:381)
> at
> scala.collection.TraversableLike$class.flatMap(TraversableLike.scala:241)
> at scala.collection.immutable.List.flatMap(List.scala:344)
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$.org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative(Canonicalize.scala:50)
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$$anonfun$org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative$1.apply(Canonicalize.scala:50)
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$$anonfun$org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative$1.apply(Canonicalize.scala:50)
> at
> scala.collection.TraversableLike$$anonfun$flatMap$1.apply(TraversableLike.scala:241)
> at
>
[jira] [Commented] (SPARK-20700) InferFiltersFromConstraints stackoverflows for query (v2)
[
https://issues.apache.org/jira/browse/SPARK-20700?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16007731#comment-16007731
]
Jiang Xingbo commented on SPARK-20700:
--
I couldn't reproduce the failure on current master branch, the test case I use
is like the following:
{code}
test("SPARK-20700: InferFiltersFromConstraints stackoverflows for query") {
withTempView("table_5") {
withView("bools") {
sql(
"""CREATE TEMPORARY VIEW table_5(varchar0002_col_1, smallint_col_2,
float_col_3, int_col_4, string_col_5, timestamp_col_6, string_col_7) AS VALUES
| ('68', CAST(NULL AS SMALLINT), CAST(244.90413 AS FLOAT), -137,
'571', TIMESTAMP('2015-01-14 00:00:00.0'), '947'),
| ('82', CAST(213 AS SMALLINT), CAST(53.184647 AS FLOAT), -724,
'-278', TIMESTAMP('1999-08-15 00:00:00.0'), '437'),
| ('-7', CAST(-15 AS SMALLINT), CAST(NULL AS FLOAT), -890, '778',
TIMESTAMP('1991-05-23 00:00:00.0'), '630'),
| ('22', CAST(676 AS SMALLINT), CAST(385.27386 AS FLOAT),
CAST(NULL AS INT), '-10', TIMESTAMP('1996-09-29 00:00:00.0'), '641'),
| ('16', CAST(430 AS SMALLINT), CAST(187.23717 AS FLOAT), 989,
CAST(NULL AS STRING), TIMESTAMP('2024-04-21 00:00:00.0'), '-234'),
| ('83', CAST(760 AS SMALLINT), CAST(-695.45386 AS FLOAT), -970,
'330', CAST(NULL AS TIMESTAMP), '-740'),
| ('68', CAST(-930 AS SMALLINT), CAST(NULL AS FLOAT), -915,
'-766', CAST(NULL AS TIMESTAMP), CAST(NULL AS STRING)),
| ('48', CAST(692 AS SMALLINT), CAST(-220.59615 AS FLOAT), 940,
'-514', CAST(NULL AS TIMESTAMP), '181'),
| ('21', CAST(44 AS SMALLINT), CAST(NULL AS FLOAT), -175, '761',
TIMESTAMP('2016-06-30 00:00:00.0'), '487'),
| ('50', CAST(953 AS SMALLINT), CAST(837.2948 AS FLOAT), 705,
CAST(NULL AS STRING), CAST(NULL AS TIMESTAMP), '-62')
""".
stripMargin)
sql("CREATE VIEW bools(a, b) as values (1, true), (1, true), (1, null)")
sql(
"""
SELECT
|AVG(-13) OVER (ORDER BY COUNT(t1.smallint_col_2) DESC ROWS 27
PRECEDING ) AS float_col,
|COUNT(t1.smallint_col_2) AS int_col
|FROM table_5 t1
|INNER JOIN (
|SELECT
|(MIN(-83) OVER (PARTITION BY t2.a ORDER BY t2.a, (t1.int_col_4) *
(t1.int_col_4) ROWS BETWEEN CURRENT ROW AND 15 FOLLOWING)) NOT IN (-222, 928)
AS boolean_col,
|t2.a,
|(t1.int_col_4) * (t1.int_col_4) AS int_col
|FROM table_5 t1
|LEFT JOIN bools t2 ON (t2.a) = (t1.int_col_4)
|WHERE
|(t1.smallint_col_2) > (t1.smallint_col_2)
|GROUP BY
|t2.a,
|(t1.int_col_4) * (t1.int_col_4)
|HAVING
|((t1.int_col_4) * (t1.int_col_4)) IN ((t1.int_col_4) *
(t1.int_col_4), SUM(t1.int_col_4))
|) t2 ON (((t2.int_col) = (t1.int_col_4)) AND ((t2.a) =
(t1.int_col_4))) AND ((t2.a) = (t1.smallint_col_2))
""".stripMargin)
}
}
}
{code}
> InferFiltersFromConstraints stackoverflows for query (v2)
> -
>
> Key: SPARK-20700
> URL: https://issues.apache.org/jira/browse/SPARK-20700
> Project: Spark
> Issue Type: Bug
> Components: Optimizer, SQL
>Affects Versions: 2.2.0
>Reporter: Josh Rosen
>
> The following (complicated) query eventually fails with a stack overflow
> during optimization:
> {code}
> CREATE TEMPORARY VIEW table_5(varchar0002_col_1, smallint_col_2, float_col_3,
> int_col_4, string_col_5, timestamp_col_6, string_col_7) AS VALUES
> ('68', CAST(NULL AS SMALLINT), CAST(244.90413 AS FLOAT), -137, '571',
> TIMESTAMP('2015-01-14 00:00:00.0'), '947'),
> ('82', CAST(213 AS SMALLINT), CAST(53.184647 AS FLOAT), -724, '-278',
> TIMESTAMP('1999-08-15 00:00:00.0'), '437'),
> ('-7', CAST(-15 AS SMALLINT), CAST(NULL AS FLOAT), -890, '778',
> TIMESTAMP('1991-05-23 00:00:00.0'), '630'),
> ('22', CAST(676 AS SMALLINT), CAST(385.27386 AS FLOAT), CAST(NULL AS INT),
> '-10', TIMESTAMP('1996-09-29 00:00:00.0'), '641'),
> ('16', CAST(430 AS SMALLINT), CAST(187.23717 AS FLOAT), 989, CAST(NULL AS
> STRING), TIMESTAMP('2024-04-21 00:00:00.0'), '-234'),
> ('83', CAST(760 AS SMALLINT), CAST(-695.45386 AS FLOAT), -970, '330',
> CAST(NULL AS TIMESTAMP), '-740'),
> ('68', CAST(-930 AS SMALLINT), CAST(NULL AS FLOAT), -915, '-766', CAST(NULL
> AS TIMESTAMP), CAST(NULL AS STRING)),
> ('48', CAST(692 AS SMALLINT), CAST(-220.59615 AS FLOAT), 940, '-514',
> CAST(NULL AS TIMESTAMP), '181'),
> ('21', CAST(44 AS SMALLINT), CAST(NULL AS FLOAT), -175, '761',
> TIMESTAMP('2016-06-30 00:00:00.0'), '487'),
> ('50', CAST(953 AS SMALLINT), CAST(837.2948 AS FLOAT), 705, CAST(NULL AS
> STRING), CAST(NULL AS TIMESTAMP), '-62');
> CREATE VIEW
[jira] [Commented] (SPARK-20700) InferFiltersFromConstraints stackoverflows for query (v2)
[
https://issues.apache.org/jira/browse/SPARK-20700?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16006201#comment-16006201
]
Jiang Xingbo commented on SPARK-20700:
--
I'm working on this, thank you![~joshrosen]
> InferFiltersFromConstraints stackoverflows for query (v2)
> -
>
> Key: SPARK-20700
> URL: https://issues.apache.org/jira/browse/SPARK-20700
> Project: Spark
> Issue Type: Bug
> Components: Optimizer, SQL
>Affects Versions: 2.2.0
>Reporter: Josh Rosen
>
> The following (complicated) query eventually fails with a stack overflow
> during optimization:
> {code}
> CREATE TEMPORARY VIEW table_5(varchar0002_col_1, smallint_col_2, float_col_3,
> int_col_4, string_col_5, timestamp_col_6, string_col_7) AS VALUES
> ('68', CAST(NULL AS SMALLINT), CAST(244.90413 AS FLOAT), -137, '571',
> TIMESTAMP('2015-01-14 00:00:00.0'), '947'),
> ('82', CAST(213 AS SMALLINT), CAST(53.184647 AS FLOAT), -724, '-278',
> TIMESTAMP('1999-08-15 00:00:00.0'), '437'),
> ('-7', CAST(-15 AS SMALLINT), CAST(NULL AS FLOAT), -890, '778',
> TIMESTAMP('1991-05-23 00:00:00.0'), '630'),
> ('22', CAST(676 AS SMALLINT), CAST(385.27386 AS FLOAT), CAST(NULL AS INT),
> '-10', TIMESTAMP('1996-09-29 00:00:00.0'), '641'),
> ('16', CAST(430 AS SMALLINT), CAST(187.23717 AS FLOAT), 989, CAST(NULL AS
> STRING), TIMESTAMP('2024-04-21 00:00:00.0'), '-234'),
> ('83', CAST(760 AS SMALLINT), CAST(-695.45386 AS FLOAT), -970, '330',
> CAST(NULL AS TIMESTAMP), '-740'),
> ('68', CAST(-930 AS SMALLINT), CAST(NULL AS FLOAT), -915, '-766', CAST(NULL
> AS TIMESTAMP), CAST(NULL AS STRING)),
> ('48', CAST(692 AS SMALLINT), CAST(-220.59615 AS FLOAT), 940, '-514',
> CAST(NULL AS TIMESTAMP), '181'),
> ('21', CAST(44 AS SMALLINT), CAST(NULL AS FLOAT), -175, '761',
> TIMESTAMP('2016-06-30 00:00:00.0'), '487'),
> ('50', CAST(953 AS SMALLINT), CAST(837.2948 AS FLOAT), 705, CAST(NULL AS
> STRING), CAST(NULL AS TIMESTAMP), '-62');
> CREATE VIEW bools(a, b) as values (1, true), (1, true), (1, null);
> SELECT
> AVG(-13) OVER (ORDER BY COUNT(t1.smallint_col_2) DESC ROWS 27 PRECEDING ) AS
> float_col,
> COUNT(t1.smallint_col_2) AS int_col
> FROM table_5 t1
> INNER JOIN (
> SELECT
> (MIN(-83) OVER (PARTITION BY t2.a ORDER BY t2.a, (t1.int_col_4) *
> (t1.int_col_4) ROWS BETWEEN CURRENT ROW AND 15 FOLLOWING)) NOT IN (-222, 928)
> AS boolean_col,
> t2.a,
> (t1.int_col_4) * (t1.int_col_4) AS int_col
> FROM table_5 t1
> LEFT JOIN bools t2 ON (t2.a) = (t1.int_col_4)
> WHERE
> (t1.smallint_col_2) > (t1.smallint_col_2)
> GROUP BY
> t2.a,
> (t1.int_col_4) * (t1.int_col_4)
> HAVING
> ((t1.int_col_4) * (t1.int_col_4)) IN ((t1.int_col_4) * (t1.int_col_4),
> SUM(t1.int_col_4))
> ) t2 ON (((t2.int_col) = (t1.int_col_4)) AND ((t2.a) = (t1.int_col_4))) AND
> ((t2.a) = (t1.smallint_col_2));
> {code}
> (I haven't tried to minimize this failing case yet).
> Based on sampled jstacks from the driver, it looks like the query might be
> repeatedly inferring filters from constraints and then pruning those filters.
> Here's part of the stack at the point where it stackoverflows:
> {code}
> [... repeats ...]
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$.org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative(Canonicalize.scala:50)
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$$anonfun$org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative$1.apply(Canonicalize.scala:50)
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$$anonfun$org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative$1.apply(Canonicalize.scala:50)
> at
> scala.collection.TraversableLike$$anonfun$flatMap$1.apply(TraversableLike.scala:241)
> at
> scala.collection.TraversableLike$$anonfun$flatMap$1.apply(TraversableLike.scala:241)
> at scala.collection.immutable.List.foreach(List.scala:381)
> at
> scala.collection.TraversableLike$class.flatMap(TraversableLike.scala:241)
> at scala.collection.immutable.List.flatMap(List.scala:344)
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$.org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative(Canonicalize.scala:50)
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$$anonfun$org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative$1.apply(Canonicalize.scala:50)
> at
> org.apache.spark.sql.catalyst.expressions.Canonicalize$$anonfun$org$apache$spark$sql$catalyst$expressions$Canonicalize$$gatherCommutative$1.apply(Canonicalize.scala:50)
> at
> scala.collection.TraversableLike$$anonfun$flatMap$1.apply(TraversableLike.scala:241)
> at
>
