Repository: tobymao/sqlglot

Issue:
Incorrect translation of DATE_SUB with INTERVAL from BigQuery to Databricks
Hello! I'm using sqlglot to translate some BQ queries to Databricks SQL and I'm receiving an error specifically on the DATE_SUB transformation.

Consider the following test table:

```sql
-- Create the table
CREATE TABLE my_dataset.my_table (
  id INT64,
  name STRING,
  event_date DATE
);

-- Insert data into the table
INSERT INTO my_dataset.my_table (id, name, event_date)
VALUES
  (1, 'Alice', DATE '2023-01-01'),
  (2, 'Bob', DATE '2023-02-15'),
  (3, 'Charlie', DATE '2023-03-20'),
  (4, 'Diana', DATE '2023-04-10'),
  (5, 'Eve', DATE '2023-05-25');
```
The original query looks something like this:

```sql
SELECT id,
       name,
       event_date,
FROM my_dataset.my_table
WHERE event_date <= DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)
```

The translated one looks like this:

```sql
SELECT id,
       name,
       event_date
FROM my_dataset.my_table
WHERE date <= DATE_ADD(CURRENT_DATE, '2' * -1)
```
This will raise the following error on Databricks SQL:

```shell
[DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE] Cannot resolve "date_add(CURRENT_DATE, (2 * -1))" due to data type mismatch: Parameter 2 requires the ("INT" or "SMALLINT" or "TINYINT") type, however "(2 * -1)" has the type "DOUBLE". SQLSTATE: 42K09
```

I think it needs a type cast to integer before passing to DATE_ADD function. Also, idk why it uses a string as the first parameter `'2'`, but it seems to work that way if we cast it to INT.

**Official Documentation**
https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date_sub
https://docs.databricks.com/aws/en/sql/language-manual/functions/date_add

Discussion:

Solution patch:
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index f7cea08a7b..6084d449e1 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -73,16 +73,14 @@ def _add_date_sql(self: Hive.Generator, expression: DATE_ADD_OR_SUB) -> str:
     if isinstance(expression, exp.DateSub):
         multiplier *= -1
 
-    if expression.expression.is_number:
-        modified_increment = exp.Literal.number(expression.expression.to_py() * multiplier)
-    else:
-        modified_increment = expression.expression
-        if multiplier != 1:
-            modified_increment = exp.Mul(  # type: ignore
-                this=modified_increment, expression=exp.Literal.number(multiplier)
-            )
-
-    return self.func(func, expression.this, modified_increment)
+    increment = expression.expression
+    if isinstance(increment, exp.Literal):
+        value = increment.to_py() if increment.is_number else int(increment.name)
+        increment = exp.Literal.number(value * multiplier)
+    elif multiplier != 1:
+        increment *= exp.Literal.number(multiplier)
+
+    return self.func(func, expression.this, increment)
 
 
 def _date_diff_sql(self: Hive.Generator, expression: exp.DateDiff | exp.TsOrDsDiff) -> str:
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index b08eb8a47e..4a2531bd5f 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -7017,7 +7017,7 @@ def maybe_copy(instance, copy=True):
     return instance.copy() if copy and instance else instance
 
 
-def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
+def _to_s(node: t.Any, verbose: bool = False, level: int = 0, repr_str: bool = False) -> str:
     """Generate a textual representation of an Expression tree"""
     indent = "\n" + ("  " * (level + 1))
     delim = f",{indent}"
@@ -7038,7 +7038,10 @@ def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
             indent = ""
             delim = ", "
 
-        items = delim.join([f"{k}={_to_s(v, verbose, level + 1)}" for k, v in args.items()])
+        repr_str = node.is_string or (isinstance(node, Identifier) and node.quoted)
+        items = delim.join(
+            [f"{k}={_to_s(v, verbose, level + 1, repr_str=repr_str)}" for k, v in args.items()]
+        )
         return f"{node.__class__.__name__}({indent}{items})"
 
     if isinstance(node, list):
@@ -7046,6 +7049,10 @@ def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
         items = f"{indent}{items}" if items else ""
         return f"[{items}]"
 
+    # We use the representation of the string to avoid stripping out important whitespace
+    if repr_str and isinstance(node, str):
+        node = repr(node)
+
     # Indent multiline strings to match the current level
     return indent.join(textwrap.dedent(str(node).strip("\n")).splitlines())
 
diff --git a/sqlglot/optimizer/pushdown_projections.py b/sqlglot/optimizer/pushdown_projections.py
index 5fa413936c..c7831941d2 100644
--- a/sqlglot/optimizer/pushdown_projections.py
+++ b/sqlglot/optimizer/pushdown_projections.py
@@ -4,6 +4,7 @@
 from sqlglot.optimizer.qualify_columns import Resolver
 from sqlglot.optimizer.scope import Scope, traverse_scope
 from sqlglot.schema import ensure_schema
+from sqlglot.errors import OptimizeError
 
 # Sentinel value that means an outer query selecting ALL columns
 SELECT_ALL = object()
@@ -49,6 +50,10 @@ def pushdown_projections(expression, schema=None, remove_unused_selections=True)
 
         if isinstance(scope.expression, exp.SetOperation):
             left, right = scope.union_scopes
+            if len(left.expression.selects) != len(right.expression.selects):
+                scope_sql = scope.expression.sql()
+                raise OptimizeError(f"Invalid set operation due to column mismatch: {scope_sql}.")
+
             referenced_columns[left] = parent_selections
 
             if any(select.is_star for select in right.expression.selects):


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 7e8b3cb941..1f4324dfc4 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -320,6 +320,13 @@ def test_bigquery(self):
             "SELECT CAST(1 AS INT64)",
         )
 
+        self.validate_all(
+            "SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)",
+            write={
+                "bigquery": "SELECT DATE_SUB(CURRENT_DATE, INTERVAL '2' DAY)",
+                "databricks": "SELECT DATE_ADD(CURRENT_DATE, -2)",
+            },
+        )
         self.validate_all(
             "SELECT DATE_SUB(DATE '2008-12-25', INTERVAL 5 DAY)",
             write={
@@ -1312,8 +1319,8 @@ def test_bigquery(self):
                 "mysql": "DATE_ADD(CURRENT_DATE, INTERVAL '-1' DAY)",
                 "postgres": "CURRENT_DATE + INTERVAL '-1 DAY'",
                 "presto": "DATE_ADD('DAY', CAST('-1' AS BIGINT), CURRENT_DATE)",
-                "hive": "DATE_ADD(CURRENT_DATE, '-1')",
-                "spark": "DATE_ADD(CURRENT_DATE, '-1')",
+                "hive": "DATE_ADD(CURRENT_DATE, -1)",
+                "spark": "DATE_ADD(CURRENT_DATE, -1)",
             },
         )
         self.validate_all(
diff --git a/tests/test_expressions.py b/tests/test_expressions.py
index f357f85ba5..ec69d13379 100644
--- a/tests/test_expressions.py
+++ b/tests/test_expressions.py
@@ -9,6 +9,19 @@
 class TestExpressions(unittest.TestCase):
     maxDiff = None
 
+    def test_to_s(self):
+        self.assertEqual(repr(parse_one("5")), "Literal(this=5, is_string=False)")
+        self.assertEqual(repr(parse_one("5.3")), "Literal(this=5.3, is_string=False)")
+        self.assertEqual(repr(parse_one("True")), "Boolean(this=True)")
+        self.assertEqual(repr(parse_one("'  x'")), "Literal(this='  x', is_string=True)")
+        self.assertEqual(repr(parse_one("' \n  x'")), "Literal(this=' \\n  x', is_string=True)")
+        self.assertEqual(
+            repr(parse_one("   x ")), "Column(\n  this=Identifier(this=x, quoted=False))"
+        )
+        self.assertEqual(
+            repr(parse_one('"   x "')), "Column(\n  this=Identifier(this='   x ', quoted=True))"
+        )
+
     def test_arg_key(self):
         self.assertEqual(parse_one("sum(1)").find(exp.Literal).arg_key, "this")