firebird
firebird copied to clipboard
FirebirdSQL
Add support for row value constructors and comparison operations [CORE3070]
Submitted by: Alexey Kuznetsov (kuaw26)
Is related to CORE2621
Votes: 2
It would be very useful if Firebird would support modern syntax for A=v1 AND B=v2 predicates as it implemented in DB2 (see Db2 cookbook p39 http://mysite.verizon.net/graeme_birchall/id1.html)
example: modern syntax SELECT id, dept, job FROM staff WHERE (id,dept) = (30,28) OR (id,years) = (90, 7) OR (dept,job) = (38,'Mgr') ORDER BY 1;
old syntax: SELECT id, dept, job FROM staff WHERE (id = 30 AND dept = 28) OR (id = 90 AND years = 7) OR (dept = 38 AND job = 'Mgr') ORDER BY 1;
Modified by: Alexey Kuznetsov (kuaw26)
description: It would be very useful if Firibird would support modern syntax for A=v1 AND B=v2 predicates as it implemented in DB2 (see Db2 cookbook p39 http://mysite.verizon.net/graeme_birchall/id1.html)
example: modern syntax SELECT id, dept, job FROM staff WHERE (id,dept) = (30,28) OR (id,years) = (90, 7) OR (dept,job) = (38,'Mgr') ORDER BY 1;
old syntax: SELECT id, dept, job FROM staff WHERE (id = 30 AND dept = 28) OR (id = 90 AND years = 7) OR (dept = 38 AND job = 'Mgr') ORDER BY 1;
=>
It would be very useful if Firebird would support modern syntax for A=v1 AND B=v2 predicates as it implemented in DB2 (see Db2 cookbook p39 http://mysite.verizon.net/graeme_birchall/id1.html)
example: modern syntax SELECT id, dept, job FROM staff WHERE (id,dept) = (30,28) OR (id,years) = (90, 7) OR (dept,job) = (38,'Mgr') ORDER BY 1;
old syntax: SELECT id, dept, job FROM staff WHERE (id = 30 AND dept = 28) OR (id = 90 AND years = 7) OR (dept = 38 AND job = 'Mgr') ORDER BY 1;
Commented by: Sean Leyne (seanleyne)
Why would this syntax be "usefull"? (I will leave the discussion of the "modern"ess [Not!] of the syntax alone for the moment)
The only use I see is to allow DB2 users to migrate their "odd" syntax to Firebird.
Commented by: @dyemanov
Sean, this syntax is absolutely standard. And I believe DB2 is not the only DBMS supporting it.
Commented by: Sean Leyne (seanleyne)
FYI, the syntax is not documented in the official IBM DB2 documentation for the latest v9.8 DB2 release (http://publib.boulder.ibm.com/infocenter/db2luw/v9r8/index.jsp), so forgive me if I don't think of the syntax as being "standard".
Commented by: Sean Leyne (seanleyne)
A review of the MS SQL and PostgresSQL documentation also finds that the proposed syntax is not documented.
Commented by: @dyemanov
http://www.postgresql.org/docs/8.4/static/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS http://www.postgresql.org/docs/8.4/static/functions-comparisons.html#ROW-WISE-COMPARISON
Commented by: Sean Leyne (seanleyne)
It would seem appropriate for the subject of this case/entry to be changed to read "Add support for ROW() constructor/comparison syntax", since the ROW() functionality is much broader than just a "short syntax" implementation.
Modified by: @dyemanov
summary: Support DB2-like short syntax for multiple A=v1 and B=v2 and ... predicates => Add support for row value constructors and comparison operations