Thread: BUG #5084: Query gives different number of rows depending on ORDER BY
BUG #5084: Query gives different number of rows depending on ORDER BY
From
"Bernt Marius Johnsen"
Date:
The following bug has been logged online:
Bug reference: 5084
Logged by: Bernt Marius Johnsen
Email address: bernt.johnsen@sun.com
PostgreSQL version: 8.3.7
Operating system: Linux 2.6.24-23-generic
Description: Query gives different number of rows depending on ORDER
BY
Details:
The following queries gives different number of rows (247 vs 260):
SELECT table1 . varchar_key AS field1 , table2 . varchar_key AS field2 ,
table2 . datetime_key AS field3 , table2 . int_key AS field4 , table2 . pk
AS field5 FROM ( C AS table1 INNER JOIN ( ( CC AS table2 LEFT JOIN C AS
table3 ON (( table3 .pk <= table2 . int_nokey ) AND (table3 .pk = table2 .
int_nokey ) ) ) ) ON (( table3 . varchar_key != table2 . varchar_key ) AND (
table3 . varchar_nokey <> table2 . varchar_key ) ) ) WHERE table1 .
varchar_key = table1 . varchar_key;
and
SELECT table1 . varchar_key AS field1 , table2 . varchar_key AS field2 ,
table2 . datetime_key AS field3 , table2 . int_key AS field4 , table2 . pk
AS field5 FROM ( C AS table1 INNER JOIN ( ( CC AS table2 LEFT JOIN C AS
table3 ON (( table3 .pk <= table2 . int_nokey ) AND (table3 .pk = table2 .
int_nokey ) ) ) ) ON (( table3 . varchar_key != table2 . varchar_key ) AND (
table3 . varchar_nokey <> table2 . varchar_key ) ) ) WHERE table1 .
varchar_key = table1 . varchar_key ORDER BY table1 . time_key DESC , field1
ASC , field4 ASC , field4 , table1 . int_key , table1 .pk DESC , ( table2 .
varchar_key || table1 . varchar_key ) , field1;
Dump of the database:
--
-- PostgreSQL database dump
--
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = public, pg_catalog;
--
-- Name: c_seq; Type: SEQUENCE; Schema: public; Owner: NNuser
--
CREATE SEQUENCE c_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE public.c_seq OWNER TO NNuser;
--
-- Name: c_seq; Type: SEQUENCE SET; Schema: public; Owner: NNuser
--
SELECT pg_catalog.setval('c_seq', 20, true);
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: c; Type: TABLE; Schema: public; Owner: NNuser; Tablespace:
--
CREATE TABLE c (
pk integer DEFAULT nextval('c_seq'::regclass) NOT NULL,
int_nokey integer,
int_key integer,
date_key date,
date_nokey date,
time_key time without time zone,
time_nokey time without time zone,
datetime_key timestamp without time zone,
datetime_nokey timestamp without time zone,
varchar_key character varying(1),
varchar_nokey character varying(1)
);
ALTER TABLE public.c OWNER TO NNuser;
--
-- Name: cc_seq; Type: SEQUENCE; Schema: public; Owner: NNuser
--
CREATE SEQUENCE cc_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE public.cc_seq OWNER TO NNuser;
--
-- Name: cc_seq; Type: SEQUENCE SET; Schema: public; Owner: NNuser
--
SELECT pg_catalog.setval('cc_seq', 29, true);
--
-- Name: cc; Type: TABLE; Schema: public; Owner: NNuser; Tablespace:
--
CREATE TABLE cc (
pk integer DEFAULT nextval('cc_seq'::regclass) NOT NULL,
int_nokey integer,
int_key integer,
date_key date,
date_nokey date,
time_key time without time zone,
time_nokey time without time zone,
datetime_key timestamp without time zone,
datetime_nokey timestamp without time zone,
varchar_key character varying(1),
varchar_nokey character varying(1)
);
ALTER TABLE public.cc OWNER TO NNuser;
--
-- Data for Name: c; Type: TABLE DATA; Schema: public; Owner: NNuser
--
COPY c (pk, int_nokey, int_key, date_key, date_nokey, time_key, time_nokey,
datetime_key, datetime_nokey, varchar_key, varchar_nokey) FROM stdin;
1 \N 2 \N \N 11:28:45 11:28:45 2004-10-11 18:13:16 2004-10-11 18:13:16 w w
2 7 9 2001-09-19 2001-09-19 20:25:14 20:25:14 \N \N m m
3 9 3 2004-09-12 2004-09-12 13:47:24 13:47:24 1900-01-01 00:00:00 1900-01-01
00:00:00 m m
4 7 9 \N \N 19:24:11 19:24:11 2009-07-25 00:00:00 2009-07-25 00:00:00 k k
5 4 \N 2002-07-19 2002-07-19 15:59:13 15:59:13 \N \N r r
6 2 9 2002-12-16 2002-12-16 00:00:00 00:00:00 2008-07-27 00:00:00 2008-07-27
00:00:00 t t
7 6 3 2006-02-08 2006-02-08 15:15:04 15:15:04 2002-11-13 16:37:31 2002-11-13
16:37:31 j j
8 8 8 2006-08-28 2006-08-28 11:32:06 11:32:06 1900-01-01 00:00:00 1900-01-01
00:00:00 u u
9 \N 8 2001-04-14 2001-04-14 18:32:33 18:32:33 2003-12-10
00:00:00 2003-12-10 00:00:00 h h
10 5 53 2000-01-05 2000-01-05 15:19:25 15:19:25 2001-12-21
22:38:22 2001-12-21 22:38:22 o o
11 \N 0 2003-12-06 2003-12-06 19:03:19 19:03:19 2008-12-13
23:16:44 2008-12-13 23:16:44 \N \N
12 6 5 1900-01-01 1900-01-01 00:39:46 00:39:46 2005-08-15
12:39:41 2005-08-15 12:39:41 k k
13 188 166 2002-11-27 2002-11-27 \N \N \N \N e e
14 2 3 \N \N 00:00:00 00:00:00 2006-09-11 12:06:14 2006-09-11 12:06:14 n n
15 1 0 2003-05-27 2003-05-27 13:12:11 13:12:11 2007-12-15
12:39:34 2007-12-15 12:39:34 t t
16 1 1 2005-05-03 2005-05-03 04:56:48 04:56:48 2005-08-09
00:00:00 2005-08-09 00:00:00 c c
17 0 9 2001-04-18 2001-04-18 19:56:05 19:56:05 2001-09-02
22:50:02 2001-09-02 22:50:02 m m
18 9 5 2005-12-27 2005-12-27 19:35:19 19:35:19 2005-12-16
22:58:11 2005-12-16 22:58:11 y y
19 \N 6 2004-08-20 2004-08-20 05:03:03 05:03:03 2007-04-19
00:19:53 2007-04-19 00:19:53 f f
20 4 2 1900-01-01 1900-01-01 18:38:59 18:38:59 1900-01-01
00:00:00 1900-01-01 00:00:00 d d
\.
--
-- Data for Name: cc; Type: TABLE DATA; Schema: public; Owner: NNuser
--
COPY cc (pk, int_nokey, int_key, date_key, date_nokey, time_key, time_nokey,
datetime_key, datetime_nokey, varchar_key, varchar_nokey) FROM stdin;
10 7 8 \N \N 01:27:35 01:27:35 2002-02-26 06:14:37 2002-02-26 06:14:37 v v
11 1 9 2006-06-14 2006-06-14 19:48:31 19:48:31 1900-01-01
00:00:00 1900-01-01 00:00:00 r r
12 5 9 2002-09-12 2002-09-12 00:00:00 00:00:00 2006-12-03
09:37:26 2006-12-03 09:37:26 a a
13 3 186 2005-02-15 2005-02-15 19:53:05 19:53:05 2008-05-26
12:27:10 2008-05-26 12:27:10 m m
14 6 \N \N \N 19:18:56 19:18:56 2004-12-14 16:37:30 2004-12-14 16:37:30 y y
15 92 2 2008-11-04 2008-11-04 10:55:12 10:55:12 2003-02-11
21:19:41 2003-02-11 21:19:41 j j
16 7 3 2004-09-04 2004-09-04 00:25:00 00:25:00 2009-10-18
02:27:49 2009-10-18 02:27:49 d d
17 \N 0 2006-06-05 2006-06-05 12:35:47 12:35:47 2000-09-26
07:45:57 2000-09-26 07:45:57 z z
18 3 133 1900-01-01 1900-01-01 19:53:03 19:53:03 \N \N e e
19 5 1 1900-01-01 1900-01-01 17:53:30 17:53:30 2005-11-10
12:40:29 2005-11-10 12:40:29 h h
20 1 8 1900-01-01 1900-01-01 11:35:49 11:35:49 2009-04-25
00:00:00 2009-04-25 00:00:00 b b
21 2 5 2005-01-13 2005-01-13 \N \N 2002-11-27 00:00:00 2002-11-27
00:00:00 s s
22 \N 5 2006-05-21 2006-05-21 06:01:40 06:01:40 2004-01-26
20:32:32 2004-01-26 20:32:32 e e
23 1 8 2003-09-08 2003-09-08 05:45:11 05:45:11 2007-10-26
11:41:40 2007-10-26 11:41:40 j j
24 0 6 2006-12-23 2006-12-23 00:00:00 00:00:00 2005-10-07
00:00:00 2005-10-07 00:00:00 e e
25 210 51 2006-10-15 2006-10-15 00:00:00 00:00:00 2000-07-15
05:00:34 2000-07-15 05:00:34 f f
26 8 4 2005-04-06 2005-04-06 06:11:01 06:11:01 2000-04-03
16:33:32 2000-04-03 16:33:32 v v
27 7 7 2008-04-07 2008-04-07 13:02:46 13:02:46 \N \N x x
28 5 6 2006-10-10 2006-10-10 21:44:25 21:44:25 2001-04-25
01:26:12 2001-04-25 01:26:12 m m
29 \N 4 1900-01-01 1900-01-01 22:43:58 22:43:58 2000-12-27
00:00:00 2000-12-27 00:00:00 c c
\.
--
-- Name: c_pkey; Type: CONSTRAINT; Schema: public; Owner: NNuser;
Tablespace:
--
ALTER TABLE ONLY c
ADD CONSTRAINT c_pkey PRIMARY KEY (pk);
--
-- Name: cc_pkey; Type: CONSTRAINT; Schema: public; Owner: NNuser;
Tablespace:
--
ALTER TABLE ONLY cc
ADD CONSTRAINT cc_pkey PRIMARY KEY (pk);
--
-- Name: c_date_key; Type: INDEX; Schema: public; Owner: NNuser; Tablespace:
--
CREATE INDEX c_date_key ON c USING btree (date_key);
--
-- Name: c_datetime_key; Type: INDEX; Schema: public; Owner: NNuser;
Tablespace:
--
CREATE INDEX c_datetime_key ON c USING btree (datetime_key);
--
-- Name: c_int_key; Type: INDEX; Schema: public; Owner: NNuser; Tablespace:
--
CREATE INDEX c_int_key ON c USING btree (int_key);
--
-- Name: c_time_key; Type: INDEX; Schema: public; Owner: NNuser; Tablespace:
--
CREATE INDEX c_time_key ON c USING btree (time_key);
--
-- Name: c_varchar_key; Type: INDEX; Schema: public; Owner: NNuser;
Tablespace:
--
CREATE INDEX c_varchar_key ON c USING btree (varchar_key, int_key);
--
-- Name: cc_date_key; Type: INDEX; Schema: public; Owner: NNuser;
Tablespace:
--
CREATE INDEX cc_date_key ON cc USING btree (date_key);
--
-- Name: cc_datetime_key; Type: INDEX; Schema: public; Owner: NNuser;
Tablespace:
--
CREATE INDEX cc_datetime_key ON cc USING btree (datetime_key);
--
-- Name: cc_int_key; Type: INDEX; Schema: public; Owner: NNuser; Tablespace:
--
CREATE INDEX cc_int_key ON cc USING btree (int_key);
--
-- Name: cc_time_key; Type: INDEX; Schema: public; Owner: NNuser;
Tablespace:
--
CREATE INDEX cc_time_key ON cc USING btree (time_key);
--
-- Name: cc_varchar_key; Type: INDEX; Schema: public; Owner: NNuser;
Tablespace:
--
CREATE INDEX cc_varchar_key ON cc USING btree (varchar_key, int_key);
--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
--
-- PostgreSQL database dump complete
--
Re: BUG #5084: Query gives different number of rows depending on ORDER BY
From
Heikki Linnakangas
Date:
Bernt Marius Johnsen wrote: > Dump of the database: It looks like the dump got word-wrapped somewhere along the way. Could you gzip it and post it again, please? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Re: BUG #5084: Query gives different number of rows depending on ORDER BY
From
"Bernt M. Johnsen"
Date:
>>>>>>>>>>>> Heikki Linnakangas wrote (2009-09-28 18:10:07): > Bernt Marius Johnsen wrote: > > Dump of the database: Attached. > > It looks like the dump got word-wrapped somewhere along the way. Could > you gzip it and post it again, please? > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com
Attachment
> Bernt Marius Johnsen wrote:
>> Dump of the database:
To save anyone else the bother, there's a VASTLY simpler testcase for
this one, requiring no tables at all:
test1=# explain select * from (values (1),(null)) v(k) where k = k order by k;
QUERY PLAN
-------------------------------------------------------------------
Sort (cost=0.04..0.04 rows=2 width=4)
Sort Key: "*VALUES*".column1
-> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=4)
(3 rows)
test1=# explain select * from (values (1),(null)) v(k) where k = k;
QUERY PLAN
-------------------------------------------------------------
Values Scan on "*VALUES*" (cost=0.00..0.03 rows=1 width=4)
Filter: (column1 = column1)
(2 rows)
Notice that the (k = k) qual is being dropped somewhere, which changes
the output since that's a disguised not-null condition.
--
Andrew (irc:RhodiumToad)
Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> Notice that the (k = k) qual is being dropped somewhere, which changes
> the output since that's a disguised not-null condition.
Huh ... I'm surprised it only does that with the ORDER BY present.
I suppose it's got something to do with the equivalence-class machinery.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
>> Notice that the (k = k) qual is being dropped somewhere, which changes
>> the output since that's a disguised not-null condition.
> Huh ... I'm surprised it only does that with the ORDER BY present.
> I suppose it's got something to do with the equivalence-class machinery.
After digging into it, I find that:
1. Without ORDER BY, process_equivalence generates an equivalence class
that lists k twice. This is pretty bogus but it happens to produce the
desired results in the example at hand. (In some other cases you'll get
redundant clauses out, because the eclass machinery isn't expecting
this.)
2. With ORDER BY k, the code first creates a single-element equivalence
class containing k, because it needs that to represent the desired
pathkey. Then, process_equivalence finds that both sides of the k = k
clause are already known to be in the same eclass, so it concludes that
this is redundant information.
I'm inclined to think that the best solution is to have
process_equivalence just reject any clauses that have equal() left and
right sides, ie, throw them back to be processed as ordinary
non-equivalence clauses. The only case I can think of where this might
be less than ideal is if you have "k = k AND k = x"; if both operators
are strict then the k = k test is indeed redundant and could be
discarded, but it won't be. But it doesn't seem like that's going to
come up enough to be worth stressing about. If we wanted to be smart
about it we'd have to have two kinds of single-element equivalence
classes (one that implies a k = k check is needed, and one that does
not). It doesn't seem worth the complication.
regards, tom lane
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes: Tom> After digging into it, I find that: Tom> 1. Without ORDER BY, process_equivalence generates an Tom> equivalence class that lists k twice. This is pretty bogus but Tom> it happens to produce the desired results in the example at Tom> hand. (In some other cases you'll get redundant clauses out, Tom> because the eclass machinery isn't expecting this.) Tom> 2. With ORDER BY k, the code first creates a single-element Tom> equivalence class containing k, because it needs that to Tom> represent the desired pathkey. Then, process_equivalence finds Tom> that both sides of the k = k clause are already known to be in Tom> the same eclass, so it concludes that this is redundant Tom> information. I'd found the right place in the code, but I hadn't twigged that the ORDER BY one was being called _first_, so I hadn't spotted the bug yet. Tom> I'm inclined to think that the best solution is to have Tom> process_equivalence just reject any clauses that have equal() Tom> left and right sides, ie, throw them back to be processed as Tom> ordinary non-equivalence clauses. The only case I can think of Tom> where this might be less than ideal is if you have "k = k AND k Tom> = x"; if both operators are strict then the k = k test is indeed Tom> redundant and could be discarded, but it won't be. But it Tom> doesn't seem like that's going to come up enough to be worth Tom> stressing about. If we wanted to be smart about it we'd have to Tom> have two kinds of single-element equivalence classes (one that Tom> implies a k = k check is needed, and one that does not). It Tom> doesn't seem worth the complication. Hmm. Is it ever possible for mergejoinable operators to be non-strict? Does that matter? -- Andrew (irc:RhodiumToad)
Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
> Tom> I'm inclined to think that the best solution is to have
> Tom> process_equivalence just reject any clauses that have equal()
> Tom> left and right sides, ie, throw them back to be processed as
> Tom> ordinary non-equivalence clauses.
> Hmm. Is it ever possible for mergejoinable operators to be non-strict?
> Does that matter?
I'm not sure. ISTR that nodeMergejoin makes some effort to support such
operators, but the btree code doesn't really. In any case, it doesn't
matter. Leaving the clause out of the equivalence machinery is
certainly safe; at worst we'll end up with a redundant test or two in
the final plan.
regards, tom lane
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes: Tom> I'm inclined to think that the best solution is to have Tom> process_equivalence just reject any clauses that have equal() Tom> left and right sides, ie, throw them back to be processed as Tom> ordinary non-equivalence clauses. >> Hmm. Is it ever possible for mergejoinable operators to be >> non-strict? Does that matter? Tom> I'm not sure. ISTR that nodeMergejoin makes some effort to Tom> support such operators, but the btree code doesn't really. In Tom> any case, it doesn't matter. Leaving the clause out of the Tom> equivalence machinery is certainly safe; at worst we'll end up Tom> with a redundant test or two in the final plan. Yeah, and clearly leaving in that kind of redundant test is no big deal. -- Andrew (irc:RhodiumToad)
"Bernt Marius Johnsen" <bernt.johnsen@sun.com> writes:
> Description: Query gives different number of rows depending on ORDER
> BY
The attached patch should fix this.
regards, tom lane
Index: src/backend/optimizer/README
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/README,v
retrieving revision 1.41
diff -c -r1.41 README
*** src/backend/optimizer/README 26 Oct 2007 18:10:50 -0000 1.41
--- src/backend/optimizer/README 29 Sep 2009 01:01:55 -0000
***************
*** 467,473 ****
get into EquivalenceClasses otherwise. Aggregates are disallowed in WHERE
altogether, so will never be found in a mergejoinable clause.) This is just
a convenience to maintain a uniform PathKey representation: such an
! EquivalenceClass will never be merged with any other.
An EquivalenceClass also contains a list of btree opfamily OIDs, which
determines what the equalities it represents actually "mean". All the
--- 467,476 ----
get into EquivalenceClasses otherwise. Aggregates are disallowed in WHERE
altogether, so will never be found in a mergejoinable clause.) This is just
a convenience to maintain a uniform PathKey representation: such an
! EquivalenceClass will never be merged with any other. Note in particular
! that a single-item EquivalenceClass {a.x} is *not* meant to imply an
! assertion that a.x = a.x; the practical effect of this is that a.x could
! be NULL.
An EquivalenceClass also contains a list of btree opfamily OIDs, which
determines what the equalities it represents actually "mean". All the
Index: src/backend/optimizer/path/equivclass.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/equivclass.c,v
retrieving revision 1.9.2.2
diff -c -r1.9.2.2 equivclass.c
*** src/backend/optimizer/path/equivclass.c 1 Dec 2008 21:06:20 -0000 1.9.2.2
--- src/backend/optimizer/path/equivclass.c 29 Sep 2009 01:01:55 -0000
***************
*** 114,119 ****
--- 114,132 ----
item2_relids = restrictinfo->right_relids;
/*
+ * Reject clauses of the form X=X. These are not as redundant as they
+ * might seem at first glance: assuming the operator is strict, this is
+ * really an expensive way to write X IS NOT NULL. So we must not risk
+ * just losing the clause, which would be possible if there is already
+ * a single-element EquivalenceClass containing X. The case is not
+ * common enough to be worth contorting the EC machinery for, so just
+ * reject the clause and let it be processed as a normal restriction
+ * clause.
+ */
+ if (equal(item1, item2))
+ return false; /* X=X is not a useful equivalence */
+
+ /*
* If below outer join, check for strictness, else reject.
*/
if (below_outer_join)
***************
*** 151,163 ****
*
* 4. We find neither. Make a new, two-entry EC.
*
! * Note: since all ECs are built through this process, it's impossible
! * that we'd match an item in more than one existing EC. It is possible
! * to match more than once within an EC, if someone fed us something silly
! * like "WHERE X=X". (However, we can't simply discard such clauses,
! * since they should fail when X is null; so we will build a 2-member EC
! * to ensure the correct restriction clause gets generated. Hence there
! * is no shortcut here for item1 and item2 equal.)
*/
ec1 = ec2 = NULL;
em1 = em2 = NULL;
--- 164,173 ----
*
* 4. We find neither. Make a new, two-entry EC.
*
! * Note: since all ECs are built through this process or the similar
! * search in get_eclass_for_sort_expr(), it's impossible that we'd match
! * an item in more than one existing nonvolatile EC. So it's okay to stop
! * at the first match.
*/
ec1 = ec2 = NULL;
em1 = em2 = NULL;
Index: src/test/regress/expected/select.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/select.out,v
retrieving revision 1.18
diff -c -r1.18 select.out
*** src/test/regress/expected/select.out 7 Jul 2007 20:46:45 -0000 1.18
--- src/test/regress/expected/select.out 29 Sep 2009 01:01:55 -0000
***************
*** 768,770 ****
--- 768,786 ----
(4 rows)
drop function sillysrf(int);
+ -- X = X isn't a no-op, it's effectively X IS NOT NULL assuming = is strict
+ -- (see bug #5084)
+ select * from (values (2),(null),(1)) v(k) where k = k order by k;
+ k
+ ---
+ 1
+ 2
+ (2 rows)
+
+ select * from (values (2),(null),(1)) v(k) where k = k;
+ k
+ ---
+ 2
+ 1
+ (2 rows)
+
Index: src/test/regress/sql/select.sql
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/sql/select.sql,v
retrieving revision 1.14
diff -c -r1.14 select.sql
*** src/test/regress/sql/select.sql 7 Jul 2007 20:46:45 -0000 1.14
--- src/test/regress/sql/select.sql 29 Sep 2009 01:01:55 -0000
***************
*** 202,204 ****
--- 202,209 ----
select sillysrf(-1) order by 1;
drop function sillysrf(int);
+
+ -- X = X isn't a no-op, it's effectively X IS NOT NULL assuming = is strict
+ -- (see bug #5084)
+ select * from (values (2),(null),(1)) v(k) where k = k order by k;
+ select * from (values (2),(null),(1)) v(k) where k = k;