'MariaDb does not support ANY_VALUE() function

I have a laravel project which is connected to mysql db, when I change my server, my codes got failed because my new server has a Mariadb, when I checked my logs, I have realised that, there is some unsupported function from MariaDb which is ANY_VALUE(),

how can I edit my sql according to MariaDb ?

select(DB::raw('SUM(price) as price, SUM(price_now) as price_now, 
   ANY_VALUE(price_available) as price_available'),'adult_count')

error log

phpmysqlsqllaravelmariadb


Solution 1:[1]

For today, you have solved the problem. But tomorrow, when you run the same query, you will get a different error.

In older versions of MySQL or MariaDB, you would get "any value" for price_available when not GROUPing BY it. That was effectively somewhere between "bad practice" and a "standards violation". Relatively recently, MariaDB, then later MySQL, switched to "only full group by". At that time, ANY_VALUE() came into existence for MySQL, but apparently MariaDB dropped the ball.

The old workaround, which should be safe for both old and new versions is to use MIN(price_available) or some other aggregate function. (If the column might have NULL, the various aggregates might or might not handle NULL the way you prefer.)

See also the ONLY_FULL_GROUP_BY setting.

Solution 2:[2]

I have solved with replacing this:

select(DB::raw('SUM(price) as price, SUM(price_now) as price_now, 
    ANY_VALUE(price_available) as price_available'),'adult_count')

to

select(DB::raw('SUM(price) as price, SUM(price_now) as price_now, 
    price_available as price_available'),'adult_count')

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Dharman

Related Questions