Repository: AbsaOSS/pramen (language: python)

Issue:
JDBC Native fails on variable not found when `@infoDate` is used as part of a function
## Describe the bug
JDBC Native SQL queries allow specifying arbitrary expressions, like an example below. But an error occurs when used this way:
```
za.co.absa.pramen.core.expr.exceptions.SyntaxErrorException: Unset variable 'infoDate' used.
  at za.co.absa.pramen.core.expr.parser.DateExpressionEvaluator.addVariable(DateExpressionEvaluator.scala:48)
  at za.co.absa.pramen.core.expr.parser.Parser.$anonfun$parse$1(Parser.scala:105)
```

## Code and/or configuration snippet that caused the issue
input.sql = 
```sql
SELECT * FROM my_table WHERE date_col='@{yearMonthOf(plusMonths(@infoDate, 1))}-16'
```

## Expected behavior
Pramen should return a valid date and not throw an exception in this situation.

## Context
- Pramen/pramen-py version: 1.8.9
- Spark version:
- Scala/Python version:
- Operating system:



Discussion / hints:

Fix patch:
diff --git a/.github/workflows/release.yml b/.github/workflows/release.yml
index ea95b869e..3ff081b0c 100644
--- a/.github/workflows/release.yml
+++ b/.github/workflows/release.yml
@@ -33,7 +33,7 @@ jobs:
     steps:
       - uses: actions/checkout@v4
         with:
-          ref: main
+          ref: ${{ github.event.inputs.branch }}
           
       - uses: actions/setup-python@v4
         with:
@@ -62,7 +62,7 @@ jobs:
       - name: Checkout code
         uses: actions/checkout@v4
         with:
-          ref: main
+          ref: ${{ github.event.inputs.branch }}
           
       - name: Prepare the release branch
         id: release_branch1
@@ -122,7 +122,7 @@ jobs:
       - name: Checkout code
         uses: actions/checkout@v4
         with:
-          ref: main
+          ref: ${{ github.event.inputs.branch }}
 
       - name: Setup JDK and sbt
         uses: actions/setup-java@v4.2.1
@@ -165,7 +165,7 @@ jobs:
       - name: Checkout code
         uses: actions/checkout@v4
         with:
-          ref: main
+          ref: ${{ github.event.inputs.branch }}
 
       - name: Checkout the release branch
         id: release_branch3
@@ -179,7 +179,7 @@ jobs:
           git checkout release/$VERSION
 
       - name: Create Pull Request
-        run: gh pr create -B main -H "release/$VERSION" --title "Release Pramen v$VERSION" --body 'Created by Github action'
+        run: gh pr create -B ${{ github.event.inputs.branch }} -H "release/$VERSION" --title "Release Pramen v$VERSION" --body 'Created by Github action'
         env:
           GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
           VERSION: ${{ steps.release_branch3.outputs.VERSION }}
diff --git a/pramen/core/src/main/scala/za/co/absa/pramen/core/expr/DateExprEvaluator.scala b/pramen/core/src/main/scala/za/co/absa/pramen/core/expr/DateExprEvaluator.scala
index 418d72ddd..0f9dbd36b 100644
--- a/pramen/core/src/main/scala/za/co/absa/pramen/core/expr/DateExprEvaluator.scala
+++ b/pramen/core/src/main/scala/za/co/absa/pramen/core/expr/DateExprEvaluator.scala
@@ -51,7 +51,7 @@ class DateExprEvaluator {
     val tokens = new Lexer(expression).lex()
 
     val exprBuilder = new DateExpressionEvaluator(vars.toMap, expression)
-    new Parser(tokens, exprBuilder).parse()
+    new Parser(expression, tokens, exprBuilder).parse()
 
     exprBuilder.getResult match {
       case d: LocalDate => d
@@ -63,7 +63,7 @@ class DateExprEvaluator {
     val tokens = new Lexer(expression).lex()
 
     val exprBuilder = new DateExpressionEvaluator(vars.toMap, expression)
-    new Parser(tokens, exprBuilder).parse()
+    new Parser(expression, tokens, exprBuilder).parse()
 
     exprBuilder.getResult match {
       case n: Int => n
@@ -75,8 +75,31 @@ class DateExprEvaluator {
     val tokens = new Lexer(expression).lex()
 
     val exprBuilder = new DateExpressionEvaluator(vars.toMap, expression)
-    new Parser(tokens, exprBuilder).parse()
+    new Parser(expression, tokens, exprBuilder).parse()
 
     exprBuilder.getResult
   }
+
+  def contains(variable: String): Boolean = {
+    vars.contains(variable)
+  }
+
+  def getInt(variable: String): Int = {
+    vars(variable) match {
+      case n: Int => n
+      case d => throw new IllegalArgumentException(s"Expected a numeric variable, but a date ($d) is returned for '$variable' variable.")
+    }
+  }
+
+  def getDate(variable: String): LocalDate = {
+    vars(variable) match {
+      case d: LocalDate => d
+      case n => throw new IllegalArgumentException(s"Expected a date variable, but a number ($n) is returned for '$variable' variable.")
+    }
+  }
+
+  def getAny(variable: String): Any = {
+    vars(variable)
+  }
+
 }
diff --git a/pramen/core/src/main/scala/za/co/absa/pramen/core/expr/parser/Parser.scala b/pramen/core/src/main/scala/za/co/absa/pramen/core/expr/parser/Parser.scala
index f012f1d3b..c128385e7 100644
--- a/pramen/core/src/main/scala/za/co/absa/pramen/core/expr/parser/Parser.scala
+++ b/pramen/core/src/main/scala/za/co/absa/pramen/core/expr/parser/Parser.scala
@@ -24,7 +24,7 @@ import za.co.absa.pramen.core.expr.lexer.Token._
 import java.time.LocalDate
 import scala.collection.mutable.ListBuffer
 
-class Parser(tokens: List[Token], builder: DateExprBuilder) {
+class Parser(expr: String, tokens: List[Token], builder: DateExprBuilder) {
 
   val STATE0 = 0
   val STATE_VARIABLE = 1
@@ -47,18 +47,18 @@ class Parser(tokens: List[Token], builder: DateExprBuilder) {
           case VAR_PREFIX(_) =>
             state = STATE_VARIABLE
           case COMMA(pos) =>
-            throw new SyntaxErrorException(s"Unexpected ',' at pos $pos")
+            throw new SyntaxErrorException(s"Unexpected ',' at pos $pos of expression: '$expr'")
           case OPEN_PARAN(pos) =>
             paranPos += pos
             builder.openParen(pos)
           case CLOSE_PARAN(pos) =>
             if (paranPos.isEmpty) {
-              throw new SyntaxErrorException(s"Unmatched ')' at pos $pos")
+              throw new SyntaxErrorException(s"Unmatched ')' at pos $pos of expression: '$expr'")
             }
             paranPos.remove(paranPos.size - 1)
             builder.closeParen(pos)
           case PLUS(pos) =>
-            throw new SyntaxErrorException(s"Unexpected '+' at pos $pos")
+            throw new SyntaxErrorException(s"Unexpected '+' at pos $pos of expression: '$expr'")
           case MINUS(_) =>
             state = MINUS_NUM
           case NAME(pos, s) =>
@@ -68,12 +68,12 @@ class Parser(tokens: List[Token], builder: DateExprBuilder) {
             state = STATE1
           case DATE_LITERAL(pos, s) =>
             builder.addDateLiteral(LocalDate.parse(s, dateFormatter), pos)
-          case _ => new SyntaxErrorException(s"Unexpected '$token' at pos ${token.pos}")
+          case _ => new SyntaxErrorException(s"Unexpected '$token' at pos ${token.pos} of expression: '$expr'")
         }
       } else if (state == STATE1) {
         token match {
           case VAR_PREFIX(pos) =>
-            throw new SyntaxErrorException(s"Unexpected variable at pos $pos")
+            throw new SyntaxErrorException(s"Unexpected variable at pos $pos of expression: '$expr'")
           case COMMA(_) =>
             state = STATE0
           case OPEN_PARAN(pos) =>
@@ -82,7 +82,7 @@ class Parser(tokens: List[Token], builder: DateExprBuilder) {
             state = STATE0
           case CLOSE_PARAN(pos) =>
             if (paranPos.isEmpty) {
-              throw new SyntaxErrorException(s"Unmatched ')' at pos $pos")
+              throw new SyntaxErrorException(s"Unmatched ')' at pos $pos of expression: '$expr'")
             }
             paranPos.remove(paranPos.size - 1)
             builder.closeParen(pos)
@@ -97,14 +97,14 @@ class Parser(tokens: List[Token], builder: DateExprBuilder) {
             builder.addFunction(s, pos)
           case NUM_LITERAL(pos, s) =>
             builder.addNumLiteral(s.toInt, pos)
-          case _ => new SyntaxErrorException(s"Unexpected '$token' at pos ${token.pos}")
+          case _ => new SyntaxErrorException(s"Unexpected '$token' at pos ${token.pos} of expression: '$expr'")
         }
       } else if (state == STATE_VARIABLE) {
         token match {
           case NAME(pos, s) =>
             builder.addVariable(s, pos)
             state = STATE1
-          case _ => new SyntaxErrorException(s"Unexpected '$token' at pos ${token.pos}")
+          case _ => new SyntaxErrorException(s"Unexpected '$token' at pos ${token.pos} of expression: '$expr'")
         }
       } else if (state == MINUS_NUM) {
         token match {
@@ -120,13 +120,13 @@ class Parser(tokens: List[Token], builder: DateExprBuilder) {
           case NUM_LITERAL(pos, s) =>
             builder.addNumLiteral(-s.toInt, pos)
             state = STATE1
-          case _ => new SyntaxErrorException(s"Unexpected '$token' at pos ${token.pos}")
+          case _ => new SyntaxErrorException(s"Unexpected '$token' at pos ${token.pos} of expression: '$expr'")
         }
       }
     }
 
     if (paranPos.nonEmpty) {
-      throw new SyntaxErrorException(s"Unmatched '(' at pos ${paranPos.head}")
+      throw new SyntaxErrorException(s"Unmatched '(' at pos ${paranPos.head} of expression: '$expr'")
     }
   }
 
diff --git a/pramen/core/src/main/scala/za/co/absa/pramen/core/reader/TableReaderJdbcNative.scala b/pramen/core/src/main/scala/za/co/absa/pramen/core/reader/TableReaderJdbcNative.scala
index e7a1831bd..e9f597b24 100644
--- a/pramen/core/src/main/scala/za/co/absa/pramen/core/reader/TableReaderJdbcNative.scala
+++ b/pramen/core/src/main/scala/za/co/absa/pramen/core/reader/TableReaderJdbcNative.scala
@@ -20,6 +20,7 @@ import com.typesafe.config.Config
 import org.apache.spark.sql.{DataFrame, SparkSession}
 import org.slf4j.LoggerFactory
 import za.co.absa.pramen.api.Query
+import za.co.absa.pramen.core.expr.DateExprEvaluator
 import za.co.absa.pramen.core.reader.model.{JdbcConfig, TableReaderJdbcConfig}
 import za.co.absa.pramen.core.utils.{JdbcNativeUtils, JdbcSparkUtils, StringUtils, TimeUtils}
 
@@ -128,11 +129,15 @@ object TableReaderJdbcNative {
   }
 
   def getFilteredSql(sqlExpression: String, infoDateBegin: LocalDate, infoDateEnd: LocalDate): String = {
-    val f1 = StringUtils.replaceFormattedDateExpression(sqlExpression, "dateFrom", infoDateBegin)
-    val f2 = StringUtils.replaceFormattedDateExpression(f1, "dateTo", infoDateEnd)
-    val f3 = StringUtils.replaceFormattedDateExpression(f2, "date", infoDateEnd)
-    val f4 = StringUtils.replaceFormattedDateExpression(f3, "infoDateBegin", infoDateBegin)
-    val f5 = StringUtils.replaceFormattedDateExpression(f4, "infoDateEnd", infoDateEnd)
-    StringUtils.replaceFormattedDateExpression(f5, "infoDate", infoDateEnd)
+    val expr = new DateExprEvaluator()
+
+    expr.setValue("dateFrom", infoDateBegin)
+    expr.setValue("dateTo", infoDateEnd)
+    expr.setValue("date", infoDateEnd)
+    expr.setValue("infoDateBegin", infoDateBegin)
+    expr.setValue("infoDateEnd", infoDateEnd)
+    expr.setValue("infoDate", infoDateEnd)
+
+    StringUtils.replaceFormattedDateExpression(sqlExpression, expr)
   }
 }
diff --git a/pramen/core/src/main/scala/za/co/absa/pramen/core/utils/SparkUtils.scala b/pramen/core/src/main/scala/za/co/absa/pramen/core/utils/SparkUtils.scala
index 2c9ee16bc..08288ecc9 100644
--- a/pramen/core/src/main/scala/za/co/absa/pramen/core/utils/SparkUtils.scala
+++ b/pramen/core/src/main/scala/za/co/absa/pramen/core/utils/SparkUtils.scala
@@ -25,6 +25,7 @@ import org.apache.spark.sql.types._
 import org.apache.spark.sql.{DataFrame, SparkSession}
 import org.slf4j.LoggerFactory
 import za.co.absa.pramen.api.FieldChange
+import za.co.absa.pramen.core.expr.DateExprEvaluator
 import za.co.absa.pramen.core.pipeline.TransformExpression
 
 import java.io.ByteArrayOutputStream
@@ -205,10 +206,14 @@ object SparkUtils {
 
   def applyFilters(df: DataFrame, filters: Seq[String], infoDate: LocalDate, dateFrom: LocalDate, dateTo: LocalDate): DataFrame = {
     filters.foldLeft(df)((df, filter) => {
-      val f1 = StringUtils.replaceFormattedDateExpression(filter, "dateFrom", dateFrom)
-      val f2 = StringUtils.replaceFormattedDateExpression(f1, "dateTo", dateTo)
-      val f3 = StringUtils.replaceFormattedDateExpression(f2, "date", infoDate)
-      val actualFilter = f3.replaceAll("@infoDate", s"date'${infoDate.toString}'")
+      val exprEvaluator = new DateExprEvaluator()
+
+      exprEvaluator.setValue("dateFrom", dateFrom)
+      exprEvaluator.setValue("dateTo", dateTo)
+      exprEvaluator.setValue("date", infoDate)
+
+      val withInfoDate = filter.replaceAll("@infoDate", s"date'${infoDate.toString}'")
+      val actualFilter = StringUtils.replaceFormattedDateExpression(withInfoDate, exprEvaluator)
 
       log.info(s"Applying filter: $actualFilter")
       df.filter(expr(actualFilter))
diff --git a/pramen/core/src/main/scala/za/co/absa/pramen/core/utils/StringUtils.scala b/pramen/core/src/main/scala/za/co/absa/pramen/core/utils/StringUtils.scala
index aac920484..56f456f91 100644
--- a/pramen/core/src/main/scala/za/co/absa/pramen/core/utils/StringUtils.scala
+++ b/pramen/core/src/main/scala/za/co/absa/pramen/core/utils/StringUtils.scala
@@ -18,6 +18,7 @@ package za.co.absa.pramen.core.utils
 
 import za.co.absa.pramen.core.exceptions.ThreadStackTrace
 import za.co.absa.pramen.core.expr.DateExprEvaluator
+import za.co.absa.pramen.core.expr.exceptions.SyntaxErrorException
 
 import java.time.LocalDate
 import java.time.format.DateTimeFormatter
@@ -223,17 +224,17 @@ object StringUtils {
     *
     *
     * @param template A template to replace variablesin.
-    * @param dateVar A variable name for the date (does not include '@') - case sensitive.
-    * @param date The date to replace the the variable with.
+    * @param expr An expression evaluator for date and other types of expressions.
     * @return The processed template.
     */
-  def replaceFormattedDateExpression(template: String, dateVar: String, date: LocalDate): String = {
+  def replaceFormattedDateExpression(template: String, expr: DateExprEvaluator): String = {
+    val identifierChars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_"
     val output = new StringBuilder()
     val outputPartial = new StringBuilder()
     val outputExpression = new StringBuilder()
+    var variable = new StringBuilder()
     var state = 0
     var i = 0
-    var j = 0
 
     val COPY_FROM_TEMPLATE = 0
     val POSSIBLE_VARIABLE = 1
@@ -241,83 +242,101 @@ object StringUtils {
     val FORMAT_EXPRESSION = 3
     val DATE_EXPRESSION = 4
 
-    val expr = new DateExprEvaluator
-    expr.setValue(dateVar, date)
-
-    while (i < template.length) {
-      val c = template(i)
-      state match {
-        case COPY_FROM_TEMPLATE =>
-          if (c == '@') {
-            outputExpression.clear()
-            outputPartial.clear()
-            if (i < template.length - 2 && template(i + 1) == '{') {
-              i += 1
-              state = DATE_EXPRESSION
+    try {
+      while (i < template.length) {
+        val c = template(i)
+        state match {
+          case COPY_FROM_TEMPLATE =>
+            if (c == '@') {
+              outputExpression.clear()
+              outputPartial.clear()
+              if (i < template.length - 2 && template(i + 1) == '{') {
+                i += 1
+                state = DATE_EXPRESSION
+              } else {
+                state = POSSIBLE_VARIABLE
+                outputPartial.append(s"$c")
+              }
             } else {
-              state = POSSIBLE_VARIABLE
-              j = 0
-              outputPartial.append(s"$c")
+              output.append(s"$c")
             }
-          } else {
-            output.append(s"$c")
-          }
-        case POSSIBLE_VARIABLE =>
-          outputPartial.append(s"$c")
-          if (c == dateVar(j)) {
-            j += 1
-            if (j == dateVar.length) {
-              state = END_OF_VARIABLE
-              if (i == template.length - 1) {
-                output.append(s"$date")
+          case POSSIBLE_VARIABLE =>
+            val isIdChar = identifierChars.contains(c)
+            if (isIdChar) {
+              outputPartial.append(s"$c")
+              variable.append(s"$c")
+            } else {
+              if (c == '%') {
+                state = FORMAT_EXPRESSION
+                outputPartial.clear()
+              } else if (expr.contains(variable.toString())) {
+                state = END_OF_VARIABLE
+                i -= 1
+              } else {
+                output.append(s"${outputPartial.toString()}$c")
+                outputPartial.clear()
+                variable.clear()
+                state = COPY_FROM_TEMPLATE
               }
             }
-          } else {
-            output.append(s"${outputPartial.toString()}")
-            outputPartial.clear()
-            state = COPY_FROM_TEMPLATE
-          }
-        case END_OF_VARIABLE =>
-          if (c == '%') {
-            state = FORMAT_EXPRESSION
-            outputPartial.clear()
-          } else {
-            if (outputExpression.nonEmpty) {
-              val calculatedDate = expr.evalDate(outputExpression.toString())
-              output.append(s"$calculatedDate$c")
+          case END_OF_VARIABLE =>
+            if (c == '%') {
+              state = FORMAT_EXPRESSION
+              outputPartial.clear()
             } else {
-              output.append(s"$date$c")
+              if (outputExpression.nonEmpty) {
+                val value = expr.evalAny(outputExpression.toString())
+                output.append(s"$value$c")
+              } else {
+                val value = expr.getAny(variable.toString())
+                output.append(s"$value$c")
+              }
+              variable.clear()
+              state = COPY_FROM_TEMPLATE
             }
-            state = COPY_FROM_TEMPLATE
-          }
-        case FORMAT_EXPRESSION =>
-          if (c == '%') {
-            state = COPY_FROM_TEMPLATE
-            if (outputExpression.nonEmpty) {
-              val calculatedDate = expr.evalDate(outputExpression.toString())
+          case FORMAT_EXPRESSION =>
+            if (c == '%') {
+              state = COPY_FROM_TEMPLATE
               val formatter = DateTimeFormatter.ofPattern(outputPartial.toString())
-              output.append(s"${formatter.format(calculatedDate)}")
+              if (outputExpression.nonEmpty) {
+                val calculatedDate = expr.evalDate(outputExpression.toString())
+                output.append(s"${formatter.format(calculatedDate)}")
+                variable.clear()
+              } else {
+                val date = expr.getDate(variable.toString())
+                output.append(s"${formatter.format(date)}")
+                variable.clear()
+              }
             } else {
-              val formatter = DateTimeFormatter.ofPattern(outputPartial.toString())
-              output.append(s"${formatter.format(date)}")
+              outputPartial.append(s"$c")
             }
-          } else {
-            outputPartial.append(s"$c")
-          }
-        case DATE_EXPRESSION =>
-          if (c == '}') {
-            state = END_OF_VARIABLE
-            if (i == template.length - 1) {
-              val calculatedDate = expr.evalDate(outputExpression.toString())
-              output.append(s"$calculatedDate")
+          case DATE_EXPRESSION =>
+            if (c == '}') {
+              state = END_OF_VARIABLE
+              if (i == template.length - 1) {
+                val calculatedExpr = expr.evalAny(outputExpression.toString())
+                output.append(s"$calculatedExpr")
+              }
+            } else {
+              outputExpression.append(s"$c")
             }
-          } else {
-            outputExpression.append(s"$c")
-          }
 
+        }
+        i += 1
+      }
+
+      if (state == POSSIBLE_VARIABLE) {
+        if (expr.contains(variable.toString())) {
+          val anyVal = expr.getAny(variable.toString())
+          output.append(s"$anyVal")
+        } else {
+          output.append(s"${outputPartial.toString()}")
+        }
       }
-      i += 1
+    } catch {
+      case ex: SyntaxErrorException => throw new IllegalArgumentException(s"Syntax error in SQL expression: $template", ex)
     }
+
     if (state == DATE_EXPRESSION) {
       throw new IllegalArgumentException(s"No matching '{' in the date expression: $template")
     }


Test patch:
diff --git a/pramen/core/src/test/scala/za/co/absa/pramen/core/tests/expr/DateExprEvaluatorSuite.scala b/pramen/core/src/test/scala/za/co/absa/pramen/core/tests/expr/DateExprEvaluatorSuite.scala
index 972f7d1b2..acf7a1e39 100644
--- a/pramen/core/src/test/scala/za/co/absa/pramen/core/tests/expr/DateExprEvaluatorSuite.scala
+++ b/pramen/core/src/test/scala/za/co/absa/pramen/core/tests/expr/DateExprEvaluatorSuite.scala
@@ -43,6 +43,39 @@ class DateExprEvaluatorSuite extends AnyWordSpec {
     expr.setValue("now", now)
     expr.setValue("two", 2)
 
+    "check variable existence" in {
+      assert(expr.contains("now"))
+      assert(expr.contains("two"))
+      assert(!expr.contains("dummy"))
+      assert(!expr.contains("TWO"))
+    }
+
+    "get date variable directly" in {
+      val actual = expr.getDate("now")
+
+      assert(actual == now)
+    }
+
+    "get numeric variable directly" in {
+      val actual = expr.getInt("two")
+
+      assert(actual == 2)
+    }
+
+    "get Any variable directly 1" in {
+      val actual = expr.getAny("now")
+
+      assert(actual.isInstanceOf[LocalDate])
+      assert(actual.asInstanceOf[LocalDate] == now)
+    }
+
+    "get Any variable directly 2" in {
+      val actual = expr.getAny("two")
+
+      assert(actual.isInstanceOf[Int])
+      assert(actual.asInstanceOf[Int] == 2)
+    }
+
     "evaluate a simple expression" in {
       assert(expr.evalDate("@now") == now)
     }
@@ -153,6 +186,20 @@ class DateExprEvaluatorSuite extends AnyWordSpec {
       assert(ex.getMessage.contains("could not be parsed at index"))
     }
 
+    "throw an exception on a wrong type variable (date)" in {
+      val ex = intercept[IllegalArgumentException] {
+        expr.getDate("two")
+      }
+      assert(ex.getMessage.contains("Expected a date variable"))
+    }
+
+    "throw an exception on a wrong type variable (int)" in {
+      val ex = intercept[IllegalArgumentException] {
+        expr.getInt("now")
+      }
+      assert(ex.getMessage.contains("Expected a numeric variable"))
+    }
+
     "throw an exception on a wrong type (date)" in {
       val ex = intercept[SyntaxErrorException] {
         expr.evalDate("minusMonths(@now, @now)")
diff --git a/pramen/core/src/test/scala/za/co/absa/pramen/core/tests/utils/StringUtilsSuite.scala b/pramen/core/src/test/scala/za/co/absa/pramen/core/tests/utils/StringUtilsSuite.scala
index 16a7b6edf..73cddc6b2 100644
--- a/pramen/core/src/test/scala/za/co/absa/pramen/core/tests/utils/StringUtilsSuite.scala
+++ b/pramen/core/src/test/scala/za/co/absa/pramen/core/tests/utils/StringUtilsSuite.scala
@@ -235,11 +235,15 @@ class StringUtilsSuite extends AnyWordSpec {
 
   "replaceFormattedDateExpression" should {
     val infoDate = LocalDate.of(2022, 2, 18)
+    val expr = new DateExprEvaluator()
+
+    expr.setValue("date", infoDate)
+    expr.setValue("dateTo", infoDate.plusDays(1))
 
     "work with normal variables" in {
       val template = "SELECT @dat FROM my_table_@date + 1"
 
-      val replaced = replaceFormattedDateExpression(template, "date", infoDate)
+      val replaced = replaceFormattedDateExpression(template, expr)
 
       assert(replaced == "SELECT @dat FROM my_table_2022-02-18 + 1")
     }
@@ -247,7 +251,7 @@ class StringUtilsSuite extends AnyWordSpec {
     "work with variables at the end" in {
       val template = "SELECT @dat FROM my_table_@date"
 
-      val replaced = replaceFormattedDateExpression(template, "date", infoDate)
+      val replaced = replaceFormattedDateExpression(template, expr)
 
       assert(replaced == "SELECT @dat FROM my_table_2022-02-18")
     }
@@ -255,7 +259,7 @@ class StringUtilsSuite extends AnyWordSpec {
     "work with just variables" in {
       val template = "@date"
 
-      val replaced = replaceFormattedDateExpression(template, "date", infoDate)
+      val replaced = replaceFormattedDateExpression(template, expr)
 
       assert(replaced == "2022-02-18")
     }
@@ -263,15 +267,31 @@ class StringUtilsSuite extends AnyWordSpec {
     "work with 2 variables" in {
       val template = "@date @date"
 
-      val replaced = replaceFormattedDateExpression(template, "date", infoDate)
+      val replaced = replaceFormattedDateExpression(template, expr)
 
       assert(replaced == "2022-02-18 2022-02-18")
     }
 
+    "work with 2 different variables" in {
+      val template = "@date @dateTo%yyyyMMdd%"
+
+      val replaced = replaceFormattedDateExpression(template, expr)
+
+      assert(replaced == "2022-02-18 20220219")
+    }
+
+    "work with 2 different variable expressions" in {
+      val template = "@{@date + 1}%yyyyMMdd% @{@dateTo + 1}%yyyyMMdd%"
+
+      val replaced = replaceFormattedDateExpression(template, expr)
+
+      assert(replaced == "20220219 20220220")
+    }
+
     "work with formatted variables" in {
       val template = "SELECT * FROM my_table_@date%yyyyMMdd% WHERE a = b"
 
-      val replaced = replaceFormattedDateExpression(template, "date", infoDate)
+      val replaced = replaceFormattedDateExpression(template, expr)
 
       assert(replaced == "SELECT * FROM my_table_20220218 WHERE a = b")
     }
@@ -279,7 +299,7 @@ class StringUtilsSuite extends AnyWordSpec {
     "work with just formatted variables" in {
       val template = "@date%yyyyMMdd%"
 
-      val replaced = replaceFormattedDateExpression(template, "date", infoDate)
+      val replaced = replaceFormattedDateExpression(template, expr)
 
       assert(replaced == "20220218")
     }
@@ -287,7 +307,7 @@ class StringUtilsSuite extends AnyWordSpec {
     "work with 2 formatted variables" in {
       val template = "@date%yyyyMMdd%@date%ddMMyyy%"
 
-      val replaced = replaceFormattedDateExpression(template, "date", infoDate)
+      val replaced = replaceFormattedDateExpression(template, expr)
 
       assert(replaced == "2022021818022022")
     }
@@ -295,7 +315,7 @@ class StringUtilsSuite extends AnyWordSpec {
     "work with partial formatter" in {
       val template = "@date%yyyyMM%"
 
-      val replaced = replaceFormattedDateExpression(template, "date", infoDate)
+      val replaced = replaceFormattedDateExpression(template, expr)
 
       assert(replaced == "202202")
     }
@@ -303,7 +323,7 @@ class StringUtilsSuite extends AnyWordSpec {
     "work with expressions" in {
       val template = "my_table_@{@date + 1}"
 
-      val replaced = replaceFormattedDateExpression(template, "date", infoDate)
+      val replaced = replaceFormattedDateExpression(template, expr)
 
       assert(replaced == "my_table_2022-02-19")
     }
@@ -311,7 +331,7 @@ class StringUtilsSuite extends AnyWordSpec {
     "work with formatted expressions" in {
       val template = "my_table_@{@date + 1}%yyyyMM%"
 
-      val replaced = replaceFormattedDateExpression(template, "date", infoDate)
+      val replaced = replaceFormattedDateExpression(template, expr)
 
       assert(replaced == "my_table_202202")
     }
@@ -319,24 +339,35 @@ class StringUtilsSuite extends AnyWordSpec {
     "work with formatted expressions 2" in {
       val template = "SELECT * FROM my_table_@{plusMonths(@date, 1)}%yyyyMMdd% WHERE a = b"
 
-      val replaced = replaceFormattedDateExpression(template, "date", infoDate)
+      val replaced = replaceFormattedDateExpression(template, expr)
 
       assert(replaced == "SELECT * FROM my_table_20220318 WHERE a = b")
     }
 
     "work with formatted expressions 3" in {
-      val template = "SELECT * FROM my_table WHERE snapshot_date = date'@{beginOfMonth(minusMonths(@infoDate, 1))}'"
+      val template = "SELECT * FROM my_table WHERE snapshot_date = date'@{beginOfMonth(minusMonths(@date, 1))}'"
 
-      val replaced = replaceFormattedDateExpression(template, "infoDate", infoDate)
+      val replaced = replaceFormattedDateExpression(template, expr)
 
       assert(replaced == "SELECT * FROM my_table WHERE snapshot_date = date'2022-01-01'")
     }
 
+    "throw an exception if an expression has syntax errors" in {
+      val template = "@{@date + 1}%yyyyMMdd% @{dateTo + 1}%yyyyMMdd%"
+
+      val ex = intercept[IllegalArgumentException] {
+        replaceFormattedDateExpression(template, expr)
+      }
+
+      assert(ex.getMessage.contains("Syntax error in SQL expression: @{@date + 1}%yyyyMMdd% @{dateTo + 1}%yyyyMMdd%"))
+      assert(ex.getCause.getMessage.contains("Unexpected '+' at pos 7 of expression: 'dateTo + 1'"))
+    }
+
     "throw an exception if format is incomplete" in {
       val template = "SELECT * FROM my_table WHERE snapshot_date = date'@infoDate%yyyy-mm-dd'"
 
       val ex = intercept[IllegalArgumentException] {
-        replaceFormattedDateExpression(template, "infoDate", infoDate)
+        replaceFormattedDateExpression(template, expr)
       }
 
       assert(ex.getMessage.contains("No matching '%' in the formatted date expression: SELECT * FROM my_table WHERE snapshot_date = date'@infoDate%yyyy-mm-dd'"))
@@ -346,7 +377,7 @@ class StringUtilsSuite extends AnyWordSpec {
       val template = "SELECT * FROM my_table WHERE snapshot_date = date'@{beginOfMonth(minusMonths(@infoDate, 1))'"
 
       val ex = intercept[IllegalArgumentException] {
-        replaceFormattedDateExpression(template, "infoDate", infoDate)
+        replaceFormattedDateExpression(template, expr)
       }
 
       assert(ex.getMessage.contains("No matching '{' in the date expression: SELECT * FROM my_table WHERE snapshot_date = date'@{beginOfMonth(minusMonths(@infoDate, 1))'"))