#1  
Old 15th January 2020, 10:30 PM
yannou yannou is offline
Member
 
Join Date: Nov 2013
Posts: 67
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 */
CREATE TABLE "TEST"
(
"Id" AUTOINC,
"Int_" SMALLINT,
"Date_" DATE
);
CREATE INDEX "Index_INT_DATE" ON "TEST"("Int_", "Date_");
CREATE INDEX "Index_DATE" ON "TEST"("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

Regards
  #2  
Old 31st January 2020, 01:09 AM
yannou yannou is offline
Member
 
Join Date: Nov 2013
Posts: 67
Default

Did you guy tested this ?

Should i post it in mantis ?

Regards
  #3  
Old 31st January 2020, 09:39 AM
Eivind Bakkestuen [NDD]
 
Posts: n/a
Default Re: Strange Behavior on Indices choice using current_date alias

> Should i post it in mantis ?

Please do.

--
Eivind Bakkestuen [NDD]


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 12:05 PM
Strange behavior at a client Arne De Herdt nexusdb.public.support 1 27th April 2010 06:20 PM
Closest to current_date (Tricking the optimizer) =?ISO-8859-1?Q?Cl=E9ment_Doss?= nexusdb.public.support.sql 4 7th March 2010 05:41 AM
Strange behavior in string field Alvaro GP nexusdb.public.support 5 15th May 2005 12:10 PM
strange server behavior Stéphane Thiers nexusdb.public.support 14 3rd April 2004 12:40 AM


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


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