SET timezone TO 'Etc/UTC'; /* * Pushdown regression tests. * All queries use EXPLAIN (VERBOSE, COSTS OFF) - no network calls are made. * This validates SQL-to-SPARQL translation for every pushable construct * without depending on any external triple store. */ CREATE SERVER test_server FOREIGN DATA WRAPPER rdf_fdw OPTIONS (endpoint 'http://localhost/sparql'); /* ---------------------------------------------------------------- * rdfnode_ft — rdfnode column pushdown tests * ---------------------------------------------------------------- */ CREATE FOREIGN TABLE rdfnode_ft ( p rdfnode OPTIONS (variable '?p'), o rdfnode OPTIONS (variable '?o') ) SERVER test_server OPTIONS ( sparql 'SELECT * WHERE { ?p ?o}'); /* rdfnode_opt_ft — BOUND / COALESCE tests (needs OPTIONAL binding) */ CREATE FOREIGN TABLE rdfnode_opt_ft ( p rdfnode OPTIONS (variable '?p'), o rdfnode OPTIONS (variable '?o'), x rdfnode OPTIONS (variable '?x') ) SERVER test_server OPTIONS ( sparql 'SELECT * WHERE { ?p ?o OPTIONAL {?o ?x}}'); /* ---------------------------------------------------------------- * pgtypes_ft — pg-typed column pushdown tests * ---------------------------------------------------------------- */ CREATE FOREIGN TABLE pgtypes_ft ( label text OPTIONS (variable '?label', language '*'), version bigint OPTIONS (variable '?version', literaltype 'xsd:integer'), num_smallint smallint OPTIONS (variable '?sint', literaltype 'xsd:short'), num_int int OPTIONS (variable '?int', literaltype 'xsd:int'), num_real real OPTIONS (variable '?real', literaltype 'xsd:float'), num_double double precision OPTIONS (variable '?double', literaltype 'xsd:double'), num_numeric numeric OPTIONS (variable '?numeric', literaltype 'xsd:decimal'), modified timestamp OPTIONS (variable '?modified',literaltype 'xsd:dateTime'), tstz timestamptz OPTIONS (variable '?tstz', literaltype 'xsd:dateTime'), dt date OPTIONS (variable '?dt', literaltype 'xsd:date'), ttz timetz OPTIONS (variable '?ttz', literaltype 'xsd:time'), bl boolean OPTIONS (variable '?bl', literaltype 'xsd:boolean'), type text OPTIONS (variable '?type', nodetype 'iri') ) SERVER test_server OPTIONS ( sparql 'SELECT * WHERE { ?p ?o}'); /* ================================================================ * SPARQL 15.5 - LIMIT * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft LIMIT 5; QUERY PLAN ----------------------------------------- Limit Output: p, o -> Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Limit: LIMIT 5 (8 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft FETCH FIRST 5 ROWS ONLY; QUERY PLAN ----------------------------------------- Limit Output: p, o -> Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Limit: LIMIT 5 (8 rows) /* ================================================================ * SPARQL 15.4 - OFFSET * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft OFFSET 5 LIMIT 10; QUERY PLAN ----------------------------------------- Limit Output: p, o -> Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Limit: LIMIT 15 (8 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft OFFSET 5 ROWS FETCH FIRST 10 ROWS ONLY; QUERY PLAN ----------------------------------------- Limit Output: p, o -> Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Limit: LIMIT 15 (8 rows) /* ================================================================ * SPARQL 15.1 - ORDER BY * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft ORDER BY p DESC LIMIT 3; QUERY PLAN ----------------------------------------------- Limit Output: p, o -> Sort Output: p, o Sort Key: rdfnode_ft.p DESC -> Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Sort Key: DESC (?p) Remote Limit: LIMIT 3 (12 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft ORDER BY o ASC LIMIT 3; QUERY PLAN ----------------------------------------------- Limit Output: p, o -> Sort Output: p, o Sort Key: rdfnode_ft.o -> Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Sort Key: ASC (?o) Remote Limit: LIMIT 3 (12 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft ORDER BY p DESC, o ASC LIMIT 3; QUERY PLAN ------------------------------------------------------ Limit Output: p, o -> Sort Output: p, o Sort Key: rdfnode_ft.p DESC, rdfnode_ft.o -> Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Sort Key: DESC (?p) ASC (?o) Remote Limit: LIMIT 3 (12 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft ORDER BY p DESC, o ASC OFFSET 5 LIMIT 2; QUERY PLAN ------------------------------------------------------ Limit Output: p, o -> Sort Output: p, o Sort Key: rdfnode_ft.p DESC, rdfnode_ft.o -> Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Sort Key: DESC (?p) ASC (?o) Remote Limit: LIMIT 7 (12 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft ORDER BY 1 DESC, 2 ASC OFFSET 5 LIMIT 10; QUERY PLAN ------------------------------------------------------ Limit Output: p, o -> Sort Output: p, o Sort Key: rdfnode_ft.p DESC, rdfnode_ft.o -> Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Sort Key: DESC (?p) ASC (?o) Remote Limit: LIMIT 15 (12 rows) /* ================================================================ * SPARQL 18.2.5.3 - DISTINCT * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT DISTINCT p FROM rdfnode_ft WHERE p = ''; QUERY PLAN ------------------------------------------------------------------------------------ Unique Output: p -> Sort Output: p Sort Key: rdfnode_ft.p -> Foreign Scan on public.rdfnode_ft Output: p Foreign Server: test_server Pushdown: enabled Remote Select: ?p Remote Filter: ((?p = )) Remote Sort Key: ASC (?p) (12 rows) -- DISTINCT ON is not supported and won't be pushed down EXPLAIN (VERBOSE, COSTS OFF) SELECT DISTINCT ON (p) p, o FROM rdfnode_ft WHERE p = ''; QUERY PLAN ------------------------------------------------------------------------------------ Unique Output: p, o -> Sort Output: p, o Sort Key: rdfnode_ft.p -> Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = )) Remote Sort Key: ASC (?p) (12 rows) /* ================================================================ * SPARQL 17.4.1.7 - RDFterm-equal * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft WHERE p = sparql.iri('http://www.w3.org/2000/01/rdf-schema#label') AND o = '"hello"@en'; QUERY PLAN --------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (?o = "hello"@en)) (6 rows) /* ================================================================ * SPARQL 17.4.1.9 - IN * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft WHERE p = sparql.iri('http://www.w3.org/2000/01/rdf-schema#label') AND o IN ('"hello"@en'::rdfnode, '"hello"@fr', sparql.strlang('hello', 'de')); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (?o IN ("hello"@en, "hello"@fr, "hello"@de))) (6 rows) /* ================================================================ * SPARQL 17.4.1.10 - NOT IN * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft WHERE p = sparql.iri('http://www.w3.org/2000/01/rdf-schema#label') AND o NOT IN ('"hello"@en'::rdfnode, '"hello"@fr', sparql.strlang('hello', 'de')) LIMIT 5; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Limit Output: p, o -> Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (?o NOT IN ("hello"@en, "hello"@fr, "hello"@de))) Remote Limit: LIMIT 5 (9 rows) /* ================================================================ * SPARQL 17.3 - Operator Mapping (text op rdfnode) * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft WHERE p = '' AND o > '"a"' AND o < '"z"' AND o >= '"a"' AND o <= '"z"' AND o <> '"foo"' AND sparql.str(o) BETWEEN '"a"' AND '"z"' LIMIT 3; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit Output: p, o -> Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (?o > "a") && (?o < "z") && (?o >= "a") && (?o <= "z") && (?o != "foo") && (STR(?o) >= "a") && (STR(?o) <= "z")) Remote Limit: LIMIT 3 (9 rows) /* SPARQL 17.3 - Operator Mapping (rdfnode op rdfnode) */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft WHERE p = ''::rdfnode AND o > '"a"'::rdfnode AND o < '"z"'::rdfnode AND o >= '"a"'::rdfnode AND o <= '"z"'::rdfnode AND o <> '"foo"'::rdfnode AND sparql.str(o) BETWEEN '"a"'::rdfnode AND '"z"'::rdfnode LIMIT 3; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit Output: p, o -> Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (?o > "a") && (?o < "z") && (?o >= "a") && (?o <= "z") && (?o != "foo") && (STR(?o) >= "a") && (STR(?o) <= "z")) Remote Limit: LIMIT 3 (9 rows) /* SPARQL 17.3 - Operator Mapping (smallint op rdfnode) */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft WHERE p = ''::rdfnode AND o = 100::smallint AND o <> 999::smallint AND o > 10::smallint AND o < 999::smallint AND o >= 100::smallint AND o <= 100::smallint AND o BETWEEN 10::smallint AND 200::smallint AND 100::smallint = o; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (?o = 100) && (?o != 999) && (?o > 10) && (?o < 999) && (?o >= 100) && (?o <= 100) && (?o >= 10) && (?o <= 200) && (100 = ?o)) (6 rows) /* SPARQL 17.3 - Operator Mapping (int op rdfnode) */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft WHERE p = ''::rdfnode AND o = 100::int AND o <> 999::int AND o > 10::int AND o < 999::int AND o >= 100::int AND o <= 100::int AND o BETWEEN 10::int AND 200::int AND 100::int = o; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (?o = 100) && (?o != 999) && (?o > 10) && (?o < 999) && (?o >= 100) && (?o <= 100) && (?o >= 10) && (?o <= 200) && (100 = ?o)) (6 rows) /* SPARQL 17.3 - Operator Mapping (bigint op rdfnode) */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft WHERE p = ''::rdfnode AND o = 100::bigint AND o <> 999::bigint AND o > 10::bigint AND o < 999::bigint AND o >= 100::bigint AND o <= 100::bigint AND o BETWEEN 10::bigint AND 200::bigint AND 100::bigint = o; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (?o = 100) && (?o != 999) && (?o > 10) && (?o < 999) && (?o >= 100) && (?o <= 100) && (?o >= 10) && (?o <= 200) && (100 = ?o)) (6 rows) /* SPARQL 17.3 - Operator Mapping (real op rdfnode) */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft WHERE p = ''::rdfnode AND o = 1.5::real AND o <> 9.9::real AND o > 1.0::real AND o < 9.9::real AND o >= 1.5::real AND o <= 1.5::real AND o BETWEEN 1.0::real AND 2.0::real AND 1.5::real = o; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (?o = 1.5) && (?o != 9.9) && (?o > 1) && (?o < 9.9) && (?o >= 1.5) && (?o <= 1.5) && (?o >= 1) && (?o <= 2) && (1.5 = ?o)) (6 rows) /* SPARQL 17.3 - Operator Mapping (double precision op rdfnode) */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft WHERE p = ''::rdfnode AND o = 1.5::double precision AND o <> 9.9::double precision AND o > 1.0::double precision AND o < 9.9::double precision AND o >= 1.5::double precision AND o <= 1.5::double precision AND o BETWEEN 1.0::double precision AND 2.0::double precision AND 1.5::double precision = o; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (?o = 1.5) && (?o != 9.9) && (?o > 1) && (?o < 9.9) && (?o >= 1.5) && (?o <= 1.5) && (?o >= 1) && (?o <= 2) && (1.5 = ?o)) (6 rows) /* SPARQL 17.3 - Operator Mapping (numeric op rdfnode) */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft WHERE p = ''::rdfnode AND o = 1.5::numeric AND o <> 9.9::numeric AND o > 1.0::numeric AND o < 9.9::numeric AND o >= 1.5::numeric AND o <= 1.5::numeric AND o BETWEEN 1.0::numeric AND 2.0::numeric AND 1.5::numeric = o; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (?o = 1.5) && (?o != 9.9) && (?o > 1.0) && (?o < 9.9) && (?o >= 1.5) && (?o <= 1.5) && (?o >= 1.0) && (?o <= 2.0) && (1.5 = ?o)) (6 rows) /* SPARQL 17.3 - Operator Mapping (timestamp op rdfnode) */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft WHERE p = ''::rdfnode AND o = '2015-01-01 00:00:00'::timestamp AND o <> '2020-01-01 00:00:00'::timestamp AND o > '2010-01-01 00:00:00'::timestamp AND o < '2020-01-01 00:00:00'::timestamp AND o >= '2015-01-01 00:00:00'::timestamp AND o <= '2015-01-01 00:00:00'::timestamp AND o BETWEEN '2010-01-01 00:00:00'::timestamp AND '2020-01-01 00:00:00'::timestamp AND '2015-01-01 00:00:00'::timestamp = o; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (?o != "2020-01-01T00:00:00"^^) && (?o > "2010-01-01T00:00:00"^^) && (?o < "2020-01-01T00:00:00"^^) && (?o >= "2015-01-01T00:00:00"^^) && (?o <= "2015-01-01T00:00:00"^^) && (?o >= "2010-01-01T00:00:00"^^) && (?o <= "2020-01-01T00:00:00"^^) && (?o = "2015-01-01T00:00:00"^^)) (6 rows) /* SPARQL 17.3 - Operator Mapping (timestamptz op rdfnode) */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft WHERE p = ''::rdfnode AND o = '2015-01-01 00:00:00'::timestamptz AND o <> '2020-01-01 00:00:00'::timestamptz AND o > '2010-01-01 00:00:00'::timestamptz AND o < '2020-01-01 00:00:00'::timestamptz AND o >= '2015-01-01 00:00:00'::timestamptz AND o <= '2015-01-01 00:00:00'::timestamptz AND o BETWEEN '2010-01-01 00:00:00'::timestamptz AND '2020-01-01 00:00:00'::timestamptz AND '2015-01-01 00:00:00'::timestamptz = o; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (?o != "2020-01-01T00:00:00.000000Z"^^) && (?o > "2010-01-01T00:00:00.000000Z"^^) && (?o < "2020-01-01T00:00:00.000000Z"^^) && (?o >= "2015-01-01T00:00:00.000000Z"^^) && (?o <= "2015-01-01T00:00:00.000000Z"^^) && (?o >= "2010-01-01T00:00:00.000000Z"^^) && (?o <= "2020-01-01T00:00:00.000000Z"^^) && (?o = "2015-01-01T00:00:00.000000Z"^^)) (6 rows) /* SPARQL 17.3 - Operator Mapping (date op rdfnode, via strdt/substr/str) */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft WHERE p = ''::rdfnode AND '2015-01-01'::date = sparql.strdt(sparql.substr(sparql.str(o), 1, 10), 'xsd:date') AND '2015-01-01'::date >= sparql.strdt(sparql.substr(sparql.str(o), 1, 10), 'xsd:date') AND '2020-01-01'::date > sparql.strdt(sparql.substr(sparql.str(o), 1, 10), 'xsd:date'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && ("2015-01-01"^^ = STRDT(SUBSTR(STR(?o), 1, 10), )) && ("2015-01-01"^^ >= STRDT(SUBSTR(STR(?o), 1, 10), )) && ("2020-01-01"^^ > STRDT(SUBSTR(STR(?o), 1, 10), ))) (6 rows) /* SPARQL 17.3 - Operator Mapping (timetz op rdfnode, via strdt/substr/str) */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft WHERE p = ''::rdfnode AND '12:00:00 UTC'::timetz = sparql.strdt(sparql.substr(sparql.str(o), 12, 8), 'xsd:time') AND '12:00:00 UTC'::timetz >= sparql.strdt(sparql.substr(sparql.str(o), 12, 8), 'xsd:time') AND '23:00:00 UTC'::timetz > sparql.strdt(sparql.substr(sparql.str(o), 12, 8), 'xsd:time'); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && ("12:00:00+00"^^ = STRDT(SUBSTR(STR(?o), 12, 8), )) && ("12:00:00+00"^^ >= STRDT(SUBSTR(STR(?o), 12, 8), )) && ("23:00:00+00"^^ > STRDT(SUBSTR(STR(?o), 12, 8), ))) (6 rows) /* SPARQL 17.3 - Operator Mapping (boolean op rdfnode) */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM rdfnode_ft WHERE p = ''::rdfnode AND true <> o AND false <> o; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && ("true"^^ != ?o) && ("false"^^ != ?o)) (6 rows) /* ================================================================ * SPARQL 17.3 - Operator Mapping (pg-typed columns) * ================================================================ */ /* text: =, <>, IN, NOT IN */ EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label = 'hello' AND label <> 'foo' AND label IN ('hello', 'world') AND label NOT IN ('foo', 'bar'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((STR(?label) != "foo") && (?label IN ("hello"@*, "world"@*)) && (?label NOT IN ("foo"@*, "bar"@*)) && (STR(?label) = "hello")) (6 rows) /* bigint: all operators */ EXPLAIN (VERBOSE, COSTS OFF) SELECT version FROM pgtypes_ft WHERE version = 42 AND version <> 99 AND version > 10 AND version < 99 AND version >= 42 AND version <= 42 AND version BETWEEN 10 AND 100 AND version IN (42, 43) AND version NOT IN (0, 99); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: version Foreign Server: test_server Pushdown: enabled Remote Select: ?version Remote Filter: ((?version != 99) && (?version > 10) && (?version < 99) && (?version >= 42) && (?version <= 42) && (?version >= 10) && (?version <= 100) && (?version IN (42, 43)) && (?version NOT IN (0, 99)) && (?version = 42)) (6 rows) /* smallint: all operators */ EXPLAIN (VERBOSE, COSTS OFF) SELECT num_smallint FROM pgtypes_ft WHERE num_smallint = 42::smallint AND num_smallint <> 99::smallint AND num_smallint > 10::smallint AND num_smallint < 99::smallint AND num_smallint >= 42::smallint AND num_smallint <= 42::smallint AND num_smallint BETWEEN 10::smallint AND 100::smallint; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: num_smallint Foreign Server: test_server Pushdown: enabled Remote Select: ?sint Remote Filter: ((?sint != 99) && (?sint > 10) && (?sint < 99) && (?sint >= 42) && (?sint <= 42) && (?sint >= 10) && (?sint <= 100) && (?sint = 42)) (6 rows) /* int: all operators */ EXPLAIN (VERBOSE, COSTS OFF) SELECT num_int FROM pgtypes_ft WHERE num_int = 42 AND num_int <> 99 AND num_int > 10 AND num_int < 99 AND num_int >= 42 AND num_int <= 42 AND num_int BETWEEN 10 AND 100; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: num_int Foreign Server: test_server Pushdown: enabled Remote Select: ?int Remote Filter: ((?int != 99) && (?int > 10) && (?int < 99) && (?int >= 42) && (?int <= 42) && (?int >= 10) && (?int <= 100) && (?int = 42)) (6 rows) /* real: all operators */ EXPLAIN (VERBOSE, COSTS OFF) SELECT num_real FROM pgtypes_ft WHERE num_real = 1.5::real AND num_real <> 9.9::real AND num_real > 1.0::real AND num_real < 9.9::real AND num_real >= 1.5::real AND num_real <= 1.5::real AND num_real BETWEEN 1.0::real AND 2.0::real; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: num_real Foreign Server: test_server Pushdown: enabled Remote Select: ?real Remote Filter: ((?real != 9.9) && (?real > 1) && (?real < 9.9) && (?real >= 1.5) && (?real <= 1.5) && (?real >= 1) && (?real <= 2) && (?real = 1.5)) (6 rows) /* double precision: all operators */ EXPLAIN (VERBOSE, COSTS OFF) SELECT num_double FROM pgtypes_ft WHERE num_double = 1.5::double precision AND num_double <> 9.9::double precision AND num_double > 1.0::double precision AND num_double < 9.9::double precision AND num_double >= 1.5::double precision AND num_double <= 1.5::double precision AND num_double BETWEEN 1.0::double precision AND 2.0::double precision; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.pgtypes_ft Output: num_double Foreign Server: test_server Pushdown: enabled Remote Select: ?double Remote Filter: ((?double != 9.9) && (?double > 1) && (?double < 9.9) && (?double >= 1.5) && (?double <= 1.5) && (?double >= 1) && (?double <= 2) && (?double = 1.5)) (6 rows) /* numeric: all operators */ EXPLAIN (VERBOSE, COSTS OFF) SELECT num_numeric FROM pgtypes_ft WHERE num_numeric = 1.5::numeric AND num_numeric <> 9.9::numeric AND num_numeric > 1.0::numeric AND num_numeric < 9.9::numeric AND num_numeric >= 1.5::numeric AND num_numeric <= 1.5::numeric AND num_numeric BETWEEN 1.0::numeric AND 2.0::numeric; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: num_numeric Foreign Server: test_server Pushdown: enabled Remote Select: ?numeric Remote Filter: ((?numeric != 9.9) && (?numeric > 1.0) && (?numeric < 9.9) && (?numeric >= 1.5) && (?numeric <= 1.5) && (?numeric >= 1.0) && (?numeric <= 2.0) && (?numeric = 1.5)) (6 rows) /* timestamp: all operators */ EXPLAIN (VERBOSE, COSTS OFF) SELECT modified FROM pgtypes_ft WHERE modified = '2015-07-12 20:41:25'::timestamp AND modified <> '2020-07-12 20:41:25'::timestamp AND modified > '2014-07-12 20:41:25'::timestamp AND modified < '2016-07-12 20:41:25'::timestamp AND modified >= '2015-07-12 20:41:25'::timestamp AND modified <= '2015-07-12 20:41:25'::timestamp AND modified BETWEEN '2014-07-12 20:41:25'::timestamp AND '2016-07-12 20:41:25'::timestamp; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: modified Foreign Server: test_server Pushdown: enabled Remote Select: ?modified Remote Filter: ((?modified != "2020-07-12T20:41:25"^^) && (?modified > "2014-07-12T20:41:25"^^) && (?modified < "2016-07-12T20:41:25"^^) && (?modified >= "2015-07-12T20:41:25"^^) && (?modified <= "2015-07-12T20:41:25"^^) && (?modified >= "2014-07-12T20:41:25"^^) && (?modified <= "2016-07-12T20:41:25"^^) && (?modified = "2015-07-12T20:41:25"^^)) (6 rows) /* timestamptz: all operators */ EXPLAIN (VERBOSE, COSTS OFF) SELECT tstz FROM pgtypes_ft WHERE tstz = '2011-01-10 14:45:13.815-05:00'::timestamptz AND tstz <> '2020-01-10 14:45:13.815-05:00'::timestamptz AND tstz > '2010-01-10 14:45:13.815-05:00'::timestamptz AND tstz < '2012-01-10 14:45:13.815-05:00'::timestamptz AND tstz >= '2011-01-10 14:45:13.815-05:00'::timestamptz AND tstz <= '2011-01-10 14:45:13.815-05:00'::timestamptz AND tstz BETWEEN '2010-01-10 14:45:13.815-05:00'::timestamptz AND '2012-01-10 14:45:13.815-05:00'::timestamptz; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.pgtypes_ft Output: tstz Foreign Server: test_server Pushdown: enabled Remote Select: ?tstz Remote Filter: ((?tstz != "2020-01-10T19:45:13.815000Z"^^) && (?tstz > "2010-01-10T19:45:13.815000Z"^^) && (?tstz < "2012-01-10T19:45:13.815000Z"^^) && (?tstz >= "2011-01-10T19:45:13.815000Z"^^) && (?tstz <= "2011-01-10T19:45:13.815000Z"^^) && (?tstz >= "2010-01-10T19:45:13.815000Z"^^) && (?tstz <= "2012-01-10T19:45:13.815000Z"^^) && (?tstz = "2011-01-10T19:45:13.815000Z"^^)) (6 rows) /* date: all operators */ EXPLAIN (VERBOSE, COSTS OFF) SELECT dt FROM pgtypes_ft WHERE dt = '2018-05-01'::date AND dt <> '2020-05-01'::date AND dt > '2017-05-01'::date AND dt < '2019-05-01'::date AND dt >= '2018-05-01'::date AND dt <= '2018-05-01'::date AND dt BETWEEN '2017-05-01'::date AND '2019-05-01'::date AND dt IN ('2018-05-01', '2019-05-01') AND dt NOT IN ('2000-01-01', '2020-01-01'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.pgtypes_ft Output: dt Foreign Server: test_server Pushdown: enabled Remote Select: ?dt Remote Filter: ((?dt != "2020-05-01"^^) && (?dt > "2017-05-01"^^) && (?dt < "2019-05-01"^^) && (?dt >= "2018-05-01"^^) && (?dt <= "2018-05-01"^^) && (?dt >= "2017-05-01"^^) && (?dt <= "2019-05-01"^^) && (?dt IN ("2018-05-01"^^, "2019-05-01"^^)) && (?dt NOT IN ("2000-01-01"^^, "2020-01-01"^^)) && (?dt = "2018-05-01"^^)) (6 rows) /* timetz: all operators */ EXPLAIN (VERBOSE, COSTS OFF) SELECT ttz FROM pgtypes_ft WHERE ttz = '12:00:00 UTC'::timetz AND ttz <> '23:00:00 UTC'::timetz AND ttz > '10:00:00 UTC'::timetz AND ttz < '23:00:00 UTC'::timetz AND ttz >= '12:00:00 UTC'::timetz AND ttz <= '12:00:00 UTC'::timetz AND ttz BETWEEN '10:00:00 UTC'::timetz AND '14:00:00 UTC'::timetz; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.pgtypes_ft Output: ttz Foreign Server: test_server Pushdown: enabled Remote Select: ?ttz Remote Filter: ((?ttz != "23:00:00+00"^^) && (?ttz > "10:00:00+00"^^) && (?ttz < "23:00:00+00"^^) && (?ttz >= "12:00:00+00"^^) && (?ttz <= "12:00:00+00"^^) && (?ttz >= "10:00:00+00"^^) && (?ttz <= "14:00:00+00"^^) && (?ttz = "12:00:00+00"^^)) (6 rows) /* boolean: IS / IS NOT (pushable) */ EXPLAIN (VERBOSE, COSTS OFF) SELECT bl FROM pgtypes_ft WHERE bl IS true AND bl IS NOT false; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: bl Foreign Server: test_server Pushdown: enabled Remote Select: ?bl Remote Filter: ((?bl = "true"^^) && (?bl != "false"^^)) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT bl FROM pgtypes_ft WHERE bl IS false AND bl IS NOT true; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: bl Foreign Server: test_server Pushdown: enabled Remote Select: ?bl Remote Filter: ((?bl = "false"^^) && (?bl != "true"^^)) (6 rows) /* boolean: = / <> (NOT pushable) */ EXPLAIN (VERBOSE, COSTS OFF) SELECT bl FROM pgtypes_ft WHERE bl = true AND bl <> false; QUERY PLAN --------------------------------------------- Foreign Scan on public.pgtypes_ft Output: bl Filter: (pgtypes_ft.bl AND pgtypes_ft.bl) Foreign Server: test_server Pushdown: enabled Remote Select: ?bl Remote Filter: not pushable (7 rows) /* iri column */ EXPLAIN (VERBOSE, COSTS OFF) SELECT type FROM pgtypes_ft WHERE type = 'http://example.org/SomeType'; QUERY PLAN ----------------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: type Foreign Server: test_server Pushdown: enabled Remote Select: ?type Remote Filter: ((?type = IRI("http://example.org/SomeType"))) (6 rows) /* ================================================================ * pg function pushdown (length, abs, round, ceil, floor, * substring, md5) * ================================================================ */ /* length */ EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE length(label) = 5 AND length(label) <> 1 AND length(label) > 1 AND length(label) < 99 AND length(label) >= 5 AND length(label) <= 5 AND length(label) BETWEEN 1 AND 99; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((STRLEN(?label) != 1) && (STRLEN(?label) > 1) && (STRLEN(?label) < 99) && (STRLEN(?label) >= 5) && (STRLEN(?label) <= 5) && (STRLEN(?label) >= 1) && (STRLEN(?label) <= 99) && (STRLEN(?label) = 5)) (6 rows) /* abs */ EXPLAIN (VERBOSE, COSTS OFF) SELECT version FROM pgtypes_ft WHERE abs(version) = 42 AND abs(version) > 10 AND abs(version) >= 42 AND abs(version) < 99 AND abs(version) <= 42 AND abs(version) BETWEEN 10 AND 100; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: version Foreign Server: test_server Pushdown: enabled Remote Select: ?version Remote Filter: ((ABS(?version) > 10) && (ABS(?version) >= 42) && (ABS(?version) < 99) && (ABS(?version) <= 42) && (ABS(?version) >= 10) && (ABS(?version) <= 100) && (ABS(?version) = 42)) (6 rows) /* round */ EXPLAIN (VERBOSE, COSTS OFF) SELECT num_numeric FROM pgtypes_ft WHERE round(num_numeric) = 2 AND round(num_numeric) > 1 AND round(num_numeric) >= 2 AND round(num_numeric) < 99 AND round(num_numeric) <= 2 AND round(num_numeric) BETWEEN 1 AND 99; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.pgtypes_ft Output: num_numeric Foreign Server: test_server Pushdown: enabled Remote Select: ?numeric Remote Filter: ((ROUND(?numeric) > 1) && (ROUND(?numeric) >= 2) && (ROUND(?numeric) < 99) && (ROUND(?numeric) <= 2) && (ROUND(?numeric) >= 1) && (ROUND(?numeric) <= 99) && (ROUND(?numeric) = 2)) (6 rows) /* ceil */ EXPLAIN (VERBOSE, COSTS OFF) SELECT num_numeric FROM pgtypes_ft WHERE ceil(num_numeric) = 2 AND ceil(num_numeric) > 1 AND ceil(num_numeric) >= 2 AND ceil(num_numeric) < 99 AND ceil(num_numeric) <= 2 AND ceil(num_numeric) BETWEEN 1 AND 99; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: num_numeric Foreign Server: test_server Pushdown: enabled Remote Select: ?numeric Remote Filter: ((CEIL(?numeric) > 1) && (CEIL(?numeric) >= 2) && (CEIL(?numeric) < 99) && (CEIL(?numeric) <= 2) && (CEIL(?numeric) >= 1) && (CEIL(?numeric) <= 99) && (CEIL(?numeric) = 2)) (6 rows) /* floor */ EXPLAIN (VERBOSE, COSTS OFF) SELECT num_numeric FROM pgtypes_ft WHERE floor(num_numeric) = 1 AND floor(num_numeric) > 0 AND floor(num_numeric) >= 1 AND floor(num_numeric) < 99 AND floor(num_numeric) <= 1 AND floor(num_numeric) BETWEEN 1 AND 99; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.pgtypes_ft Output: num_numeric Foreign Server: test_server Pushdown: enabled Remote Select: ?numeric Remote Filter: ((FLOOR(?numeric) > 0) && (FLOOR(?numeric) >= 1) && (FLOOR(?numeric) < 99) && (FLOOR(?numeric) <= 1) && (FLOOR(?numeric) >= 1) && (FLOOR(?numeric) <= 99) && (FLOOR(?numeric) = 1)) (6 rows) /* substring */ EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE substring(label, 1, 5) = 'hello'; QUERY PLAN ----------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((SUBSTR(?label, 1, 5) = "hello")) (6 rows) /* md5 */ EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE md5(label) = '5d41402abc4b2a76b9719d911017c592'; QUERY PLAN ----------------------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((MD5(?label) = "5d41402abc4b2a76b9719d911017c592")) (6 rows) /* ================================================================ * SPARQL 17.4.1.1 - BOUND * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.bound(p), sparql.bound(x) FROM rdfnode_opt_ft WHERE p = '' AND sparql.bound(o) AND NOT sparql.bound(x); QUERY PLAN --------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_opt_ft Output: p, o, sparql.bound(p), sparql.bound(x) Filter: (NOT sparql.bound(rdfnode_opt_ft.x)) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o ?x Remote Filter: ((?p = ) && (BOUND(?o))) (7 rows) /* ================================================================ * SPARQL 17.4.1.3 - COALESCE * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, x, sparql.coalesce(x, o, p) FROM rdfnode_opt_ft WHERE p = '' AND sparql.coalesce(x, x, p) = ''; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_opt_ft Output: p, o, x, sparql."coalesce"(VARIADIC ARRAY[x, o, p]) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o ?x Remote Filter: ((?p = ) && (COALESCE(?x, ?x, ?p) = )) (6 rows) /* ================================================================ * SPARQL 17.4.1.8 - sameTerm * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.sameterm(o, '"hello"@fr') FROM rdfnode_ft WHERE p = sparql.iri('http://www.w3.org/2000/01/rdf-schema#label') AND sparql.sameterm(p, ''); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.sameterm(o, '"hello"@fr'::rdfnode) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (SAMETERM(?p, ))) (6 rows) /* ================================================================ * SPARQL 17.4.2.1 - isIRI * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.isIRI(p), sparql.isIRI(o) FROM rdfnode_ft WHERE p = '' AND sparql.isIRI(p) AND NOT sparql.isIRI(o); QUERY PLAN ------------------------------------------------------------------------ Foreign Scan on public.rdfnode_ft Output: p, o, sparql.isiri(p), sparql.isiri(o) Filter: (NOT sparql.isiri(rdfnode_ft.o)) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (isIRI(?p))) (7 rows) /* ================================================================ * SPARQL 17.4.2.2 - isBlank * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.isblank(o) FROM rdfnode_ft WHERE sparql.isblank(o); QUERY PLAN ----------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.isblank(o) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((ISBLANK(?o))) (6 rows) /* ================================================================ * SPARQL 17.4.2.3 - isLiteral * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.isliteral(o), sparql.isliteral(p) FROM rdfnode_ft WHERE p = '' AND sparql.isliteral(o) AND NOT sparql.isliteral(p); QUERY PLAN ---------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.isliteral(o), sparql.isliteral(p) Filter: (NOT sparql.isliteral(rdfnode_ft.p)) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (ISLITERAL(?o))) (7 rows) /* ================================================================ * SPARQL 17.4.2.4 - isNumeric * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.isnumeric(o), sparql.isnumeric(p) FROM rdfnode_ft WHERE p = '' AND sparql.isnumeric(o) AND NOT sparql.isnumeric(p); QUERY PLAN ------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.isnumeric(o), sparql.isnumeric(p) Filter: (NOT sparql.isnumeric(rdfnode_ft.p)) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (ISNUMERIC(?o))) (7 rows) /* ================================================================ * SPARQL 17.4.2.5 - str * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.str(o) FROM rdfnode_ft WHERE p = '' AND sparql.str(o) = sparql.str('"hello"@en'); QUERY PLAN ----------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.str(o) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (STR(?o) = "hello")) (6 rows) /* ================================================================ * SPARQL 17.4.2.6 - lang * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.lang(o) FROM rdfnode_ft WHERE p = '' AND sparql.lang(o) = sparql.lang('"hello"@en'); QUERY PLAN --------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.lang(o) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (LANG(?o) = "en")) (6 rows) /* ================================================================ * SPARQL 17.4.2.7 - datatype * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.datatype(o) FROM rdfnode_ft WHERE p = '' AND sparql.datatype(o) = sparql.datatype('"42"^^'); QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.datatype(o) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (DATATYPE(?o) = )) (6 rows) /* ================================================================ * SPARQL 17.4.2.8 - IRI * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.iri(p) FROM rdfnode_ft WHERE sparql.iri(p) = sparql.iri('http://example.org/property') AND sparql.iri('http://example.org/property') = sparql.iri(p) AND p = sparql.iri('http://example.org/property'); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.iri(p) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((IRI(?p) = ) && ( = IRI(?p)) && (?p = )) (6 rows) /* ================================================================ * SPARQL 17.4.2.9 - BNODE * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.bnode(o) FROM rdfnode_ft WHERE p = '' AND sparql.isblank(sparql.bnode(o)); QUERY PLAN --------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.bnode(o) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (ISBLANK(BNODE(?o)))) (6 rows) /* ================================================================ * SPARQL 17.4.2.10 - STRDT * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.strdt(o, 'xsd:string') FROM rdfnode_ft WHERE p = sparql.iri('') AND '"42"^^xsd:string'::rdfnode = sparql.strdt(sparql.str(o), 'xsd:string') AND sparql.strdt(sparql.str(o), 'xsd:string') = '"42"^^xsd:string'::rdfnode AND sparql.strdt(sparql.str('"42"^^xsd:integer'), 'xsd:string') = sparql.strdt(sparql.str(o), 'xsd:string') AND sparql.strdt(sparql.str(o), 'xsd:string') = sparql.strdt(sparql.str('"42"^^xsd:integer'), 'xsd:string'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.strdt(o, '"xsd:string"'::rdfnode) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && ("42"^^ = STRDT(STR(?o), )) && (STRDT(STR(?o), ) = "42"^^) && ("42"^^ = STRDT(STR(?o), )) && (STRDT(STR(?o), ) = "42"^^)) (6 rows) /* ================================================================ * SPARQL 17.4.2.11 - STRLANG * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.strlang(o, 'en') FROM rdfnode_ft WHERE p = sparql.iri('http://www.w3.org/2000/01/rdf-schema#label') AND sparql.langmatches(sparql.lang(o), 'en') AND sparql.strlang(sparql.str(o), 'en') = sparql.strlang('"hello"', 'en') AND sparql.strlang('"hello"', 'en') = sparql.strlang(sparql.str(o), 'en') AND sparql.strlang('"hello"', 'en') = '"hello"@en' AND '"hello"@en' = sparql.strlang('"hello"', 'en'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.strlang(o, '"en"'::rdfnode) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (LANGMATCHES(LANG(?o), "en")) && (STRLANG(STR(?o), "en") = "hello"@en) && ("hello"@en = STRLANG(STR(?o), "en"))) (6 rows) /* ================================================================ * SPARQL 17.4.3.2 - STRLEN * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.strlen(o) FROM rdfnode_ft WHERE p = sparql.iri('http://www.w3.org/2000/01/rdf-schema#label') AND sparql.langmatches(sparql.lang(o), 'en') AND sparql.strlen(o) = sparql.strlen('"hello"@en') AND sparql.strlen(o) = 5 AND 5 = sparql.strlen(o); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.strlen(o) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (LANGMATCHES(LANG(?o), "en")) && (STRLEN(?o) = 5)) (6 rows) /* ================================================================ * SPARQL 17.4.3.3 - SUBSTR * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.substr(o, 1, 3) FROM rdfnode_ft WHERE p = sparql.iri('http://www.w3.org/2000/01/rdf-schema#label') AND sparql.langmatches(sparql.lang(o), 'en') AND sparql.substr(o, 1, 3) = sparql.substr('"hello"@en', 1, 3) AND sparql.substr('"hello"@en', 1, 3) = sparql.substr(o, 1, 3); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.substr(o, 1, 3) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (LANGMATCHES(LANG(?o), "en")) && (SUBSTR(?o, 1, 3) = "hel"@en) && ("hel"@en = SUBSTR(?o, 1, 3))) (6 rows) /* ================================================================ * SPARQL 17.4.3.4 - UCASE * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.ucase(o) FROM rdfnode_ft WHERE p = sparql.iri('http://www.w3.org/2000/01/rdf-schema#label') AND sparql.ucase(o) = sparql.ucase('"hello"@en') AND sparql.ucase(o) = '"HELLO"@en' AND '"HELLO"@en' = sparql.ucase(o); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.rdfnode_ft Output: p, o, sparql.ucase(o) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (UCASE(?o) = "HELLO"@en) && (UCASE(?o) = "HELLO"@en) && ("HELLO"@en = UCASE(?o))) (6 rows) /* ================================================================ * SPARQL 17.4.3.5 - LCASE * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.lcase(o) FROM rdfnode_ft WHERE p = sparql.iri('http://www.w3.org/2000/01/rdf-schema#label') AND sparql.lcase(o) = sparql.lcase('"HELLO"@en') AND sparql.lcase(o) = '"hello"@en' AND '"hello"@en' = sparql.lcase(o); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.rdfnode_ft Output: p, o, sparql.lcase(o) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (LCASE(?o) = "hello"@en) && (LCASE(?o) = "hello"@en) && ("hello"@en = LCASE(?o))) (6 rows) /* ================================================================ * SPARQL 17.4.3.6 - STRSTARTS * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.strstarts(o, sparql.str('"hel"@en')) FROM rdfnode_ft WHERE p = sparql.iri('http://www.w3.org/2000/01/rdf-schema#label') AND sparql.langmatches(sparql.lang(o), 'en') AND sparql.strstarts(o, '"hel"@en'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.strstarts(o, '"hel"'::rdfnode) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (LANGMATCHES(LANG(?o), "en")) && (STRSTARTS(?o, "hel"@en))) (6 rows) /* ================================================================ * SPARQL 17.4.3.7 - STRENDS * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.strends(o, sparql.str('"llo"@en')) FROM rdfnode_ft WHERE p = sparql.iri('http://www.w3.org/2000/01/rdf-schema#label') AND sparql.langmatches(sparql.lang(o), 'en') AND sparql.strends(o, '"llo"'); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.strends(o, '"llo"'::rdfnode) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (LANGMATCHES(LANG(?o), "en")) && (STRENDS(?o, "llo"))) (6 rows) /* ================================================================ * SPARQL 17.4.3.8 - CONTAINS * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.contains(o, '"ell"@en') FROM rdfnode_ft WHERE p = sparql.iri('http://www.w3.org/2000/01/rdf-schema#label') AND sparql.langmatches(sparql.lang(o), 'en') AND sparql.contains(o, '"ell"') AND sparql.contains(o, '"hel"'); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.contains(o, '"ell"@en'::rdfnode) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (LANGMATCHES(LANG(?o), "en")) && (CONTAINS(?o, "ell")) && (CONTAINS(?o, "hel"))) (6 rows) /* ================================================================ * SPARQL 17.4.3.9 - STRBEFORE * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.strbefore(sparql.str(o), '"llo"') FROM rdfnode_ft WHERE p = sparql.iri('http://www.w3.org/2000/01/rdf-schema#label') AND sparql.langmatches(sparql.lang(o), 'en') AND sparql.strbefore(sparql.str(o), '"llo"') = sparql.strbefore(sparql.str('"hello"@en'), '"llo"') AND sparql.strbefore(sparql.str(o), '"llo"') = '"he"' AND '"he"' = sparql.strbefore(sparql.str(o), '"llo"'); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.strbefore(sparql.str(o), '"llo"'::rdfnode) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (LANGMATCHES(LANG(?o), "en")) && (STRBEFORE(STR(?o), "llo") = "he") && (STRBEFORE(STR(?o), "llo") = "he") && ("he" = STRBEFORE(STR(?o), "llo"))) (6 rows) /* ================================================================ * SPARQL 17.4.3.10 - STRAFTER * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.strafter(sparql.str(o), '"hel"') FROM rdfnode_ft WHERE p = sparql.iri('http://www.w3.org/2000/01/rdf-schema#label') AND sparql.langmatches(sparql.lang(o), 'en') AND sparql.strafter(sparql.str(o), '"hel"') = sparql.strafter(sparql.str('"hello"@en'), '"hel"') AND sparql.strafter(sparql.str(o), '"hel"') = '"lo"'::rdfnode AND '"lo"' = sparql.strafter(sparql.str(o), '"hel"'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.rdfnode_ft Output: p, o, sparql.strafter(sparql.str(o), '"hel"'::rdfnode) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (LANGMATCHES(LANG(?o), "en")) && (STRAFTER(STR(?o), "hel") = "lo") && (STRAFTER(STR(?o), "hel") = "lo") && ("lo" = STRAFTER(STR(?o), "hel"))) (6 rows) /* ================================================================ * SPARQL 17.4.3.11 - ENCODE_FOR_URI * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.encode_for_uri(o) FROM rdfnode_ft WHERE p = sparql.iri('') AND sparql.langmatches(sparql.lang(o), 'en') AND sparql.encode_for_uri(o) = '"hello%20world"' AND '"hello%20world"' = sparql.encode_for_uri(o); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.encode_for_uri(o) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (LANGMATCHES(LANG(?o), "en")) && (ENCODE_FOR_URI(?o) = "hello%20world") && ("hello%20world" = ENCODE_FOR_URI(?o))) (6 rows) /* ================================================================ * SPARQL 17.4.3.12 - CONCAT * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.concat(o, sparql.strlang(' world', 'en')) FROM rdfnode_ft WHERE p = sparql.iri('http://www.w3.org/2000/01/rdf-schema#label') AND sparql.langmatches(sparql.lang(o), 'en') AND sparql.concat(o, '" world"') = sparql.concat('"hello"@en', '" world"') AND sparql.concat('"hello"@en', '" world"') = sparql.concat(o, '" world"'); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.concat(VARIADIC ARRAY[o, '" world"@en'::rdfnode]) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (LANGMATCHES(LANG(?o), "en")) && (CONCAT(?o, " world") = "hello world") && ("hello world" = CONCAT(?o, " world"))) (6 rows) /* ================================================================ * SPARQL 17.4.3.13 - langMatches * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.langmatches(sparql.lang(o), '*'), sparql.langmatches(sparql.lang(o), 'en') FROM rdfnode_ft WHERE sparql.langmatches(sparql.lang(o), 'en') ORDER BY p, o; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Sort Output: p, o, (sparql.langmatches(sparql.lang(o), '"*"'::rdfnode)), (sparql.langmatches(sparql.lang(o), '"en"'::rdfnode)) Sort Key: rdfnode_ft.p, rdfnode_ft.o -> Foreign Scan on public.rdfnode_ft Output: p, o, sparql.langmatches(sparql.lang(o), '"*"'::rdfnode), sparql.langmatches(sparql.lang(o), '"en"'::rdfnode) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((LANGMATCHES(LANG(?o), "en"))) Remote Sort Key: ASC (?p) ASC (?o) (10 rows) /* ================================================================ * SPARQL 17.4.3.15 - REPLACE * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.replace(o, 'hel', 'HEL') FROM rdfnode_ft WHERE p = sparql.iri('http://www.w3.org/2000/01/rdf-schema#label') AND sparql.langmatches(sparql.lang(o), 'en') AND sparql.replace(sparql.str(o), 'hel', 'HEL') = '"HELlo"'::rdfnode AND '"HELlo"' = sparql.replace(sparql.str(o), 'hel', 'HEL') AND sparql.replace(sparql.str(o), 'HEL', 'hel', 'i') = sparql.replace('"hello"', 'HEL', 'hel', 'i'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.replace(o, '"hel"'::rdfnode, '"HEL"'::rdfnode) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (LANGMATCHES(LANG(?o), "en")) && (REPLACE(STR(?o), "hel", "HEL") = "HELlo") && ("HELlo" = REPLACE(STR(?o), "hel", "HEL")) && (REPLACE(STR(?o), "HEL", "hel", "i") = "hello")) (6 rows) /* ================================================================ * SPARQL 17.4.4.1 - abs * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.abs(o) FROM rdfnode_ft WHERE p = ''::rdfnode AND sparql.abs(o) = 42::bigint AND sparql.abs(o) <> 99::bigint AND sparql.abs(o) >= 42::bigint AND sparql.abs(o) <= 42::bigint AND sparql.abs(o) BETWEEN 10::bigint AND 99::bigint AND sparql.abs(o) = '"42"^^xsd:long'::rdfnode AND sparql.abs(o) > '"10"^^xsd:long'::rdfnode AND sparql.abs(o) >= '"42"^^xsd:long'::rdfnode AND sparql.abs(o) < '"99"^^xsd:long'::rdfnode AND sparql.abs(o) <= '"42"^^xsd:long'::rdfnode AND 42::bigint = sparql.abs(o) AND '"42"^^xsd:long'::rdfnode = sparql.abs(o); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.abs(o) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (ABS(?o) = 42) && (ABS(?o) != 99) && (ABS(?o) >= 42) && (ABS(?o) <= 42) && (ABS(?o) >= 10) && (ABS(?o) <= 99) && (ABS(?o) = "42"^^) && (ABS(?o) > "10"^^) && (ABS(?o) >= "42"^^) && (ABS(?o) < "99"^^) && (ABS(?o) <= "42"^^) && (42 = ABS(?o)) && ("42"^^ = ABS(?o))) (6 rows) /* ================================================================ * SPARQL 17.4.4.2 - round * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.round(o) FROM rdfnode_ft WHERE p = ''::rdfnode AND sparql.round(o) = sparql.round(1.5) AND sparql.round(o) > 1.0 AND sparql.round(o) >= sparql.round(1.5) AND sparql.round(o) < 9.9 AND sparql.round(o) <= sparql.round(1.5) AND sparql.round(o) = '"2"^^xsd:decimal'::rdfnode AND sparql.round(o) > '"1"^^xsd:decimal'::rdfnode AND sparql.round(o) >= '"2"^^xsd:decimal'::rdfnode AND sparql.round(o) < '"9"^^xsd:decimal'::rdfnode AND sparql.round(o) <= '"2"^^xsd:decimal'::rdfnode AND sparql.round(1.5) = sparql.round(o) AND sparql.round('"1.5"^^xsd:decimal'::rdfnode) = sparql.round(o); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.round(o) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (ROUND(?o) = "2"^^) && (ROUND(?o) > 1.0) && (ROUND(?o) >= "2"^^) && (ROUND(?o) < 9.9) && (ROUND(?o) <= "2"^^) && (ROUND(?o) = "2"^^) && (ROUND(?o) > "1"^^) && (ROUND(?o) >= "2"^^) && (ROUND(?o) < "9"^^) && (ROUND(?o) <= "2"^^) && ("2"^^ = ROUND(?o)) && ("2"^^ = ROUND(?o))) (6 rows) /* ================================================================ * SPARQL 17.4.4.3 - ceil * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.ceil(o) FROM rdfnode_ft WHERE p = ''::rdfnode AND sparql.ceil(o) = sparql.ceil(1.5) AND sparql.ceil(o) > 1.0 AND sparql.ceil(o) >= sparql.ceil(1.5) AND sparql.ceil(o) < 9.9 AND sparql.ceil(o) <= sparql.ceil(1.5) AND sparql.ceil(o) = '"2"^^xsd:decimal'::rdfnode AND sparql.ceil(o) > '"1"^^xsd:decimal'::rdfnode AND sparql.ceil(o) >= '"2"^^xsd:decimal'::rdfnode AND sparql.ceil(o) < '"9"^^xsd:decimal'::rdfnode AND sparql.ceil(o) <= sparql.ceil('"1.5"^^xsd:decimal'::rdfnode) AND sparql.ceil(1.5) = sparql.ceil(o) AND sparql.ceil('"1.5"^^xsd:decimal'::rdfnode) = sparql.ceil(o); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.ceil(o) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (CEIL(?o) = "2"^^) && (CEIL(?o) > 1.0) && (CEIL(?o) >= "2"^^) && (CEIL(?o) < 9.9) && (CEIL(?o) <= "2"^^) && (CEIL(?o) = "2"^^) && (CEIL(?o) > "1"^^) && (CEIL(?o) >= "2"^^) && (CEIL(?o) < "9"^^) && (CEIL(?o) <= "2"^^) && ("2"^^ = CEIL(?o)) && ("2"^^ = CEIL(?o))) (6 rows) /* ================================================================ * SPARQL 17.4.4.4 - floor * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.floor(o) FROM rdfnode_ft WHERE p = ''::rdfnode AND sparql.floor(o) = sparql.floor(1.5) AND sparql.floor(o) > 1.0 AND sparql.floor(o) >= sparql.floor(1.5) AND sparql.floor(o) < 9.9 AND sparql.floor(o) <= sparql.floor(1.5) AND sparql.floor(o) = '"1"^^xsd:decimal'::rdfnode AND sparql.floor(o) > '"0"^^xsd:decimal'::rdfnode AND sparql.floor(o) >= '"1"^^xsd:decimal'::rdfnode AND sparql.floor(o) < '"9"^^xsd:decimal'::rdfnode AND sparql.floor(o) <= sparql.floor('"1.5"^^xsd:decimal'::rdfnode) AND sparql.floor(1.5) = sparql.floor(o) AND sparql.floor('"1.5"^^xsd:decimal'::rdfnode) = sparql.floor(o); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.floor(o) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (FLOOR(?o) = "1"^^) && (FLOOR(?o) > 1.0) && (FLOOR(?o) >= "1"^^) && (FLOOR(?o) < 9.9) && (FLOOR(?o) <= "1"^^) && (FLOOR(?o) = "1"^^) && (FLOOR(?o) > "0"^^) && (FLOOR(?o) >= "1"^^) && (FLOOR(?o) < "9"^^) && (FLOOR(?o) <= "1"^^) && ("1"^^ = FLOOR(?o)) && ("1"^^ = FLOOR(?o))) (6 rows) /* ================================================================ * SPARQL 17.4.5.2 - year * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.year(o) FROM rdfnode_ft WHERE p = sparql.iri('http://example.org/date') AND sparql.year(o) = 2015 AND sparql.year(o) > 2000 AND sparql.year(o) < 2020 AND sparql.year(o) >= 2015 AND sparql.year(o) <= 2015 AND sparql.year(o) = sparql.year('"2015-07-08T00:00:00Z"^^xsd:dateTime') AND sparql.year(o) > sparql.year('"2000-01-01T00:00:00Z"^^xsd:dateTime') AND sparql.year(o) < sparql.year('"2020-01-01T00:00:00Z"^^xsd:dateTime') AND sparql.year(o) >= sparql.year('"2015-07-08T00:00:00Z"^^xsd:dateTime') AND sparql.year(o) <= sparql.year('"2015-07-08T00:00:00Z"^^xsd:dateTime'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Result Output: p, o, sparql.year(o) One-Time Filter: (sparql.year('"2015-07-08T00:00:00Z"^^xsd:dateTime'::text) = 2015) -> Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (YEAR(?o) > 2000) && (YEAR(?o) < 2020) && (YEAR(?o) >= 2015) && (YEAR(?o) <= 2015) && (YEAR(?o) > YEAR("2000-01-01T00:00:00Z"^^)) && (YEAR(?o) < YEAR("2020-01-01T00:00:00Z"^^)) && (YEAR(?o) >= YEAR("2015-07-08T00:00:00Z"^^)) && (YEAR(?o) <= YEAR("2015-07-08T00:00:00Z"^^)) && (YEAR(?o) = 2015) && (YEAR("2015-07-08T00:00:00Z"^^) = 2015)) (9 rows) /* ================================================================ * SPARQL 17.4.5.3 - month * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.month(o) FROM rdfnode_ft WHERE p = sparql.iri('http://example.org/date') AND sparql.month(o) = 7 AND sparql.month(o) > 1 AND sparql.month(o) < 12 AND sparql.month(o) >= 7 AND sparql.month(o) <= 7 AND sparql.month(o) = sparql.month('"2015-07-08T00:00:00Z"^^xsd:dateTime') AND sparql.month(o) > sparql.month('"2015-01-08T00:00:00Z"^^xsd:dateTime') AND sparql.month(o) < sparql.month('"2015-12-08T00:00:00Z"^^xsd:dateTime') AND sparql.month(o) >= sparql.month('"2015-07-08T00:00:00Z"^^xsd:dateTime') AND sparql.month(o) <= sparql.month('"2015-07-08T00:00:00Z"^^xsd:dateTime'); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Result Output: p, o, sparql.month(o) One-Time Filter: (sparql.month('"2015-07-08T00:00:00Z"^^xsd:dateTime'::text) = 7) -> Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (MONTH(?o) > 1) && (MONTH(?o) < 12) && (MONTH(?o) >= 7) && (MONTH(?o) <= 7) && (MONTH(?o) > MONTH("2015-01-08T00:00:00Z"^^)) && (MONTH(?o) < MONTH("2015-12-08T00:00:00Z"^^)) && (MONTH(?o) >= MONTH("2015-07-08T00:00:00Z"^^)) && (MONTH(?o) <= MONTH("2015-07-08T00:00:00Z"^^)) && (MONTH(?o) = 7) && (MONTH("2015-07-08T00:00:00Z"^^) = 7)) (9 rows) /* ================================================================ * SPARQL 17.4.5.4 - day * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.day(o) FROM rdfnode_ft WHERE p = sparql.iri('http://example.org/date') AND sparql.day(o) = 8 AND sparql.day(o) > 1 AND sparql.day(o) < 30 AND sparql.day(o) >= 8 AND sparql.day(o) <= 8 AND sparql.day(o) = sparql.day('"2015-07-08T00:00:00Z"^^xsd:dateTime') AND sparql.day(o) > sparql.day('"2015-07-01T00:00:00Z"^^xsd:dateTime') AND sparql.day(o) < sparql.day('"2015-07-30T00:00:00Z"^^xsd:dateTime') AND sparql.day(o) >= sparql.day('"2015-07-08T00:00:00Z"^^xsd:dateTime') AND sparql.day(o) <= sparql.day('"2015-07-08T00:00:00Z"^^xsd:dateTime'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Result Output: p, o, sparql.day(o) One-Time Filter: (sparql.day('"2015-07-08T00:00:00Z"^^xsd:dateTime'::text) = 8) -> Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (DAY(?o) > 1) && (DAY(?o) < 30) && (DAY(?o) >= 8) && (DAY(?o) <= 8) && (DAY(?o) > DAY("2015-07-01T00:00:00Z"^^)) && (DAY(?o) < DAY("2015-07-30T00:00:00Z"^^)) && (DAY(?o) >= DAY("2015-07-08T00:00:00Z"^^)) && (DAY(?o) <= DAY("2015-07-08T00:00:00Z"^^)) && (DAY(?o) = 8) && (DAY("2015-07-08T00:00:00Z"^^) = 8)) (9 rows) /* ================================================================ * SPARQL 17.4.5.5 - hours * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.hours(o) FROM rdfnode_ft WHERE p = sparql.iri('http://example.org/date') AND sparql.hours(o) = 20 AND sparql.hours(o) > 0 AND sparql.hours(o) < 23 AND sparql.hours(o) >= 20 AND sparql.hours(o) <= 20 AND sparql.hours(o) = sparql.hours('"2015-07-08T20:41:25Z"^^xsd:dateTime') AND sparql.hours(o) < sparql.hours('"2015-07-08T23:00:00Z"^^xsd:dateTime') AND sparql.hours(o) >= sparql.hours('"2015-07-08T20:41:25Z"^^xsd:dateTime') AND sparql.hours(o) <= sparql.hours('"2015-07-08T20:41:25Z"^^xsd:dateTime'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Result Output: p, o, sparql.hours(o) One-Time Filter: (sparql.hours('"2015-07-08T20:41:25Z"^^xsd:dateTime'::text) = 20) -> Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (HOURS(?o) > 0) && (HOURS(?o) < 23) && (HOURS(?o) >= 20) && (HOURS(?o) <= 20) && (HOURS(?o) < HOURS("2015-07-08T23:00:00Z"^^)) && (HOURS(?o) >= HOURS("2015-07-08T20:41:25Z"^^)) && (HOURS(?o) <= HOURS("2015-07-08T20:41:25Z"^^)) && (HOURS(?o) = 20) && (HOURS("2015-07-08T20:41:25Z"^^) = 20)) (9 rows) /* ================================================================ * SPARQL 17.4.5.6 - minutes * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.minutes(o) FROM rdfnode_ft WHERE p = sparql.iri('http://example.org/date') AND sparql.minutes(o) = 41 AND sparql.minutes(o) > 0 AND sparql.minutes(o) < 59 AND sparql.minutes(o) >= 41 AND sparql.minutes(o) <= 41 AND sparql.minutes(o) = sparql.minutes('"2015-07-08T20:41:25Z"^^xsd:dateTime') AND sparql.minutes(o) < sparql.minutes('"2015-07-08T20:59:00Z"^^xsd:dateTime') AND sparql.minutes(o) >= sparql.minutes('"2015-07-08T20:41:25Z"^^xsd:dateTime') AND sparql.minutes(o) <= sparql.minutes('"2015-07-08T20:41:25Z"^^xsd:dateTime'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Result Output: p, o, sparql.minutes(o) One-Time Filter: (sparql.minutes('"2015-07-08T20:41:25Z"^^xsd:dateTime'::text) = 41) -> Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (MINUTES(?o) > 0) && (MINUTES(?o) < 59) && (MINUTES(?o) >= 41) && (MINUTES(?o) <= 41) && (MINUTES(?o) < MINUTES("2015-07-08T20:59:00Z"^^)) && (MINUTES(?o) >= MINUTES("2015-07-08T20:41:25Z"^^)) && (MINUTES(?o) <= MINUTES("2015-07-08T20:41:25Z"^^)) && (MINUTES(?o) = 41) && (MINUTES("2015-07-08T20:41:25Z"^^) = 41)) (9 rows) /* ================================================================ * SPARQL 17.4.5.7 - seconds * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.seconds(o) FROM rdfnode_ft WHERE p = sparql.iri('http://example.org/date') AND sparql.seconds(o) = 25 AND sparql.seconds(o) > 0 AND sparql.seconds(o) < 59 AND sparql.seconds(o) >= 25 AND sparql.seconds(o) <= 25 AND sparql.seconds(o) = sparql.seconds('"2015-07-08T20:41:25Z"^^xsd:dateTime') AND sparql.seconds(o) < sparql.seconds('"2015-07-08T20:41:59Z"^^xsd:dateTime') AND sparql.seconds(o) >= sparql.seconds('"2015-07-08T20:41:25Z"^^xsd:dateTime') AND sparql.seconds(o) <= sparql.seconds('"2015-07-08T20:41:25Z"^^xsd:dateTime'); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Result Output: p, o, sparql.seconds(o) One-Time Filter: ((sparql.seconds('"2015-07-08T20:41:25Z"^^xsd:dateTime'::text))::numeric = '25'::numeric) -> Foreign Scan on public.rdfnode_ft Output: p, o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (SECONDS(?o) > 0) && (SECONDS(?o) < 59) && (SECONDS(?o) >= 25) && (SECONDS(?o) <= 25) && (SECONDS(?o) < SECONDS("2015-07-08T20:41:59Z"^^)) && (SECONDS(?o) >= SECONDS("2015-07-08T20:41:25Z"^^)) && (SECONDS(?o) <= SECONDS("2015-07-08T20:41:25Z"^^)) && (SECONDS(?o) = 25) && (SECONDS("2015-07-08T20:41:25Z"^^) = 25)) (9 rows) /* ================================================================ * SPARQL 17.4.6.1 - MD5 * ================================================================ */ EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o, sparql.md5(o) FROM rdfnode_ft WHERE p = sparql.iri('http://www.w3.org/2000/01/rdf-schema#label') AND sparql.langmatches(sparql.lang(o), 'en') AND sparql.md5(o) = sparql.md5('"hello"@en'); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: p, o, sparql.md5(o) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (LANGMATCHES(LANG(?o), "en")) && (MD5(?o) = MD5("hello"@en))) (6 rows) /* ================================================================ * Non-pushable tables * (SPARQL query contains MINUS, UNION, LIMIT, ORDER BY, GROUP BY) * ================================================================ */ CREATE FOREIGN TABLE np_minus ( p rdfnode OPTIONS (variable '?p'), o rdfnode OPTIONS (variable '?o') ) SERVER test_server OPTIONS ( sparql 'SELECT * WHERE { ?p ?o MINUS { ?o}}'); EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM np_minus WHERE p = ''; QUERY PLAN ------------------------------------------------------------ Foreign Scan on public.np_minus Output: p, o Filter: (np_minus.p = ''::rdfnode) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o (6 rows) CREATE FOREIGN TABLE np_union ( p rdfnode OPTIONS (variable '?p'), o rdfnode OPTIONS (variable '?o') ) SERVER test_server OPTIONS ( sparql 'SELECT * WHERE {{ ?p ?o} UNION { ?p ?o}}'); EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM np_union WHERE p = ''; QUERY PLAN ------------------------------------------------------------ Foreign Scan on public.np_union Output: p, o Filter: (np_union.p = ''::rdfnode) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o (6 rows) CREATE FOREIGN TABLE np_limit ( p rdfnode OPTIONS (variable '?p'), o rdfnode OPTIONS (variable '?o') ) SERVER test_server OPTIONS ( sparql 'SELECT * WHERE { ?p ?o} LIMIT 10'); EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM np_limit WHERE p = ''; QUERY PLAN ------------------------------------------------------------ Foreign Scan on public.np_limit Output: p, o Filter: (np_limit.p = ''::rdfnode) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o (6 rows) CREATE FOREIGN TABLE np_orderby ( p rdfnode OPTIONS (variable '?p'), o rdfnode OPTIONS (variable '?o') ) SERVER test_server OPTIONS ( sparql 'SELECT * WHERE { ?p ?o} ORDER BY ?o'); EXPLAIN (VERBOSE, COSTS OFF) SELECT p, o FROM np_orderby WHERE p = ''; QUERY PLAN -------------------------------------------------------------- Foreign Scan on public.np_orderby Output: p, o Filter: (np_orderby.p = ''::rdfnode) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o (6 rows) CREATE FOREIGN TABLE np_groupby ( p rdfnode OPTIONS (variable '?p'), c int OPTIONS (variable '?c') ) SERVER test_server OPTIONS ( sparql 'SELECT ?p (COUNT(?o) AS ?c) WHERE { ?p ?o} GROUP BY ?p'); EXPLAIN (VERBOSE, COSTS OFF) SELECT p, c FROM np_groupby WHERE c > 1; QUERY PLAN ----------------------------------- Foreign Scan on public.np_groupby Output: p, c Filter: (np_groupby.c > 1) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?c (6 rows) DROP SERVER test_server CASCADE; NOTICE: drop cascades to 8 other objects DETAIL: drop cascades to foreign table rdfnode_ft drop cascades to foreign table rdfnode_opt_ft drop cascades to foreign table pgtypes_ft drop cascades to foreign table np_minus drop cascades to foreign table np_union drop cascades to foreign table np_limit drop cascades to foreign table np_orderby drop cascades to foreign table np_groupby