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) (11 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) (11 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) (11 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) (11 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) (11 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 (arithmetic between rdfnode and a PostgreSQL number). An integer constant reaches the endpoint as an xsd:integer and a numeric one as an xsd:decimal, which is what the operator here computes with, so the FILTER and the operator agree. */ EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o + 1 > '"10"^^xsd:integer'::rdfnode AND o * 2 < '"99"^^xsd:integer'::rdfnode AND 2 * o < '"99"^^xsd:integer'::rdfnode AND o + 1.5 > '"10"^^xsd:decimal'::rdfnode; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: (((?o + 1) > "10"^^) && ((?o * 2) < "99"^^) && ((2 * ?o) < "99"^^) && ((?o + 1.5) > "10"^^)) (6 rows) /* an arithmetic operand is parenthesised, whichever side the number is on */ EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE (o + 1) * 2 > '"10"^^xsd:integer'::rdfnode; QUERY PLAN ---------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: ((((?o + 1) * 2) > "10"^^)) (6 rows) /* a float constant is sent as the typed literal the operator converts it to: SPARQL reads a bare 2.5 as an xsd:decimal, and the endpoint would then compute in a different datatype than the operator. NaN and infinity have no bare spelling at all. */ EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o * 2.5::double precision > '"10"^^xsd:double'::rdfnode; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: (((?o * "2.5"^^) > "10"^^)) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o + 2.5::real > '"10"^^xsd:float'::rdfnode; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: (((?o + "2.5"^^) > "10"^^)) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE 'NaN'::double precision * o = o AND o + '-Infinity'::real < o; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: ((("NaN"^^ * ?o) = ?o) && ((?o + "-INF"^^) < ?o)) (6 rows) /* SPARQL has no FILTER form for - and /, so both stay local */ EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o - 1 > '"10"^^xsd:integer'::rdfnode; QUERY PLAN ---------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Filter: ((rdfnode_ft.o - 1) > '"10"^^'::rdfnode) Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: not pushable (7 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o / 2 > '"10"^^xsd:integer'::rdfnode; QUERY PLAN ---------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Filter: ((rdfnode_ft.o / 2) > '"10"^^'::rdfnode) Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: not pushable (7 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 Filter: ((rdfnode_ft.o = 'Thu Jan 01 00:00:00 2015'::timestamp without time zone) AND (rdfnode_ft.o <> 'Wed Jan 01 00:00:00 2020'::timestamp without time zone) AND (rdfnode_ft.o > 'Fri Jan 01 00:00:00 2010'::timestamp without time zone) AND (rdfnode_ft.o < 'Wed Jan 01 00:00:00 2020'::timestamp without time zone) AND (rdfnode_ft.o >= 'Thu Jan 01 00:00:00 2015'::timestamp without time zone) AND (rdfnode_ft.o <= 'Thu Jan 01 00:00:00 2015'::timestamp without time zone) AND (rdfnode_ft.o >= 'Fri Jan 01 00:00:00 2010'::timestamp without time zone) AND (rdfnode_ft.o <= 'Wed Jan 01 00:00:00 2020'::timestamp without time zone) AND ('Thu Jan 01 00:00:00 2015'::timestamp without time zone = rdfnode_ft.o)) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = )) (7 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 Filter: ((rdfnode_ft.o = 'Thu Jan 01 00:00:00 2015 UTC'::timestamp with time zone) AND (rdfnode_ft.o <> 'Wed Jan 01 00:00:00 2020 UTC'::timestamp with time zone) AND (rdfnode_ft.o > 'Fri Jan 01 00:00:00 2010 UTC'::timestamp with time zone) AND (rdfnode_ft.o < 'Wed Jan 01 00:00:00 2020 UTC'::timestamp with time zone) AND (rdfnode_ft.o >= 'Thu Jan 01 00:00:00 2015 UTC'::timestamp with time zone) AND (rdfnode_ft.o <= 'Thu Jan 01 00:00:00 2015 UTC'::timestamp with time zone) AND (rdfnode_ft.o >= 'Fri Jan 01 00:00:00 2010 UTC'::timestamp with time zone) AND (rdfnode_ft.o <= 'Wed Jan 01 00:00:00 2020 UTC'::timestamp with time zone) AND ('Thu Jan 01 00:00:00 2015 UTC'::timestamp with time zone = rdfnode_ft.o)) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = )) (7 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 Filter: (('01-01-2015'::date = sparql.strdt(sparql.substr(sparql.str(rdfnode_ft.o), 1, 10), '"xsd:date"'::rdfnode)) AND ('01-01-2015'::date >= sparql.strdt(sparql.substr(sparql.str(rdfnode_ft.o), 1, 10), '"xsd:date"'::rdfnode)) AND ('01-01-2020'::date > sparql.strdt(sparql.substr(sparql.str(rdfnode_ft.o), 1, 10), '"xsd:date"'::rdfnode))) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = )) (7 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 Filter: (('12:00:00+00'::time with time zone = sparql.strdt(sparql.substr(sparql.str(rdfnode_ft.o), 12, 8), '"xsd:time"'::rdfnode)) AND ('12:00:00+00'::time with time zone >= sparql.strdt(sparql.substr(sparql.str(rdfnode_ft.o), 12, 8), '"xsd:time"'::rdfnode)) AND ('23:00:00+00'::time with time zone > sparql.strdt(sparql.substr(sparql.str(rdfnode_ft.o), 12, 8), '"xsd:time"'::rdfnode))) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = )) (7 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) /* * A literal whose datatype is outside the operator table of SPARQL 17.3 falls * to RDFterm-equal (17.4.1.7), which raises a type error for two literals that * are not the same term. A FILTER drops the row an error comes from, so * '?o != C' keeps nothing at the endpoint while the operator here keeps every * term that is not C: '!=' against such a literal stays local. '=' does not * have to, since the endpoint's TRUE and type error select the same rows as * the operator's true and false. */ EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o <> '"http://a"^^xsd:anyURI'::rdfnode; QUERY PLAN ---------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Filter: (rdfnode_ft.o <> '"http://a"^^'::rdfnode) Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: not pushable (7 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o != '"a"^^'::rdfnode; QUERY PLAN ---------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Filter: (rdfnode_ft.o <> '"a"^^'::rdfnode) Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: not pushable (7 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE '"http://a"^^xsd:anyURI'::rdfnode <> o; QUERY PLAN ---------------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Filter: ('"http://a"^^'::rdfnode <> rdfnode_ft.o) Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: not pushable (7 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o = '"http://a"^^xsd:anyURI'::rdfnode; QUERY PLAN --------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: ((?o = "http://a"^^)) (6 rows) /* the datatypes the table does cover are unaffected, and so is a language-tagged literal, which RDFterm-equal answers FALSE for rather than raising, and an IRI */ EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o <> '"a"'::rdfnode; QUERY PLAN ----------------------------------- Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: ((?o != "a")) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o <> '"a"^^xsd:string'::rdfnode; QUERY PLAN --------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: ((?o != "a"^^)) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o <> '"1"^^xsd:integer'::rdfnode; QUERY PLAN ---------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: ((?o != "1"^^)) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o <> '"a"@en'::rdfnode; QUERY PLAN ----------------------------------- Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: ((?o != "a"@en)) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o <> ''::rdfnode; QUERY PLAN --------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: ((?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'); WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Filter: ((pgtypes_ft.label = ANY ('{hello,world}'::text[])) AND (pgtypes_ft.label <> ALL ('{foo,bar}'::text[]))) Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((STR(?label) != "foo") && (STR(?label) = "hello")) (7 rows) /* ---------------------------------------------------------------- * LIKE / NOT LIKE -> REGEX / !REGEX * * A LIKE pattern and a regular expression do not mean the same thing, * so CreateRegexString() has to translate rather than copy. These pin * the translation; the rows they would match are not the point. * ---------------------------------------------------------------- */ /* the whole pattern is anchored, whichever wildcard sits at either end: * LIKE matches the entire value, REGEX matches anywhere by default */ EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label LIKE 'foo'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ----------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((REGEX(STR(?label),"^foo$","s"))) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label LIKE '%foo%'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN --------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((REGEX(STR(?label),"^.*foo.*$","s"))) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label LIKE '_foo'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------------------------ Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((REGEX(STR(?label),"^.foo$","s"))) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label LIKE 'foo_'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------------------------ Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((REGEX(STR(?label),"^foo.$","s"))) (6 rows) /* a pattern that opens or closes with a regex anchor is still ordinary * text to LIKE, so the anchor is escaped and the pattern anchored around it */ EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label LIKE '^foo'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN -------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((REGEX(STR(?label),"^\\^foo$","s"))) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label LIKE 'foo$'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN -------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((REGEX(STR(?label),"^foo\\$$","s"))) (6 rows) /* regex metacharacters are escaped */ EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label LIKE 'a.b'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((REGEX(STR(?label),"^a\\.b$","s"))) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label LIKE 'a(b)c'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ----------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((REGEX(STR(?label),"^a\\(b\\)c$","s"))) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label LIKE 'a|b'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((REGEX(STR(?label),"^a\\|b$","s"))) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label LIKE 'C++'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN --------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((REGEX(STR(?label),"^C\\+\\+$","s"))) (6 rows) /* characters that are not metacharacters are left alone: XML Schema * regular expressions reject an escape that has no meaning */ EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label LIKE 'a-b'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ----------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((REGEX(STR(?label),"^a-b$","s"))) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label LIKE 'a/b'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ----------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((REGEX(STR(?label),"^a/b$","s"))) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label LIKE 'a:b'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ----------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((REGEX(STR(?label),"^a:b$","s"))) (6 rows) /* a backslash escapes the wildcard that follows it, which then stands for * itself rather than becoming ".*" or "." */ EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label LIKE 'a\%b'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ----------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((REGEX(STR(?label),"^a%b$","s"))) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label LIKE 'a\_b'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ----------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((REGEX(STR(?label),"^a_b$","s"))) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label LIKE 'a\\b'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN -------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((REGEX(STR(?label),"^a\\\\b$","s"))) (6 rows) /* and a pattern may not end with one */ SELECT label FROM pgtypes_ft WHERE label LIKE 'ab\'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. ERROR: LIKE pattern must not end with escape character /* a double quote would close the SPARQL string literal, and a control * character cannot appear in one at all */ EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label LIKE 'x"y'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------------------------ Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((REGEX(STR(?label),"^x\"y$","s"))) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label LIKE E'a\nb\tc\rd'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------------------------------ Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((REGEX(STR(?label),"^a\nb\tc\rd$","s"))) (6 rows) /* NOT LIKE negates the whole match */ EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label NOT LIKE '%foo%'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ---------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((!REGEX(STR(?label),"^.*foo.*$","s"))) (6 rows) /* ILIKE is not pushed down. SPARQL's "i" flag folds case by Unicode rule, * while ILIKE follows the database collation, and the two disagree: under a * Turkish collation 'Istanbul' ILIKE 'i%' is false, where REGEX with "i" * matches. */ EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label ILIKE '%foo%'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------------------ Foreign Scan on public.pgtypes_ft Output: label Filter: (pgtypes_ft.label ~~* '%foo%'::text) Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: not pushable (7 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label NOT ILIKE '%foo%'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Filter: (pgtypes_ft.label !~~* '%foo%'::text) Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: not pushable (7 rows) /* the pattern has to be a constant and the value a plain column, or the * REGEX would be built from something other than what is being compared */ EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label LIKE label; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN -------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Filter: (pgtypes_ft.label ~~ pgtypes_ft.label) Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: not pushable (7 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE upper(label) LIKE '%foo%'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------------------------ Foreign Scan on public.pgtypes_ft Output: label Filter: (upper(pgtypes_ft.label) ~~ '%foo%'::text) Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: not pushable (7 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); WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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'); WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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:00"^^) && (?ttz > "10:00:00+00:00"^^) && (?ttz < "23:00:00+00:00"^^) && (?ttz >= "12:00:00+00:00"^^) && (?ttz <= "12:00:00+00:00"^^) && (?ttz >= "10:00:00+00:00"^^) && (?ttz <= "14:00:00+00:00"^^) && (?ttz = "12:00: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; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: bl Filter: (pgtypes_ft.bl IS NOT FALSE) Foreign Server: test_server Pushdown: enabled Remote Select: ?bl Remote Filter: ((?bl = "true"^^)) (7 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT bl FROM pgtypes_ft WHERE bl IS false AND bl IS NOT true; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN -------------------------------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: bl Filter: (pgtypes_ft.bl IS NOT TRUE) Foreign Server: test_server Pushdown: enabled Remote Select: ?bl Remote Filter: ((?bl = "false"^^)) (7 rows) /* boolean: = / <> (NOT pushable) */ EXPLAIN (VERBOSE, COSTS OFF) SELECT bl FROM pgtypes_ft WHERE bl = true AND bl <> false; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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) /* iri column: the value is written into a SPARQL string, so a '"' in it must be escaped rather than ending the string and letting the rest be read as query text. The constant on the right and the constant on the left are two separate branches of the deparser, so both are covered. */ EXPLAIN (VERBOSE, COSTS OFF) SELECT type FROM pgtypes_ft WHERE type = 'http://example.org/a") || isLiteral("x'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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/a\") || isLiteral(\"x"))) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT type FROM pgtypes_ft WHERE 'http://example.org/a") || isLiteral("x' = type; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------------------------------------------------ Foreign Scan on public.pgtypes_ft Output: type Foreign Server: test_server Pushdown: enabled Remote Select: ?type Remote Filter: ((IRI("http://example.org/a\") || isLiteral(\"x") = ?type)) (6 rows) /* a backslash already in the value must not be joined to the escape added for the quote that follows it */ EXPLAIN (VERBOSE, COSTS OFF) SELECT type FROM pgtypes_ft WHERE type = 'http://example.org/a\") || isLiteral("x'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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/a\\\") || isLiteral(\"x"))) (6 rows) /* a newline cannot stand inside a SPARQL string literal either */ EXPLAIN (VERBOSE, COSTS OFF) SELECT type FROM pgtypes_ft WHERE type = E'http://example.org/a\nb'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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/a\nb"))) (6 rows) /* The escaping happens once, where the constant is read, so every other column option gets it too: a line break in the value used to reach the endpoint as a line break, and the request was refused. */ EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label = E'a\nb'; -- language '*' WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((STR(?label) = "a\nb")) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label = E'a\tb'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((STR(?label) = "a\tb")) (6 rows) /* the literaltype branch needs a text column carrying the option */ CREATE FOREIGN TABLE escaping_ft ( typed text OPTIONS (variable '?typed', literaltype 'xsd:string'), tagged text OPTIONS (variable '?tagged', language 'en'), plain text OPTIONS (variable '?plain') ) SERVER test_server OPTIONS ( sparql 'SELECT * WHERE { ?p ?o}'); EXPLAIN (VERBOSE, COSTS OFF) SELECT typed FROM escaping_ft WHERE typed = E'a\nb'; WARNING: the rdf_fdw FOREIGN TABLE "escaping_ft" has columns using native PostgreSQL types which are deprecated: typed, tagged, plain HINT: Use the "rdfnode" type instead. QUERY PLAN --------------------------------------------------------------------------------- Foreign Scan on public.escaping_ft Output: typed Foreign Server: test_server Pushdown: enabled Remote Select: ?typed Remote Filter: ((?typed = "a\nb"^^)) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT tagged FROM escaping_ft WHERE tagged = E'a\nb'; WARNING: the rdf_fdw FOREIGN TABLE "escaping_ft" has columns using native PostgreSQL types which are deprecated: typed, tagged, plain HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------------ Foreign Scan on public.escaping_ft Output: tagged Foreign Server: test_server Pushdown: enabled Remote Select: ?tagged Remote Filter: ((?tagged = "a\nb"@en)) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT plain FROM escaping_ft WHERE plain = E'a\nb'; WARNING: the rdf_fdw FOREIGN TABLE "escaping_ft" has columns using native PostgreSQL types which are deprecated: typed, tagged, plain HINT: Use the "rdfnode" type instead. QUERY PLAN -------------------------------------- Foreign Scan on public.escaping_ft Output: plain Foreign Server: test_server Pushdown: enabled Remote Select: ?plain Remote Filter: ((?plain = "a\nb")) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT plain FROM escaping_ft WHERE plain = 'a\nb'; WARNING: the rdf_fdw FOREIGN TABLE "escaping_ft" has columns using native PostgreSQL types which are deprecated: typed, tagged, plain HINT: Use the "rdfnode" type instead. QUERY PLAN --------------------------------------- Foreign Scan on public.escaping_ft Output: plain Foreign Server: test_server Pushdown: enabled Remote Select: ?plain Remote Filter: ((?plain = "a\\nb")) (6 rows) /* A value may contain '@' and '^^'. A native constant carries no annotation, and an rdfnode's annotation is what follows its closing quote: these used to be pushed down as "x"@en, "user"@en and "a"@en. */ EXPLAIN (VERBOSE, COSTS OFF) SELECT tagged FROM escaping_ft WHERE tagged = 'x^^y'; WARNING: the rdf_fdw FOREIGN TABLE "escaping_ft" has columns using native PostgreSQL types which are deprecated: typed, tagged, plain HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------------ Foreign Scan on public.escaping_ft Output: tagged Foreign Server: test_server Pushdown: enabled Remote Select: ?tagged Remote Filter: ((?tagged = "x^^y"@en)) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT tagged FROM escaping_ft WHERE tagged = 'user@host'; WARNING: the rdf_fdw FOREIGN TABLE "escaping_ft" has columns using native PostgreSQL types which are deprecated: typed, tagged, plain HINT: Use the "rdfnode" type instead. QUERY PLAN ----------------------------------------------- Foreign Scan on public.escaping_ft Output: tagged Foreign Server: test_server Pushdown: enabled Remote Select: ?tagged Remote Filter: ((?tagged = "user@host"@en)) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o = '"a@b"@en'::rdfnode; QUERY PLAN ------------------------------------ Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: ((?o = "a@b"@en)) (6 rows) /* A backslash in a native datum is a backslash, not the start of an escape. It used to be handed over as written, so "a\nb" arrived at the endpoint meaning a line break. */ EXPLAIN (VERBOSE, COSTS OFF) SELECT label FROM pgtypes_ft WHERE label = 'a\nb'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN -------------------------------------------- Foreign Scan on public.pgtypes_ft Output: label Foreign Server: test_server Pushdown: enabled Remote Select: ?label Remote Filter: ((STR(?label) = "a\\nb")) (6 rows) /* An rdfnode's lexical form already carries its escapes, so they must not be escaped a second time: both of these mean one line break, not a backslash followed by an n. */ EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o = '"a\nb"@en'::rdfnode; QUERY PLAN ------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: ((?o = "a\nb"@en)) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o = (E'"a\nb"@en')::rdfnode; QUERY PLAN ------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: ((?o = "a\nb"@en)) (6 rows) /* and a plain rdfnode constant carrying a raw tab used to be wrapped twice, so the term became the string '"ab"', quotes included */ EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o = (E'a\tb')::rdfnode; QUERY PLAN ----------------------------------- Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: ((?o = "a\tb")) (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; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.pgtypes_ft Output: num_numeric Filter: ((round(pgtypes_ft.num_numeric, 0) > '1'::numeric) AND (round(pgtypes_ft.num_numeric, 0) >= '2'::numeric) AND (round(pgtypes_ft.num_numeric, 0) < '99'::numeric) AND (round(pgtypes_ft.num_numeric, 0) <= '2'::numeric) AND (round(pgtypes_ft.num_numeric, 0) >= '1'::numeric) AND (round(pgtypes_ft.num_numeric, 0) <= '99'::numeric) AND (round(pgtypes_ft.num_numeric, 0) = '2'::numeric)) Foreign Server: test_server Pushdown: enabled Remote Select: ?numeric Remote Filter: not pushable (7 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; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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'; WARNING: the rdf_fdw FOREIGN TABLE "pgtypes_ft" has columns using native PostgreSQL types which are deprecated: label, version, num_smallint, num_int, num_real, num_double, num_numeric, modified, tstz, dt, ttz, bl, type HINT: Use the "rdfnode" type instead. 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) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o ?x Remote Filter: ((?p = ) && (BOUND(?o)) && ((!BOUND(?x)))) (6 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) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (isIRI(?p)) && ((!isIRI(?o)))) (6 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) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (isLITERAL(?o)) && ((!isLITERAL(?p)))) (6 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) Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((?p = ) && (ISNUMERIC(?o)) && ((!ISNUMERIC(?p)))) (6 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') AND sparql.strstarts(o, '"日本"') AND sparql.strstarts(o, '"🐘"'); 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)) && (STRSTARTS(?o, "日本")) && (STRSTARTS(?o, "🐘"))) (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"') AND sparql.strends(o, '"日本"') AND sparql.strends(o, '"🐘"'); 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")) && (STRENDS(?o, "日本")) && (STRENDS(?o, "🐘"))) (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) = '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: unsupported SPARQL (5 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: unsupported SPARQL (5 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: unsupported SPARQL (5 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: unsupported SPARQL (5 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; WARNING: the rdf_fdw FOREIGN TABLE "np_groupby" has columns using native PostgreSQL types which are deprecated: c HINT: Use the "rdfnode" type instead. QUERY PLAN ----------------------------------- Foreign Scan on public.np_groupby Output: p, c Filter: (np_groupby.c > 1) Foreign Server: test_server Pushdown: unsupported SPARQL (5 rows) /* ================================================================ * Non-pushable tables * (the supplied query says something a rewrite would not preserve) * ================================================================ */ /* * A rewrite replaces the supplied SELECT clause with one of its own and * appends its own solution modifiers. That is only faithful where the clause * says nothing but which variables to project: a DISTINCT, a REDUCED or an * expression alias is part of what the query asks for and would be dropped, * and a BASE decides what the relative IRIs beneath it mean. A projection that * does not carry a mapped column's variable cannot be rewritten to one that * does, either - the variable is not bound. Such a query is sent as it was * written and everything is applied locally. */ CREATE FOREIGN TABLE np_distinct ( p rdfnode OPTIONS (variable '?p') ) SERVER test_server OPTIONS ( sparql 'SELECT DISTINCT ?p WHERE { ?p ?o}'); EXPLAIN (VERBOSE, COSTS OFF) SELECT p FROM np_distinct WHERE p = ''; QUERY PLAN --------------------------------------------------------------- Foreign Scan on public.np_distinct Output: p Filter: (np_distinct.p = ''::rdfnode) Foreign Server: test_server Pushdown: unsupported SPARQL (5 rows) CREATE FOREIGN TABLE np_reduced ( p rdfnode OPTIONS (variable '?p') ) SERVER test_server OPTIONS ( sparql 'SELECT REDUCED ?p WHERE { ?p ?o}'); EXPLAIN (VERBOSE, COSTS OFF) SELECT p FROM np_reduced WHERE p = ''; QUERY PLAN -------------------------------------------------------------- Foreign Scan on public.np_reduced Output: p Filter: (np_reduced.p = ''::rdfnode) Foreign Server: test_server Pushdown: unsupported SPARQL (5 rows) CREATE FOREIGN TABLE np_alias ( p rdfnode OPTIONS (variable '?p') ) SERVER test_server OPTIONS ( sparql 'SELECT (?o AS ?p) WHERE { ?x ?o}'); EXPLAIN (VERBOSE, COSTS OFF) SELECT p FROM np_alias WHERE p = ''; QUERY PLAN ------------------------------------------------------------ Foreign Scan on public.np_alias Output: p Filter: (np_alias.p = ''::rdfnode) Foreign Server: test_server Pushdown: unsupported SPARQL (5 rows) CREATE FOREIGN TABLE np_base ( p rdfnode OPTIONS (variable '?p') ) SERVER test_server OPTIONS ( sparql 'BASE SELECT ?p WHERE { ?p ?o}'); EXPLAIN (VERBOSE, COSTS OFF) SELECT p FROM np_base WHERE p = ''; QUERY PLAN ----------------------------------------------------------- Foreign Scan on public.np_base Output: p Filter: (np_base.p = ''::rdfnode) Foreign Server: test_server Pushdown: unsupported SPARQL (5 rows) /* the projection does not bind the variable the column is mapped to */ CREATE FOREIGN TABLE np_unprojected ( p rdfnode OPTIONS (variable '?p') ) SERVER test_server OPTIONS ( sparql 'SELECT ?s WHERE {?s ?p ?o}'); EXPLAIN (VERBOSE, COSTS OFF) SELECT p FROM np_unprojected WHERE p = ''; QUERY PLAN ------------------------------------------------------------------ Foreign Scan on public.np_unprojected Output: p Filter: (np_unprojected.p = ''::rdfnode) Foreign Server: test_server Pushdown: unsupported SPARQL (5 rows) /* * A keyword written inside a string is not a keyword, whichever of SPARQL's * four quotings the string uses, and neither is one inside an IRI or a * comment. A query carrying one is still rewritten: the text is read in order * and what is not query text is stepped over, rather than the quoting being * inferred from how many double quotes came before. */ CREATE FOREIGN TABLE kw_dquote ( p rdfnode OPTIONS (variable '?p'), o rdfnode OPTIONS (variable '?o') ) SERVER test_server OPTIONS ( sparql 'SELECT ?p ?o WHERE {?s ?p ?o FILTER(?o != " SELECT ")}'); EXPLAIN (VERBOSE, COSTS OFF) SELECT p FROM kw_dquote WHERE p = ''; QUERY PLAN -------------------------------------------------- Foreign Scan on public.kw_dquote Output: p Foreign Server: test_server Pushdown: enabled Remote Select: ?p Remote Filter: ((?p = )) (6 rows) CREATE FOREIGN TABLE kw_squote ( p rdfnode OPTIONS (variable '?p'), o rdfnode OPTIONS (variable '?o') ) SERVER test_server OPTIONS ( sparql $$SELECT ?p ?o WHERE {?s ?p ?o FILTER(?o != ' SELECT ')}$$); EXPLAIN (VERBOSE, COSTS OFF) SELECT p FROM kw_squote WHERE p = ''; QUERY PLAN -------------------------------------------------- Foreign Scan on public.kw_squote Output: p Foreign Server: test_server Pushdown: enabled Remote Select: ?p Remote Filter: ((?p = )) (6 rows) CREATE FOREIGN TABLE kw_tquote ( p rdfnode OPTIONS (variable '?p'), o rdfnode OPTIONS (variable '?o') ) SERVER test_server OPTIONS ( sparql 'SELECT ?p ?o WHERE {?s ?p ?o FILTER(?o != """ SELECT """)}'); EXPLAIN (VERBOSE, COSTS OFF) SELECT p FROM kw_tquote WHERE p = ''; QUERY PLAN -------------------------------------------------- Foreign Scan on public.kw_tquote Output: p Foreign Server: test_server Pushdown: enabled Remote Select: ?p Remote Filter: ((?p = )) (6 rows) /* a real second SELECT is still a subquery, and still stops the rewrite */ CREATE FOREIGN TABLE kw_subselect ( p rdfnode OPTIONS (variable '?p'), o rdfnode OPTIONS (variable '?o') ) SERVER test_server OPTIONS ( sparql 'SELECT ?p ?o WHERE { SELECT ?p ?o WHERE {?s ?p ?o} }'); EXPLAIN (VERBOSE, COSTS OFF) SELECT p FROM kw_subselect WHERE p = ''; QUERY PLAN ---------------------------------------------------------------- Foreign Scan on public.kw_subselect Output: p Filter: (kw_subselect.p = ''::rdfnode) Foreign Server: test_server Pushdown: unsupported SPARQL (5 rows) /* a plain projection naming the mapped variable is still rewritten */ CREATE FOREIGN TABLE np_plain ( p rdfnode OPTIONS (variable '?p') ) SERVER test_server OPTIONS ( sparql 'SELECT ?p WHERE { ?p ?o}'); EXPLAIN (VERBOSE, COSTS OFF) SELECT p FROM np_plain WHERE p = ''; QUERY PLAN -------------------------------------------------- Foreign Scan on public.np_plain Output: p Foreign Server: test_server Pushdown: enabled Remote Select: ?p Remote Filter: ((?p = )) (6 rows) /* ================================================================ * Arithmetic grouping * ================================================================ */ /* The shape of the expression tree, not the textual order of the operators, * decides what the filter means. SPARQL applies its own precedence to whatever * it is handed, so an expression whose grouping departs from that precedence * has to be parenthesised on the way out: (n + 1) * 2 and n + 1 * 2 select * different rows, and both are written with the same three operands. */ CREATE FOREIGN TABLE arith_ft ( n int OPTIONS (variable '?n'), o rdfnode OPTIONS (variable '?o') ) SERVER test_server OPTIONS ( sparql 'SELECT ?n ?o WHERE { ?p ?o}'); EXPLAIN (VERBOSE, COSTS OFF) SELECT n FROM arith_ft WHERE (n + 1) * 2 = 10; WARNING: the rdf_fdw FOREIGN TABLE "arith_ft" has columns using native PostgreSQL types which are deprecated: n HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------------ Foreign Scan on public.arith_ft Output: n Foreign Server: test_server Pushdown: enabled Remote Select: ?n Remote Filter: ((((?n + 1) * 2) = 10)) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT n FROM arith_ft WHERE n + 1 * 2 = 10; WARNING: the rdf_fdw FOREIGN TABLE "arith_ft" has columns using native PostgreSQL types which are deprecated: n HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------ Foreign Scan on public.arith_ft Output: n Foreign Server: test_server Pushdown: enabled Remote Select: ?n Remote Filter: (((?n + 2) = 10)) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT n FROM arith_ft WHERE (n + 2) * (n + 3) = 20; WARNING: the rdf_fdw FOREIGN TABLE "arith_ft" has columns using native PostgreSQL types which are deprecated: n HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------------------- Foreign Scan on public.arith_ft Output: n Foreign Server: test_server Pushdown: enabled Remote Select: ?n Remote Filter: ((((?n + 2) * (?n + 3)) = 20)) (6 rows) /* ================================================================ * Array comparisons that cannot be translated * ================================================================ */ /* SQL's IN and NOT IN give NULL its own truth table, which SPARQL has no term * for, and an array built at run time rather than folded to a constant carries * elements this deparser renders as bare identifiers. Neither can be sent, so * both stay with the executor -- a remote filter here would be a syntax error * at the endpoint rather than a wrong answer. */ EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o IN ('"hello"', NULL); QUERY PLAN ------------------------------------------------------------------ Foreign Scan on public.rdfnode_ft Output: o Filter: (rdfnode_ft.o = ANY ('{"\"hello\"",NULL}'::rdfnode[])) Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: not pushable (7 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o = ANY(ARRAY[o, '"hello"'::rdfnode]); QUERY PLAN -------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Filter: (rdfnode_ft.o = ANY (ARRAY[rdfnode_ft.o, '"hello"'::rdfnode])) Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: not pushable (7 rows) /* a constant list of ordinary values is still pushed down */ EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o IN ('"hello"', '"world"'); QUERY PLAN ----------------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: ((?o IN ("hello", "world"))) (6 rows) /* ================================================================ * LIMIT pushdown boundaries * ================================================================ */ /* A remote LIMIT decides which rows the endpoint sends, so it may only be used * where that is also the set the query wants. A sort is the case where it is * not: SPARQL orders RDF terms by value, while PostgreSQL orders rdfnode by its * stored representation, so the two disagree on ordinary data -- "9" sorts * after "10" here and before it there. Whichever rows the endpoint kept, the * local sort cannot recover the ones it dropped. */ EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft ORDER BY o LIMIT 3; QUERY PLAN ----------------------------------------------- Limit Output: o -> Sort Output: o Sort Key: rdfnode_ft.o -> Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Sort Key: ASC (?o) (11 rows) /* the same holds when the sort cannot be pushed at all, where a remote limit * would hand back an arbitrary three rows for the executor to sort */ EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft ORDER BY o::text || 'x' LIMIT 3; QUERY PLAN --------------------------------------------------------- Limit Output: o, (((o)::text || 'x'::text)) -> Sort Output: o, (((o)::text || 'x'::text)) Sort Key: (((rdfnode_ft.o)::text || 'x'::text)) -> Foreign Scan on public.rdfnode_ft Output: o, ((o)::text || 'x'::text) Foreign Server: test_server Pushdown: enabled Remote Select: ?o (10 rows) /* a join decides which rows survive after the scan, so the limit cannot be * applied to either side on its own */ EXPLAIN (VERBOSE, COSTS OFF) SELECT a.o FROM rdfnode_ft a, rdfnode_ft b WHERE a.o = b.o LIMIT 3; QUERY PLAN ------------------------------------------------------- Limit Output: a.o -> Nested Loop Output: a.o Join Filter: (a.o = b.o) -> Foreign Scan on public.rdfnode_ft a Output: a.p, a.o Foreign Server: test_server Pushdown: enabled Remote Select: ?o -> Materialize Output: b.o -> Foreign Scan on public.rdfnode_ft b Output: b.o Foreign Server: test_server Pushdown: enabled Remote Select: ?o (17 rows) /* a plain limit on a single scan is still pushed down, and OFFSET is added to * it in 64-bit arithmetic -- the sum of two values that each fit in an int32 * need not */ EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft LIMIT 3; QUERY PLAN ----------------------------------------- Limit Output: o -> Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Limit: LIMIT 3 (8 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft OFFSET 3000000000 LIMIT 10; QUERY PLAN ----------------------------------------- Limit Output: o -> Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Limit: LIMIT 3000000010 (8 rows) /* ================================================================ * Function identity and casts * ================================================================ */ /* Recognising a function by name alone means a user's own function is shipped * as the SPARQL builtin that happens to share its name, and never runs. The * one below returns false for every row; sent as SPARQL CONTAINS it would * return whatever the endpoint decides. PL/pgSQL rather than SQL so that the * planner cannot inline it away before the deparser sees it. */ CREATE FUNCTION contains(rdfnode, rdfnode) RETURNS boolean AS $$ BEGIN RETURN false; END; $$ LANGUAGE plpgsql IMMUTABLE; EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE contains(o, '"x"'::rdfnode); QUERY PLAN -------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Filter: contains(rdfnode_ft.o, '"x"'::rdfnode) Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: not pushable (7 rows) DROP FUNCTION contains(rdfnode, rdfnode); /* Comparing a term with a PostgreSQL temporal type is not the comparison the * endpoint would make, so it stays local whichever temporal type and whichever * operand order is used. The local operator reads the term's lexical form with * the temporal type's input function, which discards the timezone offset of an * xsd:dateTime and accepts an xsd:date where an xsd:dateTime was asked for; * SPARQL compares instants and only between matching datatypes. */ EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o = '2015-01-01 00:00:00'::timestamp; QUERY PLAN ------------------------------------------------------------------------------------ Foreign Scan on public.rdfnode_ft Output: o Filter: (rdfnode_ft.o = 'Thu Jan 01 00:00:00 2015'::timestamp without time zone) Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: not pushable (7 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE '2015-01-01 00:00:00'::timestamptz >= o; QUERY PLAN -------------------------------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Filter: ('Thu Jan 01 00:00:00 2015 UTC'::timestamp with time zone >= rdfnode_ft.o) Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: not pushable (7 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o <> '2015-01-01'::date; QUERY PLAN ------------------------------------------------ Foreign Scan on public.rdfnode_ft Output: o Filter: (rdfnode_ft.o <> '01-01-2015'::date) Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: not pushable (7 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o < '12:00:00'::time; QUERY PLAN --------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Filter: (rdfnode_ft.o < '12:00:00'::time without time zone) Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: not pushable (7 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o > '12:00:00+00'::timetz; QUERY PLAN --------------------------------------------------------------- Foreign Scan on public.rdfnode_ft Output: o Filter: (rdfnode_ft.o > '12:00:00+00'::time with time zone) Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: not pushable (7 rows) /* a comparison against a value of the column's own type is still pushed */ EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM rdfnode_ft WHERE o = '"x"'::rdfnode; QUERY PLAN ----------------------------------- Foreign Scan on public.rdfnode_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: ((?o = "x")) (6 rows) /* A string constant is never a column, even when its value spells the name of * one. A column used to be recognised by comparing the deparsed argument with * the column names, so 'p' was sent as ?p, 'len' as the expression of the len * column, and the name of a dropped column as that column's variable, which it * does not have: a NULL pointer, printed as "(null)" or terminating the * backend, depending on the snprintf() doing the printing. */ CREATE FOREIGN TABLE colname_ft ( p rdfnode OPTIONS (variable '?p'), o rdfnode OPTIONS (variable '?o'), gone rdfnode OPTIONS (variable '?g'), label text OPTIONS (variable '?label'), len int OPTIONS (variable '?len', expression 'STRLEN(?o)') ) SERVER test_server OPTIONS ( sparql 'SELECT * WHERE { ?p ?o}'); ALTER FOREIGN TABLE colname_ft DROP COLUMN gone; EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM colname_ft WHERE sparql.contains(o, 'p'); WARNING: the rdf_fdw FOREIGN TABLE "colname_ft" has columns using native PostgreSQL types which are deprecated: label, len HINT: Use the "rdfnode" type instead. QUERY PLAN ---------------------------------------- Foreign Scan on public.colname_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: ((CONTAINS(?o, "p"))) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM colname_ft WHERE sparql.coalesce(o, 'p') = '"x"'; WARNING: the rdf_fdw FOREIGN TABLE "colname_ft" has columns using native PostgreSQL types which are deprecated: label, len HINT: Use the "rdfnode" type instead. QUERY PLAN ---------------------------------------------- Foreign Scan on public.colname_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: ((COALESCE(?o, "p") = "x")) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM colname_ft WHERE label = 'len'; WARNING: the rdf_fdw FOREIGN TABLE "colname_ft" has columns using native PostgreSQL types which are deprecated: label, len HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------- Foreign Scan on public.colname_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o ?label Remote Filter: ((?label = "len")) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM colname_ft WHERE sparql.contains(o, '........pg.dropped.3........'); WARNING: the rdf_fdw FOREIGN TABLE "colname_ft" has columns using native PostgreSQL types which are deprecated: label, len HINT: Use the "rdfnode" type instead. QUERY PLAN ------------------------------------------------------------------- Foreign Scan on public.colname_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o Remote Filter: ((CONTAINS(?o, "........pg.dropped.3........"))) (6 rows) /* a column is still recognised as one */ EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM colname_ft WHERE sparql.contains(o, p); WARNING: the rdf_fdw FOREIGN TABLE "colname_ft" has columns using native PostgreSQL types which are deprecated: label, len HINT: Use the "rdfnode" type instead. QUERY PLAN --------------------------------------- Foreign Scan on public.colname_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((CONTAINS(?o, ?p))) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM colname_ft WHERE sparql.coalesce(o, p) = '"x"'; WARNING: the rdf_fdw FOREIGN TABLE "colname_ft" has columns using native PostgreSQL types which are deprecated: label, len HINT: Use the "rdfnode" type instead. QUERY PLAN --------------------------------------------- Foreign Scan on public.colname_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?p ?o Remote Filter: ((COALESCE(?o, ?p) = "x")) (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT o FROM colname_ft WHERE length(label) = len; WARNING: the rdf_fdw FOREIGN TABLE "colname_ft" has columns using native PostgreSQL types which are deprecated: label, len HINT: Use the "rdfnode" type instead. QUERY PLAN ---------------------------------------------------- Foreign Scan on public.colname_ft Output: o Foreign Server: test_server Pushdown: enabled Remote Select: ?o ?label (STRLEN(?o) AS ?len) Remote Filter: ((STRLEN(?label) = (STRLEN(?o)))) (6 rows) DROP FOREIGN TABLE colname_ft; DROP SERVER test_server CASCADE; NOTICE: drop cascades to 20 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 escaping_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 drop cascades to foreign table np_distinct drop cascades to foreign table np_reduced drop cascades to foreign table np_alias drop cascades to foreign table np_base drop cascades to foreign table np_unprojected drop cascades to foreign table kw_dquote drop cascades to foreign table kw_squote drop cascades to foreign table kw_tquote drop cascades to foreign table kw_subselect drop cascades to foreign table np_plain drop cascades to foreign table arith_ft