IllegalArgumentException: Invalid column DISTINCT bucket_display_name

Question

I'm retrieving list of distinct folders list having video files with number of videos in each folder, and this is working fine in devices having Android P and below, but when I run on devices having Android Q the app crashes. How can I make it work for devices running Android Q

java.lang.IllegalArgumentException: Invalid column DISTINCT bucket_display_name

Logcat:

java.lang.IllegalArgumentException: Invalid column DISTINCT bucket_display_name
        at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:170)
        at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:140)
        at android.content.ContentProviderProxy.query(ContentProviderNative.java:423)
        at android.content.ContentResolver.query(ContentResolver.java:944)
        at android.content.ContentResolver.query(ContentResolver.java:880)
        at android.content.ContentResolver.query(ContentResolver.java:836)
        at com.aisar.mediaplayer.fragments.VideoFolderFragment$MediaQuery.getAllVideo(VideoFolderFragment.java:364)
        at com.aisar.mediaplayer.fragments.VideoFolderFragment$VideosLoader.loadVideos(VideoFolderFragment.java:434)
        at com.aisar.mediaplayer.fragments.VideoFolderFragment$VideosLoader.access$1100(VideoFolderFragment.java:413)
        at com.aisar.mediaplayer.fragments.VideoFolderFragment$5.run(VideoFolderFragment.java:189)
        at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:289)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641)
        at java.lang.Thread.run(Thread.java:919)

My Code:

public class MediaQuery {
        private Context context;
        private int count = 0;
        private Cursor cursor;
        List<ModelVideoFolder> videoItems;

        public MediaQuery(Context context) {
            this.context = context;
        }

        public List<ModelVideoFolder> getAllVideo(String query) {
            String selection = null;
            String[] projection = {
                    "DISTINCT " + MediaStore.Video.Media.BUCKET_DISPLAY_NAME,
                    MediaStore.Video.Media.BUCKET_ID
            };
            cursor = context.getContentResolver().query(
                    MediaStore.Video.Media.EXTERNAL_CONTENT_URI,
                    projection,
                    selection,
                    null,
                    query);
            videoItems = new ArrayList<>();
            ModelVideoFolder videoItem;
            while (cursor.moveToNext()) {
                videoItem = new ModelVideoFolder(
                        "" + cursor.getString(1),
                        "" + cursor.getString(0),
                        "",
                        "",
                        "" + getVideosCount(cursor.getString(1))
                );
                videoItems.add(videoItem);
            }
            return videoItems;
        }

        public int getVideosCount(String BUCKET_ID) {
            int count = 0;
            String selection = null;
            String[] projection = {
                    MediaStore.Video.Media.BUCKET_ID,
            };
            Cursor cursor = getActivity().getContentResolver().query(
                    MediaStore.Video.Media.EXTERNAL_CONTENT_URI,
                    projection,
                    selection,
                    null,
                    null);
            while (cursor.moveToNext()) {
                if (BUCKET_ID.equals(cursor.getString(0))) {
                    //add only those videos that are in selected/chosen folder
                    count++;
                }
            }
            return count;
        }
    }

Answer 1

This is due to the restrictions in Android Q.

In Android Q the projection must contain only valid column names without additional statements. Is not possible anymore to embed any type of SQL statement in the projection.

So, projections such as "DISTINCT " + YourColumName, or even trying to make a column alias such as "ExistingColumnName AS AnotherName" will always fail.

The workaround is to perform multiple queries (cursors) to get your required metrics, and construct with the results a CursorWrapper or MatrixCursor.

See the next issue link, where is stated this behavior as expected, since is part of the improved storage security model in Q:

https://issuetracker.google.com/issues/130965914

For your specific problem, a solution could be as next:

1. First query for a cursor to obtain the list of the BUCKET_ID values where all the videos are located. In the selection you can filter to target only video files by using MediaStore.Files.FileColumns.MEDIA_TYPE = MediaStore.Files.FileColumns.MEDIA_TYPE_VIDEO

2. With the retrieved cursor, iterate all the BUCKET_ID values to perform individual queries per bucket and retrieve the video records, from which you can resolve the count. While iterating keep track of each BUCKET_ID and skip any already queried. And don't forget to also perform the same MEDIA_TYPE filter selection, to avoid querying none-video files that may reside in the same bucket.

Try the next snippet based in your question code, I haven't test it but you may get an idea about how to proceed:

public static class MediaQuery
{
    @NonNull
    public static HashMap<String, ModelVideoFolder> get(@NonNull final Context context)
    {
        final HashMap<String, ModelVideoFolder> output     = new HashMap<>();
        final Uri                               contentUri = MediaStore.Video.Media.EXTERNAL_CONTENT_URI;

        final String[] projection = {MediaStore.Video.Media.BUCKET_DISPLAY_NAME,
                MediaStore.Video.Media.BUCKET_ID};

        try (final Cursor cursor = context.getContentResolver().query(contentUri,
                projection, null, null, null))
        {
            if ((cursor != null) && (cursor.moveToFirst() == true))
            {
                final int columnBucketName = cursor.getColumnIndexOrThrow(MediaStore.Video.Media.BUCKET_DISPLAY_NAME);
                final int columnBucketId   = cursor.getColumnIndexOrThrow(MediaStore.Video.Media.BUCKET_ID);

                do
                {
                    final String bucketName = cursor.getString(columnBucketName);
                    final String bucketId   = cursor.getString(columnBucketId);

                    if (output.containsKey(bucketId) == false)
                    {
                        final int count = MediaQuery.getCount(context, contentUri, bucketId);

                        final ModelVideoFolder item = new ModelVideoFolder(
                                bucketName, bucketId, null, null, count);

                        output.put(bucketId, item);
                    }

                } while (cursor.moveToNext());
            }
        }

        return output;
    }

    private static int getCount(@NonNull final Context context, @NonNull final Uri contentUri,
                                @NonNull final String bucketId)
    {
        try (final Cursor cursor = context.getContentResolver().query(contentUri,
                null, MediaStore.Video.Media.BUCKET_ID + "=?", new String[]{bucketId}, null))
        {
            return ((cursor == null) || (cursor.moveToFirst() == false)) ? 0 : cursor.getCount();
        }
    }
}