VistaDB Support Center

Start a new topic

VistaDB 5.1 Beta - Date Queries Broken

I think I found an issue in VistaDB 5.1 Beta (5.0.9.1345).


When I do a query involving multiple dates in the Where clause zero results are being returned instead of the expected result.


 

-- result = 2087
SELECT Count(t1.StudentId)
FROM [tblTest] t1
INNER JOIN [tblEnrollment] t2 ON t1.[StudentID] = t2.[StudentID]

-- result = 2011
SELECT Count(t1.[StudentId])
FROM [tblTest] t1
INNER JOIN [tblEnrollment] t2 ON t1.[StudentID] = t2.[StudentID]
WHERE t1.[TimeTested] >= t2.[DateStart]

-- result = 2087
SELECT Count(t1.[StudentId])
FROM [tblTest] t1
INNER JOIN [tblEnrollment] t2 ON t1.[StudentID] = t2.[StudentID]
WHERE t1.[TimeTested] <= t2.[DateEnd]

-- result = 2011
SELECT Count(t1.[StudentId])
FROM [tblTest] t1
INNER JOIN [tblEnrollment] t2 ON t1.[StudentID] = t2.[StudentID]
WHERE t1.[TimeTested] >= t2.[DateStart] AND t1.[TimeTested] <= t2.[DateEnd]

-- result = 2011
SELECT Count(t1.[StudentId])
FROM [tblTest] t1
INNER JOIN [tblEnrollment] t2 ON t1.[StudentID] = t2.[StudentID]
WHERE t1.[TimeTested] BETWEEN t2.[DateStart] AND t2.[DateEnd]

-- result = 0, expected 2011
SELECT Count(t1.[StudentId])
FROM [tblTest] t1
INNER JOIN [tblEnrollment] t2 ON t1.[StudentID] = t2.[StudentID]
WHERE t1.[TimeTested] >= t2.[DateStart] OR (t1.[TimeTested] BETWEEN t2.[DateStart] AND t2.[DateEnd])

-- result = 0, expected 2011
SELECT Count(t1.[StudentId])
FROM [tblTest] t1
INNER JOIN [tblEnrollment] t2 ON t1.[StudentID] = t2.[StudentID]
WHERE t1.[TimeTested] >= t2.[DateStart] OR  (t1.[TimeTested] >= t2.[DateStart] AND t1.[TimeTested] <= t2.[DateEnd])

 


The same queries run under VistaDB 5.0.5.1338 return the correct results.


The attached file has the queries above plus example database.

zip

Thanks for reporting that!  We'll dig into it to get it resolved as soon as we can.

For the public forum - we've sent Chris a hotfix and are going to roll out a Beta 2 of 5.1 with this fix and other issues.  As always, thanks for folks using our beta versions!

Login to post a comment