v0.1 to v0.2 upgrade

v0.2.* (latest v0.2.7)

New features

• Adds support for DISTINCT, LET (from FROM clause), system stored procedure calls (EXEC)
• Adds parser support for DML statements (INSERT, UPDATE, DELETE), Parameter, BY variable
• Improvements to LIKE pattern compilation performance
• Adds function to convert from UNIX epoch to TIMESTAMP and TIMESTAMP to UNIX epoch

Deprecated items

• AstRewriter, AstRewriterBase, MetaStrippingRewriter, RewriterTestBase
  • Existing AST rewriters upgraded to use PIG’s VisitorTransforms
  • AstRewriterBase to VisitorTransform guide provided here
• V0AstSerializer, AstSerializer, AstDeserializer, and classes & functions relating to the V0Ast
  • Users are encouraged to use PartiqlAst. Do not use ExprNode which was deprecated in v0.6.* and will be removed in an upcoming major version (see partiql-lang-kotlin#682 for tracking).

Misc/bug fixes

• New error codes for division by 0 and modulo 0
• [fix] float negative zero equality
• Removes invalid syntax check on case expressions w/ type parameters e.g., CAST(a AS DECIMAL(1, 2)) now does not throw
• [fix] LIMIT with value over 2^31 returning no values
• [fix] LIMIT clause execution order
• [fix] stop treating date parts as if they are string literals
• [fix] parsing of TRIM specification keywords (BOTH, LEADING, and TRAILING)
• Adds some AST rewriters — SelectStarRewriter and StaticTypeRewriter

Breaking changes

Breaking behavioral changes

1. JOIN requires an ON clause. In v0.1.*, the ON clause was optional, which caused ambiguous parsing of multiple JOIN

// ----- v0.1.* -----
// Initialization of components related to evaluating a query end-to-end
val ion = IonSystemBuilder.standard().build()
val parser = SqlParser(ion)
val pipeline = CompilerPipeline.standard(ion)
val evaluationSession = EvaluationSession.standard()

// Query with a JOIN without an ON clause. In v0.1.*, the ON condition is optional.
val query = "SELECT * FROM <<{'a': 1}>> INNER JOIN <<{'a': 2}>>"
val parsedQuery = parser.parseExprNode(query)
val compiledQuery = pipeline.compile(parsedQuery)
val result = compiledQuery.eval(evaluationSession)

// Query successfully evaluates. Here we compare the result to its string representation.
assertEquals("<<{'a': 1, 'a': 2}>>", result.toString())

// Query with an ON clause successfully parses and can be evaluated
val resultWithOn = pipeline.compile("SELECT * FROM <<{'a': 1}>> INNER JOIN <<{'a': 2}>> ON true").eval(evaluationSession)
assertEquals(result.toString(), resultWithOn.toString())

// ----- v0.2.* -----
// Query with a JOIN without an ON clause. Starting in v0.2.0, the ON condition is REQUIRED except for cross
// joins. When not provided, a parser error is thrown.
val query = "SELECT * FROM <<{'a': 1}>> INNER JOIN <<{'a': 2}>>"
assertFailsWith<ParserException> {
    parser.parseExprNode(query)
}

// Query with an ON clause still successfully parses and can be evaluated in v0.2.* onwards
val resultWithOn = pipeline.compile("SELECT * FROM <<{'a': 1}>> INNER JOIN <<{'a': 2}>> ON true").eval(evaluationSession)
assertEquals("<<{'a': 1, 'a': 2}>>", resultWithOn.toString())

• v0.1.* example code
• v0.2.* example code

Breaking API changes

NOTE: ExprNode is deprecated and replaced with PartiqlAst. Users can look at ExprNodeToStatement.kt and RewriterToVisitorTransformGuide.md to see how to upgrade from ExprNode to PartiqlAst.

1. Refactoring of ExprNode AST (see 16fefe0) FromSourceExpr variables (changed to use LetVariables).

// ----- v0.1.* -----
// FROM source and FROM source UNPIVOT are modeled differently between v0.1.* and v0.2.*
// The following is an AstNode/ExprNode representation of '... FROM foo AS f AT g' in v0.1.*
val fromExpr = VariableReference(id = "foo", case = CaseSensitivity.INSENSITIVE, metas = metaContainerOf())
FromSourceExpr(
    expr = fromExpr,
    asName = SymbolicName(name = "f", metas = metaContainerOf()),
    atName = SymbolicName(name = "g", metas = metaContainerOf())
)

// The following models '... FROM UNPIVOT foo AS f AT g'
FromSourceUnpivot(
    expr = fromExpr,
    asName = SymbolicName(name = "f", metas = metaContainerOf()),
    atName = SymbolicName(name = "g", metas = metaContainerOf()),
    metas = metaContainerOf()
)

// ----- v0.2.* -----
// The following is an AstNode/ExprNode representation of '... FROM foo AS f AT g' in v0.2.*
val fromExpr = VariableReference(id = "foo", case = CaseSensitivity.INSENSITIVE, metas = metaContainerOf())
FromSourceExpr(
    expr = fromExpr,
    variables = LetVariables(
        asName = SymbolicName(name = "f", metas = metaContainerOf()),
        atName = SymbolicName(name = "g", metas = metaContainerOf())
        // v0.2.0 onwards also allows specifying a `BY` variable in FROM sources
    )
)

// The following models '... FROM UNPIVOT foo AS f AT g'
FromSourceUnpivot(
    expr = fromExpr,
    variables = LetVariables(
        asName = SymbolicName(name = "f", metas = metaContainerOf()),
        atName = SymbolicName(name = "g", metas = metaContainerOf())
    ),
    metas = metaContainerOf()
)

• v0.1.* example code
• v0.2.* example code

2. Refactoring of ExprNode AST (see 16fefe0) List and Bag ExprNodes defined under a Seq class

// ----- v0.1.* -----
val ion = IonSystemBuilder.standard().build()
val elem1 = Literal(ionValue = ion.singleValue("1"), metas = metaContainerOf())
val elem2 = Literal(ionValue = ion.singleValue("2"), metas = metaContainerOf())
val elem3 = Literal(ionValue = ion.singleValue("3"), metas = metaContainerOf())

// LIST and BAG are modeled differently between v0.1.* and v0.2.*
// The following is an AstNode/ExprNode representation of [1, 2, 3]
ListExprNode(
    values = listOf(
        elem1,
        elem2,
        elem3
    ),
    metas = metaContainerOf()
)

// The following is an AstNode/ExprNode representation of <<1, 2, 3>>
Bag(
    bag = listOf(
        elem1,
        elem2,
        elem3
    ),
    metas = metaContainerOf()
)

// ----- v0.2.* -----
// The following is an AstNode/ExprNode representation of [1, 2, 3]
Seq(
    type = SeqType.LIST,
    values = listOf(
        elem1,
        elem2,
        elem3
    ),
    metas = metaContainerOf()
)

// The following is an AstNode/ExprNode representation of <<1, 2, 3>>
Seq(
    type = SeqType.BAG,
    values = listOf(
        elem1,
        elem2,
        elem3
    ),
    metas = metaContainerOf()
)

// v0.2.0 onwards also allows for specifying s-expressions. E.g. (1 2 3)
Seq(
    type = SeqType.SEXP,
    values = listOf(
        elem1,
        elem2,
        elem3
    ),
    metas = metaContainerOf()
)

• v0.1.* example code
• v0.2.* example code

3. Starting in v0.2.*, the classes and functions related to V0AstSerializer, AstSerializer, AstDeserializer have been deprecated and will be removed in a future PartiQL release. Below is an example demonstrating how to update V0Ast IonSexp rewriting code to use PartiqlAst.

// ----- v0.1.* -----
// v0.1.* parsed the PartiQL statement to an `ExprNode` that could be serialized to the V0Ast. `ExprNode` and `V0Ast`
// have been deprecated in favor of PartiqlAst.
val node: ExprNode = SqlParser(ion).parseExprNode("SELECT * FROM <<{'a': 1, 'b': {'c': 23}}>>")
val ionSexp: IonSexp = V0AstSerializer.serialize(node, ion)

// below is a way to recursively rewrite a V0Ast/IonSexp statement. This particular example function rewrites
// any encountered int literal to 42.
fun rewriteIntsTo42(sexp: IonSexp): IonSexp {
    val rewritten = sexp.map { child ->
        when (child) {
            is IonSexp -> rewriteIntsTo42(child)
            is IonInt -> ion.newInt(42)
            else -> child.clone()
        }
    }
    return ion.newSexp(*rewritten.toTypedArray())
}

val rewrittenIonSexp = rewriteIntsTo42(ionSexp)
// the rewritten statement will be equivalent to the following statement (excluding source location metas)
val expectedIonSexp = SqlParser(ion).parse("SELECT * FROM <<{'a': 42, 'b': {'c': 42}}>>")
assertEquals(expectedIonSexp.filterMetaNodes(), rewrittenIonSexp.filterMetaNodes())

// v0.2.* onwards recommend using the `PartiqlAst` over any other AST versions
val partiqlAst = SqlParser(ion).parseAstStatement("SELECT * FROM <<{'a': 1, 'b': {'c': 23}}>>")

// below shows a way to create the same recursive rewriting function on `PartiqlAst` statements using the
// `VisitorTransform` class
class RewriteIntsTo42: PartiqlAst.VisitorTransform() {
    override fun transformExprLit(node: PartiqlAst.Expr.Lit): PartiqlAst.Expr {
        val newValue = when (node.value) {
            is IntElement -> ionInt(42)
            else -> node.value
        }
        val newNode = PartiqlAst.build {
            lit(newValue)
        }
        return super.transformExprLit(newNode)
    }
}
val rewrittenStatement = RewriteIntsTo42().transformStatement(partiqlAst)
val expectedPartiqlAst = SqlParser(ion).parseAstStatement("SELECT * FROM <<{'a': 42, 'b': {'c': 42}}>>")
assertEquals(expectedPartiqlAst, rewrittenStatement)

• v0.1.* example code
• v0.2.* example code