Hello Teodor,
It may be good for 't' of 'f' but it seems too free grammar to accept 'tr'
or 'fa' or even 'o' which actually not known to be on or off.
Yes, it really works like that. I tried to make something clearer than
"src/bin/psql/variable.c". Maybe I did not succeed.
Ok, I see. Current coding accepts truexxx, falsexxx, yesxx, noxxx but doesn't
accept offxxx and onxxx. Not so consistent as it could be.
I've checked, but truexxx is not accepted as true. I have added a test
case which fails on "malformed variable", i.e. it went up to scanning a
double. When comparing ("truexxx", "true", 7) the fifth char is different,
so it is != 0. Or I'm missing something.
Also it doesn't accept 1 and 0 as psql does, but it's obviously why.
Yep. I have added a comment that it will be an int, and if a boolean is
needed it would work as expected.
Sorry, but I found more notices:
1) '~' and unary '-' should be commented, it's not so easy to guess about how
they actually implemented (-1 XOR value, remember that -1 is 0xfffff....)
Ok, I agree that it looks strange. I have added comments for both. I have
replaced -1 by 0xffff.... so that the code is hopefully clearer.
2)
- | expr '%' expr { $$ = make_op(yyscanner, "%", $1, $3); }
+ | expr '%' expr { $$ = make_op(yyscanner, "mod", $1, $3); }
why is MOD operation renamed? Do I miss something in thread?
Because I have added MOD as an explicit function to match SQL, so now % is
just a shorthand for calling it. Before the patch there was only the '%'
operator. Changing the name is enough for the function to be found.
pg> SELECT 11 % 3, MOD(11, 3);
2 | 2
Looking to psql and pgbench scripting implementation, isn't it better to
integrate lua in psql & pgbench?
Hmmm... if it starts on this slope, everyone will have its opinion (lua,
tcl, python, ruby, perl, insert-script-name-here...) and it must interact
with SQL, I'm not sure how to embed SQL & another language cleanly. So the
idea is just to extend backslash command capabilities of psql & pgbench,
preferably consistently, when need (i.e. use cases) arises.
Attached a new version with these changes.
--
Fabien.
diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 4431fc3..ea8f305 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -904,14 +904,32 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<para>
Sets variable <replaceable>varname</replaceable> to a value calculated
from <replaceable>expression</replaceable>.
- The expression may contain integer constants such as <literal>5432</literal>,
+ The expression may contain the <literal>NULL</literal> constant,
+ boolean constants <literal>TRUE</literal> and <literal>FALSE</literal>,
+ integer constants such as <literal>5432</literal>,
double constants such as <literal>3.14159</literal>,
references to variables <literal>:</literal><replaceable>variablename</replaceable>,
- unary operators (<literal>+</literal>, <literal>-</literal>) and binary operators
- (<literal>+</literal>, <literal>-</literal>, <literal>*</literal>, <literal>/</literal>,
- <literal>%</literal>) with their usual precedence and associativity,
- <link linkend="pgbench-builtin-functions">function calls</link>, and
- parentheses.
+ <link linkend="pgbench-builtin-operators">operators</link>
+ with their usual SQL precedence and associativity,
+ <link linkend="pgbench-builtin-functions">function calls</link>,
+ SQL <link linkend="functions-case"><token>CASE</token> generic conditional
+ expressions</link> and parentheses.
+ </para>
+
+ <para>
+ Functions and most operators return <literal>NULL</literal> on
+ <literal>NULL</literal> input.
+ </para>
+
+ <para>
+ For conditional purposes, non zero numerical values are
+ <literal>TRUE</literal>, zero numerical values and <literal>NULL</literal>
+ are <literal>FALSE</literal>.
+ </para>
+
+ <para>
+ When no final <token>ELSE</token> clause is provided to a
+ <token>CASE</token>, the default value is <literal>NULL</literal>.
</para>
<para>
@@ -920,6 +938,7 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
\set ntellers 10 * :scale
\set aid (1021 * random(1, 100000 * :scale)) % \
(100000 * :scale) + 1
+\set divx CASE WHEN :x <> 0 THEN :y/:x ELSE NULL END
</programlisting></para>
</listitem>
</varlistentry>
@@ -996,6 +1015,177 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
</variablelist>
</refsect2>
+ <refsect2 id="pgbench-builtin-operators">
+ <title>Built-In Operators</title>
+
+ <para>
+ The arithmetic, bitwise, comparison and logical operators listed in
+ <xref linkend="pgbench-operators"/> are built into <application>pgbench</application>
+ and may be used in expressions appearing in
+ <link linkend="pgbench-metacommand-set"><literal>\set</literal></link>.
+ </para>
+
+ <table id="pgbench-operators">
+ <title>pgbench Operators by increasing precedence</title>
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Operator</entry>
+ <entry>Description</entry>
+ <entry>Example</entry>
+ <entry>Result</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><literal>OR</literal></entry>
+ <entry>logical or</entry>
+ <entry><literal>5 or 0</literal></entry>
+ <entry><literal>TRUE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>AND</literal></entry>
+ <entry>logical and</entry>
+ <entry><literal>3 and 0</literal></entry>
+ <entry><literal>FALSE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>NOT</literal></entry>
+ <entry>logical not</entry>
+ <entry><literal>not false</literal></entry>
+ <entry><literal>TRUE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>IS [NOT] (NULL|TRUE|FALSE)</literal></entry>
+ <entry>value tests</entry>
+ <entry><literal>1 is null</literal></entry>
+ <entry><literal>FALSE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>ISNULL|NOTNULL</literal></entry>
+ <entry>null tests</entry>
+ <entry><literal>1 notnull</literal></entry>
+ <entry><literal>TRUE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>=</literal></entry>
+ <entry>is equal</entry>
+ <entry><literal>5 = 4</literal></entry>
+ <entry><literal>FALSE</literal></entry>
+ </row>
+ <row>
+ <entry><literal><></literal></entry>
+ <entry>is not equal</entry>
+ <entry><literal>5 <> 4</literal></entry>
+ <entry><literal>TRUE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>!=</literal></entry>
+ <entry>is not equal</entry>
+ <entry><literal>5 != 5</literal></entry>
+ <entry><literal>FALSE</literal></entry>
+ </row>
+ <row>
+ <entry><literal><</literal></entry>
+ <entry>lower than</entry>
+ <entry><literal>5 < 4</literal></entry>
+ <entry><literal>FALSE</literal></entry>
+ </row>
+ <row>
+ <entry><literal><=</literal></entry>
+ <entry>lower or equal</entry>
+ <entry><literal>5 <= 4</literal></entry>
+ <entry><literal>FALSE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>></literal></entry>
+ <entry>greater than</entry>
+ <entry><literal>5 > 4</literal></entry>
+ <entry><literal>TRUE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>>=</literal></entry>
+ <entry>greater or equal</entry>
+ <entry><literal>5 >= 4</literal></entry>
+ <entry><literal>TRUE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>|</literal></entry>
+ <entry>integer bitwise OR</entry>
+ <entry><literal>1 | 2</literal></entry>
+ <entry><literal>3</literal></entry>
+ </row>
+ <row>
+ <entry><literal>#</literal></entry>
+ <entry>integer bitwise XOR</entry>
+ <entry><literal>1 # 3</literal></entry>
+ <entry><literal>2</literal></entry>
+ </row>
+ <row>
+ <entry><literal>&</literal></entry>
+ <entry>integer bitwise AND</entry>
+ <entry><literal>1 & 3</literal></entry>
+ <entry><literal>1</literal></entry>
+ </row>
+ <row>
+ <entry><literal>~</literal></entry>
+ <entry>integer bitwise NOT</entry>
+ <entry><literal>~ 1</literal></entry>
+ <entry><literal>-2</literal></entry>
+ </row>
+ <row>
+ <entry><literal><<</literal></entry>
+ <entry>integer bitwise shift left</entry>
+ <entry><literal>1 << 2</literal></entry>
+ <entry><literal>4</literal></entry>
+ </row>
+ <row>
+ <entry><literal>>></literal></entry>
+ <entry>integer bitwise shift right</entry>
+ <entry><literal>8 >> 2</literal></entry>
+ <entry><literal>2</literal></entry>
+ </row>
+ <row>
+ <entry><literal>+</literal></entry>
+ <entry>addition</entry>
+ <entry><literal>5 + 4</literal></entry>
+ <entry><literal>9</literal></entry>
+ </row>
+ <row>
+ <entry><literal>-</literal></entry>
+ <entry>substraction</entry>
+ <entry><literal>3 - 2.0</literal></entry>
+ <entry><literal>1.0</literal></entry>
+ </row>
+ <row>
+ <entry><literal>*</literal></entry>
+ <entry>multiplication</entry>
+ <entry><literal>5 * 4</literal></entry>
+ <entry><literal>20</literal></entry>
+ </row>
+ <row>
+ <entry><literal>/</literal></entry>
+ <entry>division (integer truncates the results)</entry>
+ <entry><literal>5 / 3</literal></entry>
+ <entry><literal>1</literal></entry>
+ </row>
+ <row>
+ <entry><literal>%</literal></entry>
+ <entry>modulo</entry>
+ <entry><literal>3 % 2</literal></entry>
+ <entry><literal>1</literal></entry>
+ </row>
+ <row>
+ <entry><literal>-</literal></entry>
+ <entry>opposite</entry>
+ <entry><literal>- 2.0</literal></entry>
+ <entry><literal>-2.0</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </refsect2>
+
<refsect2 id="pgbench-builtin-functions">
<title>Built-In Functions</title>
@@ -1042,6 +1232,13 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<entry><literal>5432.0</literal></entry>
</row>
<row>
+ <entry><literal><function>exp(<replaceable>x</replaceable>)</function></literal></entry>
+ <entry>double</entry>
+ <entry>exponential</entry>
+ <entry><literal>exp(1.0)</literal></entry>
+ <entry><literal>2.718281828459045</literal></entry>
+ </row>
+ <row>
<entry><literal><function>greatest(<replaceable>a</replaceable> [, <replaceable>...</replaceable> ] )</function></literal></entry>
<entry>double if any <replaceable>a</replaceable> is double, else integer</entry>
<entry>largest value among arguments</entry>
@@ -1063,6 +1260,20 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
<entry><literal>2.1</literal></entry>
</row>
<row>
+ <entry><literal><function>ln(<replaceable>x</replaceable>)</function></literal></entry>
+ <entry>double</entry>
+ <entry>natural logarithm</entry>
+ <entry><literal>ln(2.718281828459045)</literal></entry>
+ <entry><literal>1.0</literal></entry>
+ </row>
+ <row>
+ <entry><literal><function>mod(<replaceable>i</replaceable>, <replaceable>bj</replaceable>)</function></literal></entry>
+ <entry>integer</entry>
+ <entry>modulo</entry>
+ <entry><literal>mod(54, 32)</literal></entry>
+ <entry><literal>22</literal></entry>
+ </row>
+ <row>
<entry><literal><function>pi()</function></literal></entry>
<entry>double</entry>
<entry>value of the constant PI</entry>
diff --git a/src/bin/pgbench/exprparse.y b/src/bin/pgbench/exprparse.y
index 25d5ad4..2de950f 100644
--- a/src/bin/pgbench/exprparse.y
+++ b/src/bin/pgbench/exprparse.y
@@ -19,13 +19,17 @@
PgBenchExpr *expr_parse_result;
static PgBenchExprList *make_elist(PgBenchExpr *exp, PgBenchExprList *list);
+static PgBenchExpr *make_null_constant(void);
+static PgBenchExpr *make_boolean_constant(bool bval);
static PgBenchExpr *make_integer_constant(int64 ival);
static PgBenchExpr *make_double_constant(double dval);
static PgBenchExpr *make_variable(char *varname);
static PgBenchExpr *make_op(yyscan_t yyscanner, const char *operator,
PgBenchExpr *lexpr, PgBenchExpr *rexpr);
+static PgBenchExpr *make_uop(yyscan_t yyscanner, const char *operator, PgBenchExpr *expr);
static int find_func(yyscan_t yyscanner, const char *fname);
static PgBenchExpr *make_func(yyscan_t yyscanner, int fnumber, PgBenchExprList *args);
+static PgBenchExpr *make_case(yyscan_t yyscanner, PgBenchExprList *when_then_list, PgBenchExpr *else_part);
%}
@@ -40,23 +44,33 @@ static PgBenchExpr *make_func(yyscan_t yyscanner, int fnumber, PgBenchExprList *
{
int64 ival;
double dval;
+ bool bval;
char *str;
PgBenchExpr *expr;
PgBenchExprList *elist;
}
-%type <elist> elist
-%type <expr> expr
+%type <elist> elist when_then_list
+%type <expr> expr case_control
%type <ival> INTEGER_CONST function
%type <dval> DOUBLE_CONST
+%type <bval> BOOLEAN_CONST
%type <str> VARIABLE FUNCTION
-%token INTEGER_CONST DOUBLE_CONST VARIABLE FUNCTION
+%token NULL_CONST INTEGER_CONST DOUBLE_CONST BOOLEAN_CONST VARIABLE FUNCTION
+%token AND_OP OR_OP NOT_OP NE_OP LE_OP GE_OP LS_OP RS_OP IS_OP
+%token CASE_KW WHEN_KW THEN_KW ELSE_KW END_KW
-/* Precedence: lowest to highest */
+/* Precedence: lowest to highest, taken from postgres SQL parser */
+%left OR_OP
+%left AND_OP
+%right NOT_OP
+%nonassoc IS_OP ISNULL_OP NOTNULL_OP
+%nonassoc '<' '>' '=' LE_OP GE_OP NE_OP
+%left '|' '#' '&' LS_OP RS_OP '~'
%left '+' '-'
%left '*' '/' '%'
-%right UMINUS
+%right UNARY
%%
@@ -68,26 +82,89 @@ elist: { $$ = NULL; }
;
expr: '(' expr ')' { $$ = $2; }
- | '+' expr %prec UMINUS { $$ = $2; }
- | '-' expr %prec UMINUS { $$ = make_op(yyscanner, "-",
+ | '+' expr %prec UNARY { $$ = $2; }
+ /* unary minus "-x" implemented as "0 - x" */
+ | '-' expr %prec UNARY { $$ = make_op(yyscanner, "-",
make_integer_constant(0), $2); }
+ /* binary ones complement "~x" implemented as 0xffff... xor x" */
+ | '~' expr { $$ = make_op(yyscanner, "#",
+ make_integer_constant(0xffffffffffffffff), $2); }
+ | NOT_OP expr { $$ = make_uop(yyscanner, "!not", $2); }
| expr '+' expr { $$ = make_op(yyscanner, "+", $1, $3); }
| expr '-' expr { $$ = make_op(yyscanner, "-", $1, $3); }
| expr '*' expr { $$ = make_op(yyscanner, "*", $1, $3); }
| expr '/' expr { $$ = make_op(yyscanner, "/", $1, $3); }
- | expr '%' expr { $$ = make_op(yyscanner, "%", $1, $3); }
+ | expr '%' expr { $$ = make_op(yyscanner, "mod", $1, $3); }
+ | expr '<' expr { $$ = make_op(yyscanner, "<", $1, $3); }
+ | expr LE_OP expr { $$ = make_op(yyscanner, "<=", $1, $3); }
+ | expr '>' expr { $$ = make_op(yyscanner, "<", $3, $1); }
+ | expr GE_OP expr { $$ = make_op(yyscanner, "<=", $3, $1); }
+ | expr '=' expr { $$ = make_op(yyscanner, "=", $1, $3); }
+ | expr NE_OP expr { $$ = make_op(yyscanner, "<>", $1, $3); }
+ | expr '&' expr { $$ = make_op(yyscanner, "&", $1, $3); }
+ | expr '|' expr { $$ = make_op(yyscanner, "|", $1, $3); }
+ | expr '#' expr { $$ = make_op(yyscanner, "#", $1, $3); }
+ | expr LS_OP expr { $$ = make_op(yyscanner, "<<", $1, $3); }
+ | expr RS_OP expr { $$ = make_op(yyscanner, ">>", $1, $3); }
+ | expr AND_OP expr { $$ = make_op(yyscanner, "!and", $1, $3); }
+ | expr OR_OP expr { $$ = make_op(yyscanner, "!or", $1, $3); }
+ /* IS variants */
+ | expr ISNULL_OP { $$ = make_op(yyscanner, "!is", $1, make_null_constant()); }
+ | expr NOTNULL_OP {
+ $$ = make_uop(yyscanner, "!not",
+ make_op(yyscanner, "!is", $1, make_null_constant()));
+ }
+ | expr IS_OP NULL_CONST { $$ = make_op(yyscanner, "!is", $1, make_null_constant()); }
+ | expr IS_OP NOT_OP NULL_CONST
+ {
+ $$ = make_uop(yyscanner, "!not",
+ make_op(yyscanner, "!is", $1, make_null_constant()));
+ }
+ | expr IS_OP BOOLEAN_CONST
+ {
+ $$ = make_op(yyscanner, "!is", $1, make_boolean_constant($3));
+ }
+ | expr IS_OP NOT_OP BOOLEAN_CONST
+ {
+ $$ = make_uop(yyscanner, "!not",
+ make_op(yyscanner, "!is", $1, make_boolean_constant($4)));
+ }
+ /* constants */
+ | NULL_CONST { $$ = make_null_constant(); }
+ | BOOLEAN_CONST { $$ = make_boolean_constant($1); }
| INTEGER_CONST { $$ = make_integer_constant($1); }
| DOUBLE_CONST { $$ = make_double_constant($1); }
+ /* misc */
| VARIABLE { $$ = make_variable($1); }
| function '(' elist ')' { $$ = make_func(yyscanner, $1, $3); }
+ | case_control { $$ = $1; }
;
+when_then_list:
+ when_then_list WHEN_KW expr THEN_KW expr { $$ = make_elist($5, make_elist($3, $1)); }
+ | WHEN_KW expr THEN_KW expr { $$ = make_elist($4, make_elist($2, NULL)); }
+
+case_control:
+ CASE_KW when_then_list END_KW { $$ = make_case(yyscanner, $2, make_null_constant()); }
+ | CASE_KW when_then_list ELSE_KW expr END_KW { $$ = make_case(yyscanner, $2, $4); }
+
function: FUNCTION { $$ = find_func(yyscanner, $1); pg_free($1); }
;
%%
static PgBenchExpr *
+make_null_constant(void)
+{
+ PgBenchExpr *expr = pg_malloc(sizeof(PgBenchExpr));
+
+ expr->etype = ENODE_CONSTANT;
+ expr->u.constant.type = PGBT_NULL;
+ expr->u.constant.u.ival = 0;
+ return expr;
+}
+
+static PgBenchExpr *
make_integer_constant(int64 ival)
{
PgBenchExpr *expr = pg_malloc(sizeof(PgBenchExpr));
@@ -110,6 +187,17 @@ make_double_constant(double dval)
}
static PgBenchExpr *
+make_boolean_constant(bool bval)
+{
+ PgBenchExpr *expr = pg_malloc(sizeof(PgBenchExpr));
+
+ expr->etype = ENODE_CONSTANT;
+ expr->u.constant.type = PGBT_BOOLEAN;
+ expr->u.constant.u.bval = bval;
+ return expr;
+}
+
+static PgBenchExpr *
make_variable(char *varname)
{
PgBenchExpr *expr = pg_malloc(sizeof(PgBenchExpr));
@@ -119,6 +207,7 @@ make_variable(char *varname)
return expr;
}
+/* binary operators */
static PgBenchExpr *
make_op(yyscan_t yyscanner, const char *operator,
PgBenchExpr *lexpr, PgBenchExpr *rexpr)
@@ -127,11 +216,19 @@ make_op(yyscan_t yyscanner, const char *operator,
make_elist(rexpr, make_elist(lexpr, NULL)));
}
+/* unary operator */
+static PgBenchExpr *
+make_uop(yyscan_t yyscanner, const char *operator, PgBenchExpr *expr)
+{
+ return make_func(yyscanner, find_func(yyscanner, operator), make_elist(expr, NULL));
+}
+
/*
* List of available functions:
- * - fname: function name
+ * - fname: function name, "!..." for special internal functions
* - nargs: number of arguments
* -1 is a special value for least & greatest meaning #args >= 1
+ * -2 is for the "CASE WHEN ..." function, which has #args >= 3 and odd
* - tag: function identifier from PgBenchFunction enum
*/
static const struct
@@ -155,7 +252,7 @@ static const struct
"/", 2, PGBENCH_DIV
},
{
- "%", 2, PGBENCH_MOD
+ "mod", 2, PGBENCH_MOD
},
/* actual functions */
{
@@ -177,6 +274,12 @@ static const struct
"sqrt", 1, PGBENCH_SQRT
},
{
+ "ln", 1, PGBENCH_LN
+ },
+ {
+ "exp", 1, PGBENCH_EXP
+ },
+ {
"int", 1, PGBENCH_INT
},
{
@@ -194,6 +297,48 @@ static const struct
{
"random_zipfian", 3, PGBENCH_RANDOM_ZIPFIAN
},
+ {
+ "!and", 2, PGBENCH_AND
+ },
+ {
+ "!or", 2, PGBENCH_OR
+ },
+ {
+ "!not", 1, PGBENCH_NOT
+ },
+ {
+ "&", 2, PGBENCH_BITAND
+ },
+ {
+ "|", 2, PGBENCH_BITOR
+ },
+ {
+ "#", 2, PGBENCH_BITXOR
+ },
+ {
+ "<<", 2, PGBENCH_LSHIFT
+ },
+ {
+ ">>", 2, PGBENCH_RSHIFT
+ },
+ {
+ "=", 2, PGBENCH_EQ
+ },
+ {
+ "<>", 2, PGBENCH_NE
+ },
+ {
+ "<=", 2, PGBENCH_LE
+ },
+ {
+ "<", 2, PGBENCH_LT
+ },
+ {
+ "!is", 2, PGBENCH_IS
+ },
+ {
+ "!case_end", -2, PGBENCH_CASE
+ },
/* keep as last array element */
{
NULL, 0, 0
@@ -282,6 +427,14 @@ make_func(yyscan_t yyscanner, int fnumber, PgBenchExprList *args)
elist_length(args) == 0)
expr_yyerror_more(yyscanner, "at least one argument expected",
PGBENCH_FUNCTIONS[fnumber].fname);
+ /* special case: case (when ... then ...)+ (else ...)? end */
+ if (PGBENCH_FUNCTIONS[fnumber].nargs == -2)
+ {
+ int len = elist_length(args);
+ if (len < 3 || len % 2 != 1)
+ expr_yyerror_more(yyscanner, "odd and >= 3 number of arguments expected",
+ "case control structure");
+ }
expr->etype = ENODE_FUNCTION;
expr->u.function.function = PGBENCH_FUNCTIONS[fnumber].tag;
@@ -294,6 +447,14 @@ make_func(yyscan_t yyscanner, int fnumber, PgBenchExprList *args)
return expr;
}
+static PgBenchExpr *
+make_case(yyscan_t yyscanner, PgBenchExprList *when_then_list, PgBenchExpr *else_part)
+{
+ return make_func(yyscanner,
+ find_func(yyscanner, "!case_end"),
+ make_elist(else_part, when_then_list));
+}
+
/*
* exprscan.l is compiled as part of exprparse.y. Currently, this is
* unavoidable because exprparse does not create a .h file to export
diff --git a/src/bin/pgbench/exprscan.l b/src/bin/pgbench/exprscan.l
index 9f46fb9..f4eb0d9 100644
--- a/src/bin/pgbench/exprscan.l
+++ b/src/bin/pgbench/exprscan.l
@@ -71,6 +71,22 @@ newline [\n]
/* Line continuation marker */
continuation \\{newline}
+/* case insensitive keywords */
+and [Aa][Nn][Dd]
+or [Oo][Rr]
+not [Nn][Oo][Tt]
+case [Cc][Aa][Ss][Ee]
+when [Ww][Hh][Ee][Nn]
+then [Tt][Hh][Ee][Nn]
+else [Ee][Ll][Ss][Ee]
+end [Ee][Nn][Dd]
+true [Tt][Rr][Uu][Ee]
+false [Ff][Aa][Ll][Ss][Ee]
+null [Nn][Uu][Ll][Ll]
+is [Ii][Ss]
+isnull [Ii][Ss][Nn][Uu][Ll][Ll]
+notnull [Nn][Oo][Tt][Nn][Uu][Ll][Ll]
+
/* Exclusive states */
%x EXPR
@@ -129,15 +145,52 @@ continuation \\{newline}
"-" { return '-'; }
"*" { return '*'; }
"/" { return '/'; }
-"%" { return '%'; }
+"%" { return '%'; } /* C version, also in Pg SQL */
+"=" { return '='; }
+"<>" { return NE_OP; }
+"!=" { return NE_OP; } /* C version, also in Pg SQL */
+"<=" { return LE_OP; }
+">=" { return GE_OP; }
+"<<" { return LS_OP; }
+">>" { return RS_OP; }
+"<" { return '<'; }
+">" { return '>'; }
+"|" { return '|'; }
+"&" { return '&'; }
+"#" { return '#'; }
+"~" { return '~'; }
+
"(" { return '('; }
")" { return ')'; }
"," { return ','; }
+{and} { return AND_OP; }
+{or} { return OR_OP; }
+{not} { return NOT_OP; }
+{is} { return IS_OP; }
+{isnull} { return ISNULL_OP; }
+{notnull} { return NOTNULL_OP; }
+
+{case} { return CASE_KW; }
+{when} { return WHEN_KW; }
+{then} { return THEN_KW; }
+{else} { return ELSE_KW; }
+{end} { return END_KW; }
+
:{alnum}+ {
yylval->str = pg_strdup(yytext + 1);
return VARIABLE;
}
+
+{null} { return NULL_CONST; }
+{true} {
+ yylval->bval = true;
+ return BOOLEAN_CONST;
+ }
+{false} {
+ yylval->bval = false;
+ return BOOLEAN_CONST;
+ }
{digit}+ {
yylval->ival = strtoint64(yytext);
return INTEGER_CONST;
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 7ce6f60..59f56d1 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -189,19 +189,20 @@ const char *progname;
volatile bool timer_exceeded = false; /* flag from signal handler */
/*
- * Variable definitions. If a variable has a string value, "value" is that
- * value, is_numeric is false, and num_value is undefined. If the value is
- * known to be numeric, is_numeric is true and num_value contains the value
- * (in any permitted numeric variant). In this case "value" contains the
- * string equivalent of the number, if we've had occasion to compute that,
- * or NULL if we haven't.
+ * Variable definitions.
+ *
+ * If a variable only has a string value, "svalue" is that value, and value is
+ * "not set". If the value is known, "value" contains the value (in any
+ * variant).
+ *
+ * In this case "svalue" contains the string equivalent of the value, if we've
+ * had occasion to compute that, or NULL if we haven't.
*/
typedef struct
{
char *name; /* variable's name */
- char *value; /* its value in string form, if known */
- bool is_numeric; /* is numeric value known? */
- PgBenchValue num_value; /* variable's value in numeric form */
+ char *svalue; /* its value in string form, if known */
+ PgBenchValue value; /* actual variable's value */
} Variable;
#define MAX_SCRIPTS 128 /* max number of SQL scripts allowed */
@@ -488,6 +489,8 @@ static const BuiltinScript builtin_script[] =
/* Function prototypes */
+static void setNullValue(PgBenchValue *pv);
+static void setBoolValue(PgBenchValue *pv, bool bval);
static void setIntValue(PgBenchValue *pv, int64 ival);
static void setDoubleValue(PgBenchValue *pv, double dval);
static bool evaluateExpr(TState *, CState *, PgBenchExpr *, PgBenchValue *);
@@ -1146,50 +1149,82 @@ getVariable(CState *st, char *name)
if (var == NULL)
return NULL; /* not found */
- if (var->value)
- return var->value; /* we have it in string form */
+ if (var->svalue)
+ return var->svalue; /* we have it in string form */
- /* We need to produce a string equivalent of the numeric value */
- Assert(var->is_numeric);
- if (var->num_value.type == PGBT_INT)
+ /* We need to produce a string equivalent of the value */
+ Assert(var->value.type != PGBT_NO_VALUE);
+ if (var->value.type == PGBT_NULL)
+ snprintf(stringform, sizeof(stringform), "NULL");
+ else if (var->value.type == PGBT_BOOLEAN)
snprintf(stringform, sizeof(stringform),
- INT64_FORMAT, var->num_value.u.ival);
- else
- {
- Assert(var->num_value.type == PGBT_DOUBLE);
+ "%s", var->value.u.bval ? "true" : "false");
+ else if (var->value.type == PGBT_INT)
snprintf(stringform, sizeof(stringform),
- "%.*g", DBL_DIG, var->num_value.u.dval);
- }
- var->value = pg_strdup(stringform);
- return var->value;
+ INT64_FORMAT, var->value.u.ival);
+ else if (var->value.type == PGBT_DOUBLE)
+ snprintf(stringform, sizeof(stringform),
+ "%.*g", DBL_DIG, var->value.u.dval);
+ else /* internal error, unexpected type */
+ Assert(0);
+ var->svalue = pg_strdup(stringform);
+ return var->svalue;
}
-/* Try to convert variable to numeric form; return false on failure */
+/* Try to convert variable to a value; return false on failure */
static bool
-makeVariableNumeric(Variable *var)
+makeVariableValue(Variable *var)
{
- if (var->is_numeric)
+ size_t slen;
+
+ if (var->value.type != PGBT_NO_VALUE)
return true; /* no work */
- if (is_an_int(var->value))
+ slen = strlen(var->svalue);
+
+ if (slen == 0)
+ /* what should it do on ""? */
+ return false;
+
+ if (pg_strcasecmp(var->svalue, "null") == 0)
{
- setIntValue(&var->num_value, strtoint64(var->value));
- var->is_numeric = true;
+ setNullValue(&var->value);
+ }
+ /*
+ * accept prefixes such as y, ye, n, no... but not for "o".
+ * 0/1 are recognized later as an int, which is converted
+ * to bool if needed.
+ */
+ else if (pg_strncasecmp(var->svalue, "true", slen) == 0 ||
+ pg_strncasecmp(var->svalue, "yes", slen) == 0 ||
+ pg_strcasecmp(var->svalue, "on") == 0)
+ {
+ setBoolValue(&var->value, true);
+ }
+ else if (pg_strncasecmp(var->svalue, "false", slen) == 0 ||
+ pg_strncasecmp(var->svalue, "no", slen) == 0 ||
+ pg_strcasecmp(var->svalue, "off") == 0 ||
+ pg_strcasecmp(var->svalue, "of") == 0)
+ {
+ setBoolValue(&var->value, false);
+ }
+ else if (is_an_int(var->svalue))
+ {
+ setIntValue(&var->value, strtoint64(var->svalue));
}
else /* type should be double */
{
double dv;
char xs;
- if (sscanf(var->value, "%lf%c", &dv, &xs) != 1)
+ if (sscanf(var->svalue, "%lf%c", &dv, &xs) != 1)
{
fprintf(stderr,
"malformed variable \"%s\" value: \"%s\"\n",
- var->name, var->value);
+ var->name, var->svalue);
return false;
}
- setDoubleValue(&var->num_value, dv);
- var->is_numeric = true;
+ setDoubleValue(&var->value, dv);
}
return true;
}
@@ -1266,7 +1301,7 @@ lookupCreateVariable(CState *st, const char *context, char *name)
var = &newvars[st->nvariables];
var->name = pg_strdup(name);
- var->value = NULL;
+ var->svalue = NULL;
/* caller is expected to initialize remaining fields */
st->nvariables++;
@@ -1292,18 +1327,18 @@ putVariable(CState *st, const char *context, char *name, const char *value)
/* dup then free, in case value is pointing at this variable */
val = pg_strdup(value);
- if (var->value)
- free(var->value);
- var->value = val;
- var->is_numeric = false;
+ if (var->svalue)
+ free(var->svalue);
+ var->svalue = val;
+ var->value.type = PGBT_NO_VALUE;
return true;
}
-/* Assign a numeric value to a variable, creating it if need be */
+/* Assign a value to a variable, creating it if need be */
/* Returns false on failure (bad name) */
static bool
-putVariableNumber(CState *st, const char *context, char *name,
+putVariableValue(CState *st, const char *context, char *name,
const PgBenchValue *value)
{
Variable *var;
@@ -1312,11 +1347,10 @@ putVariableNumber(CState *st, const char *context, char *name,
if (!var)
return false;
- if (var->value)
- free(var->value);
- var->value = NULL;
- var->is_numeric = true;
- var->num_value = *value;
+ if (var->svalue)
+ free(var->svalue);
+ var->svalue = NULL;
+ var->value = *value;
return true;
}
@@ -1329,7 +1363,7 @@ putVariableInt(CState *st, const char *context, char *name, int64 value)
PgBenchValue val;
setIntValue(&val, value);
- return putVariableNumber(st, context, name, &val);
+ return putVariableValue(st, context, name, &val);
}
/*
@@ -1428,6 +1462,67 @@ getQueryParams(CState *st, const Command *command, const char **params)
params[i] = getVariable(st, command->argv[i + 1]);
}
+static char *
+valueTypeName(PgBenchValue *pval)
+{
+ if (pval->type == PGBT_NO_VALUE)
+ return "none";
+ else if (pval->type == PGBT_NULL)
+ return "null";
+ else if (pval->type == PGBT_INT)
+ return "int";
+ else if (pval->type == PGBT_DOUBLE)
+ return "double";
+ else if (pval->type == PGBT_BOOLEAN)
+ return "boolean";
+ else
+ {
+ /* internal error, should never get there */
+ Assert(false);
+ return NULL;
+ }
+}
+
+/* get a value as a boolean, or tell if there is a problem */
+static bool
+coerceToBool(PgBenchValue *pval, bool *bval)
+{
+ if (pval->type == PGBT_BOOLEAN)
+ {
+ *bval = pval->u.bval;
+ return true;
+ }
+ else /* NULL, INT or DOUBLE */
+ {
+ fprintf(stderr, "cannot coerce %s to boolean\n", valueTypeName(pval));
+ return false;
+ }
+}
+
+/*
+ * Return true or false from an expression for conditional purposes.
+ * Non zero numerical values are true, zero and NULL are false.
+ */
+static bool
+valueTruth(PgBenchValue *pval)
+{
+ switch (pval->type)
+ {
+ case PGBT_NULL:
+ return false;
+ case PGBT_BOOLEAN:
+ return pval->u.bval;
+ case PGBT_INT:
+ return pval->u.ival != 0;
+ case PGBT_DOUBLE:
+ return pval->u.dval != 0.0;
+ default:
+ /* internal error, unexpected type */
+ Assert(0);
+ return false;
+ }
+}
+
/* get a value as an int, tell if there is a problem */
static bool
coerceToInt(PgBenchValue *pval, int64 *ival)
@@ -1437,11 +1532,10 @@ coerceToInt(PgBenchValue *pval, int64 *ival)
*ival = pval->u.ival;
return true;
}
- else
+ else if (pval->type == PGBT_DOUBLE)
{
double dval = pval->u.dval;
- Assert(pval->type == PGBT_DOUBLE);
if (dval < PG_INT64_MIN || PG_INT64_MAX < dval)
{
fprintf(stderr, "double to int overflow for %f\n", dval);
@@ -1450,6 +1544,11 @@ coerceToInt(PgBenchValue *pval, int64 *ival)
*ival = (int64) dval;
return true;
}
+ else /* BOOLEAN or NULL */
+ {
+ fprintf(stderr, "cannot coerce %s to int\n", valueTypeName(pval));
+ return false;
+ }
}
/* get a value as a double, or tell if there is a problem */
@@ -1461,12 +1560,32 @@ coerceToDouble(PgBenchValue *pval, double *dval)
*dval = pval->u.dval;
return true;
}
- else
+ else if (pval->type == PGBT_INT)
{
- Assert(pval->type == PGBT_INT);
*dval = (double) pval->u.ival;
return true;
}
+ else /* BOOLEAN or NULL */
+ {
+ fprintf(stderr, "cannot coerce %s to double\n", valueTypeName(pval));
+ return false;
+ }
+}
+
+/* assign a null value */
+static void
+setNullValue(PgBenchValue *pv)
+{
+ pv->type = PGBT_NULL;
+ pv->u.ival = 0;
+}
+
+/* assign a boolean value */
+static void
+setBoolValue(PgBenchValue *pv, bool bval)
+{
+ pv->type = PGBT_BOOLEAN;
+ pv->u.bval = bval;
}
/* assign an integer value */
static void
@@ -1495,13 +1614,17 @@ evalFunc(TState *thread, CState *st,
PgBenchFunction func, PgBenchExprLink *args, PgBenchValue *retval)
{
/* evaluate all function arguments */
- int nargs = 0;
- PgBenchValue vargs[MAX_FARGS];
+ int nargs = 0;
+ PgBenchValue vargs[MAX_FARGS];
PgBenchExprLink *l = args;
+ bool has_null = false;
for (nargs = 0; nargs < MAX_FARGS && l != NULL; nargs++, l = l->next)
+ {
if (!evaluateExpr(thread, st, l->expr, &vargs[nargs]))
return false;
+ has_null |= vargs[nargs].type == PGBT_NULL;
+ }
if (l != NULL)
{
@@ -1510,6 +1633,14 @@ evalFunc(TState *thread, CState *st,
return false;
}
+ /* NULL arguments */
+ if (has_null &&
+ func != PGBENCH_IS && func != PGBENCH_CASE && func != PGBENCH_DEBUG)
+ {
+ setNullValue(retval);
+ return true;
+ }
+
/* then evaluate function */
switch (func)
{
@@ -1519,6 +1650,10 @@ evalFunc(TState *thread, CState *st,
case PGBENCH_MUL:
case PGBENCH_DIV:
case PGBENCH_MOD:
+ case PGBENCH_EQ:
+ case PGBENCH_NE:
+ case PGBENCH_LE:
+ case PGBENCH_LT:
{
PgBenchValue *lval = &vargs[0],
*rval = &vargs[1];
@@ -1554,6 +1689,22 @@ evalFunc(TState *thread, CState *st,
setDoubleValue(retval, ld / rd);
return true;
+ case PGBENCH_EQ:
+ setBoolValue(retval, ld == rd);
+ return true;
+
+ case PGBENCH_NE:
+ setBoolValue(retval, ld != rd);
+ return true;
+
+ case PGBENCH_LE:
+ setBoolValue(retval, ld <= rd);
+ return true;
+
+ case PGBENCH_LT:
+ setBoolValue(retval, ld < rd);
+ return true;
+
default:
/* cannot get here */
Assert(0);
@@ -1582,6 +1733,22 @@ evalFunc(TState *thread, CState *st,
setIntValue(retval, li * ri);
return true;
+ case PGBENCH_EQ:
+ setBoolValue(retval, li == ri);
+ return true;
+
+ case PGBENCH_NE:
+ setBoolValue(retval, li != ri);
+ return true;
+
+ case PGBENCH_LE:
+ setBoolValue(retval, li <= ri);
+ return true;
+
+ case PGBENCH_LT:
+ setBoolValue(retval, li < ri);
+ return true;
+
case PGBENCH_DIV:
case PGBENCH_MOD:
if (ri == 0)
@@ -1622,6 +1789,63 @@ evalFunc(TState *thread, CState *st,
}
}
+ /* integer bitwise operators */
+ case PGBENCH_BITAND:
+ case PGBENCH_BITOR:
+ case PGBENCH_BITXOR:
+ case PGBENCH_LSHIFT:
+ case PGBENCH_RSHIFT:
+ {
+ int64 li, ri;
+
+ if (!coerceToInt(&vargs[0], &li) || !coerceToInt(&vargs[1], &ri))
+ return false;
+
+ if (func == PGBENCH_BITAND)
+ setIntValue(retval, li & ri);
+ else if (func == PGBENCH_BITOR)
+ setIntValue(retval, li | ri);
+ else if (func == PGBENCH_BITXOR)
+ setIntValue(retval, li ^ ri);
+ else if (func == PGBENCH_LSHIFT)
+ setIntValue(retval, li << ri);
+ else if (func == PGBENCH_RSHIFT)
+ setIntValue(retval, li >> ri);
+ else /* cannot get here */
+ Assert(0);
+
+ return true;
+ }
+
+ /* logical operators */
+ case PGBENCH_AND:
+ case PGBENCH_OR:
+ {
+ bool lb, rb;
+
+ if (!coerceToBool(&vargs[0], &lb) ||
+ !coerceToBool(&vargs[1], &rb))
+ return false;
+
+ if (func == PGBENCH_AND)
+ setBoolValue(retval, lb && rb);
+ else if (func == PGBENCH_OR)
+ setBoolValue(retval, lb || rb);
+ else /* cannot get here */
+ Assert(0);
+
+ return true;
+ }
+ case PGBENCH_NOT:
+ {
+ bool b;
+ if (!coerceToBool(&vargs[0], &b))
+ return false;
+
+ setBoolValue(retval, !b);
+ return true;
+ }
+
/* no arguments */
case PGBENCH_PI:
setDoubleValue(retval, M_PI);
@@ -1660,13 +1884,16 @@ evalFunc(TState *thread, CState *st,
fprintf(stderr, "debug(script=%d,command=%d): ",
st->use_file, st->command + 1);
- if (varg->type == PGBT_INT)
+ if (varg->type == PGBT_NULL)
+ fprintf(stderr, "null\n");
+ else if (varg->type == PGBT_BOOLEAN)
+ fprintf(stderr, "boolean %s\n", varg->u.bval ? "true" : "false");
+ else if (varg->type == PGBT_INT)
fprintf(stderr, "int " INT64_FORMAT "\n", varg->u.ival);
- else
- {
- Assert(varg->type == PGBT_DOUBLE);
+ else if (varg->type == PGBT_DOUBLE)
fprintf(stderr, "double %.*g\n", DBL_DIG, varg->u.dval);
- }
+ else /* internal error, unexpected type */
+ Assert(0);
*retval = *varg;
@@ -1676,6 +1903,8 @@ evalFunc(TState *thread, CState *st,
/* 1 double argument */
case PGBENCH_DOUBLE:
case PGBENCH_SQRT:
+ case PGBENCH_LN:
+ case PGBENCH_EXP:
{
double dval;
@@ -1686,6 +1915,11 @@ evalFunc(TState *thread, CState *st,
if (func == PGBENCH_SQRT)
dval = sqrt(dval);
+ else if (func == PGBENCH_LN)
+ dval = log(dval);
+ else if (func == PGBENCH_EXP)
+ dval = exp(dval);
+ /* else is cast: do nothing */
setDoubleValue(retval, dval);
return true;
@@ -1764,6 +1998,23 @@ evalFunc(TState *thread, CState *st,
}
return true;
}
+ case PGBENCH_CASE:
+ {
+ int n_when = nargs / 2, i;
+ Assert(nargs >= 3 && nargs % 2 == 1);
+ /* return on first true when condition */
+ for (i = 0; i < n_when; i++)
+ {
+ if (valueTruth(&vargs[2*i]))
+ {
+ *retval = vargs[2*i+1];
+ return true;
+ }
+ }
+ /* else value is last */
+ *retval = vargs[nargs-1];
+ return true;
+ }
/* random functions */
case PGBENCH_RANDOM:
@@ -1850,6 +2101,16 @@ evalFunc(TState *thread, CState *st,
return true;
}
+ case PGBENCH_IS:
+ {
+ Assert(nargs == 2);
+ /* note: this simple implementation is more permissive than SQL */
+ setBoolValue(retval,
+ vargs[0].type == vargs[1].type &&
+ vargs[0].u.bval == vargs[1].u.bval);
+ return true;
+ }
+
default:
/* cannot get here */
Assert(0);
@@ -1886,10 +2147,10 @@ evaluateExpr(TState *thread, CState *st, PgBenchExpr *expr, PgBenchValue *retval
return false;
}
- if (!makeVariableNumeric(var))
+ if (!makeVariableValue(var))
return false;
- *retval = var->num_value;
+ *retval = var->value;
return true;
}
@@ -2461,7 +2722,7 @@ doCustom(TState *thread, CState *st, StatsData *agg)
break;
}
- if (!putVariableNumber(st, argv[0], argv[1], &result))
+ if (!putVariableValue(st, argv[0], argv[1], &result))
{
commandFailed(st, "assignment of meta-command 'set' failed");
st->state = CSTATE_ABORTED;
@@ -4564,16 +4825,16 @@ main(int argc, char **argv)
{
Variable *var = &state[0].variables[j];
- if (var->is_numeric)
+ if (var->value.type != PGBT_NO_VALUE)
{
- if (!putVariableNumber(&state[i], "startup",
- var->name, &var->num_value))
+ if (!putVariableValue(&state[i], "startup",
+ var->name, &var->value))
exit(1);
}
else
{
if (!putVariable(&state[i], "startup",
- var->name, var->value))
+ var->name, var->svalue))
exit(1);
}
}
diff --git a/src/bin/pgbench/pgbench.h b/src/bin/pgbench/pgbench.h
index 83fee1a..bbca2be 100644
--- a/src/bin/pgbench/pgbench.h
+++ b/src/bin/pgbench/pgbench.h
@@ -33,8 +33,11 @@ union YYSTYPE;
*/
typedef enum
{
+ PGBT_NO_VALUE,
+ PGBT_NULL,
PGBT_INT,
- PGBT_DOUBLE
+ PGBT_DOUBLE,
+ PGBT_BOOLEAN
/* add other types here */
} PgBenchValueType;
@@ -45,6 +48,7 @@ typedef struct
{
int64 ival;
double dval;
+ bool bval;
/* add other types here */
} u;
} PgBenchValue;
@@ -73,10 +77,26 @@ typedef enum PgBenchFunction
PGBENCH_DOUBLE,
PGBENCH_PI,
PGBENCH_SQRT,
+ PGBENCH_LN,
+ PGBENCH_EXP,
PGBENCH_RANDOM,
PGBENCH_RANDOM_GAUSSIAN,
PGBENCH_RANDOM_EXPONENTIAL,
- PGBENCH_RANDOM_ZIPFIAN
+ PGBENCH_RANDOM_ZIPFIAN,
+ PGBENCH_AND,
+ PGBENCH_OR,
+ PGBENCH_NOT,
+ PGBENCH_BITAND,
+ PGBENCH_BITOR,
+ PGBENCH_BITXOR,
+ PGBENCH_LSHIFT,
+ PGBENCH_RSHIFT,
+ PGBENCH_EQ,
+ PGBENCH_NE,
+ PGBENCH_LE,
+ PGBENCH_LT,
+ PGBENCH_IS,
+ PGBENCH_CASE
} PgBenchFunction;
typedef struct PgBenchExpr PgBenchExpr;
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl
index e3cdf28..be5bbfe 100644
--- a/src/bin/pgbench/t/001_pgbench_with_server.pl
+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl
@@ -211,10 +211,13 @@ COMMIT;
# test expressions
pgbench(
- '-t 1 -Dfoo=-10.1 -Dbla=false -Di=+3 -Dminint=-9223372036854775808',
+ '-t 1 -Dfoo=-10.1 -Dbla=false -Di=+3 -Dminint=-9223372036854775808 -Dn=null -Dt=t -Df=of -Dd=1.0',
0,
[ qr{type: .*/001_pgbench_expressions}, qr{processed: 1/1} ],
- [ qr{command=4.: int 4\b},
+ [ qr{command=1.: int 1\d\b},
+ qr{command=2.: int 1\d\d\b},
+ qr{command=3.: int 1\d\d\d\b},
+ qr{command=4.: int 4\b},
qr{command=5.: int 5\b},
qr{command=6.: int 6\b},
qr{command=7.: int 7\b},
@@ -223,49 +226,102 @@ pgbench(
qr{command=10.: int 10\b},
qr{command=11.: int 11\b},
qr{command=12.: int 12\b},
- qr{command=13.: double 13\b},
- qr{command=14.: double 14\b},
qr{command=15.: double 15\b},
qr{command=16.: double 16\b},
qr{command=17.: double 17\b},
- qr{command=18.: double 18\b},
- qr{command=19.: double 19\b},
- qr{command=20.: double 20\b},
- qr{command=21.: int 9223372036854775807\b},
- qr{command=23.: int [1-9]\b}, ],
+ qr{command=18.: int 9223372036854775807\b},
+ qr{command=20.: int [1-9]\b},
+ qr{command=21.: boolean false\b},
+ qr{command=22.: boolean true\b},
+ qr{command=23.: int 23\b},
+ qr{command=24.: int 24\b},
+ qr{command=25.: double 25\b},
+ qr{command=26.: int 26\b},
+ qr{command=27.: int 27\b},
+ qr{command=28.: double 28\b},
+ qr{command=29.: int 29\b},
+ qr{command=30.: int 30\b},
+ qr{command=31.: boolean true\b},
+ qr{command=32.: null\b},
+ qr{command=33.: null\b},
+ qr{command=34.: boolean true\b},
+ qr{command=35.: boolean true\b},
+ qr{command=36.: boolean true\b},
+ qr{command=37.: int 37\b},
+ qr{command=38.: boolean true\b},
+ qr{command=39.: boolean true\b},
+ ],
'pgbench expressions',
{ '001_pgbench_expressions' => q{-- integer functions
-\set i1 debug(random(1, 100))
-\set i2 debug(random_exponential(1, 100, 10.0))
-\set i3 debug(random_gaussian(1, 100, 10.0))
+\set i1 debug(random(10, 19))
+\set i2 debug(random_exponential(100, 199, 10.0))
+\set i3 debug(random_gaussian(1000, 1999, 10.0))
\set i4 debug(abs(-4))
\set i5 debug(greatest(5, 4, 3, 2))
\set i6 debug(11 + least(-5, -4, -3, -2))
\set i7 debug(int(7.3))
--- integer operators
-\set i8 debug(17 / 5 + 5)
-\set i9 debug(- (3 * 4 - 3) / -1 + 3 % -1)
+-- integer arithmetic and bit-wise operators
+\set i8 debug(17 / (4|1) + ( 4 + (7 >> 2)))
+\set i9 debug(- (3 * 4 - (-(~ 1) + -(~ 0))) / -1 + 3 % -1)
\set ia debug(10 + (0 + 0 * 0 - 0 / 1))
\set ib debug(:ia + :scale)
-\set ic debug(64 % 13)
--- double functions
-\set d1 debug(sqrt(3.0) * abs(-0.8E1))
-\set d2 debug(double(1 + 1) * 7)
+\set ic debug(64 % (((2 + 1 * 2 + (1 # 2) | 4 * (2 & 11)) - (1 << 2)) + 2))
+-- double functions and operators
+\set d1 debug(sqrt(+1.5 * 2.0) * abs(-0.8E1))
+\set d2 debug(double(1 + 1) * (-75.0 / :foo))
\set pi debug(pi() * 4.9)
-\set d4 debug(greatest(4, 2, -1.17) * 4.0)
+\set d4 debug(greatest(4, 2, -1.17) * 4.0 * Ln(Exp(1.0)))
\set d5 debug(least(-5.18, .0E0, 1.0/0) * -3.3)
--- double operators
-\set d6 debug((0.5 * 12.1 - 0.05) * (31.0 / 10))
-\set d7 debug(11.1 + 7.9)
-\set d8 debug(:foo * -2)
-- forced overflow
\set maxint debug(:minint - 1)
-- reset a variable
\set i1 0
-- yet another integer function
\set id debug(random_zipfian(1, 9, 1.3))
+-- comparisons and logical operations
+\set c0 debug(1.0 = 0.0 and 1.0 != 0.0)
+\set c1 debug(0 = 1 Or 1.0 = 1)
+\set c4 debug(case when 0 < 1 then 23 else 0 end)
+\set c5 debug(case when true then 24 else 0 end)
+\set c6 debug(case when false THEN -1 when 1 = 1 then 4 + 19 + 2.0 end )
+\set c7 debug(case when (1 > 0) and (1 >= 0) and (0 < 1) and (0 <= 1) and (0 != 1) and (0 = 0) and (0 <> 1) then 26 else 0 end)
+\set c8 debug(CASE \
+ WHEN (1.0 > 0.0) AND (1.0 >= 0.0) AND (0.0 < 1.0) AND (0.0 <= 1.0) AND \
+ (0.0 != 1.0) AND (0.0 = 0.0) AND (0.0 <> 1.0) AND (0.0 = 0.0) \
+ THEN 27 \
+ ELSE 0 \
+ END)
+\set c9 debug(CASE WHEN NOT FALSE THEN 3 * 9.3333334 END)
+\set ca debug(case when false then 0 when 1-1 <> 0 then 1 else 29 end)
+\set cb debug(1 + mod(13 * 7 + 12, 13) - mod(-19 * 11 - 17, 19))
+\set cc debug(NOT (0 > 1) AND (1 <= 1) AND NOT (0 >= 1) AND (0 < 1) AND \
+ NOT (false and true) AND (false OR TRUE) AND (NOT :f) AND (NOT FALSE) AND \
+ NOT (NOT TRUE))
+-- NULL value and associated operators
+\set n0 debug(NULL + NULL * exp(NULL))
+\set n1 debug(:n0)
+\set n2 debug(NOT (:n0 IS NOT NULL OR :d1 IS NULL))
+\set n3 debug(:n0 IS NULL AND :d1 IS NOT NULL AND :d1 NOTNULL)
+\set n4 debug(:n0 ISNULL AND NOT :n0 IS TRUE AND :n0 IS NOT FALSE)
+\set n5 debug(CASE WHEN :n IS NULL THEN 37 ELSE NULL END)
+-- use a variables of all types
+\set n6 debug(:n IS NULL AND NOT :f AND :t)
+-- conditional truth
+\set cs debug(CASE WHEN 1 THEN TRUE END AND CASE WHEN 1.0 THEN TRUE END AND CASE WHEN :n THEN NULL ELSE TRUE END)
+-- substitute variables of all possible types
+\set v0 NULL
+\set v1 TRUE
+\set v2 5432
+\set v3 -54.21E-2
+SELECT :v0, :v1, :v2, :v3;
} });
+=head
+
+} });
+
+=cut
+
# backslash commands
pgbench(
'-t 1', 0,
@@ -384,8 +440,42 @@ SELECT LEAST(:i, :i, :i, :i, :i, :i, :i, :i, :i, :i, :i);
q{\set i random_zipfian(0, 10, 1000000)} ],
[ 'set non numeric value', 0,
[qr{malformed variable "foo" value: "bla"}], q{\set i :foo + 1} ],
- [ 'set no expression', 1, [qr{syntax error}], q{\set i} ],
- [ 'set missing argument', 1, [qr{missing argument}i], q{\set} ],
+ [ 'set no expression',
+ 1,
+ [qr{syntax error}],
+ q{\set i} ],
+ [ 'set missing argument',
+ 1,
+ [qr{missing argument}i],
+ q{\set} ],
+ [ 'set not a bool',
+ 0,
+ [ qr{cannot coerce double to boolean} ],
+ q{\set b 0.0 OR TRUE} ],
+ [ 'set not an int',
+ 0,
+ [ qr{cannot coerce boolean to int} ],
+ q{\set i TRUE + 2} ],
+ [ 'set not an double',
+ 0,
+ [ qr{cannot coerce boolean to double} ],
+ q{\set d ln(TRUE)} ],
+ [ 'set case error',
+ 1,
+ [ qr{syntax error in command "set"} ],
+ q{\set i CASE TRUE THEN 1 ELSE 0 END} ],
+ [ 'set random error',
+ 0,
+ [ qr{cannot coerce boolean to int} ],
+ q{\set b random(FALSE, TRUE)} ],
+ [ 'set number of args mismatch',
+ 1,
+ [ qr{unexpected number of arguments} ],
+ q{\set d ln(1.0, 2.0))} ],
+ [ 'set at least one arg',
+ 1,
+ [ qr{at least one argument expected} ],
+ q{\set i greatest())} ],
# SETSHELL
[ 'setshell not an int', 0,
@@ -407,7 +497,10 @@ SELECT LEAST(:i, :i, :i, :i, :i, :i, :i, :i, :i, :i, :i);
# MISC
[ 'misc invalid backslash command', 1,
[qr{invalid command .* "nosuchcommand"}], q{\nosuchcommand} ],
- [ 'misc empty script', 1, [qr{empty command list for script}], q{} ],);
+ [ 'misc empty script', 1, [qr{empty command list for script}], q{} ],
+ [ 'bad boolean', 0, [qr{malformed variable.*trueXXX}], q{\set b :badtrue or true} ],
+ );
+
for my $e (@errors)
{
@@ -415,7 +508,7 @@ for my $e (@errors)
my $n = '001_pgbench_error_' . $name;
$n =~ s/ /_/g;
pgbench(
- '-n -t 1 -Dfoo=bla -M prepared',
+ '-n -t 1 -Dfoo=bla -Dnull=null -Dtrue=true -Done=1 -Dzero=0.0 -Dbadtrue=trueXXX -M prepared',
$status,
[ $status ? qr{^$} : qr{processed: 0/1} ],
$re,
