SQLite sub query with LIMIT not work in 2.3 device

Question

This query working 4.0 perfectly but not in 2.3 device, If i remove LIMIT clause and sub query than works perfect in 2.3 device

whats problem with LIMIT in 2.3 device ???

Tested same APK in 4 different device 2.3 show only one result not all and 4.0 works perfect

Here is my fiddle

SELECT * FROM (SELECT pk_video_master_id AS _id,    
video_title AS title1,
COUNT(fk_video_master_id) AS sub_title_1,   
SUM(video_detail_length) AS sub_title_2,
-1007 AS module 
FROM tbl_video_master 
LEFT JOIN tbl_video_detail ON fk_video_master_id = pk_video_master_id
WHERE title1 LIKE '%n%' GROUP BY _id LIMIT 3) 

UNION ALL 

SELECT * FROM (SELECT pk_surveys_id AS _id ,
surveys_title AS title1,
surveys_end_date AS sub_title_1,
COUNT(fk_survey_detail_survey_id) AS sub_title_2,
-1006 AS module
FROM tbl_surveys 
LEFT JOIN tbl_survey_detail ON fk_survey_detail_survey_id = pk_surveys_id 
WHERE title1 LIKE '%n%' GROUP BY _id LIMIT 5);

help me ... thanks

that is the question what is wrong on the 2.3 device? strange but fact i had tested twice by removing sub query with LIMIT its work perfect — MilapTank, Mar 28 '14 at 07:48
yes if you see fiddle in that result module column have 2 module -1007 and -1006 so in 2.3 its show only -1006 result not -1007 and 4.0 show all result there is no error but result in query not showing in 2.3 device — MilapTank, Mar 28 '14 at 07:51
Older Android versions may use older SQLite versions. Check here: http://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android?answertab=votes#tab-top — Jozua, Apr 14 '14 at 12:52
us this to get version of sqlite http://stackoverflow.com/a/3613043/1109425 — nandeesh, Apr 16 '14 at 17:01

Ersin Gülbahar · Answer 1 · 2014-04-17T05:05:36.337

0

You can try ROWID BETWEEN 1 AND 5 instead of LIMIT

OR

You can select -1006 and then you can select -1007 as well. So you can enforce the LIMIT by java not sql. I mean do not use union select both of records separately as two result set and then java make union results set as you want

edited Apr 17 '14 at 05:05

answered Apr 17 '14 at 05:00

Ersin Gülbahar

6,713
15
60
114

SQLite sub query with LIMIT not work in 2.3 device

1 Answers1