Subscribe to this thread
Home - General / All posts - Cannot parse query when wrapped in a function
bdg56 post(s)
#07-Oct-19 13:39

I have a query that runs fine but when I wrap it into a function it fails to parse.

Can someone show me what I am doing wrong?


VALUE @conv TABLE = CALL CoordConverterMake(






 SELECT [mfd_id], SPLIT

   CALL TileGeomToValues([LC_1]::[NLCD_2016_Land_Cover_L48_20190424],

  CoordConvert(@conv, [Geom (I)]))

 FROM [gPoly]

 where [mfd_ID] = 244272;



TABLE CALL f(244272);




--(SELECT mfd_id FROM @p) END;


--TABLE CALL f(mfd_root);


9,223 post(s)
#07-Oct-19 17:00

[Need to check.]


9,223 post(s)
#07-Oct-19 22:16

My sleepy first answer (deleted) was wrong.

The right answer is much simpler.

Remove the semicolon at the end of the (SELECT...) definition inside the function. It's not technically a statement in that position, so doesn't take a statement terminator.

bdg56 post(s)
#07-Oct-19 23:40

I wish that were the case. The semicolon was a straw clutch.




SELECT [mfd_id], SPLIT

CALL TileGeomToValues([LC_1]::[NLCD_2016_Land_Cover_L48_20190424],

CoordConvert(@conv, [Geom (I)]))

FROM [gPoly]

where [mfd_ID] = 244272

) END;

Cannot parse query.


9,223 post(s)
#07-Oct-19 23:59

It wasn't a straw. It was a small error which prevented the query from being parsed.

There may be more errors.

What you have directly above excludes the VALUE @conv setup, which is also necessary for the code to parse. Include that, or run it separately first (in the same command session).

If you still have an error, check the name of the geom field in [gPoly]. Is it really Geom (I) (inherited from Manifold 8)?

bdg56 post(s)
#08-Oct-19 12:21

gPoly is a hold over from M8, [Geom (I)] is correct.

Below are the runs showing good and bad.


374 post(s)
#08-Oct-19 13:16

Just guessing.

What if you actually use parameter @p in expression ?

 where [mfd_ID] = @p

bdg56 post(s)
#08-Oct-19 19:00

That was where I started. There was way more code. I tried to reduce to what would demonstrate the problem. I just retested as suggested. No joy found, as shown in the log window.


 where [mfd_ID] = @p



TABLE CALL f(244272);

Cannot parse query.


9,223 post(s)
#08-Oct-19 19:48

This is very mysterious.

A vague suspicion--perhaps it was an oversight in revising the compiler for the new TileGeomToValues functions.

Those functions are unusual in that they address an image which does not appear in the query sources (in the FROM list). That may require an exception to normal compiler checks.

Perhaps the exception was made for functions inside SELECT statements (and other places--we could test VALUES and VALUE too), but not for SELECT inside a function definition.

I hope to make some tests this morning on different data, but it looks like something that is worth reporting to Tech.

Two more questions for clarification: is [NLCD_2016_Land_Cover_L48_20190424] an image, as required, or a table? And what happens if it is a local image, not contained in child datasource [LC_1]?

Yes, both of those things should be treated the same whether in a SELECT statement or in a function definition. But it seems worth asking all the same.

bdg56 post(s)
#08-Oct-19 20:58

Thanks for your interest and help, Tim. [NLCD_2016_Land_Cover_L48_20190424] is an Erdas image referenced by me a few days ago with "strange" coordinate system. I have not tested code with any other datasource. I'll try that.

I did move the script directly to LC_1, opened as a standalone map with no joy.

As a query it runs. As a function it fails to parse.



467 post(s)
#08-Oct-19 22:34

It might be caused by some invisible character. I remember that I encountered that at least once.

After selecting and deleting all "spaces" at the end of a line the error was gone.

What if you recontruct your query on a new command tab?

bdg56 post(s)
#09-Oct-19 01:44

After painstakingly retyping I feel comfortable in saying invisible characters are not the problem.



467 post(s)
#09-Oct-19 06:51

(Almost) sorry for the suggestion.

bdg56 post(s)
#09-Oct-19 10:02

The query when run in LC_1 using the table instead of the image does not issue a "Cannot parse" error. The result table is empty, however. When run nestled in the Function it still fails to parse.


9,147 post(s)
#12-Oct-19 15:42

Sorry, I am late to the thread.

The function takes @p, and then uses @conv. Not using the passed @p is fine - you don't have to use any of the parameters that you pass, this is a little weird but it happens all the time when you are incrementally working on functions and are trying different things, this is allowed. But using the not passed @conv is not fine - inside the body of a function @conv is interpreted as a parameter, that you have a global value named @conv outside of the function does not matter. Since you don't have a parameter named @conv, the body fails to compile. You cannot reference a global value from within the body of a function, mostly because this is just unnecessarily complicates the picture. If you want the function to do something with the help of a global value, just pass that value in as a parameter of its own: FUNCTION f(@p INT64, @conv TABLE) TABLE AS ... END; - simply adding @conv to the parameter list should fix the body.

Maybe there's something else, too, but the above definitely has to be fixed.


9,147 post(s)
#12-Oct-19 15:46

(We'll work on error reports. Promise. It would have been so much better if the error was not "Cannot parse query" but rather "Invalid parameter: conv".)


9,223 post(s)
#12-Oct-19 20:59

I'm surprised to learn (so late) that functions have their own state, separate from that of the command window or the query containing them.

I would rather that they inherited state.

Using the term "global variable" begs the question and muddies the waters--since they are not global.

Currently, global scope does not include functions defined within it. In other words scope does not pierce functiion calls. I didn't know that.

This is all easily workable, but needs clear signalling, hopefully in the manual.


5,994 post(s)
#13-Oct-19 06:27

but needs clear signalling, hopefully in the manual.

Agreed. It's in there in the Functions topic, but not clearly:

All references to parameters inside a function body are resolved to local parameters.

That should be made clear, with an example or two.


9,223 post(s)
#14-Oct-19 02:31

Thanks Dimitri. I agree with you, but to my mind the most important place for the clarification is in the section on VALUE. This is not directly about parameters.

Without trying to be argumentative, the problem in that section (as I see it) is that the term "global value" either is incorrect or anyway gives the wrong impression.

Values stored with the VALUE statement are not global in the sense of applying to the entire command context. They only apply (as it turns out) to the top level. In particular, functions are excluded from their scope of application.

I think a better term than "global value" would be simply "stored value".

But the exact term is not important, if the impression given by "global value" can be counteracted in some way. E.g. simply by pointing out what it is not intended to mean.


9,223 post(s)
#12-Oct-19 21:26

You cannot reference a global value from within the body of a function, mostly because this just unnecessarily complicates the picture.

For some brains, it would simplify the picture.

But it would be less functional in style, and possibly less bulletproof.

The current arrangement should mean, though, that the FUNCTION syntax include the ability to define local VALUE members, and enclosed FUNCTIONs.

I don't think it does that, for now.

Without that, in my opinion outer variables should pierce function calls. What is the downside?


9,147 post(s)
#14-Oct-19 09:54

We don't think it is a good idea to let a function body declare its own VALUEs (this complicates the syntax greatly in that the function body starts allowing multiple statements and this opens the door for trying to define functions inside functions), but we agree that there are arguments in favor of making FUNCTION bodies see existing VALUEs that we have.

Here's our thinking:

The pro of making a FUNCTION body see a VALUE is the ease of use. After you define a VALUE, you can use it in all text you write after that definition, be it a regular statement (SELECT) or a function (FUNCTION).

The con of making a FUNCTION body see a VALUE is that this introduces ambiguity: when a function body refers to @a, this could be either a parameter or a global value. If there is both a parameter named @a, and a global value named @a, the parameter should silently win (if we report this ambiguity as an error or let the global value win, then whenever you declare a new VALUE, you risk breaking functions that use the same name as a parameter, this is impossible to work with). So, there is an ambiguity that has to be resolved silently. Ambiguities that are resolved silently are nearly always bad, and here is why it is bad in this case: if you have both a parameter named @a and a global value named @a, decide that a function does not need to take @a as a parameter, remove it from the list of parameters, but forget to remove it from the function body, the function body will still compile but will probably not do what you want. It would have been better if it didn't compile and you got a reminder that you are still referencing @a even though you stopped passing it.

Another con of making a FUNCTION body see a VALUE is that after the function is compiled, the VALUE can be redefined, and when that happens, the compiled function will continue to use the old value. This is similar to how you can define FUNCTION f(), then define FUNCTION g() calling f(), then redefine FUNCTION f() to something else and g() has to continue using the old definition of f(), but while this is kind of inevitable with functions, it is not inevitable with values - because you can just never reference global values and accept them as parameters instead. That said, this whole notion of redefining functions and values exists mostly because we allow running SQL interactively in a command window, and redefining things is useful in that context. For persistent queries we are considering an option to disallow redefining functions and values, making any redefinition a runtime error. If we do this, this particular point in favor of not allowing function bodies see values will lose most of its strength.

Sum total: currently, functions can call other functions, global values can call functions and use other global values, but functions cannot use global values. There are arguments in favor of allowing functions to use global values. Some of the issues get easier if functions and global values can no longer be redefined, we are considering adding this restriction in some form.


9,223 post(s)
#15-Oct-19 02:57

It's great that you take the time to discuss this and explain the thinking. Very much appreciated.

I will keep my comments back very brief, because I don't want to be trying your patience.

The pro of making a FUNCTION body...


The con of making a FUNCTION body see a VALUE is that this introduces ambiguity: when a function body refers to @a, this could be either a parameter or a global value.

Yes. But this raises an obvious question. Would it have been better if paramaters and stored values did not share the same prefix symbol? If one had been, say, $ instead, then that paragraph of the argument would evaporate, I think. But as settled practice it may be something we will live with.

Another con of making a FUNCTION body see a VALUE is that after the function is compiled, the VALUE can be redefined, and when that happens, the compiled function will continue to use the old value.

That seems a very good argument against, yes. This would probably be a trap for the unwary, quite confusing and best avoided.

That said, this whole notion of redefining functions and values exists mostly because we allow running SQL interactively in a command window, and redefining things is useful in that context. For persistent queries we are considering an option to disallow redefining functions and values, making any redefinition a runtime error.

While I don't often want to redefine a stored value or (even less often) a function even in an interactive window, not being able to do so when necessary might be a pain. The example that I think I would most trip over is VALUE @t TABLE = t ... then I make a change to the schema of t, and now want to redefine @t to match the new schema (because I must). If @t tracked changes to t (to its schema), then it would be different.

In any case, I think there is great value in keeping command windows and persistent queries as closely aligned as possible. There would be a downside to giving them different rules.


9,223 post(s)
#15-Oct-19 04:12

A rethink: there would be nothing wrong with requiring function and value definitions to be static in both interactive command sessions and in static query windows.

A new definition would require a new name. (Redefining with old name -> helpful error.)

It should also be possible for a previous definition that is an expensive object or has its own state to be deleted, recovering resources.

The same rules should apply to interactive windows and to static components. Copying and pasting text from one to the other should always just work, to the extent reasonably possible.

[It *could* still be possible for function and value definitions to track referenced objects. I expect there is a reason why they can't, so I hesitate to add this. It would probably be a different discussion.]


9,147 post(s)
#15-Oct-19 10:13

There is a clash:

On the one hand, you want to be able to redefine FUNCTIONs / VALUEs because that's intrinsic to the process of incrementally building a query in the command window. You define a function, then improve it, etc. Or you define a value, run some test queries, see that the value is wrong or just want to test against some other data, so you redefine it, etc.

On the other hand, you don't want to be able to redefine FUNCTIONs / VALUEs *unknowingly* because you can redefine something that shouldn't be redefined.

There are multiple ways of trying to meet both ends. First, the command window can be allowed to redefine, and persistent queries can be disallowed that. We agree this is perhaps not good, because as you say, persistent queries behaving exactly like interactive queries is important - we don't want to be losing that. So, second option: we can add a PRAGMA or a comment that would disallow redefines. Or, third option: we can allow redefines, but flag them as warnings - in a command window we will log redefinitions into the log tab, and when running a persistent query we will log redefinitions into the log window.


9,223 post(s)
#15-Oct-19 10:41

Fourth option? Disallow strict reassignment and redefinition everywhere (throwing an error), while introducing




after which novel definition or assignment with the previous name would become possible;

...and possibly now enabling value assignments to be truly global (piercing function calls).

Not so sure about the last thing (ideally I would prefer a different symbol prefix as well, to remove all ambiguity, although in principle anyone who uses the same name for a parameter and a stored value gets everything he deserves...). More sure that the DROPs would be ~ideal.


9,147 post(s)
#15-Oct-19 10:58

Functions / values that have already referenced the dropped function / value will continue using the old definitions. Eg, in this code:


FUNCTION numbers() TABLE AS ( VALUES (1), (2), (3) AS (p) ) END;

VALUE @n TABLE = CALL numbers();



...@n will still continue to work and provide data for the SELECT. However, attempts to call numbers() will fail.

If that seems fine (it does to me, but then again, that's because I know how it works internally), then sure, we can use DROP with functions / values, why not.

PS: Also, DROP FUNCTION / VALUE on a function / value that does not exist should perhaps not be an error and just do nothing.


9,223 post(s)
#15-Oct-19 11:24

Perfect example.

Seems fine to me too, because I would have read the manual where this helpful example appeared!

Same as at present. Functions are compiled at the point where they are defined. Subsequent changes to the “sources” of the function do not trigger recompilation. Same with VALUE assignments. That is how it works.

Adding a prohibition on redefinition plus DROP would enable one also to say, more clearly and more strongly: function definitions and value assignments never change (are immutable), but can be replaced if they are first DROPped.


9,223 post(s)
#15-Oct-19 12:01

By talking through this with you and trying to word things more clearly I now see why function definitions and value assignments should never track objects (or anything else).

For the same reason I no longer think value assignments should pierce function calls. They should have to be passed. (But should therefore not be called "global".)

SQL9 does not go on and on about this in its documentation, but it is broadly functional in its approach. That is solid and good, self-consistent, scalable, and fast.



9,147 post(s)
#15-Oct-19 13:48

That seems to be the best solution - continue requiring VALUEs to be passed to FUNCTION bodies as parameters, but stop calling VALUEs global values, they are values in the default scope, while 'global' has this connotation of being available everywhere which they are not. This is consistent with EXECUTE, which also establishes its own scope (VALUEs from the outer query are not available inside EXECUTE, but you can easily pass them as parameters), etc.

As regards adding some rules to make redefinitions of FUNCTIONs and VALUEs possible, but explicit, one last bit - thinking out aloud:

FUNCTION / DROP FUNCTION seems wrong as a pair. Normally, everything that can be DROPped is first CREATEd. We can always make an exception, but the less exceptions the better. So, we could (1) require CREATE FUNCTION / VALUE and allow DROP FUNCTION / VALUE, or (2) require DEFINE FUNCTION / VALUE and allow DROP FUNCTION / VALUE (or UNDEFINE ?), or maybe (3) continue to allow FUNCTION / VALUE, but also allow OVERRIDE FUNCTION / VALUE (or REDEFINE ?) which would override the object if it is already defined. These are just the first thoughts, we'll think some more. (But we really want to make redefinitions at least vocal, and ideally explicit and thus approved by the query writer.)


9,223 post(s)
#16-Oct-19 03:32

At the risk of being a PIA:

I best like



because that seems to be a more explanatory verb for what we are doing there than CREATE. (CREATE seems to imply external existence. Something you can see in the Project pane--unless it is a temp datasource within local state.)




because that is familiar and common to all types of thing, so there is nothing new to remember.

[[P.s. given a common keyword you could even merge the syntax for defining functions and values:

DEFINE f(@p) <type> AS <definition> END;

DEFINE @v <type> = <definition>;

The main difference is the presence/absence of parentheses. I'm not sure if I'm suggesting that or just being a real PIA (even to myself), but it doesn't seem completely inappropriate if VALUE assignments are mainly syntactic sugar for simple FUNCTIONs.]]


9,223 post(s)
#16-Oct-19 03:58

[[Then just


DROP @v;

I also made a small mistake that you will have noticed:

DEFINE f(@p <type>) <type> ...



9,223 post(s)
#16-Oct-19 04:17

[[No because that doesn't match other DROPs. Type keyword required.]]

bdg56 post(s)
#13-Oct-19 13:19

Thanks Adam. As suggested, adding the @conv to the parameter does fix the problem.


611 post(s)
#26-Oct-19 11:59

Does a comparison can be done beetween SQL logic ( behaviour) implemented by Manifold and language like javascript when cover :

--argument in function use pass by value(copy) for primitive type and reference (pointer) for object ( function array for change value by reference we need to use explicit affectation of each items store by the object pass by reference )

--variable and scope ( variable scope exist inside block scope that mean curly bracket)

--delete ( set to undefine) work only for property create using operator "

:"  ( 

property assignment at object definition, ":" instead equal= glyph= sign= function= operator "=" ) .

--prototype to create custom "class " ( object using function )

--no return value in function return "undefined" ( not always do it short)


Manifold User Community Use Agreement Copyright (C) 2007-2019 Manifold Software Limited. All rights reserved.