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));
}
}