SQLite query works on android 4.4, but crashes on android 5.0

Question

The following SQLite query works fine on android 4.4 and below, but it causes an exception: "android.database.sqlite.SQLiteException: ambiguous column name: number (code 1):......" on android 5.0 and later. I checked the SQLite release documents, but did not see any changes that can effect my work. Is there any thing that I missing?

select * from 
(
    select 
        '0' as queryid,
        CNCT._id,
        coalesce(case when length(C.abId)=0 then null else C.abId end, 
        (
            select  
                addrBkId  from numbers as nm, 
                contacts as cot 
            where nm.number=coalesce(C.number,S.Number) and nm.contactID = cot._id 
            order by cot.lastMod desc limit 1)) as addrBkId, 
        coalesce(
            case when length(C.abId)=0 then null else C.abId end, 
            (
                select  
                    addrBkId  from numbers as nm, 
                    contacts as cot 
                where nm.number=coalesce(C.number,S.Number) and nm.contactID = cot._id 
                order by cot.lastMod desc 
                limit 1
            )
        ) as uqAddrBkId,
        CNCT.displayName,
        CNCT.firstName,
        CNCT.lastName,
        CNCT.favorite,
        coalesce(C.location, 
        (
            select 
                location from calls as css 
            where css.number = S.Number
        )) as location,
        0 as numberType,
        coalesce(C.number,S.Number) number, 
        N.txt,A.type,
        coalesce(A.callID,A.smsId) actId,
        max(A.startEpoch) as maa,
        max(A.startTime),
        strftime('%w',datetime(A.startEpoch,'unixepoch','localtime'))+0 dayOfWeek, 
        strftime('%W',datetime(A.startEpoch,'unixepoch','localtime'))+0 weekOfYear,C.duration, 
        case 
            when C.callResult='vmail' then 'vmail'||C._id 
            when C.callType='callin' and C.callResult='missed' then 'missed' 
            else C.callType end as newCallType, 
        C.callResult,
        C.extension,
        C.msgId,
        C.audioUrl, 
        C.name, 
        C.state,
        C.syncParams, 
        S.smsId,
        S.dir,
        S.state, 
        N.noteId,
        N.color from activity as A 
    left outer join calls C on A.callId=C.callId 
    left outer join sms S on A.smsId=S.smsId 
    left outer join contacts CNCT on coalesce(case when length(C.abId)=0 then null else C.abId end, 
        (
            select  addrBkId  from numbers as nm, 
                contacts as cot 
            where nm.number=coalesce(C.number,S.Number) and nm.contactID = cot._id 
            order by cot.updated desc 
            limit 1)
        )=CNCT.addrBkId 
        left outer join 
        (
            select * from notes as nt 
            order by nt.lastMod asc
        ) as N on CNCT.addrBkId=N.addrBkId 
    where (C.state<>5 or C.state is NULL) and (C.callResult<>'abandoned' or C.callResult is NULL) 
    group by newCallType,number,weekOfYear,dayOfWeek 
    order by max(A.startEpoch) asc
) 
group by _id 
order by maa desc 
limit 3

Logic check https://stackoverflow.com/questions/12662954/query-error-with-ambiguous-column-name-in-sql?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa — IntelliJ Amiya, Apr 25 '18 at 13:00

score 0 · Answer 1 · answered Apr 25 '18 at 19:43

... where nm.number=coalesce(C.number,S.Number) ...
... where nm.number=coalesce(C.number,S.Number) ...
... where css.number = S.Number) ...
... coalesce(C.number,S.Number) ...
... where nm.number=coalesce(C.number,S.Number) ...
... group by newCallType,number,...
                         ^^^^^^

All occurences of number are qualified with a table alias, except the last one. That one indeed is ambiguous.

SQLite query works on android 4.4, but crashes on android 5.0

1 Answers1