[SPARK-48168][SQL] Add bitwise shifting operators support

[yaooqinn]

What changes were proposed in this pull request?

This PR introduces three bitwise shifting operators as aliases for existing shifting functions.

Why are the changes needed?

The bit shifting functions named in alphabet form vary from one platform to anthor. Take our shiftleft as an example,

• Hive, shiftleft (where we copied it from)
• MsSQL Server LEFT_SHIFT
• MySQL, N/A
• PostgreSQL, N/A
• Presto, bitwise_left_shift

The bit shifting operators share a much more common and consistent way for users to port their queries.

For self-consistent with existing bit operators in Spark, AND &, OR |, XOR ^ and NOT ~, we now add <<, >> and >>>.

Does this PR introduce any user-facing change?

Yes, new operators added but not behavior change

How was this patch tested?

new tests

Was this patch authored or co-authored using generative AI tooling?

no

[yaooqinn]

Thank you @dongjoon-hyun Let me look into this issue, it might take a while

[dongjoon-hyun]

No problem, @yaooqinn . We have enough time for 4.0.0. :)

[dongjoon-hyun]

It seems that TPCDS golden files are affected still.

[info] *** 23 TESTS FAILED ***
[error] Failed: Total 3499, Failed 23, Errors 0, Passed 3476, Ignored 4
[error] Failed tests:
[error] 	org.apache.spark.sql.SQLQuerySuite
[error] 	org.apache.spark.sql.TPCDSV1_4_PlanStabilitySuite
[error] 	org.apache.spark.sql.TPCDSV2_7_PlanStabilityWithStatsSuite
[error] 	org.apache.spark.sql.TPCDSV1_4_PlanStabilityWithStatsSuite
[error] 	org.apache.spark.sql.TPCDSV2_7_PlanStabilitySuite
[error] (sql / Test / test) sbt.TestsFailedException: Tests unsuccessful

[yaooqinn]

Thank you @dongjoon-hyun for providing the logs

[yaooqinn]

cc @cloud-fan

[ulysses-you]

thanks, merged to master(4.0.0)

[cloud-fan]

Have we considered the operator precedence? Does it follow C? If yes let's add tests and document it. https://en.cppreference.com/w/c/language/operator_precedence

[yaooqinn]

Hi @cloud-fan Thank you for the check.

I have made a follow-up to fix the precedence of these operators, the new precedence is between '+/-' and '&' with a left-to-right logic. https://github.com/apache/spark/pull/46753

As for the documentation you mentioned, it's orthogonal as there is no existing doc for this topic yet. So, I created SPARK-48426 to follow.