Function to convert from Comma seperated string list to IN predicate set (1, 2, 3, n) [CORE3155]

[firebird-automations]

Submitted by: Tim Kelly (m00bh000)

Relate to CORE3460

I execute the following query often:

Select * FROM PEOPLE WHERE REGION_ID IN (:A, :B, :C)
:A = 'A2D3', :B = 'C1D4', :C = 'FFE3'

but the number of params which make up the IN predicate of the query changes very often, so the next time it might be:

Select * FROM PEOPLE WHERE REGION_ID IN (:A, :B, :C, :D, :E)
:A = 'A2D3', :B = 'C1D4', :C = 'FFE3', :D = '127D', :E = '123A'

This makes the query impossible to "PREPARE" / cache since there are many different versions of it depending upon the number of paramaters that are required. It would be much better just to have one query with a parameter which is a string-list, which could be converted into a set (as required by IN) using a function, say STR_LIST_TO_SET(<string_list>):

Select * FROM PEOPLE WHERE REGION_ID IN STR_LIST_TO_SET(:A)
:A = '''A2D3'', ''C1D4'', ''FFE3'', ''127D'',''123A'''

Why don't I just use regular-expressions? Because experimenting they are grossly inefficient compared with using IN \in this type of scenario.

What do people think?

[firebird-automations]

Modified by: Tim Kelly (m00bh000)

description: I execute the following query often:

Select * FROM PEOPLE WHERE REGION_ID IN (:A, :B, :C)
:A = 'A2D3', :B = 'C1D4', :C = 'FFE3'

but the number of params which make up the IN predicate of the query changes very often, so the next time it might be:

Select * FROM MY_TABLE WHERE REGION_ID IN (:A, :B, :C, :D, :E)
:A = 'A2D3', :B = 'C1D4', :C = 'FFE3', :D = '127D', :E = '123A'

This makes the query impossible to "PREPARE" / cache since there are many different versions of it depending upon the number of paramaters that are required. It would be much better just to have one query with a parameter which is a string-list, which could be converted into a set (as required by IN) using a function, say STR_LIST_TO_SET(<string_list>):

Select * FROM MY_TABLE WHERE REGION_ID IN STR_LIST_TO_SET(:A)
:A = '''A2D3'', ''C1D4'', ''FFE3'', ''127D'',''123A'''

Why don't I just use regular-expressions? Because experimenting they are grossly inefficient compared with using IN \in this type of scenario.

What do people think?

=>

I execute the following query often:

Select * FROM PEOPLE WHERE REGION_ID IN (:A, :B, :C)
:A = 'A2D3', :B = 'C1D4', :C = 'FFE3'

but the number of params which make up the IN predicate of the query changes very often, so the next time it might be:

Select * FROM MY_TABLE WHERE REGION_ID IN (:A, :B, :C, :D, :E)
:A = 'A2D3', :B = 'C1D4', :C = 'FFE3', :D = '127D', :E = '123A'

This makes the query impossible to "PREPARE" / cache since there are many different versions of it depending upon the number of paramaters that are required. It would be much better just to have one query with a parameter which is a string-list, which could be converted into a set (as required by IN) using a function, say STR_LIST_TO_SET(<string_list>):

Select * FROM PEOPLE WHERE REGION_ID IN STR_LIST_TO_SET(:A)
:A = '''A2D3'', ''C1D4'', ''FFE3'', ''127D'',''123A'''

Why don't I just use regular-expressions? Because experimenting they are grossly inefficient compared with using IN \in this type of scenario.

What do people think?

[firebird-automations]

Modified by: Tim Kelly (m00bh000)

description: I execute the following query often:

Select * FROM PEOPLE WHERE REGION_ID IN (:A, :B, :C)
:A = 'A2D3', :B = 'C1D4', :C = 'FFE3'

but the number of params which make up the IN predicate of the query changes very often, so the next time it might be:

Select * FROM MY_TABLE WHERE REGION_ID IN (:A, :B, :C, :D, :E)
:A = 'A2D3', :B = 'C1D4', :C = 'FFE3', :D = '127D', :E = '123A'

This makes the query impossible to "PREPARE" / cache since there are many different versions of it depending upon the number of paramaters that are required. It would be much better just to have one query with a parameter which is a string-list, which could be converted into a set (as required by IN) using a function, say STR_LIST_TO_SET(<string_list>):

Select * FROM PEOPLE WHERE REGION_ID IN STR_LIST_TO_SET(:A)
:A = '''A2D3'', ''C1D4'', ''FFE3'', ''127D'',''123A'''

Why don't I just use regular-expressions? Because experimenting they are grossly inefficient compared with using IN \in this type of scenario.

What do people think?

=>

I execute the following query often:

Select * FROM PEOPLE WHERE REGION_ID IN (:A, :B, :C)
:A = 'A2D3', :B = 'C1D4', :C = 'FFE3'

but the number of params which make up the IN predicate of the query changes very often, so the next time it might be:

Select * FROM PEOPLE WHERE REGION_ID IN (:A, :B, :C, :D, :E)
:A = 'A2D3', :B = 'C1D4', :C = 'FFE3', :D = '127D', :E = '123A'

This makes the query impossible to "PREPARE" / cache since there are many different versions of it depending upon the number of paramaters that are required. It would be much better just to have one query with a parameter which is a string-list, which could be converted into a set (as required by IN) using a function, say STR_LIST_TO_SET(<string_list>):

Select * FROM PEOPLE WHERE REGION_ID IN STR_LIST_TO_SET(:A)
:A = '''A2D3'', ''C1D4'', ''FFE3'', ''127D'',''123A'''

Why don't I just use regular-expressions? Because experimenting they are grossly inefficient compared with using IN \in this type of scenario.

What do people think?

[firebird-automations]

Commented by: @dyemanov

IN could be efficient (as compared to regular expressions or other approaches) only when an index scan could be used for a lookup. But it's possible only when number of elements inside the IN list in known at the prepare time. So I don't see how it could be done.

[firebird-automations]

Commented by: Tim Kelly (m00bh000)

I wondered if this might be the case. For now I'm going to use a query with 16 params, the one's i don't need will be empty strings.... it works and makes my app much faster as the query is prepared and ready to go.... I hate it though what if someone wanted to use 17... where do i stop!!

There must be a smarter way of allowing these type of queries to be prepared... any other ideas?

[firebird-automations]

Commented by: @asfernandes

Something like "field IN LIST <string>" already was discussed and rejected.

Converting a string to a set and allow IN <set> would be a completely different thing. It would need first to have sets (or arrays, or table variables, etc).

[firebird-automations]

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

[firebird-automations]

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

[firebird-automations]

Modified by: @dyemanov

Link: This issue relate to CORE3460 [ CORE3460 ]