Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [sql-standard]

For questions concerning the SQL Standard itself (also known as the ANSI or ISO SQL Standard) including writing queries that conform to that standard. This tag is NOT to be used for questions about SQL Server Standard Edition.

0
votes
1answer
34 views

Recursive CTE throws temp tablespace is empty error

I need to aggregate the contents of multiple rows into a row as a delimited text. Here's the simplified table with sample data which represents what i want to do. CREATE TABLE SPD_OWNER.EMP ( ...
1
vote
1answer
36 views

ORDER BY usage in subquery (derived table) SQL standard allowed or not

MariaDB documentation is mentioning that ORDER BY clause in subqueries (derived table) are never allowed by SQL standards. Meaning the SQL query SELECT field1 , field2 FROM ( SELECT ...
0
votes
2answers
46 views

Can any database table name support double quotes "?

I need to know whether any database supports double quotes as a part of their table name. I am especially interested in JDBC compliant databases. And if they do, can I please know how?
0
votes
0answers
25 views

Why information_schema.columns is_nullable is varchar? [duplicate]

Looking for all columns "not null" is very useful sometimes, right? So, I'm thinking on "why on earth the 'is_nullable' columns from information_schema.columns is varchar, not boolean?" On first ...
9
votes
3answers
2k views

Is the keyword “ALIAS” actually used?

According to PostgreSQL 7.1 through 9.1 (now unsupported), ALIAS is listed as a reserved word, at least for SQL-99. Later versions do not show it - suggesting that it has been dropped as a reserved ...
8
votes
4answers
1k views

Best practices to maximize portability in SQL Server 2016

When it comes to developing the prototype of a solution, often the technologies has not been decided yet and might not be the same that will be used in the finished product. In this scenarios I tend ...
0
votes
1answer
86 views

Does the SQL Standard mention trigger events on DDL (ALTER, CREATE, DROP)?

PostgreSQL supports "Event Triggers" which is defined as, To supplement the trigger mechanism discussed in Chapter 39, PostgreSQL also provides event triggers. Unlike regular triggers, which are ...
4
votes
1answer
71 views

How locks in Postgres behave when quitting the JVM abnormally

I have a table 'car', as follows Table "public.car" Column | Type | Collation | Nullable | Default ----------+--------+-----------+----------+--------- car_no | bigint | ...
0
votes
0answers
23 views

Standard behaviour in executing multiple alter statements in Postgres

I was just experimenting with ALTER TABLE commands in psql. I came across a fact that we can give multiple alter clause within same ALTER TABLE command. Like ALTER TABLE EmployeeLoans add col1, add ...
3
votes
1answer
420 views

Order of Execution of Set clause in Update Query (Postgres)

I recently came across this strange behaviour in Postgres. I had a table like the following one: sasdb=# \d emp_manager_rel Table "db3004db.emp_manager_rel" Column | Type | ...
0
votes
1answer
378 views

Updating Multiple Tables with single Update query [duplicate]

I have two tables TableA, TableB as follows. Table "db3004db.tableb" Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- empid | integer | ...
-3
votes
1answer
580 views

What is a “clob” or Character Large Object?

I see in this post on listagg — Rows to Delimited Strings talking about SQL's LISTAGG() clause ON OVERFLOW The return type of listagg is either varchar or clob with an implementation defined length ...
2
votes
2answers
105 views

Creating same unique constraint with different names

In MySQL 5.7 alter table mydb.roles add constraint uk1 unique (role_name); alter table mydb.roles add constraint uk2 unique (role_name); The 2nd alter command threw me a warning stating: ...
5
votes
2answers
1k views

Is Microsoft SQL Server 2016 fully ANSI SQL-92 compliant?

I am trying to find a compliance confirmation for MS SQL 2016 - if it is fully compliant with ANSI SQL-92 starndard. I found this article on Microsoft Docs which states it is not, but it refers to ...
2
votes
1answer
91 views

What is the SQL Standard method of doing LIMIT OFFSET?

I know LIMIT and OFFSET are pretty common but unstandardized methods to page through a result set. I know there is a standardized method, but I can never find this when I'm going to look it up, The ...
1
vote
2answers
112 views

Are duplicates in Read Commited standard behaviour?

This refers to this question: Duplicate records returned from table with no duplicates The "read commited" isolation level assumes that one transaction could read same record differently, but it does ...
0
votes
2answers
61 views

What is the F301: the CORRESPONDING clause in query expression?

The SQL 2011 Spec supports a CORRESPONDING clause, Feature F301, “CORRESPONDING in query expressions”: What is this feature? How is it used? And, is it supported by any industry RDBMs?
2
votes
1answer
398 views

How do I do a JOIN in ANSI SQL 89 syntax?

In Oracle, I use SELECT * FROM table1...JOIN... where the dots represent either the type of join or the condition on which to be joined. Is this ANSI 89 syntax? If not, then how would I perform a ...
0
votes
2answers
82 views

Postgres CTE execution guarantees, inlining

Following query with non deterministic function yields two same rows - the CTE is computed and re-used. WITH foo AS (SELECT uuid_generate_v4() AS id) SELECT id FROM foo UNION ALL SELECT id FROM foo |...
1
vote
1answer
71 views

How does the SQL Spec specify literals with a decimal should be typed?

Different databases implicitly type numeric literals differently, MySQL types 1.0 as decimal(2,1) PostgreSQL types 1.0 as numeric Which one of these is correct?
5
votes
1answer
320 views

Where can I find the SQL-89 standard?

This question is different but similar to this request looking for SQL-86. SQL 1989 is almost 30 years old. Is this spec freely available anywhere. The standard does not have a Wikipage or the like? ...
7
votes
1answer
909 views

Why does information_schema have “YES” and “NO” character strings rather than booleans?

I was taken by surprise to find that the is_nullable column for this query is typed as character varying (3) rather than boolean: select column_name, data_type, is_nullable from ...
1
vote
1answer
113 views

Does the SQL-1992 standard restrict naming identifiers to 18 characters?

In this draft of SQL:1992, I found… Section 5.2 Leveling Rules .... The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions: a) No <...
0
votes
2answers
2k views

What does `<>` mean?

What does <> do in the following WHERE clause, WHERE posn_id <> rid
6
votes
2answers
414 views

Does the spec permit column-count mismatch on INSERT?

I'm just wondering if the spec permits excluding the column-list clause in an INSERT statement if you're inserting in fewer columns than the table provides. I see PostgreSQL does, and MySQL and SQL ...
4
votes
2answers
2k views

In PostgreSQL, what is the difference between a “Stored Procedure” and other types of functions?

I understand the distinction between: Scalar functions Set-Returning-Functions (SRF)s Internal functions Window functions Aggregate functions of all sorts User-Implemented functions (which in ...
0
votes
1answer
108 views

How does the spec store JSON on a table?

From this question, I quoted the docs, "MariaDB implements this as a LONGTEXT rather, as the JSON data type contradicts the SQL standard, and MariaDB's benchmarks indicate that performance is at ...
3
votes
2answers
869 views

Can IDENTITY COLUMNS generate UUIDs?

I'm just wondering if either the spec of the implementation (PostgreSQL) provides for generating identity columns from UUIds or the like. Is there an alternative to, CREATE TABLE f ( id uuid ...
1
vote
0answers
611 views

Why Turning ANSI_WARNINGS Off Causes Error Updating Col Used as a Source of a Computed Col?

I have a stored procedure that updates the source column used in a computed column. That stored procedure has SET ANSI_WARNINGS OFF;. When the procedure executes, and tries to update the source ...
0
votes
1answer
540 views

ANSI Joins in Oracle

We are doing a large ETL load from Oracle 12c. Every now and then Oracle optimizer decides to create a new execution plan for a SQL statement that has previously been running just fine. Usually this ...
2
votes
2answers
88 views

Definition of “table variable” and “base table”

When reading SQL standard text (SQL:2003), I can not get the meaning of following sentence (in "Cluase 4.3 Tables" in SQL:2003-1): SQL-data consists entirely of table variables, called base tables. ...
2
votes
1answer
185 views

What is the order when SELECT-ing unordered set from an ordered SELECT?

What does the SQL standard and its mainstream DB implementations (SQL Server, Oracle, DB2, PostgreSQL, Firebird, MySQL, etc.) say about the final order in the following general case: SELECT * FROM (...
3
votes
1answer
369 views

Is INSERT INTO … SET standard SQL?

I'm just wondering if this syntax is standard or where it comes from? For example INSERT INTO gtest3 SET gDateTime='2017-07-16 01:00:00' You can see it documented in MySQL but PostgreSQL does not ...
2
votes
1answer
339 views

Tool to test RDBMS SQL dialect for ANSI SQL standard compliance

We are developing an interface to our internal data storage and manipulation tool that would allow people to use SQL dialect. We decided that it would be perfect to make our SQL dialect as close to ...
1
vote
1answer
559 views

Does MariaDB or MySQL implement the VALUES (expression) table value constructor?

I am just wondering if MariaDB or MySQL implements the <table value constructor> in the SQL Spec. In SQL Server and PostgreSQL this is done with standardized VALUES (expression)? SELECT * FROM (...
0
votes
1answer
43 views

Are null values allowed in any column of a table in SQL?

I have doubts over null values.Therefore can you please explain me briefly what is a null value in sql and can we use null values at any place in sql tables?
5
votes
3answers
264 views

What actually is a SQL clause?

This may sound like an overly simple question but I'm not finding it easy to locate a proper answer. To the question "What are sql clauses?" most of the resources on internet simply provide a list ...
1
vote
1answer
51 views

Does ANSI SQL standard inlcude FORALL predicate?

I need to get all predicate of SQL (that was included in ANSI standard any version). Some databases (Oracle) have FORALL predicate. But I am not sure if that is part of the SQL specifications. Does ...
22
votes
2answers
1k views

Where can I find the first standardization of SQL, SQL-86?

This question is different but similar to this request looking for SQL-89. The first draft of SQL is labeled SQL-86. Numerous references are made to it. Is this available to download? Wikipedia doesn'...
12
votes
1answer
1k views

Does the SQL Spec require a GROUP BY in EXISTS ()

Microsoft currently permits this syntax. SELECT * FROM ( VALUES (1) ) AS g(x) WHERE EXISTS ( SELECT * FROM ( VALUES (1),(1) ) AS t(x) WHERE g.x = t.x HAVING count(*) > 1 ); Notice ...
8
votes
2answers
587 views

Does SQL Server support custom DOMAINs?

PostgreSQL supports the DOMAIN specification, from SQL 2011 working draft spec, A domain is a named user-defined object that can be specified as an alternative to a data type in certain places ...
10
votes
2answers
2k views

Are there standard SQL functions with side effects?

Do the SQL standards define functions with side-effects? For example, do they have functions for writing into files* or to update values in certain columns of a table when you do something like ...
3
votes
1answer
755 views

Ansi SQL: Auto Numbered Column

Is there an ANSI standard for auto-numbered columns. Currently, we have a choice of SERIAL, AUTOINCREMENT, AUTO_INCREMENT, IDENTITY() and good old NEXTVAL() among others. I read somewhere that there ...
5
votes
1answer
2k views

Using a CREATE TABLE AS SELECT how do I specify a WITH condition (CTE)?

There is an old and deprecated command in PostgreSQL that predates CREATE TABLE AS SELECT (CTAS) called SELECT ... INTO .... FROM, it supports WITH clauses / Common Table Expressions (CTE). So, for ...
5
votes
2answers
97 views

How do i crack this non-ansi code blob

I've just started a new job about a month ago working for a software company that is really understaffed. I've been tasked with modernizing some of this old non-ansi SQL code and I'm having a ...
0
votes
2answers
82 views

Is it possible to reliably number rows in some order conforming to SQL standard?

For example, if I have a table with single column numbers and I want to 1) sort them in asc/desc order 2) (re) number them from 1 to n, using that order (so I can say that this number is ith biggest/...
2
votes
1answer
1k views

What does “ISO standard equivalent” actually mean?

I'm reading about comparison operators. In the documentation, Comparison Operators Modified by ANY, SOME, or ALL it says (emphasis added): Comparison operators that introduce a subquery can be ...
1
vote
1answer
32 views

Is the everything-is-arithmetic MySQL specific?

Today I learned that SELECT f1,EXISTS(SELECT ...),f2 is valid in MySQL and, reading from StackOverflow, apparently in PostgreSQL as well and the EXISTS part is 0 when the subquery result is empty and ...
3
votes
1answer
387 views

Is an anomaly possible if a serializable transaction interfers with another isolation level?

Let's assume transaction A runs in SERIALIZABLE and transaction B runs in a more permissive isolation level, like READ UNCOMMITTED. Can Transaction A now suffer from any anomaly introduced by ...
5
votes
2answers
3k views

Difference between “<> ANY(…)” and “NOT = ANY(…)”?

Could you please explain the difference between these two operators? sID != smth and not sID = smth. At a first glance they seem perfectly equal. But they give different results.