sql: non-deterministic planning error, invalid types in scalar expression #35418
Assignees
Labels
A-sql-optimizer
SQL logical planning and optimizations.
C-bug
Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.
S-3-ux-surprise
Issue leaves users wondering whether CRDB is behaving properly. Likely to hurt reputation/adoption.
Comments
awoods187
added
the
S-3-ux-surprise
|
Here's another repro |
awoods187
added
the
C-investigation
|
This is a planning error and should thus be deterministic. I am surprised (and a bit unhappy) it is not. However I suspect this is what is happening: depending on the current table statistics, the optimizer may choose to either eliminate or transform a part of the query, and the error is only raised after that point depending on which plan is selected. Would be curious what the outcome is with |
knz
added
A-sql-optimizer
knz
changed the title
|
|
Are you saying you always get results (i.e. no error whatsoever) with |
|
I just ran it 15 times in a row manually with the opt turned on and it worked every time in 4 seconds. I ran it 10 times in a row manually with the opt turned off and it worked every time in 30 seconds. So far, I've hit it 4 times when running |
|
For those following along i repro'd this with the opt=off with |
|
I ran into this problem about three weeks ago when working on #34891. I think I was constantly hitting the error on a single node on my laptop. |
|
Turns out that the logging is misleading. The error is with Q11, not Q10. Q11 will always fail because CRDB doesn't allow a float to be multiplied by an int. The fix is simple: explicitly cast the int to a float. I'll open a PR that makes the change to the TPCH workload. |
|
awesome find! Glad this can be closed |
andy-kimball
added a commit
to andy-kimball/cockroach
that referenced
this issue
Mar 8, 2019
Fix a couple problems in the TPCH workload: 1. Use r_name = 'EUROPE' in Q2 to match the TPCH spec sample values 2. Add ::float cast in Q11 because CRDB does not implicitly cast when multiplying float and int. Fixes cockroachdb#35418 Release note: None
craig bot
pushed a commit
that referenced
this issue
Mar 9, 2019
35533: sql: Fix TPCH workload r=andy-kimball a=andy-kimball Fix a couple problems in the TPCH workload: 1. Use r_name = 'EUROPE' in Q2 to match the TPCH spec sample values 2. Add ::float cast in Q11 because CRDB does not implicitly cast when multiplying float and int. Fixes #35418 Release note: None Co-authored-by: Andrew Kimball <[email protected]>
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Describe the problem
When running TPC-H I received the following error:
But when I reproduce on a new cluster with the exact same setup:
To Reproduce
export CLUSTER=andy-hints
roachprod create $CLUSTER -n 2 --gce-machine-type=n1-standard-16
roachprod run $CLUSTER -- 'sudo umount /mnt/data1; sudo mount -o discard,defaults,nobarrier /dev/sdb /mnt/data1/; mount | grep /mnt/data1'
roachprod stage $CLUSTER:1 cockroach
roachprod stage $CLUSTER:2 workload
roachprod start $CLUSTER:1
roachprod sql $CLUSTER:1
CREATE DATABASE tpch;
RESTORE tpch.* FROM 'gs://cockroach-fixtures/workload/tpch/scalefactor=1/backup' WITH into_db = 'tpch';
roachprod adminurl --open $CLUSTER:1
roachprod run $CLUSTER:2 "./workload run tpch --queries 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22 --scale-factor=1 {pgurl:1}"
Expected behavior
I would expect the query to always work or never work.
Environment:
v19.1.0-beta.20190304-135-gd8f7e85
The text was updated successfully, but these errors were encountered: