Prev Previous Post   Next Post Next
Old 15th January 2020, 09:30 PM
yannou yannou is offline
Join Date: Nov 2013
Posts: 69
Default Strange Behavior on Indices choice using current_date alias

Hello guys I found something strange in the index choice for some query that are using "current_date".

I did not test other alias.

For example we have this test table with 2 indices :

- 1 using int + date
- 1 using date only

/* TEST */
"Date_" DATE
CREATE INDEX "Index_INT_DATE" ON "TEST"("Int_", "Date_");

Let see what index are used when I query those :

SELECT * FROM "TEST" where int_ = 1 and date_ >= current_date ; // use Index_DATE => WRONG
SELECT * FROM "TEST" where int_ = 1 and date_ = current_date ; // use Index_INT_DATE => GOOD
SELECT * FROM "TEST" where int_ = 1 and date_ > DATE '2020-01-15'; // use Index_INT_DATE => GOOD

It seems using "Date_ > current_date" (or "Date_ < current_date") makes nexus use a "bad index" (not the best)

Querying "Date_> DATE '2020-01-15'" is working fine.

Hopes you understand


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Strange behavior in a Japanese environment Shinji Tomoyuki Binaries 1 27th October 2015 11:05 AM
Strange behavior at a client Arne De Herdt 1 27th April 2010 05:20 PM
Closest to current_date (Tricking the optimizer) =?ISO-8859-1?Q?Cl=E9ment_Doss?= 4 7th March 2010 04:41 AM
Strange behavior in string field Alvaro GP 5 15th May 2005 11:10 AM
strange server behavior Stéphane Thiers 14 2nd April 2004 11:40 PM

All times are GMT +11. The time now is 10:06 PM.

Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.