Move optimize_subquery_sort into optimizer as a new rule EliminateSort

[jayzhan211]

          I have no idea why we have optimize plan rule `optimize_subquery_sort` in `create_relation`, I think we should move such rule to optimizer 🤔

Originally posted by @jayzhan211 in #15300 (comment)

[acking-you]

take

[irenjj]

Using datafusion-optimizer in datafusion-sql can lead to dependency issue:

thread 'main' panicked at src/main.rs:84:9:
circular dependency detected from datafusion-optimizer to self via one of {"datafusion-functions-window", "datafusion-functions-window-common", "datafusion-expr-common", "datafusion-expr", "datafusion-physical-expr", "datafusion-doc", "datafusion-functions-aggregate-common", "datafusion-sql", "datafusion-execution", "datafusion-macros", "datafusion-physical-expr-common", "datafusion-functions-nested", "datafusion-functions", "datafusion-common", "datafusion-functions-aggregate"}

[jayzhan211]

Using datafusion-optimizer in datafusion-sql can lead to dependency issue:

thread 'main' panicked at src/main.rs:84:9:
circular dependency detected from datafusion-optimizer to self via one of {"datafusion-functions-window", "datafusion-functions-window-common", "datafusion-expr-common", "datafusion-expr", "datafusion-physical-expr", "datafusion-doc", "datafusion-functions-aggregate-common", "datafusion-sql", "datafusion-execution", "datafusion-macros", "datafusion-physical-expr-common", "datafusion-functions-nested", "datafusion-functions", "datafusion-common", "datafusion-functions-aggregate"}

You shouldn't call the optimizer in sql, after the plan is built, we will call optimizer to rewrite the plan.

[irenjj]

Using datafusion-optimizer in datafusion-sql can lead to dependency issue:

thread 'main' panicked at src/main.rs:84:9:
circular dependency detected from datafusion-optimizer to self via one of {"datafusion-functions-window", "datafusion-functions-window-common", "datafusion-expr-common", "datafusion-expr", "datafusion-physical-expr", "datafusion-doc", "datafusion-functions-aggregate-common", "datafusion-sql", "datafusion-execution", "datafusion-macros", "datafusion-physical-expr-common", "datafusion-functions-nested", "datafusion-functions", "datafusion-common", "datafusion-functions-aggregate"}

You shouldn't call the optimizer in sql, after the plan is built, we will call optimizer to rewrite the plan.

got it