public void LEFT() { // LEFT("Hello", -3) AssertEvaluation( expected: CosmosString.Empty, sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.Left, hello, negativeThree)); // LEFT("Hello", 0) AssertEvaluation( expected: CosmosString.Empty, sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.Left, hello, SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(0)))); // LEFT("Hello", 2) AssertEvaluation( expected: CosmosString.Create("He"), sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.Left, hello, SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(2)))); // LEFT("Hello", 6) AssertEvaluation( expected: CosmosString.Create("Hello"), sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.Left, hello, six)); }
public void UPPER() { // UPPER("\u00B5") // MICRO SIGN does not have an upper casing AssertEvaluation( expected: CosmosString.Create("\u00B5"), sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.Upper, SqlLiteralScalarExpression.Create(SqlStringLiteral.Create("\u00B5")))); }
public void CONTAINS() { // CONTAINS("hello", "") // -> all strings contain empty string. AssertEvaluation( expected: CosmosBoolean.Create(true), sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.Contains, SqlLiteralScalarExpression.Create(SqlStringLiteral.Create("Hello")), SqlLiteralScalarExpression.Create(SqlStringLiteral.Create(string.Empty)))); }
public void REPLACE() { // REPLACE("Hello", "", "World") // replacing the empty string within a string is undefined behavior // SQL Server just returns the original string and that's the behavior the backend matches AssertEvaluation( expected: Undefined, sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.Replace, hello, SqlLiteralScalarExpression.Create(SqlStringLiteral.Create(string.Empty)), world)); }
public void RIGHT() { // Right("Hello", -3) AssertEvaluation( expected: CosmosString.Empty, sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.Right, hello, negativeThree)); // Right("Hello", 1.5) AssertEvaluation( expected: Undefined, sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.Right, hello, floatingPoint)); // Right("Hello", 0) AssertEvaluation( expected: CosmosString.Empty, sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.Right, hello, SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(0)))); // Right("Hello", 2) AssertEvaluation( expected: CosmosString.Create("lo"), sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.Right, hello, SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(2)))); // Right("Hello", 6) AssertEvaluation( expected: CosmosString.Create("Hello"), sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.Right, hello, six)); // Right("Hello", int.MaxValue + 1) AssertEvaluation( expected: CosmosString.Create("Hello"), sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.Right, hello, SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create((long)int.MaxValue + 1)))); }
public void ROUND() { // ROUND(4.84090499760142E+15) // offline engine has double precision errors AssertEvaluation( expected: CosmosNumber64.Create(4.84090499760142E+15), sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.Round, SqlLiteralScalarExpression.Create( SqlNumberLiteral.Create( JToken.Parse("4840904997601420").Value <double>())))); // ROUND(0.5, 1) // -> should round up AssertEvaluation( expected: CosmosNumber64.Create(1), sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.Round, SqlLiteralScalarExpression.Create( SqlNumberLiteral.Create(0.5)))); }
public void REPLICATE() { // REPLICATE("Hello", -1) // -> undefined AssertEvaluation( expected: Undefined, sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.Replicate, hello, SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(-1)))); // REPLICATE("Hello", 1.5) // -> REPLICATE("Hello", 1) due to truncation AssertEvaluation( expected: CosmosString.Create("Hello"), sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.Replicate, hello, SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(1.5)))); // REPLICATE("Hello", 10000) // -> undefined due to 10kb string cap AssertEvaluation( expected: Undefined, sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.Replicate, hello, SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(10000)))); // REPLICATE("Hello", EXP(400)) // -> undefined due to 10kb string cap AssertEvaluation( expected: Undefined, sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.Replicate, hello, SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(Math.Exp(400))))); }
public void ARRAY_SLICE() { // ARRAY_SLICE(["apples", "strawberries", "bananas"], 1) AssertEvaluation( expected: CosmosArray.Create(CosmosString.Create("strawberries"), CosmosString.Create("bananas")), sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.ArraySlice, JTokenToSqlScalarExpression.Convert(new JArray("apples", "strawberries", "bananas")), SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(1)))); // ARRAY_SLICE(["apples", "strawberries", "bananas"], 1, 1) AssertEvaluation( expected: CosmosArray.Create(CosmosString.Create("strawberries")), sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.ArraySlice, JTokenToSqlScalarExpression.Convert(new JArray("apples", "strawberries", "bananas")), SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(1)), SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(1)))); // ARRAY_SLICE(["apples", "strawberries", "bananas"], -2, 1) AssertEvaluation( expected: CosmosArray.Create(CosmosString.Create("strawberries")), sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.ArraySlice, JTokenToSqlScalarExpression.Convert(new JArray("apples", "strawberries", "bananas")), SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(-2)), SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(1)))); // ARRAY_SLICE(["apples", "strawberries", "bananas"], -2, 2) AssertEvaluation( expected: CosmosArray.Create(CosmosString.Create("strawberries"), CosmosString.Create("bananas")), sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.ArraySlice, JTokenToSqlScalarExpression.Convert(new JArray("apples", "strawberries", "bananas")), SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(-2)), SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(2)))); // ARRAY_SLICE(["apples", "strawberries", "bananas"], 1, 0) AssertEvaluation( expected: CosmosArray.Create(), sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.ArraySlice, JTokenToSqlScalarExpression.Convert(new JArray("apples", "strawberries", "bananas")), SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(1)), SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(0)))); // ARRAY_SLICE(["apples", "strawberries", "bananas"], 1, 0) AssertEvaluation( expected: CosmosArray.Create(CosmosString.Create("strawberries"), CosmosString.Create("bananas")), sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.ArraySlice, JTokenToSqlScalarExpression.Convert(new JArray("apples", "strawberries", "bananas")), SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(1)), SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(1000)))); // ARRAY_SLICE(["apples", "strawberries", "bananas"], 1, 0) AssertEvaluation( expected: CosmosArray.Create(), sqlScalarExpression: SqlFunctionCallScalarExpression.CreateBuiltin( SqlFunctionCallScalarExpression.Identifiers.ArraySlice, JTokenToSqlScalarExpression.Convert(new JArray("apples", "strawberries", "bananas")), SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(1)), SqlLiteralScalarExpression.Create(SqlNumberLiteral.Create(-100)))); }
/// <summary> /// Converts a JToken to a semantically equivalent SqlScalarExpression. /// </summary> /// <param name="token">The JToken to convert.</param> /// <returns>The semantically equivalent SqlScalarExpression.</returns> public static SqlScalarExpression Convert(JToken token) { if (token == null) { return(Undefined); } switch (token.Type) { case JTokenType.Array: { List <SqlScalarExpression> items = new List <SqlScalarExpression>(); foreach (JToken element in token) { items.Add(JTokenToSqlScalarExpression.Convert(element)); } return(SqlArrayCreateScalarExpression.Create(items.ToArray())); } case JTokenType.Boolean: { SqlBooleanLiteral literal = SqlBooleanLiteral.Create(token.ToObject <bool>()); return(SqlLiteralScalarExpression.Create(literal)); } case JTokenType.Null: { SqlNullLiteral literal = SqlNullLiteral.Singleton; return(SqlLiteralScalarExpression.Create(literal)); } case JTokenType.Integer: case JTokenType.Float: { SqlNumberLiteral literal = SqlNumberLiteral.Create(token.ToObject <double>()); return(SqlLiteralScalarExpression.Create(literal)); } case JTokenType.Object: { List <SqlObjectProperty> properties = new List <SqlObjectProperty>(); foreach (JProperty prop in (JToken)token) { SqlPropertyName name = SqlPropertyName.Create(prop.Name); JToken value = prop.Value; SqlScalarExpression expression = JTokenToSqlScalarExpression.Convert(value); SqlObjectProperty property = SqlObjectProperty.Create(name, expression); properties.Add(property); } return(SqlObjectCreateScalarExpression.Create(properties.ToArray())); } case JTokenType.String: { SqlStringLiteral literal = SqlStringLiteral.Create(token.ToObject <string>()); return(SqlLiteralScalarExpression.Create(literal)); } default: throw new ArgumentException(string.Format(CultureInfo.CurrentUICulture, "Unsupported JsonType {0}", token.Type)); } }