diff options
26 files changed, 386 insertions, 36 deletions
diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml index 8641e1925ed..a83d9564e5a 100644 --- a/doc/src/sgml/ref/create_view.sgml +++ b/doc/src/sgml/ref/create_view.sgml @@ -251,9 +251,10 @@ CREATE VIEW [ <replaceable>schema</> . ] <replaceable>view_name</> AS WITH RECUR <programlisting> CREATE VIEW vista AS SELECT 'Hello World'; </programlisting> - is bad form in two ways: the column name defaults to <literal>?column?</>, - and the column data type defaults to <type>unknown</>. If you want a - string literal in a view's result, use something like: + is bad form because the column name defaults to <literal>?column?</>; + also, the column data type defaults to <type>text</>, which might not + be what you wanted. Better style for a string literal in a view's + result is something like: <programlisting> CREATE VIEW vista AS SELECT text 'Hello World' AS hello; </programlisting> diff --git a/doc/src/sgml/typeconv.sgml b/doc/src/sgml/typeconv.sgml index c031c01ed35..63d41f03f3f 100644 --- a/doc/src/sgml/typeconv.sgml +++ b/doc/src/sgml/typeconv.sgml @@ -984,7 +984,8 @@ domain's base type for all subsequent steps. <para> If all inputs are of type <type>unknown</type>, resolve as type <type>text</type> (the preferred type of the string category). -Otherwise, <type>unknown</type> inputs are ignored. +Otherwise, <type>unknown</type> inputs are ignored for the purposes +of the remaining rules. </para> </step> @@ -1076,6 +1077,53 @@ but <type>integer</> can be implicitly cast to <type>real</>, the union result type is resolved as <type>real</>. </para> </example> +</sect1> + +<sect1 id="typeconv-select"> +<title><literal>SELECT</literal> Output Columns</title> + +<indexterm zone="typeconv-select"> + <primary>SELECT</primary> + <secondary>determination of result type</secondary> +</indexterm> + +<para> +The rules given in the preceding sections will result in assignment +of non-<type>unknown</> data types to all expressions in a SQL query, +except for unspecified-type literals that appear as simple output +columns of a <command>SELECT</> command. For example, in + +<screen> +SELECT 'Hello World'; +</screen> + +there is nothing to identify what type the string literal should be +taken as. In this situation <productname>PostgreSQL</> will fall back +to resolving the literal's type as <type>text</>. +</para> + +<para> +When the <command>SELECT</> is one arm of a <literal>UNION</> +(or <literal>INTERSECT</> or <literal>EXCEPT</>) construct, or when it +appears within <command>INSERT ... SELECT</>, this rule is not applied +since rules given in preceding sections take precedence. The type of an +unspecified-type literal can be taken from the other <literal>UNION</> arm +in the first case, or from the destination column in the second case. +</para> + +<para> +<literal>RETURNING</> lists are treated the same as <command>SELECT</> +output lists for this purpose. +</para> + +<note> + <para> + Prior to <productname>PostgreSQL</> 10, this rule did not exist, and + unspecified-type literals in a <command>SELECT</> output list were + left as type <type>unknown</>. That had assorted bad consequences, + so it's been changed. + </para> +</note> </sect1> </chapter> diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index a56ddbbd3ba..d7a3513d295 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -490,18 +490,8 @@ CheckAttributeType(const char *attname, char att_typtype = get_typtype(atttypid); Oid att_typelem; - if (atttypid == UNKNOWNOID) - { - /* - * Warn user, but don't fail, if column to be created has UNKNOWN type - * (usually as a result of a 'retrieve into' - jolly) - */ - ereport(WARNING, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("column \"%s\" has type %s", attname, "unknown"), - errdetail("Proceeding with relation creation anyway."))); - } - else if (att_typtype == TYPTYPE_PSEUDO) + if (atttypid == UNKNOWNOID || + att_typtype == TYPTYPE_PSEUDO) { /* * Refuse any attempt to create a pseudo-type column, except for a diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index a02a77a03a5..f954dc15f0e 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -156,13 +156,15 @@ parse_analyze_varparams(RawStmt *parseTree, const char *sourceText, Query * parse_sub_analyze(Node *parseTree, ParseState *parentParseState, CommonTableExpr *parentCTE, - bool locked_from_parent) + bool locked_from_parent, + bool resolve_unknowns) { ParseState *pstate = make_parsestate(parentParseState); Query *query; pstate->p_parent_cte = parentCTE; pstate->p_locked_from_parent = locked_from_parent; + pstate->p_resolve_unknowns = resolve_unknowns; query = transformStmt(pstate, parseTree); @@ -570,10 +572,17 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt) * otherwise the behavior of SELECT within INSERT might be different * from a stand-alone SELECT. (Indeed, Postgres up through 6.5 had * bugs of just that nature...) + * + * The sole exception is that we prevent resolving unknown-type + * outputs as TEXT. This does not change the semantics since if the + * column type matters semantically, it would have been resolved to + * something else anyway. Doing this lets us resolve such outputs as + * the target column's type, which we handle below. */ sub_pstate->p_rtable = sub_rtable; sub_pstate->p_joinexprs = NIL; /* sub_rtable has no joins */ sub_pstate->p_namespace = sub_namespace; + sub_pstate->p_resolve_unknowns = false; selectQuery = transformStmt(sub_pstate, stmt->selectStmt); @@ -1269,6 +1278,10 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt) pstate->p_windowdefs, &qry->targetList); + /* resolve any still-unresolved output columns as being type text */ + if (pstate->p_resolve_unknowns) + resolveTargetListUnknowns(pstate, qry->targetList); + qry->rtable = pstate->p_rtable; qry->jointree = makeFromExpr(pstate->p_joinlist, qual); @@ -1843,11 +1856,19 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt, /* * Transform SelectStmt into a Query. * + * This works the same as SELECT transformation normally would, except + * that we prevent resolving unknown-type outputs as TEXT. This does + * not change the subquery's semantics since if the column type + * matters semantically, it would have been resolved to something else + * anyway. Doing this lets us resolve such outputs using + * select_common_type(), below. + * * Note: previously transformed sub-queries don't affect the parsing * of this sub-query, because they are not in the toplevel pstate's * namespace list. */ - selectQuery = parse_sub_analyze((Node *) stmt, pstate, NULL, false); + selectQuery = parse_sub_analyze((Node *) stmt, pstate, + NULL, false, false); /* * Check for bogus references to Vars on the current query level (but @@ -2350,6 +2371,10 @@ transformReturningList(ParseState *pstate, List *returningList) /* mark column origins */ markTargetListOrigins(pstate, rlist); + /* resolve any still-unresolved output columns as being type text */ + if (pstate->p_resolve_unknowns) + resolveTargetListUnknowns(pstate, rlist); + /* restore state */ pstate->p_next_resno = save_next_resno; diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index 624ab41371f..4769e786202 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -471,7 +471,8 @@ transformRangeSubselect(ParseState *pstate, RangeSubselect *r) * Analyze and transform the subquery. */ query = parse_sub_analyze(r->subquery, pstate, NULL, - isLockedRefname(pstate, r->alias->aliasname)); + isLockedRefname(pstate, r->alias->aliasname), + true); /* Restore state */ pstate->p_lateral_active = false; diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c index fc8c15b2681..dfbcaa2cdcc 100644 --- a/src/backend/parser/parse_cte.c +++ b/src/backend/parser/parse_cte.c @@ -241,7 +241,7 @@ analyzeCTE(ParseState *pstate, CommonTableExpr *cte) /* Analysis not done already */ Assert(!IsA(cte->ctequery, Query)); - query = parse_sub_analyze(cte->ctequery, pstate, cte, false); + query = parse_sub_analyze(cte->ctequery, pstate, cte, false, true); cte->ctequery = (Node *) query; /* @@ -393,11 +393,10 @@ analyzeCTETargetList(ParseState *pstate, CommonTableExpr *cte, List *tlist) /* * If the CTE is recursive, force the exposed column type of any - * "unknown" column to "text". This corresponds to the fact that - * SELECT 'foo' UNION SELECT 'bar' will ultimately produce text. We - * might see "unknown" as a result of an untyped literal in the - * non-recursive term's select list, and if we don't convert to text - * then we'll have a mismatch against the UNION result. + * "unknown" column to "text". We must deal with this here because + * we're called on the non-recursive term before there's been any + * attempt to force unknown output columns to some other type. We + * have to resolve unknowns before looking at the recursive term. * * The column might contain 'foo' COLLATE "bar", so don't override * collation if it's already set. diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index add3be65660..c43ef19df5c 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -1846,7 +1846,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink) /* * OK, let's transform the sub-SELECT. */ - qtree = parse_sub_analyze(sublink->subselect, pstate, NULL, false); + qtree = parse_sub_analyze(sublink->subselect, pstate, NULL, false, true); /* * Check that we got a SELECT. Anything else should be impossible given diff --git a/src/backend/parser/parse_node.c b/src/backend/parser/parse_node.c index 73e7d65c359..2a5f147ca1d 100644 --- a/src/backend/parser/parse_node.c +++ b/src/backend/parser/parse_node.c @@ -51,6 +51,7 @@ make_parsestate(ParseState *parentParseState) /* Fill in fields that don't start at null/false/zero */ pstate->p_next_resno = 1; + pstate->p_resolve_unknowns = true; if (parentParseState) { diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index 081a8dd468b..2576e312390 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -289,12 +289,41 @@ transformExpressionList(ParseState *pstate, List *exprlist, /* + * resolveTargetListUnknowns() + * Convert any unknown-type targetlist entries to type TEXT. + * + * We do this after we've exhausted all other ways of identifying the output + * column types of a query. + */ +void +resolveTargetListUnknowns(ParseState *pstate, List *targetlist) +{ + ListCell *l; + + foreach(l, targetlist) + { + TargetEntry *tle = (TargetEntry *) lfirst(l); + Oid restype = exprType((Node *) tle->expr); + + if (restype == UNKNOWNOID) + { + tle->expr = (Expr *) coerce_type(pstate, (Node *) tle->expr, + restype, TEXTOID, -1, + COERCION_IMPLICIT, + COERCE_IMPLICIT_CAST, + -1); + } + } +} + + +/* * markTargetListOrigins() * Mark targetlist columns that are simple Vars with the source * table's OID and column number. * - * Currently, this is done only for SELECT targetlists, since we only - * need the info if we are going to send it to the frontend. + * Currently, this is done only for SELECT targetlists and RETURNING lists, + * since we only need the info if we are going to send it to the frontend. */ void markTargetListOrigins(ParseState *pstate, List *targetlist) diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c index b6efad429a2..ff1d7a207a7 100644 --- a/src/bin/pg_upgrade/check.c +++ b/src/bin/pg_upgrade/check.c @@ -99,6 +99,10 @@ check_and_dump_old_cluster(bool live_check) check_for_reg_data_type_usage(&old_cluster); check_for_isn_and_int8_passing_mismatch(&old_cluster); + /* Pre-PG 10 allowed tables with 'unknown' type columns */ + if (GET_MAJOR_VERSION(old_cluster.major_version) <= 906) + old_9_6_check_for_unknown_data_type_usage(&old_cluster); + /* 9.5 and below should not have roles starting with pg_ */ if (GET_MAJOR_VERSION(old_cluster.major_version) <= 905) check_for_pg_role_prefix(&old_cluster); diff --git a/src/bin/pg_upgrade/pg_upgrade.h b/src/bin/pg_upgrade/pg_upgrade.h index 42e7aebb016..968ab8a668f 100644 --- a/src/bin/pg_upgrade/pg_upgrade.h +++ b/src/bin/pg_upgrade/pg_upgrade.h @@ -442,6 +442,7 @@ void pg_putenv(const char *var, const char *val); void new_9_0_populate_pg_largeobject_metadata(ClusterInfo *cluster, bool check_mode); void old_9_3_check_for_line_data_type_usage(ClusterInfo *cluster); +void old_9_6_check_for_unknown_data_type_usage(ClusterInfo *cluster); /* parallel.c */ void parallel_exec_prog(const char *log_file, const char *opt_log_file, diff --git a/src/bin/pg_upgrade/version.c b/src/bin/pg_upgrade/version.c index fb56feabd90..aa462daed3d 100644 --- a/src/bin/pg_upgrade/version.c +++ b/src/bin/pg_upgrade/version.c @@ -185,3 +185,100 @@ old_9_3_check_for_line_data_type_usage(ClusterInfo *cluster) else check_ok(); } + + +/* + * old_9_6_check_for_unknown_data_type_usage() + * 9.6 -> 10 + * It's no longer allowed to create tables or views with "unknown"-type + * columns. We do not complain about views with such columns, because + * they should get silently converted to "text" columns during the DDL + * dump and reload; it seems unlikely to be worth making users do that + * by hand. However, if there's a table with such a column, the DDL + * reload will fail, so we should pre-detect that rather than failing + * mid-upgrade. Worse, if there's a matview with such a column, the + * DDL reload will silently change it to "text" which won't match the + * on-disk storage (which is like "cstring"). So we *must* reject that. + * Also check composite types, in case they are used for table columns. + * We needn't check indexes, because "unknown" has no opclasses. + */ +void +old_9_6_check_for_unknown_data_type_usage(ClusterInfo *cluster) +{ + int dbnum; + FILE *script = NULL; + bool found = false; + char output_path[MAXPGPATH]; + + prep_status("Checking for invalid \"unknown\" user columns"); + + snprintf(output_path, sizeof(output_path), "tables_using_unknown.txt"); + + for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++) + { + PGresult *res; + bool db_used = false; + int ntups; + int rowno; + int i_nspname, + i_relname, + i_attname; + DbInfo *active_db = &cluster->dbarr.dbs[dbnum]; + PGconn *conn = connectToServer(cluster, active_db->db_name); + + res = executeQueryOrDie(conn, + "SELECT n.nspname, c.relname, a.attname " + "FROM pg_catalog.pg_class c, " + " pg_catalog.pg_namespace n, " + " pg_catalog.pg_attribute a " + "WHERE c.oid = a.attrelid AND " + " NOT a.attisdropped AND " + " a.atttypid = 'pg_catalog.unknown'::pg_catalog.regtype AND " + " c.relkind IN ('r', 'c', 'm') AND " + " c.relnamespace = n.oid AND " + /* exclude possible orphaned temp tables */ + " n.nspname !~ '^pg_temp_' AND " + " n.nspname !~ '^pg_toast_temp_' AND " + " n.nspname NOT IN ('pg_catalog', 'information_schema')"); + + ntups = PQntuples(res); + i_nspname = PQfnumber(res, "nspname"); + i_relname = PQfnumber(res, "relname"); + i_attname = PQfnumber(res, "attname"); + for (rowno = 0; rowno < ntups; rowno++) + { + found = true; + if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL) + pg_fatal("could not open file \"%s\": %s\n", output_path, + strerror(errno)); + if (!db_used) + { + fprintf(script, "Database: %s\n", active_db->db_name); + db_used = true; + } + fprintf(script, " %s.%s.%s\n", + PQgetvalue(res, rowno, i_nspname), + PQgetvalue(res, rowno, i_relname), + PQgetvalue(res, rowno, i_attname)); + } + + PQclear(res); + + PQfinish(conn); + } + + if (script) + fclose(script); + + if (found) + { + pg_log(PG_REPORT, "fatal\n"); + pg_fatal("Your installation contains the \"unknown\" data type in user tables. This\n" + "data type is no longer allowed in tables, so this cluster cannot currently\n" + "be upgraded. You can remove the problem tables and restart the upgrade.\n" + "A list of the problem columns is in the file:\n" + " %s\n\n", output_path); + } + else + check_ok(); +} diff --git a/src/include/parser/analyze.h b/src/include/parser/analyze.h index a7e5c55ab4f..17259409a72 100644 --- a/src/include/parser/analyze.h +++ b/src/include/parser/analyze.h @@ -29,7 +29,8 @@ extern Query *parse_analyze_varparams(RawStmt *parseTree, const char *sourceText extern Query *parse_sub_analyze(Node *parseTree, ParseState *parentParseState, CommonTableExpr *parentCTE, - bool locked_from_parent); + bool locked_from_parent, + bool resolve_unknowns); extern Query *transformTopLevelStmt(ParseState *pstate, RawStmt *parseTree); extern Query *transformStmt(ParseState *pstate, Node *parseTree); diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index bc3eea9ba5a..3a25d9598d6 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -150,6 +150,9 @@ typedef Node *(*CoerceParamHook) (ParseState *pstate, Param *param, * p_locked_from_parent: true if parent query level applies FOR UPDATE/SHARE * to this subquery as a whole. * + * p_resolve_unknowns: resolve unknown-type SELECT output columns as type TEXT + * (this is true by default). + * * p_hasAggs, p_hasWindowFuncs, etc: true if we've found any of the indicated * constructs in the query. * @@ -182,6 +185,8 @@ struct ParseState List *p_locking_clause; /* raw FOR UPDATE/FOR SHARE info */ bool p_locked_from_parent; /* parent has marked this subquery * with FOR UPDATE/FOR SHARE */ + bool p_resolve_unknowns; /* resolve unknown-type SELECT outputs + * as type text */ /* Flags telling about things found in the query: */ bool p_hasAggs; diff --git a/src/include/parser/parse_target.h b/src/include/parser/parse_target.h index e035aacbf4f..d06a235df0b 100644 --- a/src/include/parser/parse_target.h +++ b/src/include/parser/parse_target.h @@ -21,6 +21,7 @@ extern List *transformTargetList(ParseState *pstate, List *targetlist, ParseExprKind exprKind); extern List *transformExpressionList(ParseState *pstate, List *exprlist, ParseExprKind exprKind, bool allowDefault); +extern void resolveTargetListUnknowns(ParseState *pstate, List *targetlist); extern void markTargetListOrigins(ParseState *pstate, List *targetlist); extern TargetEntry *transformTargetEntry(ParseState *pstate, Node *node, Node *expr, ParseExprKind exprKind, diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index 6caa9c2407d..36266f0a32b 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -199,6 +199,14 @@ CREATE TABLE array_index_op_test ( CREATE TABLE testjsonb ( j jsonb ); +CREATE TABLE unknowntab ( + u unknown -- fail +); +ERROR: column "u" has pseudo-type unknown +CREATE TYPE unknown_comptype AS ( + u unknown -- fail +); +ERROR: column "u" has pseudo-type unknown CREATE TABLE IF NOT EXISTS test_tsvector( t text, a tsvector diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out index 096bfc30c9e..ce0c8cedf89 100644 --- a/src/test/regress/expected/create_view.out +++ b/src/test/regress/expected/create_view.out @@ -288,6 +288,32 @@ SELECT relname, relkind, reloptions FROM pg_class mysecview4 | v | {security_barrier=false} (4 rows) +-- Check that unknown literals are converted to "text" in CREATE VIEW, +-- so that we don't end up with unknown-type columns. +CREATE VIEW unspecified_types AS + SELECT 42 as i, 42.5 as num, 'foo' as u, 'foo'::unknown as u2, null as n; +\d+ unspecified_types + View "testviewschm2.unspecified_types" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------+-----------+----------+---------+----------+------------- + i | integer | | | | plain | + num | numeric | | | | main | + u | text | | | | extended | + u2 | text | | | | extended | + n | text | | | | extended | +View definition: + SELECT 42 AS i, + 42.5 AS num, + 'foo'::text AS u, + 'foo'::text AS u2, + NULL::text AS n; + +SELECT * FROM unspecified_types; + i | num | u | u2 | n +----+------+-----+-----+--- + 42 | 42.5 | foo | foo | +(1 row) + -- This test checks that proper typmods are assigned in a multi-row VALUES CREATE VIEW tt1 AS SELECT * FROM ( diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out index 7a2eaa0c4a8..4ae44607a40 100644 --- a/src/test/regress/expected/matview.out +++ b/src/test/regress/expected/matview.out @@ -508,6 +508,33 @@ DETAIL: drop cascades to materialized view mvtest_mv_v drop cascades to materialized view mvtest_mv_v_2 drop cascades to materialized view mvtest_mv_v_3 drop cascades to materialized view mvtest_mv_v_4 +-- Check that unknown literals are converted to "text" in CREATE MATVIEW, +-- so that we don't end up with unknown-type columns. +CREATE MATERIALIZED VIEW mv_unspecified_types AS + SELECT 42 as i, 42.5 as num, 'foo' as u, 'foo'::unknown as u2, null as n; +\d+ mv_unspecified_types + Materialized view "public.mv_unspecified_types" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+----------+--------------+------------- + i | integer | | | | plain | | + num | numeric | | | | main | | + u | text | | | | extended | | + u2 | text | | | | extended | | + n | text | | | | extended | | +View definition: + SELECT 42 AS i, + 42.5 AS num, + 'foo'::text AS u, + 'foo'::text AS u2, + NULL::text AS n; + +SELECT * FROM mv_unspecified_types; + i | num | u | u2 | n +----+------+-----+-----+--- + 42 | 42.5 | foo | foo | +(1 row) + +DROP MATERIALIZED VIEW mv_unspecified_types; -- make sure that create WITH NO DATA does not plan the query (bug #13907) create materialized view mvtest_error as select 1/0 as x; -- fail ERROR: division by zero diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index abd3217e866..47afdc335e1 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -196,6 +196,37 @@ SELECT '' AS five, f1 AS "Correlated Field" | 3 (5 rows) +-- Unspecified-type literals in output columns should resolve as text +SELECT *, pg_typeof(f1) FROM + (SELECT 'foo' AS f1 FROM generate_series(1,3)) ss ORDER BY 1; + f1 | pg_typeof +-----+----------- + foo | text + foo | text + foo | text +(3 rows) + +-- ... unless there's context to suggest differently +explain verbose select '42' union all select '43'; + QUERY PLAN +------------------------------------------------- + Append (cost=0.00..0.04 rows=2 width=32) + -> Result (cost=0.00..0.01 rows=1 width=32) + Output: '42'::text + -> Result (cost=0.00..0.01 rows=1 width=32) + Output: '43'::text +(5 rows) + +explain verbose select '42' union all select 43; + QUERY PLAN +------------------------------------------------ + Append (cost=0.00..0.04 rows=2 width=4) + -> Result (cost=0.00..0.01 rows=1 width=4) + Output: 42 + -> Result (cost=0.00..0.01 rows=1 width=4) + Output: 43 +(5 rows) + -- -- Use some existing tables in the regression test -- diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out index 02fa08e932f..3b7f689a98b 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -133,9 +133,9 @@ SELECT * FROM t LIMIT 10; -- Test behavior with an unknown-type literal in the WITH WITH q AS (SELECT 'foo' AS x) -SELECT x, x IS OF (unknown) as is_unknown FROM q; - x | is_unknown ------+------------ +SELECT x, x IS OF (text) AS is_text FROM q; + x | is_text +-----+--------- foo | t (1 row) @@ -144,7 +144,7 @@ WITH RECURSIVE t(n) AS ( UNION ALL SELECT n || ' bar' FROM t WHERE length(n) < 20 ) -SELECT n, n IS OF (text) as is_text FROM t; +SELECT n, n IS OF (text) AS is_text FROM t; n | is_text -------------------------+--------- foo | t @@ -155,6 +155,18 @@ SELECT n, n IS OF (text) as is_text FROM t; foo bar bar bar bar bar | t (6 rows) +-- In a perfect world, this would work and resolve the literal as int ... +-- but for now, we have to be content with resolving to text too soon. +WITH RECURSIVE t(n) AS ( + SELECT '7' +UNION ALL + SELECT n+1 FROM t WHERE n < 10 +) +SELECT n, n IS OF (int) AS is_int FROM t; +ERROR: operator does not exist: text + integer +LINE 4: SELECT n+1 FROM t WHERE n < 10 + ^ +HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. -- -- Some examples with a tree -- diff --git a/src/test/regress/output/create_function_1.source b/src/test/regress/output/create_function_1.source index 30c2936f8d1..957595c51e4 100644 --- a/src/test/regress/output/create_function_1.source +++ b/src/test/regress/output/create_function_1.source @@ -59,7 +59,7 @@ CREATE FUNCTION test_atomic_ops() CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL AS 'SELECT ''not an integer'';'; ERROR: return type mismatch in function declared to return integer -DETAIL: Actual return type is unknown. +DETAIL: Actual return type is text. CONTEXT: SQL function "test1" CREATE FUNCTION test1 (int) RETURNS int LANGUAGE SQL AS 'not even SQL'; diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index 8242e7328d8..6314aa403ff 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -236,6 +236,14 @@ CREATE TABLE testjsonb ( j jsonb ); +CREATE TABLE unknowntab ( + u unknown -- fail +); + +CREATE TYPE unknown_comptype AS ( + u unknown -- fail +); + CREATE TABLE IF NOT EXISTS test_tsvector( t text, a tsvector diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql index 5fe8b94aae0..c27f1034e13 100644 --- a/src/test/regress/sql/create_view.sql +++ b/src/test/regress/sql/create_view.sql @@ -224,6 +224,14 @@ SELECT relname, relkind, reloptions FROM pg_class 'mysecview3'::regclass, 'mysecview4'::regclass) ORDER BY relname; +-- Check that unknown literals are converted to "text" in CREATE VIEW, +-- so that we don't end up with unknown-type columns. + +CREATE VIEW unspecified_types AS + SELECT 42 as i, 42.5 as num, 'foo' as u, 'foo'::unknown as u2, null as n; +\d+ unspecified_types +SELECT * FROM unspecified_types; + -- This test checks that proper typmods are assigned in a multi-row VALUES CREATE VIEW tt1 AS diff --git a/src/test/regress/sql/matview.sql b/src/test/regress/sql/matview.sql index 65a743ced96..1164b4cea21 100644 --- a/src/test/regress/sql/matview.sql +++ b/src/test/regress/sql/matview.sql @@ -198,6 +198,14 @@ SELECT * FROM mvtest_mv_v_3; SELECT * FROM mvtest_mv_v_4; DROP TABLE mvtest_v CASCADE; +-- Check that unknown literals are converted to "text" in CREATE MATVIEW, +-- so that we don't end up with unknown-type columns. +CREATE MATERIALIZED VIEW mv_unspecified_types AS + SELECT 42 as i, 42.5 as num, 'foo' as u, 'foo'::unknown as u2, null as n; +\d+ mv_unspecified_types +SELECT * FROM mv_unspecified_types; +DROP MATERIALIZED VIEW mv_unspecified_types; + -- make sure that create WITH NO DATA does not plan the query (bug #13907) create materialized view mvtest_error as select 1/0 as x; -- fail create materialized view mvtest_error as select 1/0 as x with no data; diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index 08eb825c542..9c2a73d4d77 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -80,6 +80,16 @@ SELECT '' AS five, f1 AS "Correlated Field" WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL WHERE f3 IS NOT NULL); +-- Unspecified-type literals in output columns should resolve as text + +SELECT *, pg_typeof(f1) FROM + (SELECT 'foo' AS f1 FROM generate_series(1,3)) ss ORDER BY 1; + +-- ... unless there's context to suggest differently + +explain verbose select '42' union all select '43'; +explain verbose select '42' union all select 43; + -- -- Use some existing tables in the regression test -- diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql index 7ee32bab8f6..08ddc8bae01 100644 --- a/src/test/regress/sql/with.sql +++ b/src/test/regress/sql/with.sql @@ -69,14 +69,23 @@ SELECT * FROM t LIMIT 10; -- Test behavior with an unknown-type literal in the WITH WITH q AS (SELECT 'foo' AS x) -SELECT x, x IS OF (unknown) as is_unknown FROM q; +SELECT x, x IS OF (text) AS is_text FROM q; WITH RECURSIVE t(n) AS ( SELECT 'foo' UNION ALL SELECT n || ' bar' FROM t WHERE length(n) < 20 ) -SELECT n, n IS OF (text) as is_text FROM t; +SELECT n, n IS OF (text) AS is_text FROM t; + +-- In a perfect world, this would work and resolve the literal as int ... +-- but for now, we have to be content with resolving to text too soon. +WITH RECURSIVE t(n) AS ( + SELECT '7' +UNION ALL + SELECT n+1 FROM t WHERE n < 10 +) +SELECT n, n IS OF (int) AS is_int FROM t; -- -- Some examples with a tree |