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();
        }
    }
}

Answer 2

I was facing the same problem. DISTINCT keyword doesn't work in Android 10, use hashset for distinct.

Answer 3

The DISTINCT keyword actually belongs to the SELECT statement, not to a column. For example SELECT DISTINCT Country, Name FROM CountriesTable. Therefore adding DISTINCT to a column projection is a hack which randomly worked in the previous Android versions and probably stopped working in Android 10 due to some changes. Since the ContentResolver doesn't allow raw queries, you just have to filter unique folders inside your code, e. g. by using a HashSet.