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 \) 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 \) 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](https://github.com/FirebirdSQL/firebird/issues?q=CORE2621+in%3Atitle) \[ [CORE2621](https://github.com/FirebirdSQL/firebird/issues?q=CORE2621+in%3Atitle) \]

[firebird-automations]

Modified by: @dyemanov

Version: 3\.0 Initial \[ 10301 \] =\>