Add support for row value constructors and comparison operations [CORE3070]

[firebird-automations]

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;

[firebird-automations]

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;

[firebird-automations]

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.

[firebird-automations]

Commented by: @dyemanov

Sean, this syntax is absolutely standard. And I believe DB2 is not the only DBMS supporting it.

[firebird-automations]

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".

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

A review of the MS SQL and PostgresSQL documentation also finds that the proposed syntax is not documented.

[firebird-automations]

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

[firebird-automations]

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.

[firebird-automations]

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

[firebird-automations]

Modified by: @asfernandes

Link: This issue is related to CORE2621 [ CORE2621 ]

[firebird-automations]

Modified by: @dyemanov

Version: 3.0 Initial [ 10301 ] =>