IssueTracker

Information redacted by Android Beta Feedback.

Now there is no finger print sensor icon  on the lock  screen

On Thu, Apr 20, 2023, 3:30 AM <buganizer-system@google.com> wrote:

Can you give a simple example of a UDF returning a promise to show what you have in mind?

One basic example would be doing anything with WebAssembly. The below example is naive and isn't the best way to get the web assembly file itself (fetch API), it is simpler then showing how to do it with embedded data URLs

```
var importObject = { imports: { imported_func: arg => console.log(arg) } };

WebAssembly.instantiateStreaming(fetch('simple.wasm'), importObject)
.then(obj => obj.instance.exports.exported_func());
```

If you wanted to use the await keyword you will need to use an async function, which returns a promise.

One solution that could allow promises to be returned would be to allow UDFs to be async functions themselves. This would put the onus on BigQuery as a platform supporting the instrumentation / execution of the async function, but the V8 team has offered some support if the VM is missing the appropriate APIs for this.

Here is an example of using tensorflow.js to do handwriting recognition. The run function could in theory be a UDF, I've also included an example of a wrapper function that calls run and returns the promise (as an alternative approach).

This source is copy pasted from a tensorflow.js tutorial, but should be sufficient to show that we currently have no way to utilize these kinds of libraries or patterns that are prevalent in the js ecosystem right now.

```
import {MnistData} from './data.js';

async function showExamples(data) {
  // Create a container in the visor
  const surface =
    tfvis.visor().surface({ name: 'Input Data Examples', tab: 'Input Data'});  

  // Get the examples
  const examples = data.nextTestBatch(20);
  const numExamples = examples.xs.shape[0];
 
  // Create a canvas element to render each example
  for (let i = 0; i < numExamples; i++) {
    const imageTensor = tf.tidy(() => {
      // Reshape the image to 28x28 px
      return examples.xs
        .slice([i, 0], [1, examples.xs.shape[1]])
        .reshape([28, 28, 1]);
    });
   
    const canvas = document.createElement('canvas');
    canvas.width = 28;
    canvas.height = 28;
    canvas.style = 'margin: 4px;';
    await tf.browser.toPixels(imageTensor, canvas);
    surface.drawArea.appendChild(canvas);

    imageTensor.dispose();
  }
}

async function run() {  
  const data = new MnistData();
  await data.load();
  await showExamples(data);
}

/* example of wrapping the run function in non async function
function udf () {
 return run();
}
*/
```

To clarify, can you provide a self-contained example? None of these are something that could be copy/pasted without setting up the dependent libraries (and "console", "window", etc. are not available from the sandboxed v8 environment).

Most of us who will be benefited from this feature are using external libraries with some kind of transpiler (e.g. babel, browserify) so that the "console", "window", etc. are polyfilled. The output codes are most likely too large to be pasted to the BigQuery console due to the limitation here:

> Each inline code blob is limited to a maximum size of 32 KB.
> https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions#limits

The output codes are usually stored in the GCS and incorporated as a "dependent libraries".

Here is a minimal (but unrealistic) example that can be pasted into the BigQuery console:

```sql
create temp function fn(val string) returns string language js as """

async function identity (x) {
  return x
}

return identity(val)
""";

select fn('foo')
```

That example works now:

$ bq query --use_legacy_sql=false '
create temp function fn(val string) returns string language js as """

async function identity (x) {
  return x
}

return identity(val)
""";

select fn("foo")
'
Waiting on bqjob_r51212eb1e015b964_0000016d20c6a68d_1 ... (1s) Current status: DONE  
+-----+
| f0_ |
+-----+
| foo |
+-----+

I can't definitively say that Promises work in all scenarios, so it would be great to get some feedback before updating documentation if you want to run some experiments.

So I've found an interesting regression... specifically with how errors are handled. I've created a minimal reproduction below. The result of this behavior is that any APIs that we end up losing context for any errors inside of a promise API (e.g. async functions).

The following example properly surfaces the error

```
CREATE TEMP FUNCTION `ohno`() RETURNS INT64 LANGUAGE js AS '''
throw new Error('oh no')
''';
 
SELECT ohno();
```

The below example doesn't

```
CREATE TEMP FUNCTION `ohno`() RETURNS INT64 LANGUAGE js AS '''
async function main() {
  throw new Error('oh no')
}
return main();
''';
 
SELECT ohno();
```

Surprisingly this works

```
CREATE TEMP FUNCTION `ohno`() RETURNS INT64 LANGUAGE js AS '''
async function main() {
  throw 'oh no';
}
return main();
''';
 
SELECT ohno();
```

This example bubbles up a runtime error as expected
```
CREATE TEMP FUNCTION `ohno`() RETURNS INT64 LANGUAGE js AS '''
dontExist();
''';
 
SELECT ohno();
```

This shows the behavior swallowing a runtime error

```
CREATE TEMP FUNCTION `ohno`() RETURNS INT64 LANGUAGE js AS '''
async function main() {
  dontExist();
}
return main();
''';
 
SELECT ohno();
```

Btw, an example running WebAssembly in #standardSQL:

- https://stackoverflow.com/a/57897906/132438

Almost anything can be thrown in JavaScript. After some quick investigation, I found that throwing a primitive mostly works:

- boolean
- null
- undefined
- number
- string
- bigint e.g. throw BigInt(9007199254740991)
- object e.g. throw {foo: 'bar'}
- array e.g. throw ["foo", 3.14]

A symbol shows up empty e.g. throw Symbol("Sym") => "JavaScript Promise was rejected:"
An error object is shown as a empty map e.g. new Error('foobar') => "JavaScript Promise was rejected: {}"

Which somewhat reminds me of the JSON.stringify behaviour in V8:

```
// Node.js REPL
const e = new Error('msg')
JSON.stringify(e)
// '{}'

const s = Symbol("Sym")
JSON.stringify(s)
// undefined
```

Maybe we should try the .toString() method instead?

Yeah, I think using toString instead of stringify addresses the issue. Thanks for the examples! Here's what I see with an experimental fix:

```
CREATE TEMP FUNCTION `ohno`() RETURNS INT64 LANGUAGE js AS '''
throw new Error('oh no')
''';
 
SELECT ohno();
```
Error: oh no at ohno() line 2, column 1


```
CREATE TEMP FUNCTION `ohno`() RETURNS INT64 LANGUAGE js AS '''
async function main() {
  throw new Error('oh no')
}
return main();
''';
 
SELECT ohno();
```
JavaScript Promise was rejected: Error: oh no

```
CREATE TEMP FUNCTION `ohno`() RETURNS INT64 LANGUAGE js AS '''
async function main() {
  throw 'oh no';
}
return main();
''';
 
SELECT ohno();
```
JavaScript Promise was rejected: "oh no"

```
CREATE TEMP FUNCTION `ohno`() RETURNS INT64 LANGUAGE js AS '''
dontExist();
''';
 
SELECT ohno();
```
ReferenceError: dontExist is not defined at ohno() line 2, column 1

```
CREATE TEMP FUNCTION `ohno`() RETURNS INT64 LANGUAGE js AS '''
async function main() {
  dontExist();
}
return main();
''';
 
SELECT ohno();
```
JavaScript Promise was rejected: ReferenceError: dontExist is not defined

I submitted the fix, which should roll out in a few weeks, but in the meantime let me know if you encounter other issues. Thanks!

Awesome! Thanks!

Crowdsourcing this a bit since everyone has been very helpful so far...does this description of how Promises are handled sound right? I want to get the terminology correct if possible:

If the return value of the JavaScript UDF is a [`Promise`](https://www.ecma-international.org/ecma-262/#sec-promise-objects), BigQuery waits for the `Promise` until it is settled. If the `Promise` settles into a fulfilled state, BigQuery returns its result. If the `Promise` settles into a rejected state, BigQuery returns an error.

Looks good to me!

Thanks! Documented here now: https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions#sql-type-encodings-in-javascript