Comment 2 for bug 870046

And here is one with ORDER BY:

CREATE TABLE lineitem ( l_orderkey int NOT NULL , l_linenumber int NOT NULL , PRIMARY KEY (l_orderkey,l_linenumber)) ;
INSERT INTO lineitem VALUES (514,4),(582,3),(582,4);

CREATE TABLE orders ( o_orderkey int NOT NULL , o_custkey int, KEY (o_custkey), PRIMARY KEY (o_orderkey)) ;
INSERT INTO orders VALUES (484,7),(485,11),(486,7),(487,11),(512,7),(513,7),(514,8);

CREATE TABLE customer ( c_custkey int NOT NULL , c_nationkey int, c_acctbal double, KEY (c_nationkey), PRIMARY KEY (c_custkey)) ;
INSERT INTO customer VALUES (8,17,'6819.74'),(10,5,'2753.54');

CREATE TABLE nation ( n_nationkey int NOT NULL , PRIMARY KEY (n_nationkey)) ;
INSERT INTO nation VALUES (17),(19);

CREATE TABLE supplier ( s_nationkey int, KEY (s_nationkey)) ;
INSERT INTO supplier VALUES (17);

SET SESSION optimizer_switch='index_condition_pushdown=ON';
SELECT c_acctbal FROM lineitem JOIN orders ON ( l_orderkey = o_orderkey ) JOIN customer ON ( o_custkey = c_custkey ) JOIN nation ON ( c_nationkey = n_nationkey ) JOIN supplier ON ( s_nationkey = n_nationkey ) WHERE c_acctbal < 200 ORDER BY c_acctbal;