You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@age.apache.org by jg...@apache.org on 2020/12/17 19:22:56 UTC
[incubator-age] branch master updated: Add mixed types for min() &
max()
This is an automated email from the ASF dual-hosted git repository.
jgemignani pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-age.git
The following commit(s) were added to refs/heads/master by this push:
new b3d6618 Add mixed types for min() & max()
b3d6618 is described below
commit b3d66184e3a93d6db04bfb674d81b50c2617f104
Author: John Gemignani <jr...@gmail.com>
AuthorDate: Wed Dec 16 17:39:46 2020 -0800
Add mixed types for min() & max()
Added mixed types for min() & max().
Added sortop for min() & max().
This was overlooked, my mistake.
Added regression tests.
---
age--0.2.0.sql | 46 ++++++++++++++++--
regress/expected/expr.out | 64 +++++++++++++++++--------
regress/sql/expr.sql | 18 ++++---
src/backend/parser/cypher_gram.y | 4 --
src/backend/utils/adt/agtype.c | 100 +++++++++++++++++++++++++++++++++++++++
5 files changed, 197 insertions(+), 35 deletions(-)
diff --git a/age--0.2.0.sql b/age--0.2.0.sql
index 46f532d..3be289f 100644
--- a/age--0.2.0.sql
+++ b/age--0.2.0.sql
@@ -1252,12 +1252,12 @@ LANGUAGE c
IMMUTABLE
PARALLEL SAFE
AS 'MODULE_PATHNAME';
--- aggregate for stdev
+-- aggregate for age_stdev
CREATE AGGREGATE ag_catalog.age_stdev(float8)
(
stype = _float8,
sfunc = float8_accum,
- finalfunc = age_float8_stddev_samp_aggfinalfn,
+ finalfunc = ag_catalog.age_float8_stddev_samp_aggfinalfn,
combinefunc = float8_combine,
finalfunc_modify = read_only,
initcond = '{0,0,0}',
@@ -1270,12 +1270,12 @@ LANGUAGE c
IMMUTABLE
PARALLEL SAFE
AS 'MODULE_PATHNAME';
--- aggregate for stdevp
+-- aggregate for age_stdevp
CREATE AGGREGATE ag_catalog.age_stdevp (float8)
(
stype = _float8,
sfunc = float8_accum,
- finalfunc = age_float8_stddev_pop_aggfinalfn,
+ finalfunc = ag_catalog.age_float8_stddev_pop_aggfinalfn,
combinefunc = float8_combine,
finalfunc_modify = read_only,
initcond = '{0,0,0}',
@@ -1283,6 +1283,44 @@ CREATE AGGREGATE ag_catalog.age_stdevp (float8)
);
--
+-- aggregate transfers function for min & max
+--
+-- max
+CREATE FUNCTION ag_catalog.age_agtype_larger_aggtransfn(agtype, agtype)
+RETURNS agtype
+LANGUAGE c
+IMMUTABLE
+PARALLEL SAFE
+AS 'MODULE_PATHNAME';
+-- aggregate for max
+CREATE AGGREGATE ag_catalog.age_max (agtype)
+(
+ stype = agtype,
+ sfunc = ag_catalog.age_agtype_larger_aggtransfn,
+ combinefunc = ag_catalog.age_agtype_larger_aggtransfn,
+ finalfunc_modify = read_only,
+ sortop = >,
+ parallel = safe
+);
+-- min
+CREATE FUNCTION ag_catalog.age_agtype_smaller_aggtransfn(agtype, agtype)
+RETURNS agtype
+LANGUAGE c
+IMMUTABLE
+PARALLEL SAFE
+AS 'MODULE_PATHNAME';
+-- aggregate for min
+CREATE AGGREGATE ag_catalog.age_min (agtype)
+(
+ stype = agtype,
+ sfunc = ag_catalog.age_agtype_smaller_aggtransfn,
+ combinefunc = ag_catalog.age_agtype_smaller_aggtransfn,
+ finalfunc_modify = read_only,
+ sortop = <,
+ parallel = safe
+);
+
+--
-- function for typecasting an agtype value to another agtype value
--
CREATE FUNCTION ag_catalog.agtype_typecast_numeric(agtype)
diff --git a/regress/expected/expr.out b/regress/expected/expr.out
index c4a2067..daedc33 100644
--- a/regress/expected/expr.out
+++ b/regress/expected/expr.out
@@ -4161,22 +4161,22 @@ NOTICE: graph "UCSC" has been created
(1 row)
-SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jack", gpa: 3.0, age: 21})$$) AS (a agtype);
+SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jack", gpa: 3.0, age: 21, zip: 94110})$$) AS (a agtype);
a
---
(0 rows)
-SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jill", gpa: 3.5, age: 27})$$) AS (a agtype);
+SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jill", gpa: 3.5, age: 27, zip: 95060})$$) AS (a agtype);
a
---
(0 rows)
-SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jim", gpa: 3.75, age: 32})$$) AS (a agtype);
+SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jim", gpa: 3.75, age: 32, zip: 96062})$$) AS (a agtype);
a
---
(0 rows)
-SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Rick", gpa: 2.5, age: 24})$$) AS (a agtype);
+SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Rick", gpa: 2.5, age: 24, zip: "95060"})$$) AS (a agtype);
a
---
(0 rows)
@@ -4186,7 +4186,7 @@ SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Ann", gpa: 3.8::numeric
---
(0 rows)
-SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Derek", gpa: 4.0, age: 19})$$) AS (a agtype);
+SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Derek", gpa: 4.0, age: 19, zip: 90210})$$) AS (a agtype);
a
---
(0 rows)
@@ -4197,14 +4197,14 @@ SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jessica", gpa: 3.9::num
(0 rows)
SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN (u) $$) AS (vertex agtype);
- vertex
--------------------------------------------------------------------------------------------------------------------------
- {"id": 844424930131969, "label": "students", "properties": {"age": 21, "gpa": 3.0, "name": "Jack"}}::vertex
- {"id": 844424930131970, "label": "students", "properties": {"age": 27, "gpa": 3.5, "name": "Jill"}}::vertex
- {"id": 844424930131971, "label": "students", "properties": {"age": 32, "gpa": 3.75, "name": "Jim"}}::vertex
- {"id": 844424930131972, "label": "students", "properties": {"age": 24, "gpa": 2.5, "name": "Rick"}}::vertex
+ vertex
+-----------------------------------------------------------------------------------------------------------------------------
+ {"id": 844424930131969, "label": "students", "properties": {"age": 21, "gpa": 3.0, "zip": 94110, "name": "Jack"}}::vertex
+ {"id": 844424930131970, "label": "students", "properties": {"age": 27, "gpa": 3.5, "zip": 95060, "name": "Jill"}}::vertex
+ {"id": 844424930131971, "label": "students", "properties": {"age": 32, "gpa": 3.75, "zip": 96062, "name": "Jim"}}::vertex
+ {"id": 844424930131972, "label": "students", "properties": {"age": 24, "gpa": 2.5, "zip": "95060", "name": "Rick"}}::vertex
{"id": 844424930131973, "label": "students", "properties": {"age": 23, "gpa": 3.8::numeric, "name": "Ann"}}::vertex
- {"id": 844424930131974, "label": "students", "properties": {"age": 19, "gpa": 4.0, "name": "Derek"}}::vertex
+ {"id": 844424930131974, "label": "students", "properties": {"age": 19, "gpa": 4.0, "zip": 90210, "name": "Derek"}}::vertex
{"id": 844424930131975, "label": "students", "properties": {"age": 20, "gpa": 3.9::numeric, "name": "Jessica"}}::vertex
(7 rows)
@@ -4227,14 +4227,14 @@ SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Mike", age: 18})$$) AS
(0 rows)
SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN (u) $$) AS (vertex agtype);
- vertex
--------------------------------------------------------------------------------------------------------------------------
- {"id": 844424930131969, "label": "students", "properties": {"age": 21, "gpa": 3.0, "name": "Jack"}}::vertex
- {"id": 844424930131970, "label": "students", "properties": {"age": 27, "gpa": 3.5, "name": "Jill"}}::vertex
- {"id": 844424930131971, "label": "students", "properties": {"age": 32, "gpa": 3.75, "name": "Jim"}}::vertex
- {"id": 844424930131972, "label": "students", "properties": {"age": 24, "gpa": 2.5, "name": "Rick"}}::vertex
+ vertex
+-----------------------------------------------------------------------------------------------------------------------------
+ {"id": 844424930131969, "label": "students", "properties": {"age": 21, "gpa": 3.0, "zip": 94110, "name": "Jack"}}::vertex
+ {"id": 844424930131970, "label": "students", "properties": {"age": 27, "gpa": 3.5, "zip": 95060, "name": "Jill"}}::vertex
+ {"id": 844424930131971, "label": "students", "properties": {"age": 32, "gpa": 3.75, "zip": 96062, "name": "Jim"}}::vertex
+ {"id": 844424930131972, "label": "students", "properties": {"age": 24, "gpa": 2.5, "zip": "95060", "name": "Rick"}}::vertex
{"id": 844424930131973, "label": "students", "properties": {"age": 23, "gpa": 3.8::numeric, "name": "Ann"}}::vertex
- {"id": 844424930131974, "label": "students", "properties": {"age": 19, "gpa": 4.0, "name": "Derek"}}::vertex
+ {"id": 844424930131974, "label": "students", "properties": {"age": 19, "gpa": 4.0, "zip": 90210, "name": "Derek"}}::vertex
{"id": 844424930131975, "label": "students", "properties": {"age": 20, "gpa": 3.9::numeric, "name": "Jessica"}}::vertex
{"id": 844424930131976, "label": "students", "properties": {"age": 24, "name": "Dave"}}::vertex
{"id": 844424930131977, "label": "students", "properties": {"age": 18, "name": "Mike"}}::vertex
@@ -4292,6 +4292,28 @@ AS (min agtype, max agtype, count agtype, count_star agtype);
2.5 | 4.0 | 7 | 9
(1 row)
+SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN min(u.gpa), max(u.gpa), count(u.gpa), count(*) $$)
+AS (min agtype, max agtype, count agtype, count_star agtype);
+ min | max | count | count_star
+-----+-----+-------+------------
+ 2.5 | 4.0 | 7 | 9
+(1 row)
+
+SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN min(u.name), max(u.name), count(u.name), count(*) $$)
+AS (min agtype, max agtype, count agtype, count_star agtype);
+ min | max | count | count_star
+-------+--------+-------+------------
+ "Ann" | "Rick" | 9 | 9
+(1 row)
+
+-- check that min() & max() can work against mixed types
+SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN min(u.zip), max(u.zip), count(u.zip), count(*) $$)
+AS (min agtype, max agtype, count agtype, count_star agtype);
+ min | max | count | count_star
+---------+-------+-------+------------
+ "95060" | 96062 | 5 | 9
+(1 row)
+
-- should return null
SELECT * FROM cypher('UCSC', $$ RETURN min(NULL) $$) AS (min agtype);
min
@@ -4307,12 +4329,12 @@ SELECT * FROM cypher('UCSC', $$ RETURN max(NULL) $$) AS (max agtype);
-- should fail
SELECT * FROM cypher('UCSC', $$ RETURN min() $$) AS (min agtype);
-ERROR: function pg_catalog.min() does not exist
+ERROR: function ag_catalog.age_min() does not exist
LINE 1: SELECT * FROM cypher('UCSC', $$ RETURN min() $$) AS (min agt...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SELECT * FROM cypher('UCSC', $$ RETURN max() $$) AS (max agtype);
-ERROR: function pg_catalog.max() does not exist
+ERROR: function ag_catalog.age_max() does not exist
LINE 1: SELECT * FROM cypher('UCSC', $$ RETURN max() $$) AS (max agt...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
diff --git a/regress/sql/expr.sql b/regress/sql/expr.sql
index b3e20b7..8de52f4 100644
--- a/regress/sql/expr.sql
+++ b/regress/sql/expr.sql
@@ -1777,13 +1777,12 @@ $$) as (result agtype);
-- aggregate functions avg(), sum(), count(), & count(*)
--
SELECT create_graph('UCSC');
-
-SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jack", gpa: 3.0, age: 21})$$) AS (a agtype);
-SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jill", gpa: 3.5, age: 27})$$) AS (a agtype);
-SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jim", gpa: 3.75, age: 32})$$) AS (a agtype);
-SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Rick", gpa: 2.5, age: 24})$$) AS (a agtype);
+SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jack", gpa: 3.0, age: 21, zip: 94110})$$) AS (a agtype);
+SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jill", gpa: 3.5, age: 27, zip: 95060})$$) AS (a agtype);
+SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jim", gpa: 3.75, age: 32, zip: 96062})$$) AS (a agtype);
+SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Rick", gpa: 2.5, age: 24, zip: "95060"})$$) AS (a agtype);
SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Ann", gpa: 3.8::numeric, age: 23})$$) AS (a agtype);
-SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Derek", gpa: 4.0, age: 19})$$) AS (a agtype);
+SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Derek", gpa: 4.0, age: 19, zip: 90210})$$) AS (a agtype);
SELECT * FROM cypher('UCSC', $$CREATE (:students {name: "Jessica", gpa: 3.9::numeric, age: 20})$$) AS (a agtype);
SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN (u) $$) AS (vertex agtype);
SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN avg(u.gpa), sum(u.gpa), sum(u.gpa)/count(u.gpa), count(u.gpa), count(*) $$)
@@ -1809,6 +1808,13 @@ SELECT * FROM cypher('UCSC', $$ RETURN count() $$) AS (count agtype);
--
SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN min(u.gpa), max(u.gpa), count(u.gpa), count(*) $$)
AS (min agtype, max agtype, count agtype, count_star agtype);
+SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN min(u.gpa), max(u.gpa), count(u.gpa), count(*) $$)
+AS (min agtype, max agtype, count agtype, count_star agtype);
+SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN min(u.name), max(u.name), count(u.name), count(*) $$)
+AS (min agtype, max agtype, count agtype, count_star agtype);
+-- check that min() & max() can work against mixed types
+SELECT * FROM cypher('UCSC', $$ MATCH (u) RETURN min(u.zip), max(u.zip), count(u.zip), count(*) $$)
+AS (min agtype, max agtype, count agtype, count_star agtype);
-- should return null
SELECT * FROM cypher('UCSC', $$ RETURN min(NULL) $$) AS (min agtype);
SELECT * FROM cypher('UCSC', $$ RETURN max(NULL) $$) AS (max agtype);
diff --git a/src/backend/parser/cypher_gram.y b/src/backend/parser/cypher_gram.y
index d78e62c..ea3cda2 100644
--- a/src/backend/parser/cypher_gram.y
+++ b/src/backend/parser/cypher_gram.y
@@ -1596,10 +1596,6 @@ static Node *make_function_expr(List *func_name, List *exprs, int location)
funcname = SystemFuncName("sum");
else if (pg_strcasecmp(name, "count") == 0)
funcname = SystemFuncName("count");
- else if (pg_strcasecmp(name, "min") == 0)
- funcname = SystemFuncName("min");
- else if (pg_strcasecmp(name, "max") == 0)
- funcname = SystemFuncName("max");
else
/*
* We don't qualify AGE functions here. This is done in the
diff --git a/src/backend/utils/adt/agtype.c b/src/backend/utils/adt/agtype.c
index 92d359f..899d5bd 100644
--- a/src/backend/utils/adt/agtype.c
+++ b/src/backend/utils/adt/agtype.c
@@ -7153,3 +7153,103 @@ Datum age_float8_stddev_pop_aggfinalfn(PG_FUNCTION_ARGS)
return result;
}
+
+PG_FUNCTION_INFO_V1(age_agtype_larger_aggtransfn);
+
+Datum age_agtype_larger_aggtransfn(PG_FUNCTION_ARGS)
+{
+ agtype *agtype_arg1;
+ agtype *agtype_arg2;
+ agtype_value *agtv_arg1;
+ agtype_value *agtv_arg2;
+ agtype *agtype_larger;
+ int test;
+
+ /* for max we need to ignore NULL values */
+ /* if both are NULL return NULL */
+ if (PG_ARGISNULL(0) && PG_ARGISNULL(1))
+ PG_RETURN_NULL();
+
+ /* if either are NULL, return the other */
+ if (PG_ARGISNULL(0))
+ PG_RETURN_POINTER(AG_GET_ARG_AGTYPE_P(1));
+
+ if (PG_ARGISNULL(1))
+ PG_RETURN_POINTER(AG_GET_ARG_AGTYPE_P(0));
+
+ /* get the arguments */
+ agtype_arg1 = AG_GET_ARG_AGTYPE_P(0);
+ agtype_arg2 = AG_GET_ARG_AGTYPE_P(1);
+
+ /* get the values because we need to test for AGTV_NULL */
+ agtv_arg1 = get_ith_agtype_value_from_container(&agtype_arg1->root, 0);
+ agtv_arg2 = get_ith_agtype_value_from_container(&agtype_arg2->root, 0);
+
+ /* check for AGTV_NULL, same as NULL above */
+ if (agtv_arg1->type == AGTV_NULL && agtv_arg2->type == AGTV_NULL)
+ PG_RETURN_NULL();
+
+ if (agtv_arg1->type == AGTV_NULL)
+ PG_RETURN_POINTER(AG_GET_ARG_AGTYPE_P(1));
+
+ if (agtv_arg2->type == AGTV_NULL)
+ PG_RETURN_POINTER(AG_GET_ARG_AGTYPE_P(0));
+
+ /* test for max value */
+ test = compare_agtype_containers_orderability(&agtype_arg1->root,
+ &agtype_arg2->root);
+
+ agtype_larger = (test >= 0) ? agtype_arg1 : agtype_arg2;
+
+ PG_RETURN_POINTER(agtype_larger);
+}
+
+PG_FUNCTION_INFO_V1(age_agtype_smaller_aggtransfn);
+
+Datum age_agtype_smaller_aggtransfn(PG_FUNCTION_ARGS)
+{
+ agtype *agtype_arg1;
+ agtype *agtype_arg2;
+ agtype_value *agtv_arg1;
+ agtype_value *agtv_arg2;
+ agtype *agtype_smaller;
+ int test;
+
+ /* for min we need to ignore NULL values */
+ /* if both are NULL return NULL */
+ if (PG_ARGISNULL(0) && PG_ARGISNULL(1))
+ PG_RETURN_NULL();
+
+ /* if either are NULL, return the other */
+ if (PG_ARGISNULL(0))
+ PG_RETURN_POINTER(AG_GET_ARG_AGTYPE_P(1));
+
+ if (PG_ARGISNULL(1))
+ PG_RETURN_POINTER(AG_GET_ARG_AGTYPE_P(0));
+
+ /* get the arguments */
+ agtype_arg1 = AG_GET_ARG_AGTYPE_P(0);
+ agtype_arg2 = AG_GET_ARG_AGTYPE_P(1);
+
+ /* get the values because we need to test for AGTV_NULL */
+ agtv_arg1 = get_ith_agtype_value_from_container(&agtype_arg1->root, 0);
+ agtv_arg2 = get_ith_agtype_value_from_container(&agtype_arg2->root, 0);
+
+ /* check for AGTV_NULL, same as NULL above */
+ if (agtv_arg1->type == AGTV_NULL && agtv_arg2->type == AGTV_NULL)
+ PG_RETURN_NULL();
+
+ if (agtv_arg1->type == AGTV_NULL)
+ PG_RETURN_POINTER(AG_GET_ARG_AGTYPE_P(1));
+
+ if (agtv_arg2->type == AGTV_NULL)
+ PG_RETURN_POINTER(AG_GET_ARG_AGTYPE_P(0));
+
+ /* test for min value */
+ test = compare_agtype_containers_orderability(&agtype_arg1->root,
+ &agtype_arg2->root);
+
+ agtype_smaller = (test <= 0) ? agtype_arg1 : agtype_arg2;
+
+ PG_RETURN_POINTER(agtype_smaller);
+}