All Questions
Tagged with ansi-sql-standard or sql-standard
120 questions
0
votes
1
answer
52
views
ANSI SQL - Generate a sequence and get values
wondering if the ANSI SQL standard has a portable way to create a sequence object and get values from it?
I can't find a portable way, but search engines seem to confuse ANSI sql with MS SQL server, ...
- 121
1
vote
1
answer
62
views
Since SQL-92, what new kinds of RDBMS constraints have been invented and which RDBMS implement them?
Backstory: I'm having to do some computer archeology for a data integration project, involving getting data out a multiple-decades-old old Progress, now OpenEdge, database application for AS/400.
...
- 603
0
votes
1
answer
102
views
Is UPDATE FROM still not in the ANSI standard?
This article fascinated me it claims that FROM clauses in UPDATE are not part of the ANSI standard. Unfortunately, it is very old and I don't care to buy a copy of the latest ANSI standard for SQL. Is ...
- 801
0
votes
0
answers
123
views
Should/can COMMIT/ROLLBACK emit an error when no transaction is active?
What does ISO/IEC 9075-1:2023 standard say about COMMIT/ROLLBACK queries? Should (or can) they emit an error/exception when executed outside an active transaction?
I am asking because of https://jira....
- 418
0
votes
0
answers
110
views
Are there guarantees that ANY_VALUE in aggregation and HAVING will use the same value?
I would assume this to be the case, since it seems like an easy optimization and to make things more predictable. Given:
SELECT ANY_VALUE(country) AS country
FROM countries
HAVING ANY_VALUE(country) = ...
- 353
2
votes
1
answer
1k
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 ...
- 137
5
votes
1
answer
128
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.
...
- 152
6
votes
3
answers
976
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
406
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
1k
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
125
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
173
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
12
votes
3
answers
5k
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
106
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
4
votes
2
answers
3k
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 ...
- 143
3
votes
1
answer
3k
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 ...
- 133
1
vote
4
answers
3k
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
165
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
220
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--...
- 193
1
vote
0
answers
108
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
73
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
6k
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,692
0
votes
2
answers
2k
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,561
2
votes
1
answer
952
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
81
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
433
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' ...
- 11
0
votes
1
answer
602
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
154
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
152
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
85
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
2k
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,692
5
votes
1
answer
619
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 ...
- 674
0
votes
1
answer
208
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
3k
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,088
0
votes
2
answers
3k
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 ...
user4930
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
577
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 ...
- 64.7k
5
votes
2
answers
3k
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,919
4
votes
1
answer
1k
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 | ...
- 674
2
votes
0
answers
853
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 ...
- 674
7
votes
1
answer
4k
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 | ...
- 674
0
votes
1
answer
4k
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 | ...
- 674
-2
votes
1
answer
7k
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 ...
- 64.7k
2
votes
2
answers
2k
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:
...
- 674
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
4
votes
1
answer
2k
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 ...
- 64.7k
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?
- 64.7k
4
votes
1
answer
6k
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 ...
- 147