Whaty could be the cause of postgres functions losing or swapping values from a CTE?

I have a number of functions which are generally in this shape:

RETURN QUERY with cte1 as (    select a, b, c, d, e    from [some_table]    where [some_condition] ), cte2 as (     select x, y, z     from [big_table]     where [bunch_of_conditions] ) select foo.x, foo.y, bar.a, bar.b, bar.c, bar.d from cte2 foo join cte1 bar on foo.z = bar.a 

I have seen occasions where the values of bar.b and bar.d are swapped, and on others no values are returned – upon investigation, because bar.a contains only nulls.

In all cases, when I take the code out of the functions, it always runs correctly. In each case where it goes wrong, there is nothing I can point to which causes the glitch, and usually it goes away for no obvious reason. This is all running in pgAdmin under Windows 10, and the behaviour persists through stopping pgAdmin, and/or rebooting the PC.

The db where this happens is running on AWS, and I don’t have access to stop/restart that server.

It suggests to me that the problem is happening at the AWS and/or postgres server end, but that may or may not be correct.

Possibly also relevant, we have also seen cases where queries run exceptionally slowly (45 minutes instead of less that one second, being one example today).

In each case of the swap or the slowdown, it eventually returns to normal behaviour without any obvious intervention from me. I’m still experiencing the null value which should be populated.

Has anyone seen behaviour like this? Any ideas what could be causing it, or how to remedy it?

Select all rows that have all of the following column values

I have the following tables Genres, Films, Directors. They have the following schema:

CREATE TABLE GENRES(     GID INTEGER PRIMARY KEY,     GENRE VARCHAR(20) UNIQUE NOT NULL );  CREATE TABLE Films(     FID INTEGER PRIMARY KEY,     Title VARCHAR(45) UNIQUE NOT NULL,     DID INTEGER NOT NULL,     GID INTEGER NOT NULL,     FOREIGN KEY (DID) REFERENCES Directors(DID),     FOREIGN KEY (GID) REFERENCES Genres(DID) );  CREATE TABLE Directors(     DID INTEGER PRIMARY KEY,     First_Name VARCHAR(20) NOT NULL,     Last_Name VARCHAR(20) NOT NULL ); 

I want to write a query that will allow me to select all of Director information for every director that has made atleast one movie in the same genre(s) as another director. For example if Stanley Kubrick has made films in genres ‘Sci-Fi’, ‘Thriller’, and ‘Crime’, I want to select all the directors who have made at least 1 sci-fi AND 1 thriller AND 1 crime film.

I’ve tried the query seen below but this will give me directors who have made atleast 1 sci-fi OR 1 thriller OR 1 crime film.

SELECT DISTINCT D.DID, D.First_Name, D.Last_Name FROM Directors D LEFT JOIN Films F ON F.DID = D.DID LEFT JOIN Genres G ON G.GID = B.GID WHERE G.Genre IN (   SELECT DISTINCT G1.Genre   FROM Generes G1   LEFT JOIN Films F1   ON F1.GID = G1.GID   LEFT JOIN Directors D1   ON D1.DID = D1.DID   WHERE D1.First_Name = 'Stanley'   AND D1.Last_Name = 'Kubrick' );  Additionally, I am not able to check before hand which Genres the director in question has been involved with. The query should work with the only given information being the Directors First and Last name. 

How to utilise multiple values from a single shortcode attribute?

i.e [test somevalues=1,2,3,4,5]

First time poster and alsoa php newb, I’m trying to add a feature to a plugin but I seem to keep triggering a debug notice from wordpress.

$  somearray = shortcode_atts([                'somevalues' => null,     ], $  atts); $  somearray['somevalues'] = array_map( 'trim', str_getcsv( $  somearray['somevalues'], ',' ) );  

The above does work, but it results in a debug Notice: wpdb::prepare was called incorrectly.

Is there a way to achieve the same without triggering the notice? It doesn’t have to be csv, I just need it to return a string of somevalues, rather than returning an int. I guess what I want it to act like is as if it was written like [test somevalues=one,two,three,four,five]

Any suggestions on how to get around this? Many thanks

WordPress, fetching users with an exact match in a string of comma separated values in user_meta

I have data saved in user_meta -> areapref field. It is basically a string of comma delimited post IDs (e.g. 2, 37, 200, 300 etc.)

I need to check if the user has a particular ID in this field. If so… do stuff.

I can check if an ID is in areapref using LIKE. But I believe this has an issue. If one location is ID=10, and another is ID=100 – both will match with 1, or 10? Or am I misunderstanding LIKE. I fear CONTAIN would have the same issue.

I need to know how to get a list of users who have an ‘exact match’ within the areapref string? So search for ID 2, will return only 2, NOT 22, or 20, 200 etc. I’ve spent hours going through stackexchange/overflow/wp codex and cannot find an answer… though I am dense!

Here’s enough code I hope to show what I’m currently doing… any ideas greatly appreciated.

function ds_notify_new_event( $  post_ID ){ $  url = get_permalink( $  post_ID ); $  event = get_the_title( $  post_ID ); $  localeID = get_post_field( 'location_id', $  post_ID); global $  wpdb; $  locale = $  wpdb->get_var( $  wpdb->prepare( " SELECT post_id FROM {$  wpdb->prefix}em_locations WHERE ID = %d ", $  localeID ) );  $  args = array(         'role'    => 'subscriber',         'meta_query' => array(                 array( 'key' => 'areapref', 'value' => $  locale, 'compare' => 'LIKE' ),                 // just checks usr is ok with email                 array( 'key' => 'notify', 'value' => 'yes', 'compare' => '=' )              ),         'fields' => array( 'display_name', 'user_email' )     ); // retrieve users to notify about the new post $  users = get_users( $  args ); 

// do stuff with users

Entering Exact Values into a Table Using SQL

I am needing to insert particular values into a table I am working on with SQL (see function below):

INSERT INTO Table Name (…) VALUES(…);

The values that are going to be stored inside this table are values that involve another programming language that have a lot of weird symbols involving a single backslash, *, dollar signs, etc.

How can I insert values precisely as they are inside this table?

For instance, when I type ” it needs to return inside the table instead of just 2. I am unable to manually enter the two backslashes to get . This is because I am receiving an input from another user via HTML and sending it to my table with PHP. The issue I am having is that there are these special characters with SQL which are being entered in not the way I intend them to as values.

Is there a way to type these values in exactly with a certain string function that is called beforehand? Other ways of doing this would be greatly appreciated!

Plotting a small gaussian | Small values and dealing with Machine Precision

I’ve defined the following:

k := 1.38*10^-16 kev := 6.242*10^8 q := 4.8*10^-10 g := 1.66*10^-24 h := 6.63*10^-27 

and

b = ((2^(3/2)) (\[Pi]^2)*1*6*(q^2)*(((1*g*12*g)/(1*g + 12*g))^(   1/2)) )/h  T6 := 20 T := T6*10^6 e0 := ((b k T6 *10^6)/2)^(2/3)  \[CapitalDelta] := 4/\[Sqrt]3 (e0 k T6 *10^6)^(1/2)  \[CapitalDelta]kev = \[CapitalDelta]*kev e0kev = e0*kev bkev = b*kev^(1/2) 

Then, I want to plot these functions:

fexp1[x_] = E^(-bkev *(x*kev)^(-1/2)) fexp2[x_] = E^(-x/(k*T)) fexp3[x_] = fexp1[x]*fexp2[x] 

and check that this Taylor expansion works:

fgauss[x_] =   Exp[(-3 (bkev^2/(4 k T*kev ))^(1/3))]*   Exp[(-((x*kev - e0kev)^2/(\[CapitalDelta]kev/2)^2))] 

which should, e.g., as expected:

Figure 10.1

This plot came from "Stellar Astrophysics notes" of Edward Brown (also it is a known approximation).

I used this line of command to Plot:

Plot[{fexp1[x],fexp2[x],fexp3[x],fgauss[x]}, {x, 0, 50},   PlotStyle -> {{Blue, Dashed}, {Dashed, Green}, {Thick, Red}, {Thick,      Black, Dashed}}, PlotRange -> Automatic, PlotTheme -> "Detailed",   GridLines -> {{{-1, Blue}, 0, 1}, {-1, 0, 1}},   AxesLabel -> {Automatic}, Frame -> True,   FrameLabel -> {Style["EnergĂ­a E", FontSize -> 25, Black],     Style["f(E)", FontSize -> 25, Black]}, ImageSize -> Large,   PlotLegends ->    Placed[LineLegend[{"","","",""}, Background -> Directive[White, Opacity[.9]],      LabelStyle -> {15}, LegendLayout -> {"Column", 1}], {0.35, 0.75}]] 

but it seems that Mathematica doesn’t like huge negative exponentials. I know I can compute this using Python but it’s a surprise to think that Mathematica can’t deal with the problem somehow. Could you help me?

Postgres Combine Summed Values from 2 Queries / Tables into Single Row

Say I had the following 2 queries, summing values from separate tables.

I would like the sum of recorded time

SELECT      SUM(minutes) as recorded_minutes,     SUM(hours) as recorded_hours FROM recorded_time WHERE     project_id = 1 

To be combined with the sum of budgeted time in a single row

SELECT      SUM(minutes) as budgeted_minutes,     SUM(hours) as budgeted_hours FROM budgeted_time WHERE     project_id = 1 

Is it possible to do this in a single query?