[
https://issues.apache.org/jira/browse/FLINK-4604?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15572115#comment-15572115
]
Anton Mushin commented on FLINK-4604:
-------------------------------------
Sorry, I will try explain my problem.
I have two tests
{code:title=test1}
@Test
def testStddevPopAggregate(): Unit = {
val env = ExecutionEnvironment.getExecutionEnvironment
val tEnv = TableEnvironment.getTableEnvironment(env, config)
val ds = env.fromElements(
(1: Byte, 1 : Short, 1, 1L, 1F, 1D),
(2: Byte, 2 : Short, 2, 2L, 2F, 2D)).toTable(tEnv)
tEnv.registerTable("myTable", ds)
val columns = Array("_1","_2","_3","_4","_5","_6")
val sqlQuery = getSelectQuery("STDDEV_POP(?)")(columns,"myTable")
//val sqlExpectedQuery = getSelectQuery("SQRT((SUM(? * ?) - SUM(?) * SUM(?)
/ COUNT(?)) / COUNT(?))")(columns,"myTable")
val actualResult = tEnv.sql(sqlQuery).toDataSet[Row].collect()
//val expectedResult =
tEnv.sql(sqlExpectedQuery).toDataSet[Row].collect().toString.replaceAll("Buffer\\(|\\)",
"")
val expectedResult = "0,0,0,0,0.5,0.5"
TestBaseUtils.compareOrderedResultAsText(actualResult.asJava,
expectedResult)
}
{code}
{code:title=test2}
@Test
def testStddevPopAggregateWithOtherAggreagte(): Unit = {
val localconf = config
localconf.setNullCheck(true)
val env = ExecutionEnvironment.getExecutionEnvironment
val tEnv = TableEnvironment.getTableEnvironment(env, localconf)
val ds = env.fromElements(
(1: Byte, 1 : Short, 1, 1L, 1F, 1D),
(2: Byte, 2 : Short, 2, 2L, 2F, 2D)).toTable(tEnv)
tEnv.registerTable("myTable", ds)
val columns = Array("_1","_2","_3","_4","_5","_6")
val sqlQuery = getSelectQuery("STDDEV_POP(?), avg(?), sum(?), max(?),
min(?), count(?)")(columns,"myTable")
val sqlExpectedQuery = getSelectQuery("SQRT((SUM(? * ?) - SUM(?) * SUM(?) /
COUNT(?)) / COUNT(?))" +
"avg(?),sum(?),max(?),min(?),count(?)")(columns,"myTable")
val actualResult = tEnv.sql(sqlQuery).toDataSet[Row].collect()
val expectedResult =
tEnv.sql(sqlExpectedQuery).toDataSet[Row].collect().toString.replaceAll("Buffer\\(|\\)",
"")
//val expectedResult =
"0.0,1,3,2,1,2,0.0,1,3,2,1,2,0.0,1,3,2,1,2,0.0,1,3,2,1,2,0.5,1.5,3.0,2.0,1.0,2,0.5,1.5,3.0,2.0,1.0,2"
TestBaseUtils.compareOrderedResultAsText(actualResult.asJava,
expectedResult)
}
{code}
[Actual code|https://github.com/ex00/flink/compare/master...ex00:FLINK-4604]
for running tests.
First test is passed and second isn't.
I have Calcite ST:
{noformat}
org.apache.calcite.plan.RelOptPlanner$CannotPlanException: Node
[rel#7:Subset#1.DATASET.[]] could not be implemented; planner state:
Root: rel#7:Subset#1.DATASET.[]
Original rel:
Sets:
Set#0, type: RecordType(TINYINT _1, SMALLINT _2, INTEGER _3, BIGINT _4, FLOAT
_5, DOUBLE _6)
rel#4:Subset#0.NONE.[], best=null, importance=0.81
rel#0:LogicalTableScan.NONE.[](table=[_DataSetTable_0]),
rowcount=1000.0, cumulative cost={inf}
rel#26:Subset#0.DATASET.[], best=rel#25, importance=0.405
rel#25:DataSetScan.DATASET.[](table=[_DataSetTable_0]),
rowcount=1000.0, cumulative cost={1000.0 rows, 1000.0 cpu, 0.0 io}
rel#28:Subset#0.ENUMERABLE.[], best=rel#27, importance=0.405
rel#27:EnumerableTableScan.ENUMERABLE.[](table=[_DataSetTable_0]),
rowcount=1000.0, cumulative cost={1000.0 rows, 1001.0 cpu, 0.0 io}
Set#1, type: RecordType(TINYINT EXPR$0, TINYINT EXPR$1, TINYINT EXPR$2, TINYINT
EXPR$3, TINYINT EXPR$4, BIGINT EXPR$5, SMALLINT EXPR$6, SMALLINT EXPR$7,
SMALLINT EXPR$8, SMALLINT EXPR$9, SMALLINT EXPR$10, BIGINT EXPR$11, INTEGER
EXPR$12, INTEGER EXPR$13, INTEGER EXPR$14, INTEGER EXPR$15, INTEGER EXPR$16,
BIGINT EXPR$17, BIGINT EXPR$18, BIGINT EXPR$19, BIGINT EXPR$20, BIGINT EXPR$21,
BIGINT EXPR$22, BIGINT EXPR$23, FLOAT EXPR$24, FLOAT EXPR$25, FLOAT EXPR$26,
FLOAT EXPR$27, FLOAT EXPR$28, BIGINT EXPR$29, DOUBLE EXPR$30, DOUBLE EXPR$31,
DOUBLE EXPR$32, DOUBLE EXPR$33, DOUBLE EXPR$34, BIGINT EXPR$35)
rel#6:Subset#1.NONE.[], best=null, importance=0.9
rel#5:LogicalAggregate.NONE.[](input=rel#4:Subset#0.NONE.[],group={},EXPR$0=STDDEV_POP($0),EXPR$1=AVG($0),EXPR$2=SUM($0),EXPR$3=MAX($0),EXPR$4=MIN($0),EXPR$5=COUNT($0),EXPR$6=STDDEV_POP($1),EXPR$7=AVG($1),EXPR$8=SUM($1),EXPR$9=MAX($1),EXPR$10=MIN($1),EXPR$11=COUNT($1),EXPR$12=STDDEV_POP($2),EXPR$13=AVG($2),EXPR$14=SUM($2),EXPR$15=MAX($2),EXPR$16=MIN($2),EXPR$17=COUNT($2),EXPR$18=STDDEV_POP($3),EXPR$19=AVG($3),EXPR$20=SUM($3),EXPR$21=MAX($3),EXPR$22=MIN($3),EXPR$23=COUNT($3),EXPR$24=STDDEV_POP($4),EXPR$25=AVG($4),EXPR$26=SUM($4),EXPR$27=MAX($4),EXPR$28=MIN($4),EXPR$29=COUNT($4),EXPR$30=STDDEV_POP($5),EXPR$31=AVG($5),EXPR$32=SUM($5),EXPR$33=MAX($5),EXPR$34=MIN($5),EXPR$35=COUNT($5)),
rowcount=100.0, cumulative cost={inf}
rel#15:LogicalProject.NONE.[](input=rel#14:Subset#3.NONE.[],EXPR$0=CAST(POWER(/(-($0,
/(*($1, $1), $2)), $2), 0.5)):TINYINT,EXPR$1=CAST(/($1,
$2)):TINYINT,EXPR$2=CASE(=($2, 0), null,
$3),EXPR$3=$4,EXPR$4=$5,EXPR$5=$2,EXPR$6=CAST(POWER(/(-($6, /(*($7, $7), $8)),
$8), 0.5)):SMALLINT,EXPR$7=CAST(/($7, $8)):SMALLINT,EXPR$8=CASE(=($8, 0), null,
$9),EXPR$9=$10,EXPR$10=$11,EXPR$11=$8,EXPR$12=CAST(POWER(/(-($12, /(*($13,
$13), $14)), $14), 0.5)):INTEGER,EXPR$13=CAST(/($13,
$14)):INTEGER,EXPR$14=CASE(=($14, 0), null,
$15),EXPR$15=$16,EXPR$16=$17,EXPR$17=$14,EXPR$18=CAST(POWER(/(-($18, /(*($19,
$19), $20)), $20), 0.5)):BIGINT,EXPR$19=CAST(/($19,
$20)):BIGINT,EXPR$20=CASE(=($20, 0), null,
$21),EXPR$21=$22,EXPR$22=$23,EXPR$23=$20,EXPR$24=CAST(POWER(/(-($24, /(*($25,
$25), $26)), $26), 0.5)):FLOAT,EXPR$25=CAST(/($25,
$26)):FLOAT,EXPR$26=CASE(=($26, 0), null,
$27),EXPR$27=$28,EXPR$28=$29,EXPR$29=$26,EXPR$30=CAST(POWER(/(-($30, /(*($31,
$31), $32)), $32), 0.5)):DOUBLE,EXPR$31=CAST(/($31,
$32)):DOUBLE,EXPR$32=CASE(=($32, 0), null,
$33),EXPR$33=$34,EXPR$34=$35,EXPR$35=$32), rowcount=100.0, cumulative cost={inf}
rel#16:LogicalCalc.NONE.[[]](input=rel#14:Subset#3.NONE.[],expr#0..35={inputs},expr#36=*($t1,
$t1),expr#37=/($t36, $t2),expr#38=-($t0, $t37),expr#39=/($t38,
$t2),expr#40=0.5,expr#41=POWER($t39,
$t40),expr#42=CAST($t41):TINYINT,expr#43=/($t1,
$t2),expr#44=CAST($t43):TINYINT,expr#45=0,expr#46==($t2,
$t45),expr#47=null,expr#48=CASE($t46, $t47, $t3),expr#49=*($t7,
$t7),expr#50=/($t49, $t8),expr#51=-($t6, $t50),expr#52=/($t51,
$t8),expr#53=POWER($t52, $t40),expr#54=CAST($t53):SMALLINT,expr#55=/($t7,
$t8),expr#56=CAST($t55):SMALLINT,expr#57==($t8, $t45),expr#58=CASE($t57, $t47,
$t9),expr#59=*($t13, $t13),expr#60=/($t59, $t14),expr#61=-($t12,
$t60),expr#62=/($t61, $t14),expr#63=POWER($t62,
$t40),expr#64=CAST($t63):INTEGER,expr#65=/($t13,
$t14),expr#66=CAST($t65):INTEGER,expr#67==($t14, $t45),expr#68=CASE($t67, $t47,
$t15),expr#69=*($t19, $t19),expr#70=/($t69, $t20),expr#71=-($t18,
$t70),expr#72=/($t71, $t20),expr#73=POWER($t72,
$t40),expr#74=CAST($t73):BIGINT,expr#75=/($t19,
$t20),expr#76=CAST($t75):BIGINT,expr#77==($t20, $t45),expr#78=CASE($t77, $t47,
$t21),expr#79=*($t25, $t25),expr#80=/($t79, $t26),expr#81=-($t24,
$t80),expr#82=/($t81, $t26),expr#83=POWER($t82,
$t40),expr#84=CAST($t83):FLOAT,expr#85=/($t25,
$t26),expr#86=CAST($t85):FLOAT,expr#87==($t26, $t45),expr#88=CASE($t87, $t47,
$t27),expr#89=*($t31, $t31),expr#90=/($t89, $t32),expr#91=-($t30,
$t90),expr#92=/($t91, $t32),expr#93=POWER($t92,
$t40),expr#94=CAST($t93):DOUBLE,expr#95=/($t31,
$t32),expr#96=CAST($t95):DOUBLE,expr#97==($t32, $t45),expr#98=CASE($t97, $t47,
$t33),EXPR$0=$t42,EXPR$1=$t44,EXPR$2=$t48,EXPR$3=$t4,EXPR$4=$t5,EXPR$5=$t2,EXPR$6=$t54,EXPR$7=$t56,EXPR$8=$t58,EXPR$9=$t10,EXPR$10=$t11,EXPR$11=$t8,EXPR$12=$t64,EXPR$13=$t66,EXPR$14=$t68,EXPR$15=$t16,EXPR$16=$t17,EXPR$17=$t14,EXPR$18=$t74,EXPR$19=$t76,EXPR$20=$t78,EXPR$21=$t22,EXPR$22=$t23,EXPR$23=$t20,EXPR$24=$t84,EXPR$25=$t86,EXPR$26=$t88,EXPR$27=$t28,EXPR$28=$t29,EXPR$29=$t26,EXPR$30=$t94,EXPR$31=$t96,EXPR$32=$t98,EXPR$33=$t34,EXPR$34=$t35,EXPR$35=$t32),
importance=0.0, rowcount=100.0, cumulative cost={inf}
rel#19:LogicalCalc.NONE.[[]](input=rel#14:Subset#3.NONE.[],expr#0..35={inputs},expr#36=*($t1,
$t1),expr#37=/($t36, $t2),expr#38=-($t0, $t37),expr#39=/($t38,
$t2),expr#40=0.5,expr#41=POWER($t39,
$t40),expr#42=CAST($t41):TINYINT,expr#43=/($t1,
$t2),expr#44=CAST($t43):TINYINT,expr#45=0,expr#46==($t2,
$t45),expr#47=null,expr#48=CASE($t46, $t47, $t3),expr#49=*($t7,
$t7),expr#50=/($t49, $t8),expr#51=-($t6, $t50),expr#52=/($t51,
$t8),expr#53=POWER($t52, $t40),expr#54=CAST($t53):SMALLINT,expr#55=/($t7,
$t8),expr#56=CAST($t55):SMALLINT,expr#57==($t8, $t45),expr#58=CASE($t57, $t47,
$t9),expr#59=*($t13, $t13),expr#60=/($t59, $t14),expr#61=-($t12,
$t60),expr#62=/($t61, $t14),expr#63=POWER($t62,
$t40),expr#64=CAST($t63):INTEGER,expr#65=/($t13,
$t14),expr#66=CAST($t65):INTEGER,expr#67==($t14, $t45),expr#68=CASE($t67, $t47,
$t15),expr#69=*($t19, $t19),expr#70=/($t69, $t20),expr#71=-($t18,
$t70),expr#72=/($t71, $t20),expr#73=POWER($t72,
$t40),expr#74=CAST($t73):BIGINT,expr#75=/($t19, $t20),expr#76==($t20,
$t45),expr#77=CASE($t76, $t47, $t21),expr#78=*($t25, $t25),expr#79=/($t78,
$t26),expr#80=-($t24, $t79),expr#81=/($t80, $t26),expr#82=POWER($t81,
$t40),expr#83=CAST($t82):FLOAT,expr#84=/($t25, $t26),expr#85==($t26,
$t45),expr#86=CASE($t85, $t47, $t27),expr#87=*($t31, $t31),expr#88=/($t87,
$t32),expr#89=-($t30, $t88),expr#90=/($t89, $t32),expr#91=POWER($t90,
$t40),expr#92=/($t31, $t32),expr#93==($t32, $t45),expr#94=CASE($t93, $t47,
$t33),EXPR$0=$t42,EXPR$1=$t44,EXPR$2=$t48,EXPR$3=$t4,EXPR$4=$t5,EXPR$5=$t2,EXPR$6=$t54,EXPR$7=$t56,EXPR$8=$t58,EXPR$9=$t10,EXPR$10=$t11,EXPR$11=$t8,EXPR$12=$t64,EXPR$13=$t66,EXPR$14=$t68,EXPR$15=$t16,EXPR$16=$t17,EXPR$17=$t14,EXPR$18=$t74,EXPR$19=$t75,EXPR$20=$t77,EXPR$21=$t22,EXPR$22=$t23,EXPR$23=$t20,EXPR$24=$t83,EXPR$25=$t84,EXPR$26=$t86,EXPR$27=$t28,EXPR$28=$t29,EXPR$29=$t26,EXPR$30=$t91,EXPR$31=$t92,EXPR$32=$t94,EXPR$33=$t34,EXPR$34=$t35,EXPR$35=$t32),
rowcount=100.0, cumulative cost={inf}
rel#31:LogicalCalc.NONE.[[]](input=rel#23:Subset#4.NONE.[],expr#0..35={inputs},expr#36=0,expr#37==($t1,
$t36),expr#38=null,expr#39=CASE($t37, $t38, $t0),expr#40==($t3,
$t36),expr#41=CASE($t40, $t38, $t2),expr#42=*($t41, $t41),expr#43=/($t42,
$t3),expr#44=-($t39, $t43),expr#45=/($t44, $t3),expr#46=0.5,expr#47=POWER($t45,
$t46),expr#48=CAST($t47):TINYINT,expr#49=/($t41,
$t3),expr#50=CAST($t49):TINYINT,expr#51==($t7, $t36),expr#52=CASE($t51, $t38,
$t6),expr#53==($t9, $t36),expr#54=CASE($t53, $t38, $t8),expr#55=*($t54,
$t54),expr#56=/($t55, $t9),expr#57=-($t52, $t56),expr#58=/($t57,
$t9),expr#59=POWER($t58, $t46),expr#60=CAST($t59):SMALLINT,expr#61=/($t54,
$t9),expr#62=CAST($t61):SMALLINT,expr#63==($t13, $t36),expr#64=CASE($t63, $t38,
$t12),expr#65==($t15, $t36),expr#66=CASE($t65, $t38, $t14),expr#67=*($t66,
$t66),expr#68=/($t67, $t15),expr#69=-($t64, $t68),expr#70=/($t69,
$t15),expr#71=POWER($t70, $t46),expr#72=CAST($t71):INTEGER,expr#73=/($t66,
$t15),expr#74=CAST($t73):INTEGER,expr#75==($t19, $t36),expr#76=CASE($t75, $t38,
$t18),expr#77==($t21, $t36),expr#78=CASE($t77, $t38, $t20),expr#79=*($t78,
$t78),expr#80=/($t79, $t21),expr#81=-($t76, $t80),expr#82=/($t81,
$t21),expr#83=POWER($t82, $t46),expr#84=CAST($t83):BIGINT,expr#85=/($t78,
$t21),expr#86=CAST($t85):BIGINT,expr#87==($t25, $t36),expr#88=CASE($t87, $t38,
$t24),expr#89==($t27, $t36),expr#90=CASE($t89, $t38, $t26),expr#91=*($t90,
$t90),expr#92=/($t91, $t27),expr#93=-($t88, $t92),expr#94=/($t93,
$t27),expr#95=POWER($t94, $t46),expr#96=CAST($t95):FLOAT,expr#97=/($t90,
$t27),expr#98=CAST($t97):FLOAT,expr#99==($t31, $t36),expr#100=CASE($t99, $t38,
$t30),expr#101==($t33, $t36),expr#102=CASE($t101, $t38, $t32),expr#103=*($t102,
$t102),expr#104=/($t103, $t33),expr#105=-($t100, $t104),expr#106=/($t105,
$t33),expr#107=POWER($t106, $t46),expr#108=CAST($t107):DOUBLE,expr#109=/($t102,
$t33),expr#110=CAST($t109):DOUBLE,EXPR$0=$t48,EXPR$1=$t50,EXPR$2=$t41,EXPR$3=$t4,EXPR$4=$t5,EXPR$5=$t3,EXPR$6=$t60,EXPR$7=$t62,EXPR$8=$t54,EXPR$9=$t10,EXPR$10=$t11,EXPR$11=$t9,EXPR$12=$t72,EXPR$13=$t74,EXPR$14=$t66,EXPR$15=$t16,EXPR$16=$t17,EXPR$17=$t15,EXPR$18=$t84,EXPR$19=$t86,EXPR$20=$t78,EXPR$21=$t22,EXPR$22=$t23,EXPR$23=$t21,EXPR$24=$t96,EXPR$25=$t98,EXPR$26=$t90,EXPR$27=$t28,EXPR$28=$t29,EXPR$29=$t27,EXPR$30=$t108,EXPR$31=$t110,EXPR$32=$t102,EXPR$33=$t34,EXPR$34=$t35,EXPR$35=$t33),
importance=0.0, rowcount=100.0, cumulative cost={inf}
rel#34:LogicalCalc.NONE.[[]](input=rel#23:Subset#4.NONE.[],expr#0..35={inputs},expr#36=0,expr#37==($t1,
$t36),expr#38=null,expr#39=CASE($t37, $t38, $t0),expr#40==($t3,
$t36),expr#41=CASE($t40, $t38, $t2),expr#42=*($t41, $t41),expr#43=/($t42,
$t3),expr#44=-($t39, $t43),expr#45=/($t44, $t3),expr#46=0.5,expr#47=POWER($t45,
$t46),expr#48=CAST($t47):TINYINT,expr#49=/($t41,
$t3),expr#50=CAST($t49):TINYINT,expr#51==($t7, $t36),expr#52=CASE($t51, $t38,
$t6),expr#53==($t9, $t36),expr#54=CASE($t53, $t38, $t8),expr#55=*($t54,
$t54),expr#56=/($t55, $t9),expr#57=-($t52, $t56),expr#58=/($t57,
$t9),expr#59=POWER($t58, $t46),expr#60=CAST($t59):SMALLINT,expr#61=/($t54,
$t9),expr#62=CAST($t61):SMALLINT,expr#63==($t13, $t36),expr#64=CASE($t63, $t38,
$t12),expr#65==($t15, $t36),expr#66=CASE($t65, $t38, $t14),expr#67=*($t66,
$t66),expr#68=/($t67, $t15),expr#69=-($t64, $t68),expr#70=/($t69,
$t15),expr#71=POWER($t70, $t46),expr#72=CAST($t71):INTEGER,expr#73=/($t66,
$t15),expr#74=CAST($t73):INTEGER,expr#75==($t19, $t36),expr#76=CASE($t75, $t38,
$t18),expr#77==($t21, $t36),expr#78=CASE($t77, $t38, $t20),expr#79=*($t78,
$t78),expr#80=/($t79, $t21),expr#81=-($t76, $t80),expr#82=/($t81,
$t21),expr#83=POWER($t82, $t46),expr#84=CAST($t83):BIGINT,expr#85=/($t78,
$t21),expr#86==($t25, $t36),expr#87=CASE($t86, $t38, $t24),expr#88==($t27,
$t36),expr#89=CASE($t88, $t38, $t26),expr#90=*($t89, $t89),expr#91=/($t90,
$t27),expr#92=-($t87, $t91),expr#93=/($t92, $t27),expr#94=POWER($t93,
$t46),expr#95=CAST($t94):FLOAT,expr#96=/($t89, $t27),expr#97==($t31,
$t36),expr#98=CASE($t97, $t38, $t30),expr#99==($t33, $t36),expr#100=CASE($t99,
$t38, $t32),expr#101=*($t100, $t100),expr#102=/($t101, $t33),expr#103=-($t98,
$t102),expr#104=/($t103, $t33),expr#105=POWER($t104, $t46),expr#106=/($t100,
$t33),EXPR$0=$t48,EXPR$1=$t50,EXPR$2=$t41,EXPR$3=$t4,EXPR$4=$t5,EXPR$5=$t3,EXPR$6=$t60,EXPR$7=$t62,EXPR$8=$t54,EXPR$9=$t10,EXPR$10=$t11,EXPR$11=$t9,EXPR$12=$t72,EXPR$13=$t74,EXPR$14=$t66,EXPR$15=$t16,EXPR$16=$t17,EXPR$17=$t15,EXPR$18=$t84,EXPR$19=$t85,EXPR$20=$t78,EXPR$21=$t22,EXPR$22=$t23,EXPR$23=$t21,EXPR$24=$t95,EXPR$25=$t96,EXPR$26=$t89,EXPR$27=$t28,EXPR$28=$t29,EXPR$29=$t27,EXPR$30=$t105,EXPR$31=$t106,EXPR$32=$t100,EXPR$33=$t34,EXPR$34=$t35,EXPR$35=$t33),
rowcount=100.0, cumulative cost={inf}
rel#7:Subset#1.DATASET.[], best=null, importance=1.0
rel#8:AbstractConverter.DATASET.[](input=rel#6:Subset#1.NONE.[],convention=DATASET,sort=[]),
rowcount=100.0, cumulative cost={inf}
rel#18:DataSetCalc.DATASET.[[]](input=rel#17:Subset#3.DATASET.[],select=CAST(POWER(/(-($f0,
/(*($f1, $f1), $f2)), $f2), 0.5)) AS $f0, CAST(/($f1, $f2)) AS $f1,
CASE(=($f2, 0), null, EXPR$2) AS $f2, EXPR$3 AS EXPR$2, EXPR$4 AS EXPR$3, $f2
AS EXPR$4, CAST(POWER(/(-($f6, /(*($f7, $f7), $f8)), $f8), 0.5)) AS $f6,
CAST(/($f7, $f8)) AS $f7, CASE(=($f8, 0), null, EXPR$8) AS $f8, EXPR$9 AS
EXPR$8, EXPR$10 AS EXPR$9, $f8 AS EXPR$10, CAST(POWER(/(-($f12, /(*($f13,
$f13), $f14)), $f14), 0.5)) AS $f12, CAST(/($f13, $f14)) AS $f13, CASE(=($f14,
0), null, EXPR$14) AS $f14, EXPR$15 AS EXPR$14, EXPR$16 AS EXPR$15, $f14 AS
EXPR$16, CAST(POWER(/(-($f18, /(*($f19, $f19), $f20)), $f20), 0.5)) AS $f18,
CAST(/($f19, $f20)) AS $f19, CASE(=($f20, 0), null, EXPR$20) AS $f20, EXPR$21
AS EXPR$20, EXPR$22 AS EXPR$21, $f20 AS EXPR$22, CAST(POWER(/(-($f24, /(*($f25,
$f25), $f26)), $f26), 0.5)) AS $f24, CAST(/($f25, $f26)) AS $f25, CASE(=($f26,
0), null, EXPR$26) AS $f26, EXPR$27 AS EXPR$26, EXPR$28 AS EXPR$27, $f26 AS
EXPR$28, CAST(POWER(/(-($f30, /(*($f31, $f31), $f32)), $f32), 0.5)) AS $f30,
CAST(/($f31, $f32)) AS $f31, CASE(=($f32, 0), null, EXPR$32) AS $f32, EXPR$33
AS EXPR$32, EXPR$34 AS EXPR$33, $f32 AS EXPR$34), rowcount=100.0, cumulative
cost={inf}
rel#20:DataSetCalc.DATASET.[[]](input=rel#17:Subset#3.DATASET.[],select=CAST(POWER(/(-($f0,
/(*($f1, $f1), $f2)), $f2), 0.5)) AS $f0, CAST(/($f1, $f2)) AS $f1,
CASE(=($f2, 0), null, EXPR$2) AS $f2, EXPR$3 AS EXPR$2, EXPR$4 AS EXPR$3, $f2
AS EXPR$4, CAST(POWER(/(-($f6, /(*($f7, $f7), $f8)), $f8), 0.5)) AS $f6,
CAST(/($f7, $f8)) AS $f7, CASE(=($f8, 0), null, EXPR$8) AS $f8, EXPR$9 AS
EXPR$8, EXPR$10 AS EXPR$9, $f8 AS EXPR$10, CAST(POWER(/(-($f12, /(*($f13,
$f13), $f14)), $f14), 0.5)) AS $f12, CAST(/($f13, $f14)) AS $f13, CASE(=($f14,
0), null, EXPR$14) AS $f14, EXPR$15 AS EXPR$14, EXPR$16 AS EXPR$15, $f14 AS
EXPR$16, CAST(POWER(/(-($f18, /(*($f19, $f19), $f20)), $f20), 0.5)) AS $f18,
/($f19, $f20) AS $f19, CASE(=($f20, 0), null, EXPR$20) AS $f20, EXPR$21 AS
EXPR$20, EXPR$22 AS EXPR$21, $f20 AS EXPR$22, CAST(POWER(/(-($f24, /(*($f25,
$f25), $f26)), $f26), 0.5)) AS $f24, /($f25, $f26) AS $f25, CASE(=($f26, 0),
null, EXPR$26) AS $f26, EXPR$27 AS EXPR$26, EXPR$28 AS EXPR$27, $f26 AS
EXPR$28, POWER(/(-($f30, /(*($f31, $f31), $f32)), $f32), 0.5) AS $f30, /($f31,
$f32) AS $f31, CASE(=($f32, 0), null, EXPR$32) AS $f32, EXPR$33 AS EXPR$32,
EXPR$34 AS EXPR$33, $f32 AS EXPR$34), rowcount=100.0, cumulative cost={inf}
rel#33:DataSetCalc.DATASET.[[]](input=rel#32:Subset#4.DATASET.[],select=CAST(POWER(/(-(CASE(=($f1,
0), null, $f0), /(*(CASE(=($f3, 0), null, $f2), CASE(=($f3, 0), null, $f2)),
$f3)), $f3), 0.5)) AS $f0, CAST(/(CASE(=($f3, 0), null, $f2), $f3)) AS $f1,
CASE(=($f3, 0), null, $f2) AS $f2, EXPR$3 AS $f3, EXPR$4 AS EXPR$3, $f3 AS
EXPR$4, CAST(POWER(/(-(CASE(=($f7, 0), null, $f6), /(*(CASE(=($f9, 0), null,
$f8), CASE(=($f9, 0), null, $f8)), $f9)), $f9), 0.5)) AS $f6,
CAST(/(CASE(=($f9, 0), null, $f8), $f9)) AS $f7, CASE(=($f9, 0), null, $f8) AS
$f8, EXPR$9 AS $f9, EXPR$10 AS EXPR$9, $f9 AS EXPR$10,
CAST(POWER(/(-(CASE(=($f13, 0), null, $f12), /(*(CASE(=($f15, 0), null, $f14),
CASE(=($f15, 0), null, $f14)), $f15)), $f15), 0.5)) AS $f12,
CAST(/(CASE(=($f15, 0), null, $f14), $f15)) AS $f13, CASE(=($f15, 0), null,
$f14) AS $f14, EXPR$15 AS $f15, EXPR$16 AS EXPR$15, $f15 AS EXPR$16,
CAST(POWER(/(-(CASE(=($f19, 0), null, $f18), /(*(CASE(=($f21, 0), null, $f20),
CASE(=($f21, 0), null, $f20)), $f21)), $f21), 0.5)) AS $f18,
CAST(/(CASE(=($f21, 0), null, $f20), $f21)) AS $f19, CASE(=($f21, 0), null,
$f20) AS $f20, EXPR$21 AS $f21, EXPR$22 AS EXPR$21, $f21 AS EXPR$22,
CAST(POWER(/(-(CASE(=($f25, 0), null, $f24), /(*(CASE(=($f27, 0), null, $f26),
CASE(=($f27, 0), null, $f26)), $f27)), $f27), 0.5)) AS $f24,
CAST(/(CASE(=($f27, 0), null, $f26), $f27)) AS $f25, CASE(=($f27, 0), null,
$f26) AS $f26, EXPR$27 AS $f27, EXPR$28 AS EXPR$27, $f27 AS EXPR$28,
CAST(POWER(/(-(CASE(=($f31, 0), null, $f30), /(*(CASE(=($f33, 0), null, $f32),
CASE(=($f33, 0), null, $f32)), $f33)), $f33), 0.5)) AS $f30,
CAST(/(CASE(=($f33, 0), null, $f32), $f33)) AS $f31, CASE(=($f33, 0), null,
$f32) AS $f32, EXPR$33 AS $f33, EXPR$34 AS EXPR$33, $f33 AS EXPR$34),
rowcount=100.0, cumulative cost={inf}
rel#35:DataSetCalc.DATASET.[[]](input=rel#32:Subset#4.DATASET.[],select=CAST(POWER(/(-(CASE(=($f1,
0), null, $f0), /(*(CASE(=($f3, 0), null, $f2), CASE(=($f3, 0), null, $f2)),
$f3)), $f3), 0.5)) AS $f0, CAST(/(CASE(=($f3, 0), null, $f2), $f3)) AS $f1,
CASE(=($f3, 0), null, $f2) AS $f2, EXPR$3 AS $f3, EXPR$4 AS EXPR$3, $f3 AS
EXPR$4, CAST(POWER(/(-(CASE(=($f7, 0), null, $f6), /(*(CASE(=($f9, 0), null,
$f8), CASE(=($f9, 0), null, $f8)), $f9)), $f9), 0.5)) AS $f6,
CAST(/(CASE(=($f9, 0), null, $f8), $f9)) AS $f7, CASE(=($f9, 0), null, $f8) AS
$f8, EXPR$9 AS $f9, EXPR$10 AS EXPR$9, $f9 AS EXPR$10,
CAST(POWER(/(-(CASE(=($f13, 0), null, $f12), /(*(CASE(=($f15, 0), null, $f14),
CASE(=($f15, 0), null, $f14)), $f15)), $f15), 0.5)) AS $f12,
CAST(/(CASE(=($f15, 0), null, $f14), $f15)) AS $f13, CASE(=($f15, 0), null,
$f14) AS $f14, EXPR$15 AS $f15, EXPR$16 AS EXPR$15, $f15 AS EXPR$16,
CAST(POWER(/(-(CASE(=($f19, 0), null, $f18), /(*(CASE(=($f21, 0), null, $f20),
CASE(=($f21, 0), null, $f20)), $f21)), $f21), 0.5)) AS $f18, /(CASE(=($f21, 0),
null, $f20), $f21) AS $f19, CASE(=($f21, 0), null, $f20) AS $f20, EXPR$21 AS
$f21, EXPR$22 AS EXPR$21, $f21 AS EXPR$22, CAST(POWER(/(-(CASE(=($f25, 0),
null, $f24), /(*(CASE(=($f27, 0), null, $f26), CASE(=($f27, 0), null, $f26)),
$f27)), $f27), 0.5)) AS $f24, /(CASE(=($f27, 0), null, $f26), $f27) AS $f25,
CASE(=($f27, 0), null, $f26) AS $f26, EXPR$27 AS $f27, EXPR$28 AS EXPR$27, $f27
AS EXPR$28, POWER(/(-(CASE(=($f31, 0), null, $f30), /(*(CASE(=($f33, 0), null,
$f32), CASE(=($f33, 0), null, $f32)), $f33)), $f33), 0.5) AS $f30,
/(CASE(=($f33, 0), null, $f32), $f33) AS $f31, CASE(=($f33, 0), null, $f32) AS
$f32, EXPR$33 AS $f33, EXPR$34 AS EXPR$33, $f33 AS EXPR$34), rowcount=100.0,
cumulative cost={inf}
Set#2, type: RecordType(TINYINT _1, SMALLINT _2, INTEGER _3, BIGINT _4, FLOAT
_5, DOUBLE _6, TINYINT $f6, SMALLINT $f7, INTEGER $f8, BIGINT $f9, FLOAT $f10,
DOUBLE $f11)
rel#12:Subset#2.NONE.[], best=null, importance=0.7290000000000001
rel#9:LogicalProject.NONE.[](input=rel#4:Subset#0.NONE.[],_1=$0,_2=$1,_3=$2,_4=$3,_5=$4,_6=$5,$f6=*($0,
$0),$f7=*($1, $1),$f8=*($2, $2),$f9=*($3, $3),$f10=*($4, $4),$f11=*($5, $5)),
rowcount=1000.0, cumulative cost={inf}
rel#29:LogicalCalc.NONE.[[]](input=rel#4:Subset#0.NONE.[],expr#0..5={inputs},expr#6=*($t0,
$t0),expr#7=*($t1, $t1),expr#8=*($t2, $t2),expr#9=*($t3, $t3),expr#10=*($t4,
$t4),expr#11=*($t5,
$t5),_1=$t0,_2=$t1,_3=$t2,_4=$t3,_5=$t4,_6=$t5,$f6=$t6,$f7=$t7,$f8=$t8,$f9=$t9,$f10=$t10,$f11=$t11),
rowcount=1000.0, cumulative cost={inf}
rel#38:Subset#2.DATASET.[], best=rel#37, importance=0.36450000000000005
rel#37:DataSetCalc.DATASET.[[]](input=rel#26:Subset#0.DATASET.[],select=_1, _2,
_3, _4, _5, _6), rowcount=1000.0, cumulative cost={2000.0 rows, 25000.0 cpu,
0.0 io}
Set#3, type: RecordType(TINYINT $f0, TINYINT $f1, BIGINT $f2, TINYINT EXPR$2,
TINYINT EXPR$3, TINYINT EXPR$4, SMALLINT $f6, SMALLINT $f7, BIGINT $f8,
SMALLINT EXPR$8, SMALLINT EXPR$9, SMALLINT EXPR$10, INTEGER $f12, INTEGER $f13,
BIGINT $f14, INTEGER EXPR$14, INTEGER EXPR$15, INTEGER EXPR$16, BIGINT $f18,
BIGINT $f19, BIGINT $f20, BIGINT EXPR$20, BIGINT EXPR$21, BIGINT EXPR$22, FLOAT
$f24, FLOAT $f25, BIGINT $f26, FLOAT EXPR$26, FLOAT EXPR$27, FLOAT EXPR$28,
DOUBLE $f30, DOUBLE $f31, BIGINT $f32, DOUBLE EXPR$32, DOUBLE EXPR$33, DOUBLE
EXPR$34)
rel#14:Subset#3.NONE.[], best=null, importance=0.81
rel#13:LogicalAggregate.NONE.[](input=rel#12:Subset#2.NONE.[],group={},agg#0=SUM($6),agg#1=SUM($0),agg#2=COUNT($0),EXPR$2=$SUM0($0),EXPR$3=MAX($0),EXPR$4=MIN($0),agg#6=SUM($7),agg#7=SUM($1),agg#8=COUNT($1),EXPR$8=$SUM0($1),EXPR$9=MAX($1),EXPR$10=MIN($1),agg#12=SUM($8),agg#13=SUM($2),agg#14=COUNT($2),EXPR$14=$SUM0($2),EXPR$15=MAX($2),EXPR$16=MIN($2),agg#18=SUM($9),agg#19=SUM($3),agg#20=COUNT($3),EXPR$20=$SUM0($3),EXPR$21=MAX($3),EXPR$22=MIN($3),agg#24=SUM($10),agg#25=SUM($4),agg#26=COUNT($4),EXPR$26=$SUM0($4),EXPR$27=MAX($4),EXPR$28=MIN($4),agg#30=SUM($11),agg#31=SUM($5),agg#32=COUNT($5),EXPR$32=$SUM0($5),EXPR$33=MAX($5),EXPR$34=MIN($5)),
rowcount=100.0, cumulative cost={inf}
rel#24:LogicalProject.NONE.[](input=rel#23:Subset#4.NONE.[],$f0=CASE(=($1, 0),
null, $0),$f1=CASE(=($3, 0), null,
$2),$f2=$3,EXPR$2=$2,EXPR$3=$4,EXPR$4=$5,$f6=CASE(=($7, 0), null,
$6),$f7=CASE(=($9, 0), null,
$8),$f8=$9,EXPR$8=$8,EXPR$9=$10,EXPR$10=$11,$f12=CASE(=($13, 0), null,
$12),$f13=CASE(=($15, 0), null,
$14),$f14=$15,EXPR$14=$14,EXPR$15=$16,EXPR$16=$17,$f18=CASE(=($19, 0), null,
$18),$f19=CASE(=($21, 0), null,
$20),$f20=$21,EXPR$20=$20,EXPR$21=$22,EXPR$22=$23,$f24=CASE(=($25, 0), null,
$24),$f25=CASE(=($27, 0), null,
$26),$f26=$27,EXPR$26=$26,EXPR$27=$28,EXPR$28=$29,$f30=CASE(=($31, 0), null,
$30),$f31=CASE(=($33, 0), null,
$32),$f32=$33,EXPR$32=$32,EXPR$33=$34,EXPR$34=$35), rowcount=100.0, cumulative
cost={inf}
rel#30:LogicalCalc.NONE.[[]](input=rel#23:Subset#4.NONE.[],expr#0..35={inputs},expr#36=0,expr#37==($t1,
$t36),expr#38=null,expr#39=CASE($t37, $t38, $t0),expr#40==($t3,
$t36),expr#41=CASE($t40, $t38, $t2),expr#42==($t7, $t36),expr#43=CASE($t42,
$t38, $t6),expr#44==($t9, $t36),expr#45=CASE($t44, $t38, $t8),expr#46==($t13,
$t36),expr#47=CASE($t46, $t38, $t12),expr#48==($t15, $t36),expr#49=CASE($t48,
$t38, $t14),expr#50==($t19, $t36),expr#51=CASE($t50, $t38,
$t18),expr#52==($t21, $t36),expr#53=CASE($t52, $t38, $t20),expr#54==($t25,
$t36),expr#55=CASE($t54, $t38, $t24),expr#56==($t27, $t36),expr#57=CASE($t56,
$t38, $t26),expr#58==($t31, $t36),expr#59=CASE($t58, $t38,
$t30),expr#60==($t33, $t36),expr#61=CASE($t60, $t38,
$t32),$f0=$t39,$f1=$t41,$f2=$t3,EXPR$2=$t2,EXPR$3=$t4,EXPR$4=$t5,$f6=$t43,$f7=$t45,$f8=$t9,EXPR$8=$t8,EXPR$9=$t10,EXPR$10=$t11,$f12=$t47,$f13=$t49,$f14=$t15,EXPR$14=$t14,EXPR$15=$t16,EXPR$16=$t17,$f18=$t51,$f19=$t53,$f20=$t21,EXPR$20=$t20,EXPR$21=$t22,EXPR$22=$t23,$f24=$t55,$f25=$t57,$f26=$t27,EXPR$26=$t26,EXPR$27=$t28,EXPR$28=$t29,$f30=$t59,$f31=$t61,$f32=$t33,EXPR$32=$t32,EXPR$33=$t34,EXPR$34=$t35),
rowcount=100.0, cumulative cost={inf}
rel#17:Subset#3.DATASET.[], best=null, importance=0.9
rel#39:DataSetCalc.DATASET.[[]](input=rel#32:Subset#4.DATASET.[],select=CASE(=($f1,
0), null, $f0) AS $f0, CASE(=($f3, 0), null, $f2) AS $f1, $f3 AS $f2, $f2 AS
$f3, EXPR$3, EXPR$4, CASE(=($f7, 0), null, $f6) AS $f6, CASE(=($f9, 0), null,
$f8) AS $f7, $f9 AS $f8, $f8 AS $f9, EXPR$9, EXPR$10, CASE(=($f13, 0), null,
$f12) AS $f12, CASE(=($f15, 0), null, $f14) AS $f13, $f15 AS $f14, $f14 AS
$f15, EXPR$15, EXPR$16, CASE(=($f19, 0), null, $f18) AS $f18, CASE(=($f21, 0),
null, $f20) AS $f19, $f21 AS $f20, $f20 AS $f21, EXPR$21, EXPR$22, CASE(=($f25,
0), null, $f24) AS $f24, CASE(=($f27, 0), null, $f26) AS $f25, $f27 AS $f26,
$f26 AS $f27, EXPR$27, EXPR$28, CASE(=($f31, 0), null, $f30) AS $f30,
CASE(=($f33, 0), null, $f32) AS $f31, $f33 AS $f32, $f32 AS $f33, EXPR$33,
EXPR$34), rowcount=100.0, cumulative cost={inf}
Set#4, type: RecordType(TINYINT $f0, BIGINT $f1, TINYINT $f2, BIGINT $f3,
TINYINT EXPR$3, TINYINT EXPR$4, SMALLINT $f6, BIGINT $f7, SMALLINT $f8, BIGINT
$f9, SMALLINT EXPR$9, SMALLINT EXPR$10, INTEGER $f12, BIGINT $f13, INTEGER
$f14, BIGINT $f15, INTEGER EXPR$15, INTEGER EXPR$16, BIGINT $f18, BIGINT $f19,
BIGINT $f20, BIGINT $f21, BIGINT EXPR$21, BIGINT EXPR$22, FLOAT $f24, BIGINT
$f25, FLOAT $f26, BIGINT $f27, FLOAT EXPR$27, FLOAT EXPR$28, DOUBLE $f30,
BIGINT $f31, DOUBLE $f32, BIGINT $f33, DOUBLE EXPR$33, DOUBLE EXPR$34)
rel#23:Subset#4.NONE.[], best=null, importance=0.7290000000000001
rel#21:LogicalAggregate.NONE.[](input=rel#12:Subset#2.NONE.[],group={},agg#0=$SUM0($6),agg#1=COUNT($6),agg#2=$SUM0($0),agg#3=COUNT($0),EXPR$3=MAX($0),EXPR$4=MIN($0),agg#6=$SUM0($7),agg#7=COUNT($7),agg#8=$SUM0($1),agg#9=COUNT($1),EXPR$9=MAX($1),EXPR$10=MIN($1),agg#12=$SUM0($8),agg#13=COUNT($8),agg#14=$SUM0($2),agg#15=COUNT($2),EXPR$15=MAX($2),EXPR$16=MIN($2),agg#18=$SUM0($9),agg#19=COUNT($9),agg#20=$SUM0($3),agg#21=COUNT($3),EXPR$21=MAX($3),EXPR$22=MIN($3),agg#24=$SUM0($10),agg#25=COUNT($10),agg#26=$SUM0($4),agg#27=COUNT($4),EXPR$27=MAX($4),EXPR$28=MIN($4),agg#30=$SUM0($11),agg#31=COUNT($11),agg#32=$SUM0($5),agg#33=COUNT($5),EXPR$33=MAX($5),EXPR$34=MIN($5)),
rowcount=100.0, cumulative cost={inf}
rel#32:Subset#4.DATASET.[], best=null, importance=0.81
at
org.apache.calcite.plan.volcano.RelSubset$CheapestPlanReplacer.visit(RelSubset.java:443)
at
org.apache.calcite.plan.volcano.RelSubset.buildCheapestPlan(RelSubset.java:293)
at
org.apache.calcite.plan.volcano.VolcanoPlanner.findBestExp(VolcanoPlanner.java:841)
at
org.apache.calcite.tools.Programs$RuleSetProgram.run(Programs.java:334)
at
org.apache.flink.api.table.BatchTableEnvironment.translate(BatchTableEnvironment.scala:253)
{noformat}
I think what {{RelOptPlanner}} can't choose rule for which he needs to handle
this query and can't apply any RelNode suitable for this sql query. For fix it
need creating common rule which will contain rules for simple aggregate
functions (sum, max, min, count) and new functions (stddev_pop, stddev_samp,
etc)
What do you think about it?
> Add support for standard deviation/variance
> -------------------------------------------
>
> Key: FLINK-4604
> URL: https://issues.apache.org/jira/browse/FLINK-4604
> Project: Flink
> Issue Type: New Feature
> Components: Table API & SQL
> Reporter: Timo Walther
> Assignee: Anton Mushin
> Attachments: 1.jpg
>
>
> Calcite's {{AggregateReduceFunctionsRule}} can convert SQL {{AVG, STDDEV_POP,
> STDDEV_SAMP, VAR_POP, VAR_SAMP}} to sum/count functions. We should add, test
> and document this rule.
> If we also want to add this aggregates to Table API is up for discussion.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
