DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=OmzH61K86iAv7P7gfd3vv/eDrPbek11ZigI0L9Qpj8PNJj5+T7tDWwh+vwMJNACXDV
	zqUeGeMQMiRM1QjbRRw7/09p/uaVQ/sxU3M8I63uj/gAhH6eIYHFJLCNb529KKgbavMm
	g6UVc7sMQRI8jdMn1p9GULrsqGeanu+/6kQjs=
MIME-Version: 1.0
Date: Thu, 12 May 2011 01:19:33 +0200
Message-ID: <BANLkTinuD-nSNJ0LVWaSgBNRGsYZ2QJYoQ@mail.gmail.com>
Subject: CROSS JOIN ordering
From: Grzegorz Szpetkowski <gszpetkowski@gmail.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

I am curious what is proper ordering for cross joining and joining at
all for two tables. I look at the example at
http://www.postgresql.org/docs/9.0/static/queries-table-expressions.html
and there is:

SELECT * FROM t1 CROSS JOIN t2;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   1 | a    |   3 | yyy
   1 | a    |   5 | zzz
   2 | b    |   1 | xxx
   2 | b    |   3 | yyy
   2 | b    |   5 | zzz
   3 | c    |   1 | xxx
   3 | c    |   3 | yyy
   3 | c    |   5 | zzz
(9 rows)

I thought that this should be rather:

   1 | a    |   1 | xxx
   2 | b    |   1 | xxx
   3 | c    |   1 | xxx
   1 | a    |   3 | yyy
   2 | b    |   3 | yyy
   2 | b    |   3 | yyy
   1 | a    |   5 | zzz
   2 | b    |   5 | zzz
   3 | c    |   5 | zzz

DROP TABLE t1, t2;
CREATE TABLE t1 (num int, char name);
CREATE TABLE t2 (num int, value varchar(3));
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO t2 VALUES (1, 'xxx'), (2, 'yyy'), (3, 'zzz');

SELECT * FROM t1 CROSS JOIN t2;
 num | char | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |   1 | xxx
   3 | c    |   1 | xxx
   1 | a    |   2 | yyy
   2 | b    |   2 | yyy
   3 | c    |   2 | yyy
   1 | a    |   3 | zzz
   2 | b    |   3 | zzz
   3 | c    |   3 | zzz
(9 rows)

or even (same data as in documentation):

DROP TABLE t1, t2;
CREATE TABLE t1 (num int, char name);
CREATE TABLE t2 (num int, value varchar(3));
INSERT INTO t1 VALUES (1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO t2 VALUES (1, 'xxx'), (3, 'yyy'), (5, 'zzz');

SELECT * FROM t1 CROSS JOIN t2;
 num | char | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |   1 | xxx
   3 | c    |   1 | xxx
   1 | a    |   3 | yyy
   2 | b    |   3 | yyy
   3 | c    |   3 | yyy
   1 | a    |   5 | zzz
   2 | b    |   5 | zzz
   3 | c    |   5 | zzz
(9 rows)

Is there any "proper", standard ordering that I can assume for sure ?
Maybe PostgreSQL 8.4/9.0 versions have strict ordering and older
versions are using mixed ordering depends on something I don't know (I
am just guessing).

Thanks in advance.

Regards,
G. Sz.