[MDEV-29168] operator to search set columns Created: 2022-07-26  Updated: 2022-08-01

Status: Open
Project: MariaDB Server
Component/s: Data types
Fix Version/s: None

Type: Task Priority: Minor
Reporter: Marc Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

at the moment there is no good way to search in set columns. The only way is to use
FIND_IN_SET(xx, set-Col)
which is "ok" if you just have one set val to search. But it does not use the index.

If you want to search for more than one col, you must write
FIND_IN_SET(xx, set-Col) OR FIND_IN_SET(yy, set-Col)

The only "good" solution is, to query the set-structure
SET('xx','yy')
determine the bits and have a "unreadable query"
set-col = 27

It would be good to have an operator which uses the definition of the table, like it is done when the query is returned, so one could write sth like this
set-col=SET_COL('xx','yy')

where "SET_COL('xx','yy')" would return sth like 27 by looking up the value via the table defintion. Now indices could be used, and the query remains readable.

 Comments   
Comment by Sergei Golubchik [ 2022-08-01 ]

I presume, you mean FIND_IN_SET(xx, set-Col) AND FIND_IN_SET(yy, set-Col), not OR, as that's what your equivalent set-col = 27 condition does.

Comment by Marc [ 2022-08-01 ]

In this case yes.
Sure, the "(X)OR"-case is important in many applications and will still not be easy to query, since it has to do
set-col&27 > 0
But I assume using an index for this query is not possible.

Generated at Thu Feb 08 10:06:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.