[MCOL-3375] FUNCTION idblocalpm does not exist using CTE - udf are not loaded consistently Created: 2019-06-10  Updated: 2023-10-25  Resolved: 2023-10-25

Status: Closed
Project: MariaDB ColumnStore
Component/s: PrimProc
Affects Version/s: 1.2.4
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: David Hill (Inactive) Assignee: Leonid Fedorov
Resolution: Won't Do Votes: 1
Labels: None
Environment:

2um 2pm with local query


 Description   

Customer report issue, request that the related select not be placed in JIRA. So you will need to view that in the issue.

I have been using the idb function successfully, but I can't use them with a CTE :

SELECT IN THE ISSUE

ERROR 1305 (42000) at line 6: FUNCTION pic.idblocalpm does not exist

Looks like it is looking for the function in the pic schema, which schema should I call it ?

---------------------------------------

Got input from development , D.H.

I don't think changing the schema name will make a difference. What that says is that it can't find the function in the current context. Those functions were added as UDF before we had CTE. Not sure what is going on here. This needs a JIRA.

 Comments   
Comment by David Hill (Inactive) [ 2019-06-10 ]

info from DH development testing..

I tried a simplified query with that pattern. It worked:
MariaDB [dhall]> select * from (with a as (select idblocalpm() from tb1) select * from a) b;
--------------

idblocalpm()

--------------

1

--------------
1 row in set (6.433 sec)

This is annoying:
MariaDB [dhall]> select *, idbpm(d1), idblocalpm() from tb1;
-------------------------------+

d1 i1 idbpm(d1) idblocalpm()

-------------------------------+

7.12 1 1 1

-------------------------------+
1 row in set (2.250 sec)

MariaDB [dhall]> select * from tb1 where idbpm(tb1.d1)=idblocalpm();
Empty set (2.874 sec)

You can see that idbpm(d1) and idblocalpm() both return 1, yet when used in equivalence, it returns false.
And more wieirdness:
MariaDB [dhall]> select *, idbpm(d1)=idblocalpm() from tb1;
ERROR 1815 (HY000): Internal error: IDB-5002: Pseudo column function 'idbpm' is only supported in Columnstore.

Comment by David Hill (Inactive) [ 2019-06-10 ]

Customer is asking..

How do I got without it then. The problem is that the original join query was not 'extent map' aware.

This part : a.row_id between (select min(start_rid) from brows) and (select max(end_rid) from brows)

used to be : a.row_id between b.start_rid and b.end_rid ,

I guess that since the UM does not know the value before the join, it does not restrict the join and end-up joining the whole table, which is the exact opposite effect of what i intended.

Comment by David Hill (Inactive) [ 2019-06-11 ]

Update from customer:

I dig into this a little bit, and pm1 was fine with the CTE and could run the idblocalpm, so i restarted the cs and all pm can run it now. This is the same problem we had with our own udf.

Looks like the udf are not loaded consistently.

Generated at Thu Feb 08 02:42:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.