Skip to content

CAST in SQLite yields incorrect type #3688

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Open
NOOBDY opened this issue Nov 6, 2024 · 0 comments · May be fixed by #3860
Open

CAST in SQLite yields incorrect type #3688

NOOBDY opened this issue Nov 6, 2024 · 0 comments · May be fixed by #3860
Labels
📚 sqlite bug Something isn't working 💻 linux 🔧 golang

Comments

@NOOBDY
Copy link

NOOBDY commented Nov 6, 2024

Version

1.27.0

What happened?

According to SQlite docs

If the value of expr is NULL, then the result of the CAST expression is also NULL.

But using CAST in sqlc doesn't generate nullable types

Expected:

type GetGradeInfoRow struct {
	StudentID string            `json:"student_id"`
	Grade     types.NullFloat64 `json:"grade"`
}

Actual:

type GetGradeInfoRow struct {
	StudentID string  `json:"student_id"`
	Grade     float64 `json:"grade"`
}

Relevant log output

No response

Database schema

CREATE TABLE student (
    -- https://blog.ploeh.dk/2024/06/03/youll-regret-using-natural-keys/
    id INTEGER PRIMARY KEY,
    student_id TEXT NOT NULL,
    semester SEMESTER_TEXT NOT NULL,

    UNIQUE(student_id, semester)
);

CREATE TABLE homework (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    semester SEMESTER_TEXT NOT NULL,
    begin_at DATETIME_TEXT NOT NULL,
    end_at DATETIME_TEXT NOT NULL,

    UNIQUE(name, semester)
);

CREATE TABLE grade (
    id INTEGER PRIMARY KEY,
    student_id INTEGER NOT NULL,
    homework_id INTEGER NOT NULL,
    submitted_at DATETIME_TEXT NOT NULL,
    grade REAL NOT NULL,

    FOREIGN KEY (student_id) REFERENCES student (id) ON DELETE CASCADE,
    FOREIGN KEY (homework_id) REFERENCES homework (id) ON DELETE CASCADE
);

SQL queries

SELECT
    student.student_id,
    CAST(max(grade.grade) AS REAL) AS grade
FROM homework
CROSS JOIN student
LEFT JOIN grade ON
    homework.id = grade.homework_id AND
    student.id = grade.student_id
WHERE
    homework.semester = ? AND
    homework.name = ?
GROUP BY student.id
ORDER BY student.student_id ASC;

Configuration

version: "2"
sql:
  - engine: "sqlite"
    queries: "db/queries"
    schema: "db/migrations"
    database:
      uri: "sqlite3://${DB}"
    gen:
      go:
        package: "db"
        out: "service/db"
        emit_json_tags: true
        emit_empty_slices: true
        overrides:
          - db_type: "REAL"
            go_type:
              import: "github.com/is1ab/Arvosana/types"
              type: "NullFloat64"
            nullable: true

          - db_type: "DATETIME_TEXT"
            go_type:
              import: "github.com/is1ab/Arvosana/types"
              type: "Datetime"
          - db_type: "DATETIME_TEXT"
            go_type:
              import: "github.com/is1ab/Arvosana/types"
              type: "NullDatetime"
            nullable: true

          - db_type: "SEMESTER_TEXT"
            go_type:
              import: "github.com/is1ab/Arvosana/types"
              type: "Semester"
          - db_type: "SEMESTER_TEXT"
            go_type:
              import: "github.com/is1ab/Arvosana/types"
              type: "NullSemester"
            nullable: true

Playground URL

No response

What operating system are you using?

Linux

What database engines are you using?

SQLite

What type of code are you generating?

Go
@NOOBDY NOOBDY added the bug Something isn't working label Nov 6, 2024
@giuliano-macedo giuliano-macedo linked a pull request Feb 25, 2025 that will close this issue
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
📚 sqlite bug Something isn't working 💻 linux 🔧 golang
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

fix(sqlite): Removed ToLower in typecast giuliano-macedo/sqlc
1 participant
@NOOBDY