distinct – MariaDB JSON_ARRAYAGG gives wrong result

I have 2 problems in MariaDB 15.1 when using JSON_ARRAYAGG


1. The brackets () are omitted
2. wrong result, values are duplicates or omitted

My database is the following:

user:

+----+------+
| id | name |
+----+------+
|  1 | Jhon |
|  2 | Bob  |
+----+------+

car:

+----+---------+-------------+
| id | user_id | model       |
+----+---------+-------------+
|  1 |       1 | Tesla       |
|  2 |       1 | Ferrari     |
|  3 |       2 | Lamborghini |
+----+---------+-------------+

phone:

+----+---------+----------+--------+
| id | user_id | company  | number |
+----+---------+----------+--------+
|  1 |       1 | Verzion  |      1 |
|  2 |       1 | AT&T     |      2 |
|  3 |       1 | T-Mobile |      3 |
|  4 |       2 | Sprint   |      4 |
|  5 |       1 | Sprint   |      2 |
+----+---------+----------+--------+

1. The brackets () are omitted

For example this query that gets users with their list of cars:

SELECT
  user.id   AS id,
  user.name AS name,
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'id',    car.id,
      'model', car.model
    )
  ) AS cars
FROM user
INNER JOIN car ON user.id = car.user_id
GROUP BY user.id;

Result: brackets () were omitted in cars (JSON_ARRAYAGG has the behavior similar to GROUP_CONCAT)

+----+------+-----------------------------------------------------------+
| id | name | cars                                                      |
+----+------+-----------------------------------------------------------+
|  1 | Jhon | {"id": 1, "model": "Tesla"},{"id": 2, "model": "Ferrari"} |
|  2 | Bob  | {"id": 3, "model": "Lamborghini"}                         |
+----+------+-----------------------------------------------------------+

However when adding the filter WHERE user.id = 1, the brackets () are not omitted:

+----+------+-------------------------------------------------------------+
| id | name | cars                                                        |
+----+------+-------------------------------------------------------------+
|  1 | Jhon | ({"id": 1, "model": "Tesla"},{"id": 2, "model": "Ferrari"}) |
+----+------+-------------------------------------------------------------+

2. Incorrect wrong result, values are duplicates or omitted

This error is strange as the following conditions must be met:

  • Consult more than 2 tables
  • The DISTINCT option must be used
  • A user has at least 2 cars and at least 3 phones.

Duplicate values

for example, this query that gets users with their car list and their phone list:

SELECT
  user.id   AS id,
  user.name AS name,
  JSON_ARRAYAGG( DISTINCT
    JSON_OBJECT(
      'id',    car.id,
      'model', car.model
    )
  ) AS cars,
  JSON_ARRAYAGG( DISTINCT
    JSON_OBJECT(
        'id',      phone.id,
        'company', phone.company,
        'number',  phone.number
    )
  ) AS phones
FROM  user
INNER JOIN car   ON user.id =   car.user_id
INNER JOIN phone ON user.id = phone.user_id
GROUP BY user.id;

I will leave the output in json format and I will only leave the elements that interest.

Result: brackets () were omitted and duplicate Verizon

{
  "id": 1,
  "name": "Jhon",
  "phones": // ( Opening bracket expected
    {
      "id": 5,
      "company": "Sprint",
      "number": 2
    },
    {
      "id": 1,
      "company": "Verzion",
      "number": 1
    },
    {
      "id": 1,
      "company": "Verzion",
      "number": 1
    }, // Duplicate object with the DISTINCT option
    {
      "id": 2,
      "company": "AT&T",
      "number": 2
    },
    {
      "id": 3,
      "company": "T-Mobile",
      "number": 3
    }
  // ) Closing bracket expected
}

Omitted values

This error occurs when omit phone.id is omitted in the query

SELECT
  user.id   AS id,
  user.name AS name,
  JSON_ARRAYAGG( DISTINCT
    JSON_OBJECT(
      'id',    car.id,
      'model', car.model
    )
  ) AS cars,
  JSON_ARRAYAGG( DISTINCT
    JSON_OBJECT(
        --'id',      phone.id,
        'company', phone.company,
        'number',  phone.number
    )
  ) AS phones
FROM  user
INNER JOIN car   ON user.id =   car.user_id
INNER JOIN phone ON user.id = phone.user_id
GROUP BY user.id;

Result: brackets () were omitted and Sprint was omitted.

Apparently this happens because it makes an OR type between the columns of the JSON_OBJECT, since the company exists in a different row and number in a other different row

{
  "id": 1,
  "name": "Jhon",
  "phones": // ( Opening bracket expected
  //{ 
  //  "company": "Sprint",
  //  "number": 2
  //}, `Sprint` was omitted
    {
      "company": "Verzion",
      "number": 1
    },
    {
      "company": "AT&T",
      "number": 2
    },
    {
      "company": "T-Mobile",
      "number": 3
    }
  // ) Closing bracket expected
}

GROUP_CONCAT instance of JSON_ARRAYAGG solves the problem of duplicate or omitted objects

However, by adding the filter WHERE user.id = 1, the brackets () are not omitted and also the problem of duplicate or omitted objects is also solved:

{
  "id": 1,
  "name": "Jhon",
  "phones": (
    {
      "id": 1,
      "company": "Verzion",
      "number": 1
    },
    {
      "id": 2,
      "company": "AT&T",
      "number": 2
    },
    {
      "id": 3,
      "company": "T-Mobile",
      "number": 3
    },
    {
      "id": 5,
      "company": "Sprint",
      "number": 2
    }
  )
}

What am I doing wrong?


So far my solution is this, but I would like to use JSON_ARRAYAGG since the query is cleaner

SELECT
  CONCAT(
    '(',
    GROUP_CONCAT( DISTINCT   
      JSON_OBJECT(
        ...
      )
    ),
    ')'
  )

cryptography – One-way cryptographic algorithm with distinct output and verifiable property

I’m working on a API which is serving content based on user requests. What I’d like to achieve is as follows: Having a constant key stored both on the server and the client machine (as a means of identifying a specific user) I’d like to be able to generate a hash with a one-way cryptographic algorithm on the client side so that the generated hash is different each time but I would still be able to identify the user tied to that hash on the server-side (through the constant key stored on the servers database) using ‘some function’. The generated hash on the client-side would be used to make a HTTP request to the servers REST API which would verify if that hash is tied to any of the keys from the database and if so, serve the content to its users.

ex: https://someserver.com/content?hash_here – would send content back to the browser only if the hash (hash_here) matches with one of the users hash in the database (after the cryptographic ‘magic’ I’m talking about is done)

My goal is to omit authentication, as the client would be a raspberry pi device and authentication should work without setting up any credentials other than the hash key stored both on the client and server side.

My question, as somebody having close to no knowledge on the subject: Is there any cryptographic algorithm that does that, and if so, which one?

python 3.x – Find next smallest number that’s representable as a sum of distinct powers of 3

I’m solving this challenge:

Given a positive integer number, you have to find the smallest good number greater than or equal to the given number.
The positive integer is called good if it can be represented as a sum of distinct powers of 3 (i.e. no duplicates of powers of 3 are allowed).

The code passes all the test cases in the editor, but when submitted to online judge it is failing because of time limit exceeded.
Give time limit 1 second, my code execution is taking 1.01 second


t = int(input())
while t > 0:
    N = int(input())
    i=N
    while i > 0:
        if i % 3 == 2:
            N=N+1
            i=N
            continue
        i //= 3
    print(N)
    t = t - 1

Test Case

Input:

5
3
46
65
89
113

Output:

3
81
81
90
117

join – Postgresql/rails – Joining on distinct values, or only creating duplicate rows for distinct values

I have a number of Practitioners that have Certifications in different States, and they set up their hours in an Availabilities table. They can have multiple certifications in the same state, but I’m interested in my availability per state. When I join through certifications, I unintentionally create multiple records per practitioner, even within the same state. Note that this is a virtual service, so they really are available to multiple states at once.

In rails I use a query along these lines:

Practitioner.
joins(:availabilities, (certifications: :state)).
group("states.name").
select("states.name", "SUM(availabilities.end_date - availabilities.start_date)")

I’ve experimented with trying to join on a distinct select, but it doesn’t seem to work as intended:

Practitioner.
joins(certifications: :state).
select("DISTINCT practitioner.id states.name").
joins(:availabilities).
group("states.name").
select("states.name", "SUM(availabilities.end_date - availabilities.start_date)")

This appears to basically ignore the step of selecting the distinct practitioner.id and states.name and returns the same result as before. How can I ensure that a user’s availabilities are only duplicated for multiple states, not multiple certifications?

How to set distinct boot and lockscreen passwords in Android 10 (full disk encryption)

How can I set a distinct password for:

  1. The Full Disk Encryption password (used to boot the device)
  2. The Lock Screen password (used to unlock the device)

For security reasons, I use a 25-50 character password to protect my devices when off and a shorter password for the lock screen.

This was previously possible using apps such as:

  1. EncPassChanger or
  2. Cryptfs Password

I just upgraded my phone, and neither of these apps appear to work in Android 10.

How can I set a distinct password for (a) the boot password and (b) the lockscreen password in Android 10?

postgresql – How to find the three closest distinct units for each of my sites using SQL

I am trying to locate the three closest distinct units for each of my sites. I have my measured distances as total_mile, the starting locations are from_site_, and the end locations are to_unitcod. However, there are sometimes multiple end locations associated with the same to_unitcod that fall within the top three shortest distances, where I need three different units for each site.

The code below is the closest I have gotten so far. What could I add to this that will make it select three different/distinct to_unitcod for each from_site_? Right now it is pulling the three shortest measured distances between each site and unit, which is good, but I would like it to pull the three shortest distances from three different units (to_unitcod).

    SELECT *
    FROM
        (
        SELECT nearrank, total_mile, from_site_, to_poiname, to_unitcod, to_unitnam,
        ROW_NUMBER() OVER (PARTITION BY from_site_ ORDER BY total_mile ASC) rn
        FROM public.mytable
        ) X
    WHERE rn<=3
    ORDER BY from_site_;

Below is a screen shot of the resulting table from the above code if that helps illustrate the problem at all. So for example, the records for the Yukon Weapons Range have one FAIR and two DENA unit codes – where that second DENA needs to be the next closest to_unitcod that isn’t DENA or FAIR.

enter image description here

sql server – How to SELECT DISTINCT records with INNER JOIN?

I have two tables Finance Commissions May2021 (FMAY) and Consolidated Client Codes (CCC).

Here is the code for Insert Into: For table FMAY

 USE (FinanceCommissions26May2021)
GO

/****** Object:  Table (dbo).(Finance_Commissions_May26_2021$)    Script Date: 22/06/2021 11:06:26 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE (dbo).(Finance_Commissions_May26_2021$)(
    (Lender Reference) (nvarchar)(255) NULL,
    (F2) (nvarchar)(255) NULL,
    (F3) (nvarchar)(255) NULL,
    (Client) (nvarchar)(255) NULL,
    (F5) (nvarchar)(255) NULL,
    (Supplier) (nvarchar)(255) NULL,
    (Product) (nvarchar)(255) NULL,
    (Settlement / Inception Date) (nvarchar)(255) NULL,
    (Base Value) (nvarchar)(255) NULL,
    (Original Loan Amount) (money) NULL,
    (Adviser Split (%)*) (money) NULL,
    (F12) (nvarchar)(255) NULL,
    (Supplier_Amount_(incl GST)) (money) NULL,
    (Supplier_Amount_(GST)) (money) NULL,
    (Adviser Share (ex GST)) (money) NULL,
    (F16) (nvarchar)(255) NULL,
    (F17) (nvarchar)(255) NULL,
    (Adviser Share (GST)) (money) NULL,
    (Adviser Share (incl GST)) (money) NULL,
    (LastNameOnly) (nvarchar)(255) NULL,
    (FirstNameOnly) (nvarchar)(255) NULL,
    (FirstFourLastName) (nvarchar)(255) NULL,
    (ClientCode) (nvarchar)(255) NULL,
    (IndexMatch) (nvarchar)(255) NULL,
    (F25) (nvarchar)(255) NULL,
    (F26) (float) NULL
) ON (PRIMARY)
GO

FMAY

Table FMAY contains the following columns:

  • commission amounts
  • a Lender Reference Number
  • client names (LastName, FirstName).

This report comes in monthly and contains monthly commissions for some of the clients (usually contains 350-400 records).

CREATE TABLE (Finance_Commissions_May26_2021$) (
    CommissionAmount,
    LenderReferenceNumber,
    ClientLastName,
    ClientFirstName
)

& here is the code for table CCC:

USE (FinanceCommissions26May2021)
GO

/****** Object:  Table (dbo).(Consolidated_ClientCodes$)    Script Date: 22/06/2021 11:07:27 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE (dbo).(Consolidated_ClientCodes$)(
    (FirstFourLastName) (nvarchar)(255) NULL,
    (ClientCode) (nvarchar)(255) NULL,
    (Client) (nvarchar)(255) NULL
) ON (PRIMARY)
GO

CCC

Table CCC contains the following columns:

  • client names
    • (different format though – i.e. FirstName,LastName)
  • a client code (which is a unique identifier (Primary key) assigned to each client.
    • Client code takes the form of SMIT123.
    • e.g. for “John, Smith” it’s SMIT123
    • for “Mark, Smith” it’s SMIT345, and so on.
    • i.e. First Four letters of last name followed by a unique 3 digit code.

This table contains Client names and unique Client codes for ALL clients (more than 7000 records)

CREATE TABLE ('20210617145928-Exception - No C$') (
    ClientCode PRIMARY KEY, -- 'wwwwddd'
    FirstName,
    LastName
)

The problem

My aim is : To assign this unique Client code to each client within the FMAY table.

Since there was NO common column between tables FMAY and CCC, I created a new column (FirstFourLastName) which parses out FirstFour letters of Last Name (like “SMIT“) from both Tables.

Then using this new column (FirstFourLastName), I wrote code for Inner Join in SQL with an aim to assign the Unique “Client Code” to each record in table FMAY.

I am able to get the correct client code assigned but not able to ONLY select distinct records.

My code is:

select
    FMAY.(Lender Reference),
    CCC.Client,
    CCC.ClientCode,
    FMAY.FirstFourLastName,
    FMAY.(Adviser Share (ex GST)),
    FMAY.(Adviser Share (GST)),
    FMAY.(Adviser Share (incl GST)),
    FMAY.Product 
from
    (FinanceCommissions26May2021).(dbo).(Finance_Commissions_May26_2021$) FMAY
    inner join (FinanceCommissions26May2021).(dbo).('20210617145928-Exception - No C$') CCC on
        FMAY.FirstFourLastName = CCC.FirstFourLastName

The above code is giving me 6300 records, with the correct client code attached to each record though! However, I only need to assign Client Code to the 350-400 records in the FMAY table.

How can I select distinct records for my problem please?

Ffmpeg concat command creates mp4 with distinct avg_frame_rate video

I’m trying to concatenate two mp4 video files. Both of them only contain a single constant frame rate video stream.

# f1.mp4
r_frame_rate=23/1
avg_frame_rate=23/1
time_base=1/11776
duration=14.739130
nb_frames=339

# f2.mp4
r_frame_rate=23/1
avg_frame_rate=23/1
time_base=1/11776
duration=9.826087
nb_frames=226

After de concatenating process, when using the concat command, I get an mp4 video with the following output.

ffmpeg -f concat -safe 0 -i sources.txt -c:v copy output.mp4
r_frame_rate=23/1
avg_frame_rate=7183360/312341
time_base=1/11776
duration=26.523522
nb_frames=610

The video seems to be playable properly (VLC, Quicktime) but If I wonder why I’m not having a 23/1 average frame rate.

google cloud platform – Kubernetes managing many distinct UDP servers on GKE

I’m trying to set up a system that can automatically spin up and down video game servers as docker images. In this case, factoriotools/factorio-docker. Each game is a different, distinct single-pod deployment of that container, and therefore (in the simplified case) needs its own IP address that can listen on a specific UDP port. Load balancers are redundant and irrelevant, and Cloud NAT doesn’t appear to allow ingress traffic easily.

There’s a couple ways I know of to get this to work, both with pretty major compromises:

  • I can use a NodePort service, and lose control over which port the client needs to connect to. That’s an issue because the server registers itself with a server listing.
  • I can use host networking. If my information is correct, that requires privileged containers, which is Definitely Not Good.
  • I could maybe use a UDP load balancer, but even if that exists and works, it’s expensive.

There are probably ways to work around the limitations of either approach (for the second, keep the hosts short-lived and keep the firewall strict, and it should be mostly OK?), but I can’t help but think there’s a better option that I can’t find described in the official kubernetes docs. Does traefik have some trick I don’t know about? Is there some way to get a variant of MetalLB that can dynamically allocate public IP addresses as I need them?

How do I get each server-container to listen on a different public IP address with a specific UDP port, without making security impossible in the process?

abstract algebra – Why distinct irreducibles never have zeros in commom?

I am reading Dummit and Foote’s Abstract Algebra, and in Chap13, Prop 9, the book says distinct irreducibles never have zeros in common. I think one irreducible can be got from another irrducible multiply a unit, such as in R[x], $x^2+1$ and $2x^2+2$ are both irreducibles but have common zeros. Where do I think wrong?