[postgres-xl-bugs] No Rows When Order By

Kumar, Virendra Virendra.Kumar at guycarp.com
Thu Jun 28 12:17:12 PDT 2018


Hi Community,

I feel like no one is actually monitoring or looking at these emails from PG-XL community. I have not got any response to my earlier questions may be somebody can look into this one. I have PG-XL installed from 9.5_STABLE version with postgis extension on top of it. When we run a simple query like below:
--
sit_db=# select se.site_id, se.full_address
sit_db-# from zz.xxxx se
sit_db-# where ST_DWithin(se.shape_geog, ST_GeomFromText('POINT(-74.028349 40.737980)', 4326)::geography,1609 * 1)
sit_db-# and se.portfolio_id=1;
site_id |                          full_address
---------+-----------------------------------------------------------------
   93357 | 1201 zz
  121936 | 811 yy

Explain plan:
--
--------------------------------------------------------------------------------------------
Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn_25v0091_1, dn_25v0091_2, dn_25v0092_1, dn_25v0092_2, dn_25v0093_1, dn_25v0093_2, dn_25v0094_1, dn_25v0094_2
   ->  Index Scan using xxxx_shape_geog_idx on xxxx se  (cost=0.28..4.67 rows=1 width=59)
         Index Cond: (shape_geog && '0101000020E6100000F5F75278D08152C0118DEE20765E4440'::geography)
         Filter: ((portfolio_id = 1) AND ('0101000020E6100000F5F75278D08152C0118DEE20765E4440'::geography && _st_expand(shape_geog, '1609'::double precision)) AND _st_dwithin(shape_geog, '01010
00020E6100000F5F75278D08152C0118DEE20765E4440'::geography, '1609'::double precision, true))


But when I run the same query with an order by guess what, no rows selected:
sit_db=# select se.site_id, se.full_address
sit_db-# from zz.xxxx se
sit_db-# where ST_DWithin(se.shape_geog, ST_GeomFromText('POINT(-74.028349 40.737980)', 4326)::geography,1609 * 1)
sit_db-# and se.portfolio_id=1
sit_db-# order by se.site_id;
site_id | full_address
---------+--------------
(0 rows)

--------------------------------------------------------------------------------------------------
Remote Subquery Scan on all (dn_25v0091_1,dn_25v0091_2,dn_25v0092_1,dn_25v0092_2,dn_25v0093_1,dn_25v0093_2,dn_25v0094_1,dn_25v0094_2)  (cost=4.69..4.69 rows=1 width=59)
   ->  Sort  (cost=4.69..4.69 rows=1 width=59)
         Sort Key: site_id
         ->  Index Scan using xxxx_p1_shape_geog_idx on xxxx se  (cost=0.28..4.68 rows=1 width=59)
               Index Cond: (shape_geog && '0101000020E6100000F5F75278D08152C0118DEE20765E4440'::geography)
               Filter: ((portfolio_id = 1) AND ('0101000020E6100000F5F75278D08152C0118DEE20765E4440'::geography && _st_expand(shape_geog, '1609'::double precision)) AND _st_dwithin(shape_geog,
'0101000020E6100000F5F75278D08152C0118DEE20765E4440'::geography, '1609'::double precision, true))
(6 rows)

Hopefully somebody can help me out here.


Regards,
Virendra

________________________________

This message is intended only for the use of the addressee and may contain
information that is PRIVILEGED AND CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified that any
dissemination of this communication is strictly prohibited. If you have
received this communication in error, please erase all copies of the message
and its attachments and notify the sender immediately. Thank you.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/pipermail/postgres-xl-bugs-postgres-xl.org/attachments/20180628/573b39f7/attachment.html>


More information about the postgres-xl-bugs mailing list