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.
115
questions
2
votes
1
answer
484
views
Why are backslashes doubled in SQL's string encoding?
The Postgres docs state:
While the standard syntax for specifying string constants is usually convenient, it can be difficult to understand when the desired string contains many single quotes or ...
- 125
4
votes
1
answer
89
views
What are these single letter SQL keywords?
I am looking at list of SQL keywords here https://www.postgresql.org/docs/current/sql-keywords-appendix.html
There are some single letter keywords there. I cannot find any information about them.
...
- 142
6
votes
3
answers
767
views
Why are unquoted identifiers upper-cased per SQL-92?
Section 5.6 of the SQL-92 standard contains rules 10...13 per which unquoted identifiers should be upper-cased, so foo becomes FOO but "foo" remains foo.
These rules are respected by Oracle, ...
- 161
1
vote
2
answers
141
views
What is the equivalent of LIKE with wildcard the end 'abc%' using greater/less than operators?
I just found out that some specific patterns when used with LIKE text operator uses BTREE indexes to speed up queries, such as:
'abc123' (text with no wildcard)
'abc%' (text with only one wildcard in ...
0
votes
1
answer
491
views
What is the standard SQL way to rename a column?
So far I've seen ALTER TABLE RENAME, ALTER TABLE CHANGE and ALTER TABLE MODIFY.
What I'm trying to achieve is something like ALTER TABLE my_table RENAME col_old_name TO col_new_name, which can be a ...
- 125
1
vote
2
answers
77
views
Efficient generation of quantiles for indexed column
I am trying to build a query partitioner for a distributed processing system. This system would work with databases that support JDBC.
The input of this partitioner would be the name of a table, and a ...
- 113
0
votes
1
answer
42
views
What is the formal SQL:2016 definition of a SQL identifier?
What is the formal definition of a SQL identifier in the ISO/IEC 9075:2016 standard?
Most specifically, in the case of both regular and delimited identifiers, what limitations exist on:
Character set ...
- 101
11
votes
3
answers
3k
views
Is MySQL breaking the standard by allowing selecting columns that are not part of the group by clause?
I am used to Microsoft technologies including SQL Server. Today I ran across a Q&A where the following passage from the MySQL documentation was quoted:
Standard SQL would reject your query ...
0
votes
0
answers
50
views
Information schema standard - where can I find more information?
Wikipedia says that information schema is an ANSI-standard and I found many implementations of it but I can't find a paper by ANSI that describes it. Can you help me to find out more about it?
- 11
3
votes
2
answers
2k
views
Why database has char data type when varchar is more efficient?
I understand the basic difference between CHAR and VARCHAR datatype that CHAR takes up fixed length whereas VARCHAR takes up space based on the content being stored.
But if VARCHAR is so efficient in ...
- 133
1
vote
1
answer
730
views
Getting the row before a row with a certain value in SQL
I have a table like below where user actions are stored with a timestamp. My goal is to identify the action that happened before a specific action (named reference_action) and count the number of ...
- 113
1
vote
4
answers
2k
views
Why does FROM multiple tables default to cartesian product?
What was the idea behind doing a cartesian product (cross join) when i did a query like -
SELECT * FROM agents, orders
I would think that they would concatenate (like pandas). It feels more natural ...
- 129
0
votes
1
answer
113
views
PostgreSQL claims to support 170 of 179 SQL:2016 features, but it also lists 309 features of SQL:2016 that it doesn't support. Why the discrepancy?
As of September 2020, PostgreSQL claims to support most of the SQL:2016 standard, but the numbers in don't match up at all. From PostgreSQL's documentation:
As of the version 13 release in September ...
- 105
0
votes
1
answer
162
views
What is the history of the SQL Server ANSI (ISO) compatibility settings?
I know what these settings do and when to use them, what I'd really like to know is why this problem even exists.
Recently, I came across an ugly situation--make that, yet another ugly situation--...
- 183
1
vote
0
answers
82
views
Composed primary keys : "should" duplicates and null values be accepted, and according to which norm?
I just passed some SQL test including a question about the possibility to have duplicates in a primary key column.
The expected answer was NO. Mine is, "Yes, if the column is part of a composed ...
- 131
0
votes
1
answer
48
views
Correct table quoting
I want to generically use any SQL database(I'm using jdbc, it works if the driver and the URL are provided).
It seems, that different DBMS use different quoting for table names.
For example, I have ...
- 103
0
votes
1
answer
4k
views
Is the double dollar sign ($$) quotation method in SQL standard?
In PostgreSQL, strings can be quoted very flexibly using a pair of dollar signs ($) with a tag in between. For example,
SELECT $f$ Hi $f$;
Is this quoting mechanism a part of any SQL standard, or ...
- 3,187
0
votes
2
answers
1k
views
Same query, different result (depending on user's permissions)
A question about SQL in general:
If two users execute the same sql statement, is it possible that user1 gets different results than user2?
Imagine user1 has more permissions than user2.
With other ...
- 1,371
2
votes
1
answer
779
views
How do I convert a BIGINT to a DATETIME in Informix 12.1
I'm new to Informix, and I'm trying to convert a bigint to a datetime.
SELECT sdatetime
FROM CallDetail;
Results:
sdatetime
----------
1572509662678
1572518550704
1572519033540
This seems to be the ...
- 123
2
votes
1
answer
61
views
Does the ANSI SQL Standard allow filters or parameters in JOIN conditions?
Does the ANSI Standard find the following SQL sinppet syntactically correct? I am interested specifically in the last line:
SELECT name, dept_name
FROM employee JOIN department
ON employee....
- 237
0
votes
0
answers
312
views
DBCC Command Not being run by current user
I have a stored Proc that runs a DBCC command CHECKIDENT
The sproc is being run by a user with adequate permissions.
however, it is failing on the DBCC command with the error.
User 'DifferentUser' ...
- 21
0
votes
1
answer
420
views
How can I reference column aliases from a projection inside the projection?
Calculations on the projection (select) values are useful and common. Yet column aliases declared in the projection can't be used there.
select sellprice as x, cost as y, x/y as markup
from ...
- 103
2
votes
1
answer
58
views
Why pad instead of trim when comparing strings?
The ANSI/SQL-92 standard states that strings have to be padded to the same length when compared:
3) The comparison of two character strings is determined as fol-
lows:
a) If the ...
- 123
1
vote
0
answers
53
views
Why can you specify CASCADE after DROP but not after DELETE? [closed]
What is the reasoning behind the design that you can specify CASCADE after a DROP query (DML) like DROP users CASCADE; but not in a DELETE query (DDL) like DELETE FROM users WHERE user_id = 2 CASCADE;?...
- 119
1
vote
1
answer
64
views
What are some technical reasons for not including N-ary multiplication in the SQL standard? [closed]
If I want to add up two values in a query, I can use the + operator.
If I want to sum up all values returned by a query, I can use SUM.
If I want to multiply two values in a query, I can use the * ...
- 111
5
votes
1
answer
867
views
When is the AS keyword required?
In the following statement (PostgreSQL 11):
=> SELECT c cost FROM tt;
ERROR: syntax error at or near "cost"
LINE 1: SELECT c cost FROM tt;
I get an error. Adding parenthesis around the field ...
- 3,187
4
votes
1
answer
530
views
Standard Behaviour for mix of 'ON-DELETE-CASCADE' and 'ON-DELETE-RESTRICT' constraints in Mysql
In mysql 5.6, Consider these 2 examples creating relationships between A, B, C and D.
Example 1
CREATE TABLE `a` (
id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;
CREATE TABLE ...
- 634
0
votes
1
answer
166
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
1
answer
2k
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
...
- 1,068
0
votes
2
answers
2k
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?
- 143
9
votes
3
answers
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
4
answers
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 ...
- 303
1
vote
1
answer
422
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 ...
- 60.4k
5
votes
2
answers
2k
views
Is there a function which the opposite of NULLIF?
I am aware of the NULLIF function which compares two values and returns NULL if they match.
Is there a function which will compare two values and return NULL if they don’t match? That is filter out ...
- 2,579
4
votes
1
answer
885
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 | ...
- 634
1
vote
0
answers
612
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 ...
- 634
7
votes
1
answer
3k
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 | ...
- 634
0
votes
1
answer
3k
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 | ...
- 634
-2
votes
1
answer
5k
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 ...
- 60.4k
2
votes
2
answers
1k
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:
...
- 634
7
votes
3
answers
4k
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 ...
- 191
3
votes
1
answer
1k
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 ...
- 60.4k
1
vote
2
answers
1k
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 ...
- 113
0
votes
2
answers
1k
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?
- 60.4k
3
votes
1
answer
5k
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 ...
- 137
0
votes
2
answers
509
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
|...
- 281
1
vote
1
answer
379
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?
- 60.4k
9
votes
3
answers
1k
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?
...
- 60.4k
7
votes
1
answer
2k
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
...
- 181
2
votes
1
answer
251
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 <...
- 10.1k